Lesson 1: Creating a Sample Subscriber Database

Before you can define a data-driven subscription, you must have a data source that provides subscription data. In this step, you will create a small database to store the subscription data used in this tutorial. Later, when the subscription is processed, the report server retrieves this data and uses it to customize report output, delivery options, and report presentation format.

You can use a SQL Server 2000 database or a SQL Server 2005 database to store the data. This lesson assumes you are using Management Studio to create a SQL Server 2005 database.

To create a sample Subscriber database

1. Start Management Studio, and open a connection to a Database Engine.

2. Right-click on Databases, select New Database.

3. In the New Database dialog box, in Database Name, type Subscribers. Click OK.

4. Click the New Query button on the toolbar.

5. Copy the following Transact-SQL statements into the empty query:

   Use Subscribers
   CREATE TABLE [dbo].[UserInfo] (
       [SubscriptionID] [int] NOT NULL PRIMARY KEY ,
       [EmployeeID] [int] ,
       [LastName] [nvarchar] (50) NOT NULL ,
       [FileType] [bit],
       [Format] [nvarchar] (20) NOT NULL ,
   ) ON [PRIMARY]
   GO
   
   INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('1', '289', 'Valdez', '1', 'IMAGE')
   INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('2', '284', 'Alberts', '1', 'MHTML')
   INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('3', '275', 'Blythe', '1', 'PDF')
   GO
   

6. Click ! Execute.

7. Use a SELECT statement to verify that you have three rows of data. For example: select * from UserInfo

Next Steps

You have successfully created the subscription data that will drive report distribution and vary the report output for each subscriber. Next, you will modify the data source properties of the report you will distribute to subscribers. Modifying the data source properties is done to prepare the report for data-driven subscription delivery. See Lesson 2: Modifying the Report Data Source Properties.

See Also

Tasks

Tutorial: Creating a Data-Driven Subscription

Other Resources

How to: Create a Database (SQL Server Management Studio)
Adding Rows by Using INSERT and SELECT

Help and Information

Getting SQL Server 2005 Assistance