Help with sp_ExecuteSQL and xp_cmdshell

Hey all,

I having an issue getting error trapping to work correctly using sp_executesql in conjunction to a xp_cmdshell script.  I've been asked to error trap an exiting stored procedure (which currently uses exec xp_cmdshell and I want to replace it with sp_executesql instead) that is in production.  The script that I've attached below is a generic shell that I'd like to wrap around the xp_cmdshell call in the production proc.  The problem that I'm having is that although my @query parameter is using a simple generic proc (designed to fail on purpose) to simulate failure, the result returned by sp_executesql indicates that everything functioned correctly and never returns an error or value greater than 1.

Does anyway have an idea on how I can make my wrapper work using sp_executesql and catch a value other then 0, since 0 indicated that everything functioned correctly when is obivously should not?

Declare @cmd nvarchar (200)
Declare @cmd2 nvarchar(200)
Declare @query nvarchar(200)       
Select @query = N'test.dbo.spProcFailsOnPurpose' -- simple select * from TableDoesnotExists
-- statement inside the proc
select @cmd = N'''sqlcmd -E -S ' + @@servername + ' -w 1000 /Q "' + @query + '"'''
Select @cmd2 = N'exec master..xp_cmdshell ' + @cmd
Declare @ExecResult int
      BEGIN TRY      
            EXEC @ExecResult = sp_executesql @cmd2
            EXEC sp_executesql @cmd2
            Select @ExecResult as ExecResult
            Select @@ERROR
            IF (@ExecResult = 0) or (@@ERROR = 0)
                  Print 'The Result was 0 or Pass, although this should have failed'
            else
                  Print 'The result was not 0 and it actually failed!!!'
      END TRY
      BEGIN CATCH
                  RAISERROR ('Procedure Filed to run', 16, 1)
      END CATCH

Any help would be greatly appreciated.

Thanks,

Pete J
Start Free Trial