Monday, July 09, 2007

How to create a SQL script that executes other SQL scripts

Yesterday I was facing the following problem..
I have several sql scripts that I want to call from a single sql script.
I am using SQL Server 2005.
I have found two ways of doing it. The first one is by directly calling the scripts one by one, and the second is by calling all the scripts in a folder.

First of all, you need to set your server to accept 'xp_cmdshell' commands.
This is done by the following code
USE MainDB

GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
Now, let's assume that you have a folder at D:\Test, which has 3 files,
Test1.sql
Test2.sql
Test3.sql

The first way, which is the easiest way to execute this files by a sql script is by the following
USE MainDB

EXEC master..XP_CMDShell 'osql -E -i D:\Test\test1.sql'
EXEC master..XP_CMDShell 'osql -E -i D:\Test\test2.sql'
EXEC master..XP_CMDShell 'osql -E -i D:\Test\test3.sql'
If you want to run all the scripts in a folder, then you can use the following script
USE MainDB

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell 'dir /b "D:\Test\*.sql"'
GO

DECLARE cFiles CURSOR LOCAL FOR
SELECT DISTINCT [SQLFileName]
FROM ##SQLFiles
WHERE [SQLFileName] IS NOT NULL AND
[SQLFileName] != 'NULL'
ORDER BY [SQLFileName]

DECLARE @vFileName VARCHAR(200)
DECLARE @vSQLStmt VARCHAR(4000)

OPEN cFiles

FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -E -i "D:\Test\' + @vFileName + '"'''
EXECUTE (@vSQLStmt)

FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO

This code will work if you don't need a username and password to connect to your database.. If your configuration is more complicated, you will have to add some more parameters to the osql. Read more here

14 comments:

Zaf said...

"Yesterday I was in front of the following problem. I have a banch of sql scripts that I want to be called from a single sql script."

Να δώσεις το proficiency πίσω, μεγάλε. Και να επιστρέψεις στον πατέρα σου τα λεφτά που έδωσε στο φροντιστήριο αγγλικών.

Stavros said...

Ναι.. και εμένα δε μου άρεσε :)

rockordie said...

Dear "from a single sql script"
I read post and find very interesting, I wait you come back to Kazakstan and open business together.
I thing your England writing is...very nice.

Stavros said...

I am know me England is very best.. :)
I like.. How much?

Zaf said...

βάλε πίσω τις διορθώσεις ρε δειλέ...

Stavros said...

Για να μαθαίνουν οι νέοι από τα λάθη μου; :)
Άσε, μην ξεχάσει ο κόσμος και τα αγγλικά που ξέρει :)

Zaf said...

αν είναι έτσι, τουλάχιστον διόρθωσε και το "I want to be called", γιατί όλοι θα σε φωνάζουν "Mr. FromASingleSQLScript" ...

Zaf said...

nice technical post, BTW

Nikos said...

Έσκασε πάλι μύτη ο spammer ο Rodrigo!

Stavros said...

Thanks zaf

Niko, I deleted Rodrigo's comment.. I hate spam..

SeaStavros said...

Καλησπέρα συνονόματε!

Stavros said...

kalhspera kai se esena :)

Acro said...

Αυτό είναι ένα καλό παράδειγμα
των εργασιών σας που δεν καλοκαταλαβαίνουμε, αλλά με χαρά θα τις ενσωματώσουμε κάπου προκειμένου να τις χρησιμοποιήσουμε.
And indeed your London is very fast.
Me also, indeed.
Proficiency
και από Cambridge το 1989 παρακαλώ!

Micheline said...

Well said.