Re: Determining Fully Qualified Name for Linked Servers

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
<opium89@xxxxxxxxxxx> wrote:

>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.
>
>
>
>
>
>
>
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

.

Relevant Pages

  • Progress Linked Server
    ... I have the proper Data Source Connection and driver on my Windows 2003 server ... OLE DB provider 'MSDASQL' reported an error. ... I can see my tables in Enterprise Manager with the following linked server ...
    (microsoft.public.sqlserver.odbc)
  • Re: Creating a MSSQL linked server into a VFP database
    ... > trying to set up a linked server in SQL Server into a Visual FoxPro ... > I have a VFP database with a smallish table named CodeList. ... > Invalid use of schema and/or catalog for OLE DB provider 'VFPOLEDB'. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Creating a MSSQL linked server into a VFP database
    ... > trying to set up a linked server in SQL Server into a Visual FoxPro ... > I have a VFP database with a smallish table named CodeList. ... > Invalid use of schema and/or catalog for OLE DB provider 'VFPOLEDB'. ...
    (microsoft.public.sqlserver.programming)
  • Linked server to Oracle
    ... I have setup a linked server to an oracle server. ... Invalid use of schema and/or catalog for OLE DB ... the provider does not expose the necessary interfaces to ...
    (microsoft.public.sqlserver.programming)
  • Re: Add Informix linked server
    ... I think you can change the linked server name with sp_setnetname. ... > Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. ...
    (microsoft.public.sqlserver.connect)
Loading