DDL in Transactions

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
>--Original Message--
>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=FC=DFmeyer.
>
>.
>|||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 in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> 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.
>|||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
--
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> 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.
>|||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
--
"Jamie Thomson" <jamie.thomson@.int21.com> wrote in message
news:008301c367f9$f337e4b0$a301280a@.phx.gbl...
> 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?|||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
>--Original Message--
>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=3D800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Jens S=FC=DFmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
>news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
>> 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=FC=DFmeyer.
>>
>
>.
>|||> BEGIN TRANSACTION
> GO
> CREATE VIEW TempView AS select * from tempTable
> GO
> COMMIT TRANSACTION
> GO
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.