.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :SharePoint


We have Sharepoint 2007 SP1 databases on SQL Server 2005 SP3 x64. Users are uploading and viewing the documents with NO issues but we are frequently getting the below error in share point logs:

08/01/2010 08:21:33.11  w3wp.exe (0x0DA4)                        0x2168 Windows SharePoint Services    Database                       6f8g Unexpected Unexpected query execution failure, error code 266. Additional error information from SQL Server is included below. "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." Query text (if available): "DECLARE @@S uniqueidentifier; DECLARE @@DocId uniqueidentifier; DECLARE @@Level tinyint; DECLARE @@DocUIVersion int; DECLARE @@DoclibRowId int; DECLARE @@iRet int;DECLARE @DN nvarchar(256); DECLARE @LN nvarchar(128); DECLARE @FU nvarchar(260); SET @@Level=2 SET @@DocUIVersion =1 SET @@DocId='9FA36FDF-00FF-4810-92CE-A5ED5671A0C9' SET @@S='6780CE0A-2C92-463D-B86D-D8ACBB72E065'; EXEC @@DoclibRowId = proc_GenerateNextId 'C145E0B3-9F7C-4E0F-9644-7DB0AEE53EDF','1E9F697F-4032

View Complete Post

More Related Resource Links

Nested transactions, rollback causes error "Transaction count after EXECUTE indicates that a COMMIT



I have a stored proc that does something that requires a transaction, and so I've put in the begin tran, commit, and rollback on error logic in it. That works as expected. But what if I now want to use the proc in the context of a larger transaction, such as calling it multiple times or doing other things as part of the same transaction? This seems impossible to achieve without either raising an error (throwing an exception) or having SQL Server do so on my behalf, rather unhelpfully.

For instance, I wanted to test a proc and reckoned a simple way to do so would be to start a transaction, execute the procedure, and roll back the transaction if the procedure didn't abort it:

begin transaction 
exec someProc
if XACT_STATE() = 1 rollback

This works if someProc doesn't encounter any errors (so it begins and commits a transaction and the transaction count is 1 at entry and exit of the proc), but crashes and burns if the procedure rolls back the transaction, producing SQL error

Msg 266, Level 16, State 2, Procedure ExecuteImport, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

I don't understand why that is. To my mind, it is perfectly normal that the transaction count can go from any level at entry to ze

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

Doesn't restart sql server rollback uncommitted transaction?

hi, i executed a long-running sql in the management studio. The sql updates many rows and all updates are within a transaction. After runing 30 mins, the hard disk is full. That's because of the uncommitted updates. Then, i restarted the sql server because I thought after restarting, the uncommitted transaction is rollback and the occupied hard disk space is also released. But i found that the transaction is rollbacked but the hard disk space is not released. Why the hard disk space is not released? how can i release the disk space now? Thank you for your help.

Rollback Transaction or Rollback Work

Hi all, I run a delete statement,  but it deleted wrong rows that I need in my table. I didn't start with begin transaction statement, is there a way to bring these data back using any Rollback statement or any other ways? Thank you.  

Count no for Transaction

Hi, Below is my table data.I want the result as 3. col1 ------ 4 4 4 66 66 88 Result as count ---- 3 How to write a Query without using Distinct . PS.Shakeer Hussain Hyderabad

Implementing Transaction in SSIS Package - [Execute SQL Task] Error: Failed to acquire connection "<

I have a simple SSIS package with three "Execute SQL Tasks". I am using ADO.Net Connection to execute SPs on a DB server. When I execute this package It works fine. So far so good. Now, I need to implement transation on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error. [Execute SQL Task] Error: Failed to acquire connection "NYCDB0008.Export". Connection may not be configured correctly or you may not have the right permissions on this connection. "NYCDB0008.Export" is the name of the ADO.Net connection. I have been hunting for any solution but all in vain. I have tried changing all DTC settings on the dev as well as Database server. Please respond if anyone has any solution. Thanks! Anand

Transaction does not execute

I'm trying to run the following transcation but everytime I run it, it keeps executing but nothing was happening, is there anything that I'm doing wrong?


SELECT sd.OrderID ,sd.ProductID,sd.BookedDate,sd.LBooked_Date,sd.SLeadTime,sd.CustDel
FROM supplydates sc JOIN supplychaindates sd ON sc.OrderID = sd.OrderID
(sc.ProductID <> sd.ProductID
sc.BookedDate <> sd.BookedDate
sc.LBooked_Date <> sd.LBooked_Date
sc.SLead_Time <> sd.SLead_Time
sc.CustDel <> sd.CustDel
sc.Release_Date <> sd.Release_Date
sc.Print_Date <> sd.Print_Date
sc.RePrint_Date <> sd.RePrint_Date
sc.Cancel_Date <> sd.Cancel_Date
sc.Required_Date <> sd.Required_Date
sc.Earliest_Date <> sd.Earliest_Date
sc.SupAckNo <> sd.SupAckNo
sc.Ackduedate <> sd.Ackduedate
sc.PreBookDelDate <> sd.PreBookDelDate
sc.ActDelDate <> sd.ActDelDate
sc.PreBookDelDate <> sd.PreBookDelDate
sc.CustActDelDate <> sd.CustActDelDate)
sd.Active_To IS

How to execute workflows for business entities within a singular transaction



Let's assume we have a simple usual situation, we have these business entities:

  1. Sales man.
  2. Customer.
  3. ProductInstance.

