Knowledge Base

You are currently browsing the articles from Information City matching the category Knowledge Base.

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.

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.

Keywords:

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

« Older articles

Newer articles »