scheduling reports to refresh

Hi all!
Is it possible to schedule reports to refresh overnight? Particularly if
they take a long time to return data - for example in business objects i
would normally schedule a report to refresh overnight via the broadcast
agent console...is the same thing at all possible in reporting services?
does RS have sheduling functionality?
many thanks
GregYes, it has extensive functionality in this area.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:5E5D3B2C-AE87-4ABB-8E84-469A51511863@.microsoft.com...
> Hi all!
> Is it possible to schedule reports to refresh overnight? Particularly if
> they take a long time to return data - for example in business objects i
> would normally schedule a report to refresh overnight via the broadcast
> agent console...is the same thing at all possible in reporting services?
> does RS have sheduling functionality?
> many thanks
> Greg|||ok.....anything further? lol
HOW DO I DO IT!!!
anyone'
"Bruce L-C [MVP]" wrote:
> Yes, it has extensive functionality in this area.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:5E5D3B2C-AE87-4ABB-8E84-469A51511863@.microsoft.com...
> > Hi all!
> >
> > Is it possible to schedule reports to refresh overnight? Particularly if
> > they take a long time to return data - for example in business objects i
> > would normally schedule a report to refresh overnight via the broadcast
> > agent console...is the same thing at all possible in reporting services?
> > does RS have sheduling functionality?
> >
> > many thanks
> >
> > Greg
>
>|||It seemed like you just wondered if it was possible. I thought that maybe
you weren't using Reporting Services and were just making plans.
Anyway, a few of what you need to understand. Search in books online for
snapshot, schedule and history. Reading up on those things should get you
started. Here are a few links to get you rolling:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsmain/htm/rsc_ov_using_v1_4u43.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_catalog_v1_9zsm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_scheduling_v1_2qgk.asp
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:593EBCB8-9CA6-42BB-994C-CFAB8F44495F@.microsoft.com...
> ok.....anything further? lol
> HOW DO I DO IT!!!
> anyone'
> "Bruce L-C [MVP]" wrote:
>> Yes, it has extensive functionality in this area.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:5E5D3B2C-AE87-4ABB-8E84-469A51511863@.microsoft.com...
>> > Hi all!
>> >
>> > Is it possible to schedule reports to refresh overnight? Particularly
>> > if
>> > they take a long time to return data - for example in business objects
>> > i
>> > would normally schedule a report to refresh overnight via the
>> > broadcast
>> > agent console...is the same thing at all possible in reporting
>> > services?
>> > does RS have sheduling functionality?
>> >
>> > many thanks
>> >
>> > Greg
>>

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.

Schedule mail of DB sizes

Wonder if anyone can help me out here.

I'm trying to set up a job to run overnight that mails me the size of all the databases on SQL Server.

The way I'm getting the size of the db's is by running the following in SQL Query Analyzer...

EXEC sp_MSforeachdb @.command1="print '?' select cast(name as varchar(32)), round(size * 8 / 1.024,3) from ?..sysfiles"

Can anyone suggest a way that I can export the results of this to a text file that can then be mailed to me as part of a scheduled job.

Or can anyone suggest a better/easier way of doing this??

Thanks in advance. :)exec master..xp_sendmail @.recipients = 'recipients '
,@.message = 'message'
,@.query = select cast(name as varchar(128)) "LogicalFileName", (size * 8 / 1024) "Size in MB" from master..sysaltfiles'|||... I'm afraid I've not explained myself properly :(

SQL Mail is not set up on this server, so I need a file creating which I can then ftp to a location from which it can be mailed.

And I'm afraid it's not as simple as setting up SQL Mail, it has to be sent from a different box.

Any suggestions?

Thanks|||Look into isql or osql to execute the query and store results in a file|||Create an DTS package, two data sources, one as your SQL Serv db, one as "Output Text", then create a "transfer" task between the SQL serv and the text file. Edit the transfer task and set the source as "execute sql" and than select the "exec ... " string you are using now.

Then schedule your DTS package as you like it...|||no need for dts here .. simple osql or isql will do the task.|||Thanks Enigma.

Just looking into osql now and that seems to do the trick.

Schedule a Stored Procedure to run overnight

