inxdexed views

Question

inxdexed views

timscronin

SSCertifiable

Points: 6851
More actions
October 8, 2010 at 9:34 am

#230212

Can these not go across databases? I tried below
CREATE VIEW VT WITH SCHEMABINDING AS SELECT
p.patid,p.facid,p.patstatus,f.mardbname
from dbo.patients p inner join fac.dbo.facilities f on p.facid = f.facid
and I get
Msg 4512, Level 16, State 3, Procedure VT, Line 1
Cannot schema bind view 'VT' because name 'fac.dbo.facilities' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you are correct; a schemabound vie can only reference objects in the same database; that jump to the parallel table in the database "fac" is preventing the schemabinding...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

timscronin SSCertifiable Points: 6851 More actions · Answer 2

Is an indexed view allowed without the schemabinding?

Lowell SSC Guru Points: 323518 More actions · Answer 3

I am very sure it is not; an indexed view requires, among other things, that all the tables be schemabound...but that doesn't prevent you from creating a view that is not index.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!