Determining Fully Qualified Name for Linked Servers -- sql server connect

Mike Epprecht (SQL MVP)
9/1/2005 3:22:04 AM

Hi

You are missing the database name.

SELECT * FROM LinkedServer.Database.dbo.TableName
--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

[quoted text, click to view]

J.H. Chrysler
9/1/2005 8:44:41 AM

Mike, thanks for your attempt but Linked Servers do not contain database
names. MSDN states the fully qualified name for a Linked Server
contains:

linked_server_name.catalog.schema.object_name

The question is where do I find the catalog and schema names?

I had assumed that the catalog name comes from the "Catalog" field in
the Linked Server properties but can not verify this and I have no idea
where to find the schema name.

Sue Hoegemeier
9/1/2005 9:00:09 PM

That's just a matter of verbiage depending on what system
you are referring to as the linked server. Some systems
would have catalogs but no database. Some systems would have
schema but no owner. In reference to SQL Server, the
translation of linkedservername.catalog.schema.objectname
would be
linkedservername.database.owner.objectname

-Sue

On Thu, 01 Sep 2005 08:44:41 -0700, J.H. Chrysler
[quoted text, click to view]

J.H. Chrysler
9/1/2005 9:12:39 PM

Sue,

I found what I needed here with this...

1) sp_catalogs 'LinkedServerName' to get list of valid catalogs
2) sp_tables_ex 'LinkedServerName' to get list of tables with schema
name etc.

Found that the sp_catalogs does not work with either the OLEDB driver
for Foxpro or the one for ODBC. The sp_tables_ex displays the following
for ODBC:

Catalog: c:\temp (the location of the .dbf files)
Schema: NULL
Table Name: MyTables (.dbf's in c:\temp)

If I run it for the Linked Server configured with the Foxpro driver I
get:

Catalog: NULL
SCHEMA: NULL
Tables: My Tables (.dbf's in c:\temp)

Running "Select * FROM OLEDB_VFP...charzip" returns the rows from the
table as I would expect, but if I run "Select * FROM ODBC_VFP...charzip"
SQL returns

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.

Same command but adding the catlog name as displayed like this, Select *
FROM ODBC_VFP.[c:\temp]..charzip, returns:

"Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '`c:\temp`\`charzip`' from OLE DB provider
'MSDASQL'. The specified table does not exist.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro
Driver]Command contains unrecognized phrase/keyword.]

I'm baffled and it would be great to have an explanation why one works
and the other does not and why one returns the catalog as a directory
and the other displays NULL.

Sue Hoegemeier
9/2/2005 7:24:49 AM

Not all providers support the interfaces required for some
of the linked server functions. You can't expect the OLEDB
provider and the ODBC driver to support the same functions.
They won't.
Openquery is one way to get around some of the limitations.

-Sue

On Thu, 01 Sep 2005 21:12:39 -0700, J.H. Chrysler
[quoted text, click to view]

Matt Neerincx [MSFT]
9/29/2005 6:35:44 PM

To be quite honest I find this 4 part name guessing game very annoying as
well.

I find it is easiest to just manually try the various combinations of values
until one works.
Or I often give up and use OpenQuery.

Perhaps someone from Microsoft should take a day or two and buckle down and
write a bunch of example 4 part names for the various drivers, that would be
a nice KB to have handy!

--
Matt Neerincx [MSFT]

This posting is provided "AS IS", with no warranties, and confers no rights.

Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server connect

sql server connect : Determining Fully Qualified Name for Linked Servers