November 7, 2007 at 8:09 am
I have a script that contains about 10 queries to import records from tables in one db to another. I have the whole script wrapped in Transaction and Try/Catch Block (see below). The script runs fine, but when I try to close the window, I get the message "There are uncommitted transactions. Do you wish to commit these transactions before closing the window? ". Why do I have uncommitted transactions and how do I find out what they are?
BEGIN TRANSACTION
BEGIN TRY
........code, code, code......
........
.......
END TRY
BEGIN CATCH
print ERROR_MESSAGE() + ' Error #' + CAST(ERROR_NUMBER() AS varchar)
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
GO
November 7, 2007 at 8:31 am
Two things to check. One is that you might have the "implicit transaction" setting turned on, which means EVERYTHING it wrapped in a transaction. Check the properties of the server/database.
Otherwise, you can use the DBCC OPENTRAN function to find any uncommitted transactions....
Syntax is here: http://msdn2.microsoft.com/en-us/library/ms182792.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 1:15 pm
I don't see implicit transaction in the properties, how do I check it?
November 8, 2007 at 1:46 pm
Since there are several places that it could have been turned on, why not verify that that is your problem first?
I'd just add a SET IMPLICIT_TRANSACTIONS OFF to the code and see if the behavior goes away. If it does, then check the Connections under Server Properties, under the Tools->Options->Query Execution->SQL Server->ANSI, etc.
If it doesn't, it might help if you posted what the code inside of the transaction is doing, as there might be some nesting going on.
April 22, 2008 at 2:44 pm
This helped me out in a pinch. I was messing with my ANSI settings trying to learn what the ANSI nulls changed, and accidentally turned on implicit transactions. I was debugging a webservice at the time, and couldn't figure out why the DB kept locking the table! I popped the message I was getting when closing my query window and ran across this thread.
Well I found the implicit transactions setting and turned that off. Now I am back on my merry way.
Whew! Thanks guys 😉
September 4, 2008 at 6:58 am
I had the same problem this week, also had a script that called a SP a few times. After running the script and trying to close it the window 'There are uncommitted transactions. Do you wish to commit these transactions before closing the window?' popped up. I checked the SET IMPLICIT_TRANSACTIONS setting but found that it was off. I then checked the code in my SP and noticed that my BEGIN TRANSACTION statement was outside/before of my TRY block, I moved it in the TRY block and saved the SP. My script now runs without any uncommitted transaction windows popping up.
Just thought I would share my solution. 🙂
July 1, 2010 at 12:49 am
Matt Miller (#4) (11/7/2007)
Otherwise, you can use the DBCC OPENTRAN function to find any uncommitted transactions....
Syntax is here: http://msdn2.microsoft.com/en-us/library/ms182792.aspx%5B/quote%5D
[font="Verdana"]But DB is in unstable state, as for my example i have transacted over 2 tables but accidentally the SQL connection was closed.
How to get rid of uncommitted transaction!
Should kill the process from sysprocess?
Thank u?
[/font]
December 7, 2010 at 1:41 am
Thanks mate for that comment. It, helped me out.
What is annoying me is that on MSDN examples, the BEGIN TRANSACTION statement is outside the TRY..CATCH block...
January 31, 2012 at 2:47 am
Hi
Yesterday ,I received the same message like
"There are uncommitted transactions. Do you wish to commit these transactions before closing the window?"
and fully surprised that why it is coming .
While googling I found so many reply to check the setting of Sql server , But I was not satisfied with all the reply then I started looking in to the code.
I changed the format like
BEGIN TRY
BEGIN TRAN T1
Insert statement
Insert statement
Insert statement
Insert statement
COMMIT TRAN T1
END TRY
BEGIN CATCH
if @@ERROR <> 0
ROLLBACK TRAN T1
END CATCH
And after this modification while closing the code it was not throwing any message .
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply