CODEWITHSUNDEEP
reporting-services
Benjamin Sperl
Benjamin Sperl

Reputation: 21

Execute Report Data-driven Subscription with T-SQL

I'm trying to use the SQL agent job created by an SSRS data-driven subscription to generate a report. Another application uses a stored procedure to insert data. I would like to generate a report immediately after the data is inserted by modifying that stored procedure to execute the SQL Server Agent. My code is follows:

USE msdb EXEC sp_start_job @job_name = 'F2B1...'

This approach works perfectly when the Agent job is from a standard subscription. With a Data-driven description, the value of the LastStatus column is "pending" for a long while and then changes to "Done: 1 processed of 1 total; 1 errors". I do not see an error in the SQL Agent error log.

Is there a way to do something like this? If so, can I pass the parameters to the report or does it still execute the query defined in the data driven subscription?

Thanks!

Upvotes: 0

Views: 1478

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

You can also kick off a subscription by Subscription ID.

EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = '<YOUR SUBSCRIPTION ID HERE>'

Where the Event Data parameter is the Subscription ID of your report's subscription.

enter image description here

I actually haven't tried this with a Data Driven Subscription before though.

Upvotes: 0

Related Questions