Let's assume that each business entity has its own workflow defining it's lifecycle. Let's assume that a sales man's selling of a productInstance to a customer should be done by some advancement of each of the three business entities' workflows. It's obvious these advancements should be done within a singular transaction.

Can't you provide a hint on how to establish the transaction and how to scope the advancement of each of the three workflows by the given singular transaction, please?


Best regards,

Hwenrik Dahl


transaction does not rollback


I've just had a real WTF moment, so I'll try not to be too incoherent...

I have a transaction coded up to do inserts in header/detail tables.  I nested everything in a transaction, thinking that if there was a problem, the transaction would rollback and, while it would commit my new data, it would nevertheless leave the database in a consistent state.  But it seems to not have worked...

Here's the transaction:

		DECLARE	@DeliveryPointID	INT				= 1,
				@TrailerID			INT				= NULL,
				@LoadPercent		DECIMAL(8, 4)	= 100.0
		INSERT INTO dbo.Slips (DeliveryPointID, GrowerContractID, TrailerID, PackRequestID, GrossWeight, LoadPercent, ReceivedDate, VarietyID, Notes)
		VALUES (@DeliveryPointID, @ContractID, @TrailerID, @PackRequestID, @GrossWeight, @LoadPercent, @ReceivedDate, @VarietyID, @Notes)
		SELECT @SlipID = 

how store the index number till the commit transaction?


hi fiends,

i have sp that store data in three tables, t1, t2, t3

till the transaction is commited no record will be inserted.

if i store a value to the t1 table, i can fetch the idx number using scope_idenity() but not physcial record will inserted till commit tranaction.

How do i save this idx number so that i can keep it till it commits transaction. imust store this globally becuase this sp will called by a client application consequtivly four times. but it does not commit tranaction till it complete four iterations. therefore i need to save the idx generated from the first call to it?

i was thinking about creating a temp table and store the idx in there. but

1. does create seperate temp table for each call tothe sp, (think interms of multi user enviorement)

2. what sort columns that i should create in thsi temp tabel

3. is temp table a good option?


Transaction failed to rollback


I have a client which is running SQL 2005 Express on a Windows 2003 box. The system is very much barebones with extremely small storage space (10GB total!)

The other day I had done a column modification for them on one of their larger tables. Unknowingly, I was up against their threshold for diskspace and was unable to complete the transaction. I received the following:

Could not allocate space for object 'dbo.Tmp_jobresumes' in database '###' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Thereafter, the the table could not be found. Neither the original table name nor the tmp table name.This is a huge issue as I found out that they also do not have a backup of the database where I could start to restore our log files up until the point of failure.

I'm in a major pinch because the tables contains all of their assets for candidates (they are a staffing firm) over the past 7 years and I cannot seem to figure out how best to retrieve this information - in part due to the fact that I am reluctant to make any moves (i.e. restarting server, etc.) as I do not want to loose any change of recovery unless I know the implications fully. I know that the data is st

How to rollback transaction in wcf method if CommunicationException occured?



I have a simple wcf-service (wsHttpBinding) hosted in IIS which inserts data into database and returns number of inserted records and collection of errors (such as foreign key problems while inserting the data).

int ProcessCustomers(Customer[] customers, out ErrorLog[] errorLog)
This is a implementation:
public int ProcessCustomers(Customer[] customers, out ErrorLog[] errorLog)
 using (var tran = new TransactionScope())
 //insert data into database
 return cnt;

 And i have a very simple wcf-client which invokes this method and trying to catch exceptions.

 int inserted = client.ProcessCustomer(customers, out errorLogs);
catch (TimeException timeEx)
 //write to log
catch (FaultException faultEx)
 //write to log
catch (CommunicationException commEx)
 //write to log
catch (Exception ex)
 //write to log

//close the proxy

Rollback transaction for calling web services


Dear all,


I have the web form whcih processing several web services calls.

If one call failed, it will rollback transactions. But it cannot.

Due to the web services will complete for each call.

I am using sql connection and sql command.

Is it possible to do this??? 

SQL 2008 Maintenance plans missing Truncate transaction log checkbox



I am a noob SQL 2008 DBA and had a quick question on the maintenance plans. Under the database/tasks/backup function under options there is checkbox to truncate logs when they are being backed up. Yet when I create a transaction log backup job using the maintenance wizard the checkbox is missing for that option.  Why is that missing? 

Thanks, Jules

error message on mismatch in transaction count when nesting stored procedures


Is this a purely informational message that I can ignore?  (i.e. are there no other effects from this message? is this really an "error"?):
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I'd like to do my rollback in a called generic error logging sproc if I haven't already handled the rollback, in order to be sure not to leave any open transactions; so in this case, the exit of the calling procedure will have a different count (zero) than whatever it began with. 

SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

Multiple web methods in one transaction


Hi everyone

Sorry I am kinda new in web services, so it may sound a bit newbish question.

We have a web service that comunicates with the database and exposes web methods that enables the application to access the database. Each method does only one operation (Insert, Update or Delete). What I need to do is create a transaction that will be consisted of multiple calls to the database, meaning multiple calls of more then one web methods. Can I do that? (I read that if the web method does not throw exception - or ContextUtil.SetAbort() is not called - the transaction will be commited automatically when the web method finishes executing, which I don't want to happen because I want to call another web method in the same transaction, and then manually commit the transaction)

Thank you in advance.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend