Ms Sql Error 266
Contents |
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home xact_state() » SQL Server 2005 » SQL Server 2005 General Discussion » Ideas
@@trancount
about Error 266 "Transaction count... Ideas about Error 266 "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count =... Rate Topic Display Mode Topic Options Author Message DataDogDataDog Posted Thursday, November 18, 2010 4:29 AM SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, October 29, 2013 6:02 AM Points: 147, Visits: 426 I had to deal with this whilst writing a unit test framework for my TSQLCreate simple procedure that performs rollback:create proc dbo.spTestasbegin begin tran rollback tranendExecute the procedure:begin tranexec dbo.spTestif (xact_state() <> 0) rollback tranEnjoy the error:Msg 266, Level 16, State 2, Procedure spTest, Line 0Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.This feature can be very useful as a single nested rollback can abort not only the transaction but any logic (that would be rolled back anyway)But it has some serious consequences - if you have a procedure that performs a rollback and returns a value ...create proc dbo.spTestasbegin begin tran rollback tran return 666endand execute this:declare @return intbegin try begin tran exec @return = dbo.spTest if (xact_state() <> 0) rollback tranend trybegin catch if (xact_state() <> 0) rollback tran select @returnend catchIt will *always* return 0In my stored procedures I use:return 0 -- System Errorreturn 1 -- Successreturn 2 -- Logic Errorthere is a huge difference between a system and logic error!logic errors are caused by the user's actions and they should sort themselves out (and not call me)system errors are NOT the user's fault (they SHOULD call me)The problem with the 266 error is that it CONVERTS logic errors into system errors ...But what about OUTPUT parameters?cr
ASP.NET Community Standup Forums Help Home/ASP.NET Forums/Data Access/DataSource Controls - SqlDataSource, ObjectDataSource, etc/Error 266 - I'm confused, only one transaction with COMMIT and ROLLBA... Error 266 - I'm confused, only one transaction with COMMIT and ROLLBACK [Answered]RSS 4 replies Last post Jun 18, 2008 09:37 AM by trnelson ‹ Previous Thread|Next Thread › Print Share Twitter Facebook Email Shortcuts Active Threads Unanswered Threads Unresolved Threads Support Options Advanced Search Reply trnelson Member 2 Points 7 Posts Error 266 - I'm confused, only one transaction with COMMIT and ROLLBACK Jun 15, 2008 10:47 PM|trnelson|LINK I have a transaction that calls one other sproc and also executes another set of queries, http://www.sqlservercentral.com/Forums/Topic1022749-149-1.aspx but for some reason I'm getting error 266: "Msg 266, Level 16, State 2, Procedure AddUserHaveTag, Line 26. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." There is NO transaction in the sproc AddTag. It is also included below. Here is the sproc with the transaction: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[AddUserHaveTag] ( @UserHaveID int, http://forums.asp.net/t/1275844.aspx?Error+266+I+m+confused+only+one+transaction+with+COMMIT+and+ROLLBACK @Tag varchar(24), @UserHaveTagExists bit OUTPUT ) AS SET NOCOUNT OFF DECLARE @ErrorCode int DECLARE @TagID int DECLARE @TagExists bit BEGIN TRAN -- Call proc to add tag to Tags table EXEC AddTag @Tag, @TagID OUTPUT, @TagExists OUTPUT -- Check for errors IF @ErrorCode <> 0 GOTO ERROR -- Check for existing record, otherwise insert IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID) BEGIN SET @UserHaveTagExists = 1 RETURN 0 END ELSE BEGIN INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID) SET @UserHaveTagExists = 0 END -- Check for errors IF @ErrorCode <> 0 GOTO ERROR COMMIT TRAN ERROR: IF (@ErrorCode <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN ENDHere is the AddTag sproc:set ANSI_NULLS ONset QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[AddTag] ( @Tag varchar(24), @TagID int OUTPUT, @TagExists bit OUTPUT )AS SET NOCOUNT OFF IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag) BEGIN SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag SET @TagExists = 1 RETURN 0 END ELSE BEGIN INSERT INTO Tags (Tag) VALUES (@Tag) SET @TagID = SCOPE_IDENTITY() SET @TagExists = 0 ENDAny advice?Also if you see any glaring errors or things I could be doing better, I'm open to suggestions. I'm fairly new to sprocs and transactions.Thanks,Travis sproc transaction sqlserv
UsConsultingConsulting HomeServices & TechnologiesVFP ConversionAzure & Other CloudsEnergy SoftwareContact UsStaffingStaffing HomeLooking for Staff?Looking for Work?Contact UsMagazineMagazine HomeAll IssuesSubscribeMy (Digital) MagazinesWhere is my Magazine?My Subscriber AccountAdvertiseWriteFrameworkFramework HomeGet Started & DocumentationDownloadSupport & ServicesTrainingTraining HomeClassesMentoringState of .NETLunch with CODECode CampsASP.NET MVCDevNet TrainingVFP ConversionVFP Conversion HomeServicesToolsArticlesFox End of LifeContact http://www.codemag.com/article/0305111 UsSign in! Advertisement: Handling SQL Server Errors in Nested Procedures By Talmage, Ron Tweet Talmage, Ron Ron Talmage is a mentor and co-founder of Solid Quality Mentors. He is a SQL Server MVP, a PASS Regional Mentor, and current president of the Pacific Northwest SQL Server Users Group. He has been writing white papers and articles on SQL Server since way ms sql back when. This article was published in: This article was filed under: VFP and SQL Server SQL Server Data Advertisement: Basic error handling in SQL Server's programming language, Transact-SQL, is straightforward.But when you nest calls to stored procedures, and the procedures have SQL transactions, error handling becomes much more complex. In this article I'll show you some tried-and-true models for how to ms sql error handle errors in nested stored procedures with transactions.Over the past year I've worked on two projects that needed a strategy for handling errors in stored procedures where the procedures used transactions. My task was to come up with a way to gracefully exit from the stored procedures when non-fatal errors were detected so I could roll back the transaction. I'll present the result of that work in the two models you'll learn about in this article.So how do you handle errors in your code when an error occurs? Unless it encounters a broken connection, SQL Server will return an error to the client application. When you work directly with your own client or middle-tier code, you have much more control over how you handle errors. So you could just issue all your queries to SQL Server discretely from your client code and let SQL Server errors throw you into your error-catching logic, thereby keeping all your error handling in your own calling code. However, encapsulating database-oriented code in SQL Server stored procedures offers a more efficient and elegant solution. In those cas