Using TRANSACTIONS in script - 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

General SQL Server Forums

New to SQL Server Programming

Using TRANSACTIONS in script

Author

Topic

ThierryH
Starting Member

1 Post

Posted - 2010-01-12 : 11:23:39

Hello all,

A while ago I worked at a company, and the DBA person there showed me how to use BEGIN TRANSACTION, COMMIT and ROLLBACK in my sql scripts. Now some time later I work at a different company, and am I responsable for the server. Since I really liked working with Transactions i wanted to use them here as well, but when I add them to a script I get the following notification:

quote:
The BEGIN TRANSACTION SQL construct or statement is not supported.

What am I missing? I only start with BEGIN TRANSACTION, then a simple statement, for example:

 BEGIN TRANSACTION DELETE FROM Prefix
WHERE     (Prefix = 999)

Thanks for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-12 : 11:36:48

Here's in example:

BEGIN TRAN

DELETE FROM Table1 WHERE Column1 = 1

IF @@ERROR <> 0
ROLLBACK TRAN

DELETE FROM Table2 WHERE Column1 = 1

IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN

For SQL Server 2005/2008, you should look into TRY/CATCH also.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:36:56

"The BEGIN TRANSACTION SQL construct or statement is not supported."

Weird. Google has zero entries for that :(

If you just execute

BEGIN TRANSACTION

to you still get that message?

If not there must be something wrong with the actual statement you tried, perhaps the fact that you have the DELETE on the same line as the BEGIN TRANSACTION (but that may have just been how you typed it here, rather than what you executed)

Don't forget to do either COMMIT or ROLLBACK after starting the BEGIN TRANSACTION above, otherwise the database will be locked waiting patiently for you to finish your transaction block

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:39:54

where were trying to execute this? which editor are you using?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-12 : 12:52:16

I have a feeling the OP isn't using SQL Server at the new company.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:54:52

"I have a feeling the OP isn't using SQL Server at the new company"

Crossed my mind too, but I'm pretty sure that the "SQL construct or statement is not supported" part of the error message is SQL Server. I'll Goggle again and be back!

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:56:40

Been to Mr Google :) I still reckon that's the case. My search was

"SQL construct or statement is not supported"

(including the quotes)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources