Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jparker
Posting Yak Master
118 Posts |
 Posted - 2006-07-21 : 08:17:39
|
| If I were to have the following code in a stored procedure without any transaction logic yet I was doing a try catch block on errors and then rolling back at the .NET layer at what stage would an error code be returned if any if I did the following and the first insert failed?INSERT INTO JobNode (JobNodeGUID, JobNodeTypeID, CompletionStatusID, ParentJobNodeGUID, JobTypeGUID, Title, DisplayOrder, IsCurrent, IsTemplate, DateStampStart, LastAuditEntryGUID, CopyOfJobNodeGUID, JobTypeJobNodeGUID) VALUES (@JobNodeGUID, @JobNodeTypeID, @CompletionStatusID, @ParentJobNodeGUID, @JobTypeGUID, @Title, @DisplayOrder, 1, @IsTemplate, @DateStampStart, @LastAuditEntryGUID, @CopyOfJobNodeGUID, @JobTypeJobNodeGUID) INSERT INTO Job (JobNodeGUID, JobID, SalesStatusID, CustomerPartyGUID, OrderInitiatorPartyGUID, PurchaserPartyGUID, DeliveryReceiverPartyGUID, AccountManagerPartyGUID, ProductionManagerPartyGUID, DateEstimateRequired, DateDeliveryRequired, VendorPartyGUID) VALUES (@JobNodeGUID, @JobID, @SalesStatusID, @CustomerPartyGUID, @OrderInitiatorPartyGUID, @PurchaserPartyGUID, @DeliveryReceiverPartyGUID, @AccountManagerPartyGUID, @ProductionManagerPartyGUID, @DateEstimateRequired, @DateDeliveryRequired, @VendorPartyGUID) RETURNCould you please let me know whether the insert would end the stored procedure and return to the catch block after the failure or complete both inserts before completing the return |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-21 : 08:30:38
|
| The failure on the first insert would cause it to jumpo into the catch block.It's easy to set up a test - why not try it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
gugarc
Starting Member
17 Posts |
Posted - 2006-07-21 : 08:42:02
|
| When using sql 2000, see the xact_abort flag. Default is off.In this situation, a runtime error will not abort the job, so, when the first insert fails, the procedure will try to execute the next commnad - and so on). The second will also fail. But no error will be raised.Following microsoft suggestion (MCP 70229)you have to see the @@ERROR variable after each command and raise an error when @@error > 0. So the try catch outside can handle it. (So when working with nested stored procedures). |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-21 : 09:01:15
|
| This is v2005 and has a try catch block. Very different from error processing in v2000.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
transactions