Solved

Help with sp_ExecuteSQL and xp_cmdshell

Posted on 2008-06-11
8
1,941 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

746 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

14 Experts available now in Live!

Get 1:1 Help Now