Webinar Q&A: SSRS Subscriptions and Data Alerts

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](https://images.bradleyschacht.com/wp-content/uploads/2014/10/2014-10-16-Webinar-Slide-Desck-SSRS-Subscriptions.pptx "downloaded here"). A Few Notes: The default location for the RSReportServer.config file on a 64-bit installation of SQL is C:Program FilesMicrosoft SQL ServerMSRS12.<>Reporting ServicesReportServer 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-2Subscription' + 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!

Webinar Q&A: SSRS Subscriptions and Data Alerts

Comments

More from this blog

Azure Data Studio is dead...now what?

Working with Fabric shortcuts in PySpark

Gathering useful notebook and environment details at runtime

Using sempy to get SQL query CU cost from the Fabric Capacity Metrics app

List Parameters for a Python Function

Command Palette

Comments

More from this blog