Executing Script Files From Transact-SQL

[Phil]

Hi,

I have my create statments for tables, procedures, views, etc in
individual Transact-SQL script files (.sql).

I wnat to write another script file that executes these scripts in the
correct order to create the database.

What is the syntax for executing script files from Transact-SQL?

Thanks, Phil

[Erland Sommarskog]

Phil (hp*******@hotmail.com) writes:

I have my create statments for tables, procedures, views, etc in
individual Transact-SQL script files (.sql).

I wnat to write another script file that executes these scripts in the
correct order to create the database.

What is the syntax for executing script files from Transact-SQL?

There isn't one really. Once the batch has been sent to SQL Server,
the script is executing on the server and not on the machine where you
have the scripts.

You can, though, use xp_cmdshell to fork out and run a script through a
command-line tool like OSQL. Beware then that you are running from a second
connection.

Another alternative is to run the scripts with OSQL from the client machine,
and use ~r to include files. Note that ~r is a command to OSQL, and is not
understood by Query Analyzer or SQL Server.

My personal preference for install scripts is to run them in some client
language (Perl in my case). This does not have to be advanced. Basically
just something which reads the files, passes it to SQL Server through some
API call or through OSQL, and then maybe checks for errors.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp