September 1, 2004 at 1:21 pm
Received this following error:
OLEDB Error Code = 266
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
The user continued to enter data through out the length of their session and all of the data that the user entered after receiving the above error was rolled backed. I am assuming that all of the transactions after the above error were nested and then a rollback was issued through out this user’s session causing all of this uncommitted data to be deleted.
Does anyone know how you could stop this from happening once you receive the above error or is it too late and has all of the data been rolled back?
I know that we have to fix this stored procedure and I have found a lot of great articles in this forum on this issue but I am wondering if we could stop data from being deleted if we commit all data for this user when we receive the above error.
If we name our transactions or if we do not use Begin Transaction and Commit in our stored procedures will this stop this from happening.
I appreciate any help that you could provide.
Thank You
September 1, 2004 at 2:15 pm
Transactions can span batches (a dynamic SQL submission or up to a GO). Apparently OLEDB did some integrity checking and assumed you didn't want to leave a transaction open after the batch finished (more a warning than an error). As far as SQL Server is concerned its not really an "error" to my knowledge. To accomplish what you are asking you should simply need to run another batch when you detect that error with either a ROLLBACK or COMMIT as you prefer.
The problems with your other subsequent data was that they became nested transactions. Since you never, ever, committed the parent transaction I'm guessing that somewhere within the connection teardown process it eventually decided to ROLLBACK the open parent transaction which would also rollback the nested transactions. So, I figure that a commit prior to teardown would have retained the data as well by closing down the parent transaction.
September 2, 2004 at 1:50 am
Taking a guess your app code may have been
EXECUTE mySproc1
and your sproc may have been something like
create mySproc1
as
BEGIN TRAN
Insert something ...
GO -- end of mySproc1
If this was the case, and your sproc / code left a transaction open that wasn't created through the ado object, ado will probably throw that error. If you don't specifically commit the transaction, SQL Server will roll it back.
You can see how many transactions you have open with SELECT @@TRANCOUNT
Ideally, your sproc should be self contained, so that any transactions it opens, should be committed or rolledback before the end of the sproc. IF you need a tranasaction to cover more than a single sproc, open it as far up the chain as you can. If you are using ADO, use the Connection.BeginTransaction method to start the transaction. That way ADO is in control, and you can to you error checking up there.
In a self contained sproc you might have something like
create mySproc2
as
BEGIN TRAN
Insert something...
IF @@ERROR = 0 -- Everything ok
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
RETURN 1 -- Failure code
END
GO
Which specifically states that the transaction is committed or rolled back before the end of the sproc.
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply