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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply