Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Data Driven Subscriptions Expand / Collapse
Author
Message
Posted Tuesday, May 30, 2006 10:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #283642
Posted Thursday, June 1, 2006 7:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:20 AM
Points: 514, Visits: 55
Great article!  I emailed Jason a suggestion for the delay, to maybe use the ModifiedDate and LastRunTime fields in the Subscriptions table to test if LastRun is greater than Modified, at which time you can move forward. I'm going to try that and see if it works.


Post #284198
Posted Thursday, June 1, 2006 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 2, 2009 9:45 AM
Points: 2, Visits: 20

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

Sunil

Post #284199
Posted Thursday, June 1, 2006 9:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:53 AM
Points: 3,475, Visits: 584

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.

 




Regards,
Yelena Varshal

Post #284266
Posted Thursday, June 1, 2006 8:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857

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!



______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #284430
Posted Thursday, June 1, 2006 9:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:14 PM
Points: 2,693, Visits: 1,223

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.

 



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #284440
Posted Friday, June 2, 2006 7:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857

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.



______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #284677
Posted Thursday, July 6, 2006 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 30, 2009 1:45 PM
Points: 1, Visits: 9
Jason,

I'm definitely interested in taking a look at your improvements, especially the improved method of waiting for subscriptions to finish.
Post #292534
Posted Thursday, July 6, 2006 8:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 10:48 PM
Points: 1, Visits: 32

Jason,

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

Post #292686
Posted Friday, July 7, 2006 6:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857

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

 

 



______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #292777
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse