DDL in Transactions -- sql server dts

Jamie Thomson
8/21/2003 8:52:00 AM

Jens,
Thats what I always thought too. But if I try this:

BEGIN TRANSACTION
create table temptable (col1 int)
ROLLBACK TRANSACTION

the rollback works (i.e. the table isn't created). Try it!=20
There is even a server level setting that indicates=20
whether you can allow DDL in transactions or not (see=20
sp_server_info, number 110).

So, I can have DDL in a transaction but not CREATE VIEW it=20
seems. Why not?

Regards
Jamie

[quoted text, click to view]

Jamie Thomson
8/21/2003 9:56:55 AM

Thanks Gents,
Dan's suggestion works perfectly.

i.e. :
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO

Thanks for the advice.

Regards
Jamie

[quoted text, click to view]

Dan Guzman
8/21/2003 11:12:09 AM

DDL does not issue an implicit commit in SQL Server, although this may
be the case with some other RDBMS vendors.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

[quoted text, click to view]

Dan Guzman
8/21/2003 11:15:22 AM

DDL for textual objects (views, procedures, etc.) must be in a separate
batch so that SQL Server can determine where the CREATE statement ends.
Multiple batches may be executed in a single transaction. Try:

BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

[quoted text, click to view]

Aaron Bertrand - MVP
8/21/2003 1:30:13 PM

[quoted text, click to view]

As an aside, you couldn't do this inside the definition of a stored
procedure; you'd have to use dynamic SQL, I suppose. But that doesn't seem
to be an issue for the OP.

Jacco Schalkwijk
8/21/2003 5:03:39 PM

Hi Jens,

That is not true, DDL does not do an implicit commit and can be included in
a multi statement transaction. The only issue there is, is the error Jamie
got: CREATE VIEW/PROCEDURE and a few others have to be the first statement
in a batch. There is an easy way around that, as a transaction can span
multiple batches:

BEGIN TRAN
GO
CREATE VIEW.....
COMMIT TRAN

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

[quoted text, click to view]

Jens Süßmeyer
8/21/2003 5:47:02 PM

Hello Jamie !

Sorry but this aint the way it goes. DDL Statements such as
alter,create,drop fires an Implicit commit to send the changes directly to
the database.

HTH, Jens Süßmeyer.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : DDL in Transactions