T-SQL 2000: Four part table name

Question

I don't usually work with linked servers, and so I'm not sure what I'm doing wrong here.

A query like this will work to a linked foxpro server from sql 2000:

EXEC('Select * from openquery(linkedServer, ''select * from linkedTable'')')

However, from researching on the internet, something like this should also work:

Select * from linkedserver...linkedtable

but I receive this error:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error:  Invalid schema or catalog specified for the provider.].

I realize it's supposed to be ServerAlias.Category.Schema.TableName, but if I run sp_ tables _ex on the linked server, for the category for all tables I just get the network path to where the data files are, and the schema is null.

Is this server setup incorrectly? Or is what I'm trying to do not possible?

Answer 1

I think you need to be explicit about resources in the linked server part of the query, for example:

EXEC SomeLinkedServer.Database.dbo.SomeStoredProc

In other words just dotting them out doesn't work in this case, you have to be more specific.

Answer 2

It's actually:

ServerAlias.Catalog.Schema.LinkedTable

Catalog is the database that you're querying on the linked server, and catalog is the catalog of the remote table. So a valid four-part name would look lik this

ServerAlias.AdventureWorks.HumanResources.Employee

or

ServerAlias.MyDB.dbo.MyTable

Answer 3

From MSDN:

Always use fully qualified names when working with objects on linked servers. There is no support for implicit resolution to the dbo owner name for tables in linked servers

You cannot rely on the implicit schema name resolution of the '..' notation for linked servers. For a FoxPro 'server' you're going to have to use the database and schema as they map to their FoxPro counterparts in the driver you use (I think they map to folder and file name, but I have't use a ISAM file driver in more than 10 years now).