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…

Keywords:

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