Hello,
I know that it is possible to put DDL statements (i.e.
CREATE TABLE, DROP TABLE etc...) in transactions but I
have found a peculiarity that I am trying to get around.

I issued the following:

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

It gave the following error message:
Server: Msg111, level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch

Can anyone find a way around this using the simple T-SQL
code above?

Thanks in advance
Jamie


P.S. Why is there no microsoft.public.sqlserver.tsql
newsgroup?

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.

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!
There is even a server level setting that indicates
whether you can allow DDL in transactions or not (see
sp_server_info, number 110).

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

Regards
Jamie

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.


"Jens Süßmeyer" <jsuessmeyer@(Remove_ME]web.de> wrote

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....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Jens Süßmeyer" <jsuessmeyer@(Remove_ME]web.de> wrote

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....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Jamie Thomson" <jamie.thomson (AT) int21 (DOT) com> wrote

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

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.