Indexed Views that contains a linked server

Question

Indexed Views that contains a linked server

Michael Lee-158245

SSC Eights!

Points: 980
More actions
December 17, 2010 at 9:49 am

#232542

Hi All,
I've created a view that contains data on our clients from 4 databases and 2 of them are on a linked server(Separate servers). I wanted to create a unique index on the view so I inserted the following statement in the create view script:
WITH SCHEMABINDING
Here is a stripted down version of the view.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[AllProgramPatients] WITH SCHEMABINDING
AS
SELECT PatientID, '3' as ProgramId
FROM Attend.dbo.PATIENTS
union
SELECT PatientID, '4' as ProgramId
FROM Indep.dbo.PATIENTS
union
SELECT PatientID, '1' as ProgramId
FROM [RemoteServ1].BP.dbo.PATIENTS
Union
SELECT PatientID, '2' as ProgramId
FROM [RemoteServ2].Brook2.dbo.PATIENTS
When I run this script I get the following error:
Msg 4512, Level 16, State 3, Procedure AllProgramPatients, Line 3
Cannot schema bind view 'dbo.AllProgramPatients' because name 'Attend.dbo.PATIENTS' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
Are Indexed views allowed when we are working with tables outside the current db or tables from a linked server? If it can be done, what am I missing. Thanks.

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

for an index to be created, it has to be on an object that exists inside the database; that way constraints can be enforced.

constraints cannot be created accross db boundaries.; so you could index a local copy of the data from the linked server, but not against the linked server's data itself.

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!

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 2

Is there a way to create a local table/View of the database tables in the other databases and have the local table updated when there are Insert/updates to the other DB tables. I'm hoping that I don't have to create triggers to do this, but if I have to then thats what A'll do. I'm hoping for a solution that will allow me to set it up once and have all the updated sent to the Local db. Thanks

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 3

One last thing. The reason I wanted to create a index or Primary key on the original view was because I have 4 divisions in our company that I query data from and to distinguish the divisions I have added a ProgramId to each query. The Key should contain the PatientId and ProgramId.

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 4

Could Replication help me out here?

I've never had to use replication at this point, so please bare with me. I'm starting to read up on the topic. It looks as if this may be the solution, correct me if I'm wrong please. If I'm understanding the topic so far. I should be able to create a Local table in the current Application db and set it up as a "Publisher". The db's I want to get the data from would be setup as "Distributors". Is this correct so far?

Questions on replication:

Can a query be created to decide what data is actually sent to the "Publisher", or does it require that all fields be sent over?

Can the local application Query against the "Publisher" table like any other table or view?

The remote sites (Distributors) will not need access to the replicated data as they will be querying against there local data. Will this be a problem?