Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute Process Task - Not returning exit process code from SQLCMD SQL Script Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 1:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:28 AM
Points: 147, Visits: 449
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
Post #1381703
Posted Thursday, November 22, 2012 2:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 7,094, Visits: 12,583
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
Post #1387983
Posted Monday, November 26, 2012 1:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:28 AM
Points: 147, Visits: 449
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.
Post #1388824
Posted Tuesday, December 4, 2012 11:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 7,094, Visits: 12,583
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 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
Post #1392641
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse