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.

ReportViewer export to Excel: Data Regions within table/matrix cells are ignored.

Having some issues in the exported excel format. As there is a requirement from the client to add new row manually, the column wasn’t in the correct format. Thus, I thought of an idea to use a table inside another table to make the field more organized. But trying to do so causes another error.

Without further ado, checking from the internet shows that this error is a known error using table inside table for Reportviewer in exporting to Microsoft Excel. Just for sharing for those who faces this error.

Written by admin on July 7th, 2010 with no comments.
Read more articles on Entertainment and Knowledge Base and Programming.

« Older articles

Newer articles »