Solved

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

Posted on 2008-10-27
7
874 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
ID: 22818438
are you running this proc by itself or with another tran?
0
 

Author Comment

by:skip1000
ID: 22818512
by itself
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22818902
The problem, me thinks, is that you're using EXEC...do you have to use dynamic SQL?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:skip1000
ID: 22818983
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
ID: 22827862
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
ID: 22827986
would need the following to run in a new session:

SET IMPLICIT_TRANSACTIONS ON
GO
exec myproc

0
 

Author Comment

by:skip1000
ID: 24045391
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 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