I wish to have a nightly SQL Server Agent job that simply runs an SSRS report that has one parameter driven by an SQL cursor in a stored procedure and save the generated PDF into a file system directory. One report would be produced for each parameter value picked up in the cursor.
This is based on a SQL query that detects row changes in a table for previous 24 hours for example.
I need to write one PDF per row into a windows file system directory.
So I was wondering if it is possible to execute an external SSRS report via this process and export that report as PDF to the directory?
2条答案
按热度按时间nimxete21#
A subscription can be created that generates a PDF to a folder.
The subscription be triggered by a stored procedure using the AddEvent SP in ReportServer with the subscription ID of the subscription.
If the parameter needs to be updated, you can get the XML from the Subscriptions table and update it with .modify.
Here's an example of how it is used:
hs1ihplo2#
There is one issue I found with your code.
When your code changes the parameter values and then execute the EXEC command, RS is queued that it has a report to run. This is done by the SubscriptionID of the RS report, and I do not believe that you can have more than one instance of the same SubscriptionID queued up at any one time.
The problem I found with your WHILE loop is that it will quickly process the next REPLACE parameter execution BEFORE RS has a chance to run the first EXEC with the first set of replaced parameters.
I used your code as a stepping stone to my final solution, that runs a single subscription over 20 times with different parameters and email address sets, but had to incorporate a hardcoded WAITFOR command after the EXEC to allow RS the time it needs to run the report before processing the next set of REPLACE statements.
But thanks for your post...It was a GREAT stepping stone.