SqlCMD return code - SQL Server Forums

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

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

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 ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources