Executing Script Files From Transact-SQL

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, PhilPhil (hp_howell@.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, esquel@.sommarskog.se

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

Executing 2 SQL script files

I have 2 Transact-SQL scripts saved in 2 files :
ClearData - HR.sql (DDL and DML)
ClearData - Others.sql (only DDL)
I want to write a Transact-SQL script which can executes the above 2 files.
And I want to save it in a seperate file (.sql extension). How can I do
that?
Thanks
RizwanI know I can do it if I created 2 stored procedures for "ClearData - HR.sql"
and "ClearData - Others.sql" respectively and then calling these stored
procedures from my script.
But what I would love to do is call a sql script file from another sql
script file and execute it. Any input?
Thanks
Rizwan
"Rizwan" <hussains@.pendylum.com> wrote in message
news:2CT4e.16736$Fy3.977058@.news20.bellglobal.com...
> I have 2 Transact-SQL scripts saved in 2 files :
> ClearData - HR.sql (DDL and DML)
> ClearData - Others.sql (only DDL)
> I want to write a Transact-SQL script which can executes the above 2
files.
> And I want to save it in a seperate file (.sql extension). How can I do
> that?
> Thanks
> Rizwan
>|||Hi
I am not sure exactly what you require but there is the :r command when
running osql interactively see
http://msdn.microsoft.com/library/d...br />
1wxl.asp
This may also help:
http://groups-beta.google.com/group...
d4ca6efcd9ac3
John
"Rizwan" wrote:

> I know I can do it if I created 2 stored procedures for "ClearData - HR.sq
l"
> and "ClearData - Others.sql" respectively and then calling these stored
> procedures from my script.
> But what I would love to do is call a sql script file from another sql
> script file and execute it. Any input?
> Thanks
> Rizwan
>
> "Rizwan" <hussains@.pendylum.com> wrote in message
> news:2CT4e.16736$Fy3.977058@.news20.bellglobal.com...
> files.
>
>sql