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:
:: Set date/time variables to build and append to the renamed filename
IF "%HRO%"==" " SET HR=0%%TIME:~1,1%
:: Set file directories
:: Loop through all .txt files and execute sproc to upload flat file into SQL table
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,