?
Solved

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

Posted on 2008-10-27
7
Medium Priority
?
883 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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