Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SqlCMD return code

Author  Topic 

Nagac
Starting Member

5 Posts

Posted - 2015-04-20 : 12:24:34
Hi

I am Sqlcmd utility to execute Stored Procedure via batch script where i want to check the errorlevel and decide whether to continue to next steps.However Sqlcmd returns always 0 regardless of status.

I tried -b,m-1 to capture the errorlevel but noluck.

Can someone advise how to capture the nonzero values when it fails.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-20 : 13:38:16
Look at example F in this page: https://msdn.microsoft.com/en-us/library/ms180944.aspx
Go to Top of Page

Nagac
Starting Member

5 Posts

Posted - 2015-04-21 : 05:51:00
Thanks James,

But i tried -b option in my script but i don't see it is working as i do see return value 0 always

Below is the command i am using
quote:

sqlcmd -E -Q "EXECUTE master..sqlbackup '-SQL ""BACKUP DATABASE [DATABASE] TO DISK = ''D:\DBBackups\Test.sqb'' WITH PASSWORD = ''<ENCRYPTEDPASSWORD>IxY6YeZHYWfK</ENCRYPTEDPASSWORD>'', CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, KEYSIZE = 256, THREADCOUNT = 7""'" -b
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-21 : 06:19:28
We generate a temporary script file (e.g. from the BATCH file) which contains:

DECLARE @intErrNo int
EXEC @intErrNo=dbo.MySProc @Param1=xxx, @Param2=yyy
:EXIT(SELECT @intErrNo)

The BATCH file contains:

SQLCMD -d MyDatabase -i MyTempScript.SQL -o X:\MyPath\MyProcessName.OUT -b -l 300 -S . -E >>X:\MyPath\MyProcessName.ERR
IF ERRORLEVEL 1 GOTO SQL1_Error

ECHO %DATE% %TIME% SQL MyProcessName END >>X:\MyPath\MyProcessName.ERR
GOTO SQL1_DONE

:SQL1_Error
ECHO %DATE% %TIME% SQL MyProcessName *** ERROR EXIT *** : %1 >>X:\MyPath\MyProcessName.ERR
ECHO ---------- >>X:\MyPath\MyProcessName.ERR
... Some stuff here to send an EMail with the error file attached ...

GOTO SQL1_DONE

:SQL1_DONE

... rest of batch file ...
Go to Top of Page
   

- Advertisement -