Solved

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

Posted on 2008-10-27
7
878 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

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

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.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

718 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