xp_cmdshell error trapping

• robin.pryor

  SSCrazy

  Points: 2583

  More actions

  June 4, 2015 at 1:12 pm

  #297445

  My ultimate goal is to run a process on one server that will execute script files on another server. I've already put that together and it runs great, but now I'm to the testing phase.

  I intentionally added a script that should throw an error. It's trying to insert a character into an int.

  The error shows in the xp_cmdshell output, but the @rc remains 0, failing to raise an error and jump to the catch block.

  Here's some code (including some PRINT statements for debugging):

  -- The script runner process is running on DEVDBSEPQA01 as a login that has sysadmin everywhere.

  DECLARE @rc INT, @cmdStr VARCHAR(2000)

  SET @cmdStr = 'osql -S DEVDBCRMQA01 -E -i "\\DEVDBCRMQA01\SQL_SCRIPTS\DBA\DATA MANIPULATION\PopulateTestTableBad.sql"'

  BEGIN TRY

  CREATE TABLE #t (cmdout VARCHAR(255), ID INT IDENTITY)

  INSERT INTO #t EXEC @rc = master..xp_cmdshell @cmdStr

  SELECT * FROM #t

  DROP TABLE #t

  PRINT '@rc = ' + CAST(@rc AS VARCHAR(1))

  END TRY

  BEGIN CATCH

  PRINT 'Something went wrong'

  END CATCH

  When I execute this code in SSMS, I expect to see some sort of error involving character/int conversion.

  The code in PopulateTestTableBad.sql --- ARTesting.field2 is an INT:

  USE [DBA]

  GO

  INSERT INTO ARTesting(field2) VALUES('M')

  GO

  Here's what I get in the results pane:

  CMDCOUT ID

  ------------------------------------------------------------------------------------- ---------------------------

  1> 2> 1> 2> Msg 245, Level 16, State 1, Server DEVDBCRMQA01, Line 1 1

  Conversion failed when converting the varchar value 'M' to data type int. 2

  1> 3

  The Messages pane shows:

  (3 row(s) affected)

  (3 row(s) affected)

  @rc = 0