skip1000
asked on
Why does my Stored Procedure Begin Tran block require 2 Commit Tran statements?
Hi,
I've been having some problems w/ the following query in a stored procedure (see attached). Worse is that
I need to add this SET IMPLICIT_TRANSACTIONS ON before the sp to make it run, even if the session in query analyzer options already has it checked. Although it works now, I don't understand why I needed to add the SET IMPLICIT_TRANSACTIONS ON explicitly and why I needed 2 commit statements.
I've been having some problems w/ the following query in a stored procedure (see attached). Worse is that
I need to add this SET IMPLICIT_TRANSACTIONS ON before the sp to make it run, even if the session in query analyzer options already has it checked. Although it works now, I don't understand why I needed to add the SET IMPLICIT_TRANSACTIONS ON explicitly and why I needed 2 commit statements.
SET IMPLICIT_TRANSACTIONS ON
GO
Create proc myproc
Begin Tran
Set @sql = /* insert mytable in mssql
select some fields from some openquery stuff from mysql*/
exec (@sql)
Commit Tran -- this one will not commit entirely
Commit Tran -- this one will finish committing
return
chapmandew
are you running this proc by itself or with another tran?
ASKER
by itself
The problem, me thinks, is that you're using EXEC...do you have to use dynamic SQL?
ASKER
that is what i think too bc i don't have this issue if i took out the begin tran block. yes would need to be dynamic, bc i'm passing parameters to an openquery (mysql) statement and query can only be determined at runtime. as a matter of fact, all the problems go away if the transaction block isn't there. the query is just strange looking to me. I never had to use 2 commit tran w/ only 1 begin tran.
ASKER
This is what I found out so far:
If I run this the normal way (SET IMPLICIT_TRANSACTIONS OFF, by default), the sp would not run and would give this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [MySQL][ODBC 5.1 Driver]Optional feature not supported]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTran
If I add SET IMPLICIT_TRANSACTIONS ON, the sp would run normally, but i would need an extra COMMIT TRAN statement to satisfy the SET IMPLICIT_TRANSACTIONS ON statement. This is what I found from BOL:
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE
FETCH
REVOKE
CREATE
GRANT
SELECT
DELETE
INSERT
TRUNCATE TABLE
DROP
OPEN
UPDATE
a bit dodgy workaround, but i don't know what else to do.
If I run this the normal way (SET IMPLICIT_TRANSACTIONS OFF, by default), the sp would not run and would give this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [MySQL][ODBC 5.1 Driver]Optional feature not supported]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTran
If I add SET IMPLICIT_TRANSACTIONS ON, the sp would run normally, but i would need an extra COMMIT TRAN statement to satisfy the SET IMPLICIT_TRANSACTIONS ON statement. This is what I found from BOL:
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE
FETCH
REVOKE
CREATE
GRANT
SELECT
DELETE
INSERT
TRUNCATE TABLE
DROP
OPEN
UPDATE
a bit dodgy workaround, but i don't know what else to do.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have a much better and preferred solution to bypass this type of errors. Handle all OPENQUERY stuff outside of a transaction, such as using a temp table. Then in a transaction, update permanent tables using results from temp tables. This will also increase performance bc all the clunky stuff is done outside of the transaction and won't be logged.