Disable rollback with implicit_transactions

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 2000 Forums

SQL Server Development (2000)

Disable rollback with implicit_transactions

Author

Topic

BigBadBurrow
Starting Member

4 Posts

Posted - 2007-07-04 : 12:03:22

Hi there,

I have a SQL script like this which is run in a batch with implicit_transactions ON:

CREATE TABLE dbo.w2EDRMSMetaData
(
w2EDRMSMetaDataID integer IDENTITY(100,1) NOT NULL,
CallTypeCode varchar(50) NOT NULL,
tagIdentity varchar(255) NOT NULL,
tagname varchar(255),
tagdescription varchar(255),
MergeTag varchar(255)
)
GO

CREATE TABLE dbo.W2ProcEvidencePoints
(
w2ProcEvidencePointsID integer IDENTITY(100,1) NOT NULL,
ProcId integer NOT NULL,
EvidenceId varchar(12) NOT NULL,
moddate datetime,
moduser varchar(50)
)
GO

and let's say I already have a w2ProcEvidencePoints table then the w2EDRMSMetaData table isn't created as the transaction is rolled back.

If I turn implicit_transactions OFF and run the above SQL then it does create the w2EDRMSMetaData table, I guess because each statement is committed after execution, and it also gives an error saying the W2ProcEvidencePoints table exists. The trouble with this is I don't have the option to rollback anything on the transaction, e.g. if I had other 'insert' statements etc further on.

So, my question is, does anyone know if there is a way that I can have the functionality of implicit_transactions OFF, but still retain the ability to perform rollbacks? I know this can be achieved in Oracle as all DDL statements are always committed, but DML statements can be rolled back.

Many thanks.

Kristen
Test

22859 Posts

Posted - 2007-07-04 : 12:37:19

Trickey one. You can check for the existence of the table, and only create it if it doesn't exist, but sooner or later you are going to come against an error that you aren't expecting - DEADLOCK or somesuch ...

You can run a script file from OSQL with the "-b" parameter which will cause the script to abort if an error is encountered. If your script starts with an explicitly BEGIN TRANSACTION that should have the effect of "all or nothing"

Kristen

BigBadBurrow
Starting Member

4 Posts

Posted - 2007-07-05 : 05:18:25

In fact, I think it might be a bug in SQL Server 2000!! I changed the second (W2ProcEvidencePoints) CREATE to have an error in (notice 'integers'):

CREATE TABLE dbo.w2EDRMSMetaData
(
w2EDRMSMetaDataID integer IDENTITY(100,1) NOT NULL,
CallTypeCode varchar(50) NOT NULL,
tagIdentity varchar(255) NOT NULL,
tagname varchar(255),
tagdescription varchar(255),
MergeTag varchar(255)
)
GO

CREATE TABLE dbo.W2ProcEvidencePoints
(
w2ProcEvidencePointsID integer IDENTITY(100,1) NOT NULL,
ProcId integers NOT NULL,
EvidenceId varchar(12) NOT NULL,
moddate datetime,
moduser varchar(50)
)
GO

Only when this is run, it DOES create the w2EDRMSMetaData table, even though the W2ProcEvidencePoints CREATE failed - that looks to me like how it should operate (i.e. the same way as MySQL and Oracle). It seems odd to me that it would perform a rollback for one type of error, and not for another type of error.

Does anyone have SQL Server 2005 and try running a test to see if it's been fixed?

BigBadBurrow
Starting Member

4 Posts

Posted - 2007-07-05 : 05:52:50

Update: I got access to a SQL Server 2005 DB and it still operates in the same way :-(

Our company has Microsoft support so I might see if I can contact them about it because it looks like a bug to me.

Kristen
Test

22859 Posts

Posted - 2007-07-05 : 06:43:39

"it DOES create the w2EDRMSMetaData table, even though the W2ProcEvidencePoints CREATE failed"

So it should. You have two completely separate transactions - separated by the GO.

If you use an Explicit Transaction you could explicitly rollback after an error, e.g.:

BEGIN TRANSACTION
CREATE TABLE ThisOneIsGood
GO
CREATE TABLE ThisOneIsBad
GO
ROLLBACK - This will work

However, if you execute any additional statement block(s) after an error they will force a rollback:

BEGIN TRANSACTION
CREATE TABLE ThisOneIsGood - This will be rolledback
GO
CREATE TABLE ThisOneIsBad
GO
CREATE TABLE AnotherGoodOne - This will fore a rollback, and this statement will run with NO transaction

GO
ROLLBACK - This will fail - "No transaction to roll back"

Personally I think this is a PITA

but you can get around it with the "-b" parameter, as mentioned above

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources