• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2323
  • Last Modified:

Error-handling if SQLCMD fails in .bat Windows script

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
0
gometang
Asked:
gometang
  • 2
1 Solution
 
QlemoC++ DeveloperCommented:
A simple approach would be to filter the output of sqlcmd for error keywords:

sqlcmd .... | findstr /C:"duplicate key" /C:"another error message part" >nul || (
   move / copy
)

0
 
gometangAuthor Commented:
Is there anyway to capture any type of error instead of hard-coding the expected errors that pass from SQLCMD?
0
 
QlemoC++ DeveloperCommented:
We could also match with "Msg:" instead of error keywords. That would capture any error and warning message, but not reveal the error message (which is reported in the line following that "header").
0
 
yy1111Commented:
sqlcmd .... | findstr /C:"duplicate key" /C:"another error message part" >nul || (
  move / copy
)

Would u please explain more on this solution?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now