troubleshooting Question

Error-handling if SQLCMD fails in .bat Windows script

Avatar of gometang
gometang asked on
Windows BatchMicrosoft Legacy OSMicrosoft Development
4 Comments1 Solution2633 ViewsLast Modified:
Hi guys,
I am currently on WinXP SP2 against SQLEXPRESS 2008.  I have a script that executes a SQL 2008 stored proc that BULK INSERTS a list of fixed-format files from my C: drive into a table.  After each file has been BULK INSERTed into a table, the script also moves the file to a \Processed folder and renames the file by appending the date & time to the filename.

The problem I am having is figuring out how to grab errors from the SQLCMD statement that runs the stored proc.  I want to not perform the copy/rename for a file if that file BULK INSERT fails (i.e. there are primary key violations).  I have tried setting SQLCMD -b -V 1, but ERRORLEVEL stays at 0 even when the stored proc returns primary key violations from the BULK INSERT.

Here is the code I have so far:

::@ECHO OFF

:: Set date/time variables to build and append to the renamed filename
SET DAY=%DATE:~4,2%
SET MTH=%DATE:~7,2%
SET YR=%DATE:~10,4%
SET HR=%TIME:~0,2%
SET HRO=%TIME:~0,1%
IF "%HRO%"==" " SET HR=0%%TIME:~1,1%
SET MIN=%TIME:~3,2%
SET SEC=%TIME:~6,2%
SET STRDATE=%YR%%MTH%%DAY%-%HR%%MIN%%SEC%

::SET ERRORLEVEL=0

:: Set file directories
SET DIRDATA=C:\FIXEDFILEUPLOAD\Data\
SET DIRHIST=C:\FIXEDFILEUPLOAD\Data\Processed\

:: Loop through all .txt files and execute sproc to upload flat file into SQL table
cd %DIRDATA%
for %%i in (*.txt) do sqlcmd -Q "exec dbo.nkn_LoadDataSP '%DIRDATA%%%i'" -U user -P password -S MACHINE\SQLEXPRESS -d DB_TEST & move %DIRDATA%%%i %DIRHIST%  & ren %DIRHIST%%%i %%i.%STRDATE%

I know this is probably easy, but I've been having a tough time on this.

Thanks in advance,

Gome
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros