Post reply

Can I schedule a report to run via SQL Job Agent?

  • May 1, 2013 at 8:26 am

    #394701

    Hi

    I was wondering if I can schedule a report to be emailed via the job agent?

    I know I can Schedule it via SSRS

    but here is what I need...

    I run this report which runs via a SP which updates a field to say "Processed"

    When the job is run and sent, I want to run another SP that changes the "Processed" to "Completed"

    But I don't want to run it in case the first part fails for some reason...

    Thanks

    Joe

  • May 2, 2013 at 6:49 am

    #1611873

    See this link

    http://sqlserverbiblog.wordpress.com/tag/manually-starting-subscription/

  • May 2, 2013 at 7:59 am

    #1611898

    Hi Ed,

    Thanks for getting back..

    so I followed the blog and it worked fine

    USE msdb

    EXEC sp_start_job @job_name = 'C387D481-5A3D-4DF8-89BC-10A55A5FE1F4'

    But....

    I wanted to run a second step which EXEC an sp which updates a flag in the step1 sp for the report

    The problem is when I add the second step the report is blank, because the second step has updated the flag..

    Is there a way to email the report before the update in step2 or maybe run as two jobs?

    Thanks

    Joe

  • May 2, 2013 at 8:26 am

    #1611905

    I'm not sure I totally understand your situation - but a couple of approaches come to mind:

    1) Use the report query to do the update. So you would take the original Processed data set and stuff it into a temp table; then issue an update for the Completed flag; then select your data set for the report back from the temp table. I think this can be done. It's like you're issuing a series of SQL statements as you would in a strored proc.

    2) Use the SSRS subscription to output the report to, say, a PDF instead of email. Then use Database mail to send the PDF as an attachment. Then update your Completed flag.

  • May 2, 2013 at 9:07 am

    #1611920

    Hi Ed..

    Thanks,

    Actually I did sort of do the first one...

    first part of the query updates the flags marked 'Processed' to 'Completed'

    second part updates "null" to "Processed"

    then data is written to report selecting processed records...

    Since the report is scheduled daily

    the following day it will mark those records 'Processed' to 'Completed'

    if for some reason it fails the records will be marked "Processed" still sending then to the report

    That was my whole thing for step2 to mark them as processed...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply