SQL Server Performance

Problems with Distributed Transactions

Discussion in 'General Developer Questions' started by ehausig, Jul 22, 2003.

  1. ehausig New Member

    Hello everyone!<br /><br />I am receiving an error message while attempting to execute a distributed transaction:<br /><br />-----------------------------------------------------------------------------------<br />Server: Msg 7395, Level 16, State 2, Line 6<br />Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF.<br />[OLE/DB provider returned message: Cannot start more transactions on this session.]<br />OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal:<img src='/community/emoticons/emotion-7.gif' alt=':S' />tartTransaction returned 0x8004d013: ISOLEVEL=4096].<br />-----------------------------------------------------------------------------------<br /><br />I am a little confused by the XACT_ABORT option. Books online states, "When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back." However, my "transaction" consists of two phases with some error handling (see below). The queries execute fine without the transaction statements, but I need to ensure that a user doesn't hit the linked table between the time the table's contents are deleted and inserted:<br /><br />---------------------------------------------------------------------------------<br /><br />DECLARE @ReturnValue int<br /><br />BEGIN DISTRIBUTED TRANSACTION transfer_table<br /><br />-- clear out the table on linked server<br />DELETE GPH_DBS1.Sol4_BgCustom.dbo.PEMOrders<br /><br />SET @ReturnValue = @@ERROR<br />IF @ReturnValue &lt;&gt; 0 GOTO OnErrorHandler<br /><br />-- insert new records into table on linked server<br />INSERT INTO GPH_DBS1.Sol4_BgCustom.dbo.PEMOrders<br /><br />SELECT<br />ord_ElementID,<br />job_BillToName,<br />job_BillToNumber,<br />job_CompanyCode,<br />job_CreatedBy,<br />job_CreatedDate,<br />job_CustomerInvoiceContact,<br />job_CustomerName,<br />job_CustomerNumber,<br />job_CustomerPONumber,<br />job_DateCancelled,<br />job_DrugName,<br />job_FinalCompletedDate,<br />job_MarketID,<br />job_Marketer,<br />job_MarketerID,<br />job_OrderNumber,<br />job_Planner,<br />job_ProposalContact,<br />job_Protocol,<br />job_Status,<br />job_Type,<br />job_CompleteShipment,<br />job_CompleteFinishing,<br />job_CompleteDigital,<br />job_CompleteTabs,<br />job_CompleteCollator,<br />job_CompletePress,<br />job_CompletePrePress,<br />job_CompleteOffice,<br />job_OrderDate,<br />job_ProjectNumber,<br /><br />fin_ElementID,<br />fin_Name,<br />fin_CustomerOrderQuantity,<br />fin_FinalFGComplete,<br />fin_FinalFGCompleteDate,<br />fin_FinalOrderQuantityProcessed,<br />fin_FinalSamplesQuantityProcessed,<br />fin_FinalUnitPrice,<br />fin_InventoryEndNumber,<br />fin_InventoryStartNumber,<br />fin_IsLabor,<br />fin_ItemStocked,<br />fin_OrderQuantity,<br />fin_OverrunQuantity,<br />fin_PrimaryBindType,<br />fin_ProductLine,<br />fin_QuantityPerPackage,<br />fin_RepairJobNumber,<br />fin_SamplesQuantity,<br />fin_SKU,<br />fin_SKUType,<br />fin_Type,<br />fin_JobNumber<br />FROM<br />CRS2000_Integration.dbo.PEMOrders WITH (NOLOCK)<br /><br />SET @ReturnValue = @@ERROR<br />IF @ReturnValue &lt;&gt; 0 GOTO OnErrorHandler<br />COMMIT TRANSACTION transfer_table<br /><br />OnErrorHandler:<br />ROLLBACK TRANSACTION transfer_table<br /><br />OnExit:<br />--RETURN @ReturnValue<br /><br />---------------------------------------------------------------------------------<br /><br />Thanks! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Eric<br /><br />PS - In this situation, I would like to not use replication or DTS.
  2. trifunk New Member

    To be honest I've never used the GOTO statement but from the TSQL it looks like once you've committed the transaction you'll fall into the ErrorHandler routine and try and rollback the transaction which might cause the error, try putting a return statement after the commit transaction or goto the OnExit handler.

    Cheers
    Shaun

    World Domination Through Superior Software
  3. satya Moderator

  4. ehausig New Member

    Hehhe....thanks Trifunk....I realized that I had a missing GOTO OnExit after some further testing, but forgot to update the posting.

    Everything appears to work fine after setting XACT_ABORT ON.

    Thanks for the link, Satya. It appears that the problem is more do to the provider?

    "This error indicates that a data modification statement is being attempted against an OLE DB provider when the connection is in an explicit or implicit transaction...If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB Provider."

    If you have a stored procedure that accesses a table located on a linked server, does that automatically create an implicit transaction? I guess I am confused as to where the nested transaction occurs if I only have one BEGIN DISTRIBUTED TRANSACTION statement in the stored proc....or is a nested transaction automatically created by the execution of the second SQL statement in the DISTRIBUTED TRANSACTION block?

    Much appreciated,

    Eric

Share This Page