Excellent article !!  I can't wait to try it out.

Sunil

I use VBscript with Named Arguments where I pass the configuration file name. This configuration files contains 4 lines: TOaddresses, SubjectText, BodyText and Attachments. We do have to have 1 line batch files for different configuration files because I am trying to keep it simple for the person who manages these files. It could be replaced with one batch if you know you DOS programming well. This batch is just scheduled in Windows Task Scheduler.

The calling batch:

cscript "MyScriptThatSendsEmails.vbs" /ConfigFile:"TextFileContainingEmailStrings.txt"

Code in the MyScriptThatSendsEmails.vbs has to contain the following lines in order to get the name of the configuration file.

Set colNamedArguments = WScript.Arguments.Named
ConfigFile = colNamedArguments.Item("ConfigFile")

Then I use FileSystemObject to access lines in this file.

The report itself is sent as an attachment. The report has to be pre-generated by any report tool and placed in the same directory each time for the script to be able to find it.

Thanks everyone for the comments.

I have one quick update, replace the text "IF @length > 0" with "IF isNull(@length, 0) > 0" in each place in the procedure.

Also, I am working on a more robust version of this approach and will post the new info.

Thanks!

Good article, always handy to see alternative methods.

I built a C# console app that renders the reports via the webservice. Once I got my head around the .Net stuff is was relatively easy to put together.

The app takes a stored procedure name as one of its parameters and this stored procedure returns a DataReader which is used to drive the report rendering.

One of the nice features of rendering via the webservice is that the same report can be rendered in any of the registered Reporting services formats and you can have 0-n parameters for the report.

I’m happy to say that I’ve made some major improvements to the code using many of your suggestions.

Improvements:

• A more efficient (and shorter) procedure that handles errors.
• The procedure is commented better and should give you a MUCH better understanding of what is going on.
• An improved method of “waiting” until the subscription completes before continuing.
• The ability to handle multiple (almost unlimited) parameters.
• *BIG* The ability to NAME your subscriptions and refer to them by name. (As it would get difficult to manage by schedule IDs)
• More settings available on the subscription, like “include report/link, render format, etc…”

If you are interested, reply and I’ll send the updated procedures and instructions. There is a replacement for the main procedure, one to gather and format the parameters and a table to hold them. Once in place, it’s quite easy to use.

Jason,

Works great except for the waiting for the report to finish code. Can you post this update please.

To address the wait time for the subscription to complete, make the following changes to the stored proc. This solution was devised by hughthomas -> http://www.sqlservercentral.com/forums/userinfo.aspx?id=86254

Add the following lines to the end variable declaration section ...

 @starttime datetime,
 @lastruntime datetime

set @starttime = DATEADD(second, -2, getdate())
set @execTime = getdate()

Now replace the "WAITFOR DELAY '00:00:10' " with ...

   -- this give the report server time to execute the job
   SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
   While (@starttime > @lastruntime)
   Begin
    print '...'
    print @lastruntime
    WAITFOR DELAY '00:00:03'
    SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
   End