Transaction Trouble - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transaction Trouble

Author

Topic

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-16 : 10:03:18

I really can't figure out how to use transactions correctly. My current problem is that when I run the following code it says "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."


USE [PredData]

GO
BEGIN TRANSACTION

GO
/****** Object:  Table [dbo].[Accounts]    Script Date: 03/04/2010 15:12:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Accounts](
	[Id] **********,
	[Username] **********,
	[Password] **********,
	[UserLevel] **********,
 CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UK_USERNAME] UNIQUE NONCLUSTERED 
(
	[Username] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

GO
COMMIT TRANSACTION

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-16 : 10:16:49

And I know the transaction is useless in this case, but that's just because I have a bunch of these files and threw a transaction into all of them. It's not working in the others either.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-18 : 12:12:35

GO is a batch separator. so issue is you've begin tran in one batch and commit in other. if you want all of them in same transaction whats the need of GO s in between

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-06-18 : 13:06:18

We do

BEGIN TRANSACTION
GO

CREATE TABLE
GO

... other DDL stuff ...
GO

COMMIT or ROLLBACK

in our rollout scripts and it works fine.

HOWEVER ... if you execute a statement block (i.e. a section which one/more statements with a single GO at the end of it) then IF that sections fails THEN the very next thing you do MUST be a ROLLBACK.

Otherwise you execute the next statement and there is an implicit rollback of EVERYTHING since the BEGIN TRANS, then the statement executes ... and any more that you run, but they are OUTSIDE a transaction block

I replaced the "********" above with INT, changed COMMIT to ROLLBACK, and it ran fine for me (in Query Analyser)

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-21 : 12:32:01

I forgot to mention that the GO statements are not actually used. When I use the sql file through C#, I split on the GO statements and send each statement on its own. Could this be causing the problem? Maybe I need to through in a GO between files or something?

Kristen
Test

22859 Posts

Posted - 2010-06-21 : 13:45:59

I'm not sure what happens if your application sends "BEGIN TRANSACTION" as a single statement. Sure SQL will begin a transaction, but maybe it will NOT persist it to subsequent commands that you send.

Send

BEGIN TRANSACTION

as one statement, and then

SELECT @@TRANCOUNT

as a second and see what you get for the Transaction Count. If it has not incremented then the BEGIN TRANSACTION statement is not holding a transaction open, and hence COMMIT or ROLLBACK as a transaction will complain that there is no "help open" transaction to be acted on.

Example values:


SET NOCOUNT ON

SELECT [No T/A]=@@TRANCOUNT

BEGIN TRANSACTION

SELECT [T/A Level 1]=@@TRANCOUNT

BEGIN TRANSACTION Level2a

SELECT [T/A Level 2a]=@@TRANCOUNT

SAVE TRANSACTION Level2b

SELECT [T/A Level 2b]=@@TRANCOUNT

ROLLBACK TRANSACTION Level2b

SELECT [T/A Level2a again]=@@TRANCOUNT

COMMIT TRANSACTION Level2a

SELECT [T/A Level1 again]=@@TRANCOUNT

ROLLBACK

SELECT [Ground zero]=@@TRANCOUNT

SET NOCOUNT OFF

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-21 : 16:58:13

Alright I feel a little embarrassed. I never really bothered to look back at the rest of my implementation that was written a long while ago. I was splitting the files by GO statements and then opening a connection for each line, so obviously the transactions wouldn't work. Besides that, there was a simple way for me to just program the transactions in C#, since every file would use them in the same way.

Thanks for all the help anyways though guys.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-21 : 17:03:01

quote:
Originally posted by ScottyDoesKnow

I forgot to mention that the GO statements are not actually used. When I use the sql file through C#, I split on the GO statements and send each statement on its own. Could this be causing the problem? Maybe I need to through in a GO between files or something?

Possibly.

BEGIN TRANSACTION starts a local transaction for the connection issuing the statement (per BOL). So, it's possible that each statement you execute could be on a different connection (depending on how your program is set up).

EDIT: Doh, too late..

Kristen
Test

22859 Posts

Posted - 2010-06-22 : 03:56:07

quote:
Originally posted by ScottyDoesKnow

Alright I feel a little embarrassed ...

Nah, you don't want to worry about that!! but you may need to worry that whenever you have seen "ERROR" in the past then there has been no effective ROLLBACK and whatever partial statements ran will all have been executed, and not rolled back ... Bummer!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources