• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2080
  • Last Modified:

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
0
PJan8724
Asked:
PJan8724
  • 4
  • 3
1 Solution
 
srnarCommented:
sp_executesql will call another EXEC, where you are able to store @return_value and publish it out to your @ExecResult. Then you can implement some logic when the value is <> 0.

In the following snippet you can invoke erro 'divide by zero' - output value is -6 in this case.
ALTER PROCEDURE dbo.spProcFailsOnPurpose
AS
BEGIN
	--SELECT 1/0;
	SELECT 1/1;
END
 
 
DECLARE @cmd2 NVARCHAR(200);
Declare @ExecResult INT;
 
SET @cmd2=N'exec @result=dbo.spProcFailsOnPurpose';
EXEC sp_executesql @cmd2, N'@result INT output', @ExecResult out;
 
PRINT @ExecResult;

Open in new window

0
 
PJan8724Author Commented:
Hey srnar,

Okay, this seems to work for the most part.  After a little testing, the root cause of my problem seems to be the actual call to the sqlcmd statememnt (which I have to use unfortunately because of hoe the production proc is written).  The sqlcmd command seems to return a value of success even if the proc fails to complete successfully.  Any ideas on how I can get around this, and trap a failed result if the proc called in the sqlcmd command fails to complete (see stament below).

N'''sqlcmd -E -S ' + @@servername + ' -w 1000 /Q "' + @query + '"'''

Thanks,

PJ
0
 
Mark WillsTopic AdvisorCommented:
Well, is is only really executing the xp_cmdshell so it doesn't know about the external program success or otherwise...

Not sure why it must be sp_executesql, and, you normally use the output param to store the 'results', or simply run the xp_cmdshell, and capture the results...






if exists 9select * from information_schema.tables where table_name = 'temp_results') drop table temp_results
create table temp_results(line varchar(1000))
 
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      
            insert temp_results
            exec master..xp_cmdshell @cmd
            --Select @ExecResult as ExecResult
            Select @@ERROR
            set @ExecResult = (select count(*) from temp_results where line like '%error conditions%')
            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
 
select * from temp_results

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mark WillsTopic AdvisorCommented:
Oh, and check out the number of single quotes...
0
 
PJan8724Author Commented:
Hey Mark,

Thanks for the help and the quick response.  Actually I'm not married to sp_executesql, I was just trying it out since I didn't rememeber until noon today that xp_cmdshell does indeed return a success/failure code (my bad).  In any case, I just tested your code at it works, just not correctly.  According to books online the "Return Code Values" should be "0 (success) or 1 (failure)".  The Stored Procedure called (N'test.dbo.spProcFailsOnPurpose') in the xp_cmdshell as the name indicates will fail to run since I put a divide by zero (select 1/0) statement in the proc.  However my result set still returns a value of 0 (success) for the @ExecResult variable and @@ERROR also contains a value of 0 (success).  Which means that both my IF..ELSE clause and the Try..Catch block also fail to catch the error since technically there is no error to begin with.  I've attached you code with a couple of select statements added in that I used for Debugging and also the result sets are listed below along with the messages tab info.  

Results Tab INFO:
Msg 8134, Level 16, State 1, Server DHOWARD, Procedure spDivideByZero, Line 17
Divide by zero error encountered.
NULL

Messages Tab INFO:
(3 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
The Result was 0 or Pass, although this should have failed
(3 row(s) affected)

If the result is really being captured correctly, shouldn't the script have jumped to the second have of the IF..ELSE clause or tripped the Catch block and raised an error.  Any thoughts?

PJ

if exists (select * from information_schema.tables where table_name = 'temp_results') 
drop table temp_results
 
create table temp_results(line varchar(1000))
 
Declare @cmd nvarchar (200) 
Declare @cmd2 nvarchar(200)
Declare @query nvarchar(200)       
Select @query = N'test.dbo.spProcFailsOnPurpose'  -- Select 1/0 inside called Proc
-- 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      
            insert temp_results
            exec master..xp_cmdshell @cmd
            --Select @ExecResult as ExecResult
            Select @@ERROR as ErrNbr
			Select @ExecResult as Result
            set @ExecResult = (select count(*) from temp_results where line like '%error conditions%')
            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
 
select * from temp_results

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Yes, but the xp_cmdshell is probably ignorant of the output status from the stored procedure... It possibly thinks it successfully executed a stored procedure - will check that...

The idea of trapping the results in temp_results is so it can be parsed for error conditions - I just used like '%error conditions%' as an example, you probably need

like '%msg%level%state%'  

in line 20....

0
 
Mark WillsTopic AdvisorCommented:
OR, try

exec @execResult = master..xp_cmdshell @cmd

in line 16 and comment out the other setting of @execresult
0
 
PJan8724Author Commented:
Hey Mark,

Thanks for the assistanceI got it working now.  You were right on target with the
exec @execResult = master..xp_cmdshell @cmd statement and the fact that xp_cmdshell was ignorant of the return status from the SQLCMD command.  I modified my test code and included a -b parameter in the SQLCMD statement and it worked out perfectly.  I was even able to remove the temp table you suggested for parsing the code.  Thanks again for all the help.

PJ
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now