Solved

Help with sp_ExecuteSQL and xp_cmdshell

Posted on 2008-06-11
8
1,953 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 500 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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21772165
Oh, and check out the number of single quotes...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Returning data in a different format 8 46
SQL Error in WHERE Clause 5 42
SQL Query resolving a string conversion issue 26 39
SSRS troubles 4 20
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now