Posted by Alex Kuznetsov on 01/04/07 15:58

--CELKO-- wrote:
> SQL Server is weird on this, but each VIEW statement has to be in a
> batch by itself. The reason is that VIEWs can be built on VIEWs, so
> you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

BEGIN TRANSACTION
go
CREATE VIEW aaa
AS
SELECT 1 n
go
SELECT n FROM aaa
/*
n
-----------
1

(1 row(s) affected)
*/
go
CREATE VIEW aab
AS
SELECT n FROM aaa
go
SELECT n FROM aab
/*
n
-----------
1

(1 row(s) affected)
*/
go
ROLLBACK
go
SELECT n FROM aaa
/*
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'aaa'.
*/
go
DROP VIEW aaa
DROP VIEW aab
/*
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'aaa', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the view 'aab', because it does not exist in the system
catalog.
*/

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Back to original message]