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
[servername].[databasename].dbo.customers
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
3)
CREATE PROC [dbo].Runreportschedule(@EventName AS NVARCHAR(1000))
as
DECLARE @myScheduleID AS NVARCHAR(1000)
SELECT
@myScheduleID=s.ScheduleID
FROM
dbo.dbo.runreportschedule AS s
WHERE
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

[dbo].Runreportschedule 'Myschedule'



This will then send the report immediately