Thursday, 21 October 2010
Sending reports using SQL
Sometimes there may be a requirement to send a report using sql code or you may be fed up with having to change the time in report manager when you are develping a report.
1)The first step would be to create a shared schedule which is in the past
and give it a name eg My_schedule
see the screenshot for details.
In the reportserver database you will need to create a stored procedure which will allow you to do this but first you will need to create a Synonym to the schedule table. My understanding is that a synonym is normally used to save you time writing an object name if its on a different server, eg if you want to refer to a table called customers which was on a different server you would type
you can create a synoynm which will allow you to refer to this object without having to type the full name
2)create synonym dbo.mycutomers for [servername].[databasename].dbo.customers
So first create synonym for the schedule table on the reportserver database
create synonym dbo.runreportschedule for [servername].[databasename].dbo.schedule
Then create a store procedure which will fire the report schedule
CREATE PROC [dbo].Runreportschedule(@EventName AS NVARCHAR(1000))
DECLARE @myScheduleID AS NVARCHAR(1000)
dbo.dbo.runreportschedule AS s
NAME = @EventName
EXEC msdb..sp_start_job @job_name =@myScheduleID
You will need to give the system prcoedure sp_start_job permissions so that it can fire the report.
Everything is now set, All you need to do is create a subscription on report manager and then use the shared schedule you have created.
Then in query analyser execute the stored procedure
This will then send the report immediately