Solved

Why does my Stored Procedure Begin Tran block require 2 Commit Tran statements?

Posted on 2008-10-27
7
870 Views
Last Modified: 2012-05-05
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.

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

Open in new window

0
Comment
Question by:skip1000
  • 5
  • 2
7 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
are you running this proc by itself or with another tran?
0
 

Author Comment

by:skip1000
Comment Utility
by itself
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
The problem, me thinks, is that you're using EXEC...do you have to use dynamic SQL?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:skip1000
Comment Utility
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.
0
 

Author Comment

by:skip1000
Comment Utility
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::JoinTransaction returned 0x8004d00a].

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.
0
 

Accepted Solution

by:
skip1000 earned 0 total points
Comment Utility
would need the following to run in a new session:

SET IMPLICIT_TRANSACTIONS ON
GO
exec myproc

0
 

Author Comment

by:skip1000
Comment Utility
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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now