Updated 10-16-2014 at 11:20 AM Eastern

Yesterday I presented about Change Data Capture for the Pragmatic Works Free Training on the T’s. I covered the basics of what Change Data Capture (CDC) is and showed how to use it. We had a good turn out, so thank you to everyone that attended. If you were unable to join or would like to see the recording you can find the link here. The Q&A can be found below.

Q: Can you use stored procedures instead of T-SQL code when defining the data for a data driven subscription?
A: I don’t believe there is any issue there. You may run into a situation like you do with SSIS sources where it can’t determine the metadata if you use things like temp tables or table variables in the query though. Just be aware of that.

Q: Can you provide the MSDN link for how to transfer subscription ownership?
A: http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.changesubscriptionowner.aspx There is an alternate method here from Jeremiah Clark that is much easier, but there are some gotchas to be aware of that he mentiones.

Q: Can you modify the RSReportServer.config file based on the user or do changes apply to everyone?
A: Changes to this file are for the report server as a whole, so no ability to customize the settings by user or group.

Q: What method is used in the background to schedule subscriptions?
A: A SQL Agent job is used. The unfortunate part of this is the job names are just GUIDs so it is difficult to tie a job back to the particular subscription.

Q: Is there an option to automatically clean up the file system locations where subscriptions place files?
A: You would need to build your own process for that. Seems like a nifty setting though that could be added to subscriptions, but I don’t see Microsoft doing that any time soon. A simple SSIS package with a For Each loop, script task and/or a file system task could take care of this though.

Q: Can subscriptions be delivered to an FTP?
A: No. In the past when that was a requirement I created a subscription to write files to a network share. Then another process monitored that location for files. Anytime a file showed up the secondary process moved it up to the FTP. Could be a simple if it’s there move it or a much more complex system that decides on the FTP destination based on file name, contents, time of day, etc. You could definitely get creative there.

Q: How do we update credentials for the file share option?
A: You’ll have to modify the query that is used for the subscription or run through the setup and modify the credentials in one of the steps if you simply hard coded it.

Q: Can we force users to use a shared schedule?
A: Not that I’m aware of. You may be able to modify the RSReportServer.config file to do that. I’d have to do some more digging through that file to see where the scheduled options are though.

Q: Can a subscription filter the same report for different target audiences?
A: Absolutely, this would be where you switch from a regular subscription to a data driven subscription.

Q: How do you create a time stamp on the file name for non-data driven subscriptions sent to a file share?
A: All the settings for the non-data driven subscriptions are static, so unfortunately you wouldn’t be able to do this.

Q: What edition of SQL Server is required to use data driven subscriptions?
A: Enterprise or Business Intelligence. Prior to the introduction of the Business Intelligence edition it would require Enterprise.

All the demos in this session used the standard Adventure Works sample reports and the Adventure Works databases. So feel free to download those and follow along with the webinar. The recording can be found here: http://pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/687

My slide deck from the presentation can be downloaded here.

A Few Notes:

The default location for the RSReportServer.config file on a 64-bit installation of SQL is C:\Program Files\Microsoft SQL Server\MSRS12.<<Instance Name Here>>\Reporting Services\ReportServer

The Data Driven Subscription query I used was:
Data Driven Subscription Query:

SELECT

SalesOrderID,

Folder,

SubscriptionFileName

FROM (

SELECT

ROW_NUMBER() OVER (PARTITION BY SUBSTRING(Person.LastName, 1, 1) ORDER BY Person.LastName) AS IndexID,

'SO' + CONVERT(VARCHAR, SalesOrderHeader.SalesOrderID) AS SalesOrderID,

'\\CORP-IT-CNSLT-2\Subscription\' +

CASE

WHEN PATINDEX('%[A-M]%', Person.LastName) = 1 THEN 'A-M'

WHEN PATINDEX('%[N-Z]%', Person.LastName) = 1 THEN 'N-Z'

END AS Folder,

Person.LastName + '_' + Person.FirstName + '_' + CONVERT(VARCHAR, SalesOrderHeader.SalesOrderID) AS SubscriptionFileName

FROM Sales.SalesOrderHeader

INNER JOIN Sales.Customer

ON SalesOrderHeader.CustomerID = Customer.CustomerID

INNER JOIN Person.Person

ON Customer.PersonID = Person.BusinessEntityID) AS SubscriptionData

WHERE IndexID <= 5

The view to monitor SSRS Report executions is the ExecutionLog2 view from the report server database.

Thank you all again for joining our webinars each week. It’s always fun to present for everyone!