473,554 Members | 3,224 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem creating indexed views with linked servers

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.T ABLE

When I tray to create the view:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select id, descr
from SERVER.DB.dbo.T ABLE
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.T ABLE.id, SERVER.DB.dbo.T ABLE.descr
from SERVER.DB.dbo.T ABLE
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.T ABLE 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.osde rrhh.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.T ABLE 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
1 10119
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****@sommarsk og.se

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
4509
by: Mike J | last post by:
I have several stored procedures that run fine from my SQL Server database (via the exec command.), though when I call these procedures from my web application, they do not complete. I have other procedures that in fact do run fine through my web application though, so I do not believe its a front-end problem. The procedures only take about...
6
3044
by: wireless | last post by:
I've had my SQL server database running for two years now without a problem. However, just today one of the main tables started returning an error. The table is contained within a database called engineering. I back it up once a week and the file size is up to about 40 MB. The error returned when trying to return data from one table...
2
7334
by: Avirneni | last post by:
With my understanding of indexed views and according to books I read "indexed views" are supposed to perform much better than "temp tables" (temp table having primary key and indexed view with clustered index on the same keys). But when I tried in my system I am getting opposite results. With Indexed Views it takes 3 times more time. Any...
3
1803
by: noelwatson | last post by:
I am looking to create a constraint on a table that allows multiple nulls but all non-nulls must be unique. I found the following script http://www.windowsitpro.com/Files/09/21293/Listing_01.txt that works fine, but the following line CREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)
1
1323
by: Little PussyCat | last post by:
Hello, At work we have various servers, therefore we have various DTS Interfaces which transfer data between them. However when trying to save a new View to one server which gets its data from another server nothing happens, I get an hourglass and nothing else. Then I have to exit out of SQL Enterprise Manager and then no-one can access...
20
2656
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about 25-50). This allows each user to have their own set of selections. The selections table has three fields: ID (int), Sel (bit), MachName (varchar). ID...
13
1768
by: Lyle Fairfield | last post by:
I have a table: Schools, and another table: Teachers as one teacher may be assigned to more than one school I link these with a third table: SchoolTeachers I index (unique) this linking table on SchoolID and TeacherID to prevent assigning a teacher to a school more than once. So far, so good.
0
953
by: savvy | last post by:
I searching using Keywords in MS Word and pdf documents for which i have used the Index Server and linked with SQL Server but when i am running the stored procedure thro my webpage i'm getting these errors. And when i run this code in Query Analyzer its giving expeceted results. I dont know where the problem is can anyone help please . I'm...
6
7037
by: Neil | last post by:
I had a strange situation with a view in SQL 7, that I could use some input on. I had a very simple view -- select a, b, c from table1 where x=y and z=q. Field a in table1 originally was varchar 70. A long time ago I changed it to varchar 95. I used this view as an ODBC linked table in an Access MDB. Recently, there was one row which has...
0
7493
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7775
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8008
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7854
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6113
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5133
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3537
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1992
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
808
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.