By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,694 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,694 IT Pros & Developers. It's quick & easy.

Problem creating indexed views with linked servers

P: n/a
Hi, I have a problem, maybe someone can help me.

I'm traing to create a view with a Linked Server

This query works great:

select id, descr
from SERVER.DB.dbo.TABLE

When I tray to create the view:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select id, descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3
Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'
is invalid for schema binding. Names must be in two-part format and an
object cannot reference itself.

So I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3
The number name 'SERVER.DB.dbo.TABLE' contains more than the maximum
number of prefixes. The maximum is 3.
Then I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select a.id, a.descr
from SERVER.DB.dbo.TABLE as A
GO

I Have this error

Server: Msg 4512, Level 16, State 3, Procedure View1, Line 3
Cannot schema bind view 'dbo.View1' because name
'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Names
must be in two-part format and an object cannot reference itself.

This query alone works great:

select a.id, a.descr
from SERVER.DB.dbo.TABLE as A
The names aren't what I describe here (id is not valid without []).

ANY IDEAS?!??!?!

I don't know what else can I do.

I need help!!!

TANKS A LOT!!!!!!!!
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Ariel (ap@data54.com) writes:
Hi, I have a problem, maybe someone can help me.

I'm traing to create a view with a Linked Server


Stop! Take a step back and consider what you are doing!

An indexed view is a materialized view. This means that when an update
is made to a base table, this update is propagated to the view, not
only logically, but also physically.

Now, if that table on the remote server was updated would should happen?
Particularly, what would happen if our local server was unavailble? Or
the particular database was unavailable?

In practice, you cannot even have cross-database indexed views on the
same server, and you run into a stopblock already WITH SCHEMABINDING.
If you could create a schema-bounbd view that referenced another database,
each attempt to drop or rename a databaes, or set a it offline, would
need to check all other databases on the server for SCHEMABINDING
references.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.