SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Driven Subscriptions


Data Driven Subscriptions

Author
Message
Jason Selburg
Jason Selburg
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4417 Visits: 4113
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
Hugh Thomas
Hugh Thomas
Mr or Mrs. 500
Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)

Group: General Forum Members
Points: 584 Visits: 57
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.



Sunil Shah
Sunil Shah
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 20

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

Sunil


Yelena Varshal
Yelena Varshal
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5666 Visits: 597

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

Jason Selburg
Jason Selburg
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4417 Visits: 4113

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
philcart
philcart
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5445 Visits: 1441

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
Jason Selburg
Jason Selburg
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4417 Visits: 4113

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
Drew Williams
Drew Williams
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 9
Jason,

I'm definitely interested in taking a look at your improvements, especially the improved method of waiting for subscriptions to finish.
grant whellum
grant whellum
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 32

Jason,

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


Jason Selburg
Jason Selburg
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4417 Visits: 4113

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search