August 4th, 2010

You are currently browsing the articles from Information City written on August 4th, 2010.

Execute batch sql files

A friend of mine was asking me if there is any way to execute of list of sql files automatically without having to open it one by one to execute it.

I gave him this site which shared this code:

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

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell ‘dir /b “C:\SQL Scripts\*.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
— The following SET command must be on a single line or else an error will be generated.
— It is split in this script for readability purposes.
SET @vSQLStmt = ‘master.dbo.xp_cmdshell ”osql -S Server Name -U User Name -P Password
-d Database Name -i “C:\SQL Scripts\’ + @vFileName + ‘””’
EXECUTE (@vSQLStmt)

FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO

It helps but there’s no error handling. Need some fine tuning here…

Written by admin on August 4th, 2010 with no comments.
Read more articles on Knowledge Base and Programming.