MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data - Page 6

June 23, 2004

Populate the Table with Query Analyzer and Create a Select Query

Let's populate the new SubscriberDetails table by taking the next steps.

Query Analyzer opens, selected to the new RS_Subscription database, as shown in Illustration 19.

NOTE: If necessary, select the RS_Subscription database in the selector, as shown circled in Illustration 19 above.

-- RS006:  INSERT Query to Populate SubscriberDetails Table

INSERT INTO 
   dbo.SubscriberDetails (Participant, File_Share, Identification_No, Media_Format,       
      Parameter)
VALUES 
   ('Damascus, Billy','\\MOTHER1\D$\RS_SubscriberShares\DamascusB','10059','EXCEL', 
      'Non-Consumable')
GO

INSERT INTO 
dbo.SubscriberDetails (Participant, File_Share, Identification_No, Media_Format, 
   Parameter)
VALUES 
   ('Adams, Victoria','\\MOTHER1\D$\RS_SubscriberShares\AdamsV','32981','PDF', 'Food')
GO

INSERT INTO 
   dbo.SubscriberDetails(Participant, File_Share, Identification_No, Media_Format, 
      Parameter)
VALUES 
   ('Pearson, William E. III','\\MOTHER1\D$\RS_SubscriberShares\PearsonW3','04089','HTML4.0', 
      'Non-Consumable')
GO

INSERT INTO 
   dbo.SubscriberDetails(Participant, File_Share, Identification_No, Media_Format, 
      Parameter)
VALUES 
   ('Dhue, Laurie','\\MOTHER1\D$\RS_SubscriberShares\DhueL','14761','IMAGE', 
      'Non-Consumable')
GO

Our load script appears in the Query pane as depicted in Illustration 20.

The script executes, and we receive a "(1 row(s) affected)" message for each of the four lines of the script in the Results pane.

We can easily verify the fact that the table is populated by going to Enterprise Manager, but since we will need to create a query to supply necessary data to the Data-Driven Subscription we will define later, let's use this query to ascertain the fact that all data is in place in the SubscriberDetails table.

SELECT * 
FROM
dbo.SubscriberDetails

Our query appears in the Query pane as depicted in Illustration 23.

The script executes, and the Results pane is populated (Grid View), as shown in Illustration 24.

We thus obtain confirmation from the results of the SELECT statement that our table has been populated as we expected. We will save the query, so as to be able to use it in our Data-Driven Subscription setup later.