Stored Procedure Question
I have solved a problem in developing a stored procedure but don't fully
understand the solution. I am hoping that someone here can shed more light
on what is happening for me.
I have a situation where I have two databases. Database A is using a link
to database B (on a different server) to query a table. Occasionally, Database
A will need to update that very same table using an UPDATE SQL statement
(nothing complicated). Now, when this update is done inside of a transaction,
I receive the error "OLE/DB Provider Returned Message: Cannot start more
transactions on this session". The error number is 7392. I even get this
message when running the same transaction within the Query Analyzer. However,
when the UPDATE is attempted without specifying a BEGIN TRAN and COMMIT TRAN
the UPDATE SQL works fine.
The Solution that I don't understand.
What I found from the help files is that if I prefix the stored procedure
with the statement "SET XACT_ABORT ON", it will function normally with the
BEGIN TRAN and COMMIT TRAN statements. According to the Books On Line:
"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, and the OLE DB provider does not support nested transactions.
SQL Server requires this support so that, on certain error conditions, it
can terminate the effects of the data modification statement while continuing
with the transaction."
To me, this means that when using making modifications to table available
through the Linked Server there is always an implied transaction and that
nested transactions are not allowed. However, I can perform a simple SELECT
statement within a transaction against a table only available throught linked
server and it works just fine.
Can someone please explain to me what is going on?
I am sorry the length of the note. However, this is all somewhat confusing
to me and I am not sure where to begin.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL