Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with sp_ExecuteSQL and xp_cmdshell

Posted on 2008-06-11
8
Medium Priority
?
2,063 Views
Last Modified: 2008-06-12
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
Comment
Question by:PJan8724
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:srnar
ID: 21767825
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
 
LVL 1

Author Comment

by:PJan8724
ID: 21770796
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 21772155
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 21772165
Oh, and check out the number of single quotes...
0
 
LVL 1

Author Comment

by:PJan8724
ID: 21772834
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21772957
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21773003
OR, try

exec @execResult = master..xp_cmdshell @cmd

in line 16 and comment out the other setting of @execresult
0
 
LVL 1

Author Comment

by:PJan8724
ID: 21773925
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question