Execute *.SQL at once - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Execute *.SQL at once

Author

Topic

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2006-09-12 : 13:59:06

Hi All,

I have a bunch of sql files under one folder and I want to execute all of them automatically one after other. Does any one know such command so thatI don't have to execute them manually.

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-12 : 14:02:43

You could set up a sql job that executes each script as a seperate jobstep. Look at the OSQL utility in Books Online.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-12 : 14:23:42

a *.bat with many lines of osql commands?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-09-12 : 14:51:48

We use a BAT file that concatenates all SQL files (actually all SQL files after a given date) into a single SQL file ... and then we Distribute and Run that.

Kristen

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-09-12 : 15:48:33

Hi,
I have used the .cmd batch file to do this.
The following code ,I copied from my Blog:
Creating the Build Scripts to Automate the Incremental Daily Database Builds:

I had the Requirement to run multiple scripts to create the Database, Stored Procedures, Functions, Triggers; etc.It was time consuming to run the scripts manually.
So I thought of using the Batch Files to automate the process.
Tools Used:
SQLCMD: This command line utility tool is shipped with SQL Server Database Engine and provides efficient integration with Batch files and SQL Server Databases.
Before running the scripts, I have to make sure the Database Exists.
If the database does not exist, I have to create the database with the Name passed as Parameter and then execute the T-SQL Scripts. I selected SQLCMD Tool to accomplish this task.
SYNTAX:
SQLCMD
-S Server Name
-d Database Name
-b Exit on Error in the Batch
-Q Run the Query in Database and close the Connection with the Database
-q Run the Query in Database and leave the Connection Open with the Server

SQLCMD Uses the OLEDB Interface to connect to the Server where as the
SQL Management Studio uses the SQLCLIENT to connect to the SQL Server.
It might cause some difference in the Query Execution.

Passing the variables to the command batch file:
In the Batch file all the variables are treated as strings. It’s different from any other procedural languages. So we do not have to define the variable data type.

Set DatabaseServer =%1
It means the first argument we pass in the command line would be the Database Server Name.
Set DatbaseName=%2 .
The second argument would be the Database Name. While passing the arguments in the command line, there has to be space between the arguments.

The final Build Script:
ECHO OFF
REM: -b On Error Batch Abort in sqlcmd
REM: >> or > this operator can be used in place of -o option to append the output.

set ServerName=%1
set DatabaseName=%2
set WorkDirectory =%3
set LogfileName =%4

echo The ServerName used in applying this Build: %1
echo The DatabaseName used in applying this Build:%2
echo The directory used in the Build: %3
echo The Logfile Used to log Errors in this Build: %4

echo: The Build Start time %date% %time%

ECHO: Checking the existence of database: %2 in server: %1
Echo: If not exists creating the Database: %2 in Server: %1

set Query="IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='%2')
BEGIN CREATE DATABASE %2 END"
sqlcmd -S %1 -d Master -Q %Query%

echo: Checking the connection to the Server: %1 and Database: %2
set qry ="set nocount on"
sqlcmd -S %1 -d %2 -Q "%qry%"
IF %Errorlevel% NEQ 0 goto FailureExit:

Echo Applying the Scripts at Server: %1 and Database %2

for %%B in (%3\*.sql) do (sqlcmd -S %1 -d %2 -b -i "%%B" >> %4)
IF %Errorlevel% NEQ 0 goto FailureExit:

REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:

REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:

Echo The Build successfully applied at Server: %1 and Database: %2
Echo The Build completed Time %date% %Time%

:FailureExit echo The build Cancelled at Server: %1 and Database: %2

Processing the SQL Files in the Subdirectory

The following batch file processes the .sql files in the Given Server Name, DatabaseName, and WorkingDirectory.The log file is passed as the fourth Parameter where the output of the command execution saved.

echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4

for /d %%B in (%3\*) do (
@for %%C in ("%%B\*.sql") do sqlcmd -S %1 -d %2 -i %%C >>%4)

Iterating through the Files without the Double loop:

echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /r %3 %%B in (*.sql) do (sqlcmd -S %1 -d %2 -i %%B >>%4)

Using the /r command to search the .sql extension files in the Foldername passed as 3rd
parameter.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-12 : 17:03:59

Funny, I made almost the same blog post:

http://weblogs.sqlteam.com/robv/archive/2003/09/28/181.aspx

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2006-09-13 : 15:29:05

Thanks for your help guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources