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
When running a report for the first time in report manager it takes a while to run, after this initial run reports run fine. There are a ...
I have recently been looking at conditional page breaks in SSRS 2008 r2 and did not know that it is possible to do via an expression. For...
Due to the fact the reporting services allows you to query the tables you can easily write SQL which will allow you to return data from the ...
To Alternate colours for a group: =iif(RunningValue(Fields!MYFIELD.Value,countdistinct,nothing) mod 2,"silver","white")...