In SQL Server 2008 R2, Step #3 in creating data driven subscriptions involves creating a SQL query, "...that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient." My goal is to retreive this query via SSMS directly via the ReportServer database, if possible. Suppose then that the Step #3 SQL statement is: select 'joesmith@gmail.com' as EmailAddress, 'marketing' as DeptName union select 'sallyjones@gmail.com', 'finance' as DeptName. My question is: where is that SQL text string stored in the reporting services database, i.e. what table and column within the ReportServer database? The background is, you guessed it, we had a hardware failure and I've got the RDL's but not the data driven subscription details/queries. I can get to the old ReportServer database, but it is going to be tough to recreate the entire SS2005 environment. Thanks. --Bob Harford

Hi Bob, When creating the Data-Driven subscriptions, we need to specify a connection string to the data source that contains subscriber data. Then, we can write the query or command that gets subscriber data. Once the data source is specified, it indicates that the subscription delivery table can be found in the "Table" node of the corresponding database. For example, we can create a individual database for the subscriber data, or add the subscriber data to a existing database such as "AdventureWorksDW2008R2". Additionally, in the report sever database, there is a table named "Subscriptions" which contains all the subscriptions information on the report server. While this table doesn't contain the subscriber data for each subscription. Reference: Data-Driven subscriptions If you have any questions, please feel free to let me know. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Thanks Mike, I did a quick look for the "Table" node of the corresponding database but didn't find it. I'm actually not sure what I'm looking for, is a "Table" node a table or a tag in an XML data column somewhere? In any event I will look for the table node more in earnest in the next couple of days. --Bob

Hi Bob, Sorry for the delay. For a valid Data-Driven subscription, we can find out the corresponding subscriber database via the data source (a shared data source or a specified data source) property settings of the subscription, and find out the table from the query statements. For example, the subscriber data source specified for the subscription is using the following connection: data source=localhost; initial catalog=Subscribers So, we know the target database is Subscribers. Further, the query to retrieve subscriber data is as follows: Select * from UserInfo So, the target table is UserInfo. That is to say, for a valid subscription, the can definitely find out the target tables from the query. Hope this helps. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Mike, Here is my connection information: Data Source=LOCALHOST;Initial Catalog=ReportServer Running "Select * from UserInfo" against the ReportServer database yields "invalid object name" I also looked for the table "UserInfo" in all databases via this Stored Procedure (http://blog.sqlauthority.com/2008/04/29/sql-server-find-table-in-every-database-of-sql-server/), excluding only (I believe) SharePoint related databases -- the subscription is against SSRS 2008 R2 running in Native Mode). FYI, the value for step #3 in the data driven subscription process is: select '1 to go' as lFlag union select '2 to fail' as lFlag Granted this is a hello world type of an example, but my goal again is to retreive that SQL string out of a SQL Server database, presuming the SQL statement for step #3 in a data driven subscription is in fact stored in a database, and not for example in the RDL. FYI, I have connected to SQL Server Reporting Services via SSMS, and I don't see anything in there other than roles. --Bob

Hi Bob, Thanks for your posting. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names). As per your query statements, it doesnt select data from any table at all. In addition, the UserInfo table in my last post is just an example. Actually, the report server database doesnt contain a UserInfo natively. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Mike, I'm certain that you're answering a different question then the one I am trying to ask. I am not asking how to create a data driven subscription. I am not asking how to formulate a query for step #3 (Create a data-driven subscription...). I also don't understand your statement/critique on properly formulated select statements. The following query does return values in SSMS: select '1 to go' as lFlag union select '2 to fail' as lFlag. I also use it successfully in a data driven subscription. Yes, of course it is a non-sensical query, but it does test that the data driven subscrption process works (e.g. the SMTP server is up-and-running) and you can also expect the result of two emails being sent out -- which there were. I don't know how much more simply I can ask this, I am not asking how to write data driven subsription queries, when and when not to include a FROM STATEMENT IN A SQL QUERY. Rather, where is the data driven subscrpition query stored: in a SQL Server table somewhere? In a file on the disk drive? in the Microsoft cloud? In the RDL? When the power out the data center goes off for days due to Huricane HPvsOracle, then the server is powered back on, and the existing data driven subscriptions are edited, where does SSRS Report Manager retreive the text string associated with step #3 in the data driven subscriptions? And at this point Mike, I have to say I don't really care. It's taking me about as much time working through this thread as it is taking to recreate them in the first place. Thanks for your responses though. Regards, Bob Harford