SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute Process Task - Not returning exit process code from SQLCMD SQL Script


Execute Process Task - Not returning exit process code from SQLCMD SQL Script

Author
Message
Hughesy
Hughesy
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 497
Hi

I have a SSIS Package developed in 2008 R2 BIDS environment that includes a process task to execute a SQL script against a target database on a remote server on the same network. It references the SQLCMD utility stored locally and uses the -o switch to output the result of the script.

The result of the script running against the target failed correctly and spat out the results to the output file and rolled back the transaction as expected, however, it did not return an exit process code value of 1 back to the calling execute process task and therefore continued the steps downstream assuming success.

I cannot understand why this is happening, particularly, as we have moved the target database to a new server and previously the task would have returned the exit process code correctly and failed the task.

Hope you can advise/assist as this is baffling me.

Thanks

Mark
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14855 Visits: 14396
Hughesy (11/6/2012)
Hi

I have a SSIS Package developed in 2008 R2 BIDS environment that includes a process task to execute a SQL script against a target database on a remote server on the same network. It references the SQLCMD utility stored locally and uses the -o switch to output the result of the script.

The result of the script running against the target failed correctly and spat out the results to the output file and rolled back the transaction as expected, however, it did not return an exit process code value of 1 back to the calling execute process task and therefore continued the steps downstream assuming success.

I cannot understand why this is happening, particularly, as we have moved the target database to a new server and previously the task would have returned the exit process code correctly and failed the task.

Hope you can advise/assist as this is baffling me.

Thanks

Mark

When you say it "failed correctly", are you by chance catching exceptions in a TRY/CATCH block within your SQL code? When you do that, are you re-raising an exception in your CATCH block using RAISERROR with a severity of 11 or higher prior to exiting the script? If not, then sqlcmd may be viewing the batch as having been successful even though you decided to rollback all work and may sent many informational messages to the INFO output stream.

If you capture the exact sqlcmd command-line the SSIS Task is executing and you run it from a cmd shell prompt, what do you get after it finishes when you run this?

ECHO %ERRORLEVEL%


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Hughesy
Hughesy
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 497
Thanks opc! Your we're spot on. I had Raise error statements against each line with the correct level, however when passing down to the error handling at the bottom (2000 I'm afraid so no try catch) I'd missed off the raise error. Once added it works fine. Effectively the individual line raise error statements are needless.

N.B. There are Go statements to split up the dml as there is much of it (eases memory on commit using sql utility such as sqlcmd. which makes the error handling less easy.

Thanks again.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14855 Visits: 14396
Sorry for the delay. I just returned from vacation.

I am hoping you are allowed to upgrade to 2012 soon enough so you can take advantage of the newest error handling language features (e.g. TRY/CATCH and THROW). I for one would never want to go back to the 2000 coding hoops we had to jump through :-D It's so much easier to handle errors than it was in 2000, which it sounds like you are all too aware ;-)

I am happy you got it sorted, thanks for posting back!

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search