create view using data in seperate severs

Hello:

I'd like to create a view on server x which references tables on an

entirely seperate server. Is this possible? Is seems

strange to have to copy the tables over just to create a view. In

the view wizard I can't seem to 'browse' to the tables on the other

server.

The code I'm working with would conceptually be something like this:

select server name.database instance.owner.table.field

from server name.database instance.owner.table

where <field name> like 'xxxx%'

or something along those lines.

Any help would be appreciated!

Thanks.Thats right, you will not be able to browse for them. If you use SQL Server 2005, you can first create synonyms which are browsable in the designer or use the four part name syntax which will make them accessible for the designer to pull your joins together (if you want to). So Doing something like this:

Select * from LinkedServer.DatabaseName.SchemaOrOwner.ObjectName

will bring you the linked object in the design view.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I'm using SQL 2000.

Below is the conceptual code. I continue to get errors with this approach.

SELECT *

FROM servertable.databaseinstance.owner.table INNER JOIN

servertable.databaseinstance.owner.table ON

databaseinstance.owner.table.field = databaseinstance.owner.table.field

INNER JOIN

servertable.databaseinstance.owner.table ON

databaseinstance.owner.table.field = databaseinstance.owner.table.field

INNER JOIN

servertable.databaseinstance.owner.table ON

databaseinstance.owner.table.field = databaseinstance.owner.table.field

INNER JOIN

servertable.databaseinstance.owner.table ON

databaseinstance.owner.table.field = databaseinstance.owner.table.field

Any thoughts on what I might be doing wrong? The server is a linked server. The tables have select permission.

Thanks.|||

I don′t know what servertable means, but it should be something like this here (using Aliases) which makes reading more straight forward.

SELECT * From
SomeLocalTable LT
INNER Join LinkedServername.databasename.owner.table LST
ON LST.Col1 = LT.Col1
(...and so on...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

kubmg wrote:

I'm using SQL 2000.

Below is the conceptual code. I continue to get errors with this approach.

SELECT *
FROM servertable.databaseinstance.owner.table INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.field INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.field INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.field INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.field

Any thoughts on what I might be doing wrong? The server is a linked server. The tables have select permission.

Thanks.

kindly post the error your getting

|||BTW, servertable is the server name, sorry.

When I run the above code I get the following error:

Server: Msg 117, Level 15, State 2, Line 3

The number name 'servername.databaseinstance.owner.table' contains more than the maximum number of prefixes. The maximum is 3.

If I run a simplified query like below I get returned values:

select *

from servername.databaseinstance.owner.table

where field = 'xxxxxxxx'

If I add to the select query the following (which I would like to do in the view), I get the following:

select servername.databaseinstance.owner.table.fieldname

from servername.databaseinstance.owner.table

where field = 'xxxxxxxx'

Server: Msg 117, Level 15, State 2, Line 1

The number name 'servername.databaseinstance.owner.table' contains more than the maximum number of prefixes. The maximum is 3.

The joining that occurs on the tables were defined in the view

design. I suppose they are correct as I do get valid

results.|||

Hi,

select servername.databaseinstance.owner.table.fieldname
from servername.databaseinstance.owner.table
where field = 'xxxxxxxx'

Why don′t you use an alias here or no alias, as you don′t use the full declaration in where part either.

select fieldname
from servername.databaseinstance.owner.table
where field = 'xxxxxxxx'

Or

select t1.fieldname
from servername.databaseinstance.owner.table t1
where t1.field = 'xxxxxxxx'

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de