|
Home > Archive > microsoft.public.cert.mcdba > May 2003 > Views on Views with schemabinding...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Views on Views with schemabinding...
|
|
| SQL2kGuy 2003-05-13, 10:25 am |
|
The code I am experimenting with is listed below -- I get the following error when using QA...
Server: Msg 4512, Level 16, State 3, Procedure V_TEST1, Line 4
Cannot schema bind view 'V_TEST1' because name 'V_TEST' is invalid for schema binding.
Names must be in two-part format and an object cannot reference itself.
Is it legal to create a View on View with schemabinding, or do I have to go to SQL syntax prison? :-)
-- ==============================
===============
-- Create view on view with schemabinding
-- ==============================
===============
DROP VIEW V_TEST
go
DROP VIEW V_TEST1
go
CREATE VIEW V_TEST
AS
SELECT a.au_id AUTHOR_ID, a.au_lname LAST_NAME, t.title TITLE
FROM dbo.authors a INNER JOIN dbo.titleauthor ta
ON a.au_id = ta.au_id
INNER JOIN dbo.titles t
ON t.title_id = ta.title_id
go
CREATE VIEW V_TEST1
WITH SCHEMABINDING
AS
SELECT AUTHOR_ID, LAST_NAME, TITLE
FROM V_TEST v
WHERE v.LAST_NAME LIKE 'b%'
go
SELECT * FROM V_TEST
go
SELECT * FROM V_TEST1
go
Thanks in advance.
| |
|
| "SQL2kGuy" <SQL2kGuy@hotmail.com> wrote in message
news:uMnGJ$VGDHA.2344@TK2MSFTNGP12.phx.gbl...
> Is it legal to create a View on View with schemabinding, or do I have to
go to SQL syntax prison? :-)
It's legal, you just need to do two things.
First, your error message means that your second view is not using a two
part object name. Even the 'base' view has to be referred to this way.
Change
FROM V_TEST v
to
FROM dbo.V_TEST v
Also, the base view must also be defined with schemabinding - you can't
build a schema-bound view on top of another view that is not schema-bound.
So your first view must be changed like this:
CREATE VIEW V_TEST
WITH SCHEMABINDING
AS
--...
|
|
|
|
|