run scripts at regular intervals

[EddWood]

I have a series of SQL scripts that I want to run every day, they will
import data into temp tables, add & update the main tables then delete data
from the temp tables ready for the next days updates.

Is is possible/sensible to have a script with a selection of subqueries
embebbed into it to run these scripts? and can anyone advise how I structure
the 'parent' script?

[Erland Sommarskog]

The easiest way would probably be to package the scripts as stored
procedures that calls each other.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[EddWood]

Thanks that, I have looked for info on creating a stored procedure to run
these scripts but all I found are talking of passing variables, I simply
need it to run

script1.sql
script2.sql
script3.sql etc

Can you advise how I would write that script? I have 14 in total.

I then need the stored procedure to run at 05:00 each day, is it possible to
include a method to check the server time to activate the script to run at
that time?

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CCBAE80...@127.0.0.1...

[Erland Sommarskog]

EddWood (Edd_nospam_@/Wood\hotmail.co.uk) writes:
> Thanks that, I have looked for info on creating a stored procedure to run
> these scripts but all I found are talking of passing variables, I simply
> need it to run
>
> script1.sql
> script2.sql
> script3.sql etc

CREATE PROCEDURE script1 AS

...
EXEC script2

go
CREATE PROCEDURE script2 AS
...
EXEC script3

and so on. That is, you don't have to pass any parameters if you don't
have any parameters to pass.

What is a little more tricky if the scripts contain multiple batches,
and which have to be multiple batches, because one batch will not compile
unless the previous one has executed. As I don't know what's in your
scripts, I cannot tell whether this is an issue for you.

> I then need the stored procedure to run at 05:00 each day, is it
> possible to include a method to check the server time to activate the
> script to run at that time?

You can run the script from SQL Server Agent. However, if you use
Express Edition, there is no Agent. You can still use the Windows Task
Scheduler.

[Uri Dimant]

In addition
http://dimantdatabasesolutions.blogspot.com/2009/01/executing-multiple-script-file-via.html

"EddWood" <Edd_nospam_@/Wood\hotmail.co.uk> wrote in message
news:BF8F4127-B0C4-42AD...@microsoft.com...