What I want to do is to create a Web Interface to allow a User to click a
link and execute a Stored Procedure, I think this would be straight forward.
What I also want to do is to allow the user to select a check box to have
that stored procedure run over night or even schedule the Stored procedures
to run at differnet times.
through E-SQL I would just set up an Agent to do the job, the User won't
have that privelage they will have to go through a web page.
I am not even sure what approach to take? Is there a way to setup an agent
to run via stored procedures so through my code I can just call a system
stored procedure that will create the agent and schedule the job?
Thanks
SteveCreate a queue Table to hold the requests by users for Jobs to run, and a SQ
L
Agent Job to run every tem minutes, or whatever, to read the records out of
that table, and execute them if the current system datetime has passed the
datetime as stored in the queue table.. Then delete the queue table record
(or, - better, flag it as completed, when the job has run successfully.
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
AMB
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||a Queue table is not a bad idea, but I would have to write the name of a
Stored Procedure in a field and then have the agent read that name and
execute the named procedure - I am not sure how to do that but I like the
thought I will look into it.
"CBretana" wrote:
> Create a queue Table to hold the requests by users for Jobs to run, and a
SQL
> Agent Job to run every tem minutes, or whatever, to read the records out o
f
> that table, and execute them if the current system datetime has passed the
> datetime as stored in the queue table.. Then delete the queue table record
> (or, - better, flag it as completed, when the job has run successfully.
> "Steve K" wrote:
>|||Thanks I will look up these sp's and see how to use them
"Alejandro Mesa" wrote:
> See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
>
> AMB
> "Steve K" wrote:
>|||Steve,
After some research, Alej's idea is much simpler. (use sp_add_job)
I discovered you can even add the job with a flag set (@.delete_level =
3) that will cause the job to execute once and then delete iteself, which wa
s
the main reason I would have rolled-my-own system to do what you are trying
to do...
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||I think there is a disconnect here. The solution that Charly is recommending
involves writing a record into a queue table and then having a scheduled job
execute the stored proc using the queue value to determine the data on which
to
act. The job would have the stored proc that should be executed hard coded i
nto
its list of steps.
You aren't asking to execute any stored proc in the system like right?
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>a Queue table is not a bad idea, but I would have to write the name of a
> Stored Procedure in a field and then have the agent read that name and
> execute the named procedure - I am not sure how to do that but I like the
> thought I will look into it.
> "CBretana" wrote:
>|||sounds good that's the direction I am going to run.
Thanks
Steve
"CBretana" wrote:
> Steve,
> After some research, Alej's idea is much simpler. (use sp_add_job)
> I discovered you can even add the job with a flag set (@.delete_level =
> 3) that will cause the job to execute once and then delete iteself, which
was
> the main reason I would have rolled-my-own system to do what you are tryin
g
> to do...
> "Steve K" wrote:
>|||No, I would have a seperate table that contains a list of avalable stored
procedured users can run on demand from this table I would probably just
throw a grid on the screen with an execute link and allow the user to select
execute, the difficult part here is if the user wants to schedule that job t
o
run overnight then I would need some way to know which Sp they selected and
what time to run it.
does that make sense?
"Thomas Coleman" wrote:

> I think there is a disconnect here. The solution that Charly is recommendi
ng
> involves writing a record into a queue table and then having a scheduled j
ob
> execute the stored proc using the queue value to determine the data on whi
ch to
> act. The job would have the stored proc that should be executed hard coded
into
> its list of steps.
> You aren't asking to execute any stored proc in the system like right?
>
> Thomas
>
> "Steve K" <SteveK@.discussions.microsoft.com> wrote in message
> news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>
>|||Do I understand what you are planning on doing? Yes, I think so. Do I unders
tand
why you are doing it? No, but that may not be important ;->
I'm generally inclined to abstract that functionality provided to users away
from the means by which it is implemented. Thus, I wouldn't have users selec
ting
stored procedures per se and it may be the case that you are not either. Sin
ce
I'm presuming that the set of functionality provided to the user is finite,
I'd
be inclined to either make multiple queues and multiple jobs for each stored
proc or perhaps a single queue and multiple jobs for each stored proc. Users
would be presented with a list of actions to perform such that behind the sc
enes
would be a mapping of actions to stored procedures. If the user asked for a
scheduled action, the system would map that request to a post into the
appropriate queue table. That queue table would store the time and day that
the
stored procedure should be executed and the scheduled job would be set to po
ll
continuously (say every minute or every five minutes) to determine if there
is
work to do.
However, since I don't know the specifics of the problem you are attempting
to
solve, I may be completely off the mark.
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:B4674B29-52CA-4320-9408-6C47600FEBB7@.microsoft.com...
> No, I would have a seperate table that contains a list of avalable stored
> procedured users can run on demand from this table I would probably just
> throw a grid on the screen with an execute link and allow the user to sele
ct
> execute, the difficult part here is if the user wants to schedule that job
to
> run overnight then I would need some way to know which Sp they selected an
d
> what time to run it.
> does that make sense?
> "Thomas Coleman" wrote:
>