May 15, 2009 at 1:22 am
Hi,
Within a sql server agent job we run a stored procedure which uses xp_cmdshell to execute a number of bcp input commands for a number of tables. When the bcp command fails (with a failed primary key constraint for example) the xp_cmdshell still returns a 0, indicating success.
If I view the output of the bcp command I can see it fails but xp_cmdshell doesn't.
To make matters more confusing on some of our SQL instances, on other servers, it appears that the same situation DOES cause xp_cmdshell to return a 1.
Are there any environment/security/server etc settings I need to check? I've had a look at the instances and they seem the same.
Many thanks,
H
May 15, 2009 at 9:07 am
Hi Taffy
I'm not sure if this helps but I had the same problem and I handle it with a table variable which receives the output of xp_cmdshell. After this I search for "%error%" and use my own error handling. Here a simplified version of my script:
DECLARE @Output TABLE (Line NVARCHAR(MAX))
-- ...
-- Delete previous output information
DELETE FROM @Output
-- Execute the BCP command and receive the output to show errors
INSERT INTO @Output
EXECUTE xp_cmdshell @cmd
-- Show errors
IF EXISTS (SELECT TOP(1) 1 FROM @Output WHERE Line LIKE '%Error%')
RAISERROR('Error while execution of BCP!', 11, 1)
-- ...
Greets
Flo
May 15, 2009 at 10:43 am
I use xp_CmdShell a lot and I do the same thing as Florian. I never have been able to figure out these DOS return values to xp_CmdShell, they have always been very inconsistent.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2009 at 6:36 am
This works a treat!
Thanks,
H
March 1, 2015 at 5:01 am
I thought I'd reply to this old post as I've found a different problem in batch programming.
In a batch file, I'm using BCP ... || goto ERROR_HANDLING from which I then capture %ERRORLEVEL%. It works perfectly, but sometimes %ERRORLEVEL% is zero, and I was wondering why.
It seems that the command is recognised as having not been successful, yet the return code is 0.
Perhaps the OP problem could be resolved by using || too, i.e. exec sp_cmdshell 'BCP ... || dir b:\' which should (assuming you haven't got a B: drive) return a code >0 in T-SQL.
September 13, 2015 at 11:32 pm
The MSDN documentation for xp_cmdshell doesn't seem to show that this is possible, but xp_cmdshell does seem to be able to return whatever return code you want, as long as you explicitly state it using the EXIT command. The following works the same across SQL Server 2008 R2, 2012, and 2014:
DECLARE @ErrorLevel INT;
BEGIN TRY
EXEC @ErrorLevel = xp_cmdshell 'asdf || EXIT 9';
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_NUMBER();
END CATCH;
SELECT @ErrorLevel;
The CATCH block has nothing to catch, but @ErrorLevel is set to 9. I use the following and so far it works as expected:
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorState INT,
@ErrorSeverity INT;
DECLARE @ErrorLevel INT;
BEGIN TRY
EXEC @ErrorLevel = xp_cmdshell 'D:\path\to\MyScript.cmd';
IF (@ErrorLevel > 0)
BEGIN
RAISERROR(N'Error # %d in MyScript.cmd', 16, 1, @ErrorLevel);
END;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorState = ERROR_STATE(),
@ErrorSeverity = ERROR_SEVERITY();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN;
END CATCH;
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
September 13, 2015 at 11:36 pm
michael.farrow (3/1/2015)
In a batch file, I'm usingBCP ... || goto ERROR_HANDLINGfrom which I then capture %ERRORLEVEL%. It works perfectly, but sometimes %ERRORLEVEL% is zero, and I was wondering why.
You might want to try adding the following to the top of your script:
SETLOCAL ENABLEDELAYEDEXPANSION
And then check the value of (please note the use of ! instead of %):
!ERRORLEVEL!
I had to do that in order to get the return code from SQLCMD.EXE in a CMD script. Without enabling delayed variable expansion, %ERRORLEVEL% was always set to 0.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply