Scheduling a Report based on an event

Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If it's possible to detect that your stored proc is finished within SQL
(either because it sets a flag in a table or be the mire presence of data it
creates or something) there might be a way. Might not be the optimal
solution, but your SQL dataset could reference that flag so that no data is
returned at all to the report if the flag doesn't exist. You'd have to set
it up so that the data table the report references doesn't exist at all until
the stored proc is finished. Then schedule your report job to run multiple
times near the time when you expect the SQL proc to finish. Without the data
table existing, your Rpt Services job should just error out and product
nothing. Again, not an elegant solution, but if you're desperate might be
something to toy around with ... a place to start.
sebring1130
"SqlJunkies User" wrote:
> Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>|||Sure this is real easy to do.
Create a schedule that has completed in the past - so effectively it will
never fire. Associate this schedule with a Report.
Now what happens is that a SQL Agent Job is created - that maps to the
schedule. You can run SQL Agent Jobs from the SQL Agent Management interface
by hand - or you can cause that job to run through T-SQL.
All that the SQL Agent Job does is create an entry in the Report Server's
Event table at the scheduled time. The Report Server Windows Service is
polling the Event table every 10 seconds or so - and if there are any events
to process it gets on and processes them.
So what you do is either include in your long running stored procedure a
call that will create the required entry in the Event table directly - or a
call that fires the SQL Agent Job.
- One word of warning though if you start editing the schedule in the Report
Manager, then the Report Manager can end up re-creating the SQL Agent Jobs -
and you lose reference to the actual Job.
However if you are disciplined enough then this approach works fine -
(Schedule in the past, have your own process force the SQL Agent Job to run)
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...
> Is it possible to schedule a report based on a flag or stored procedure
> completing. Currently have an overnight load process which must complete
> before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.