How to reference a Linked Server that use ODBC driver

How to reference a Linked Server that use ODBC driver

Post by Noel Dia » Sat, 27 Jan 2001 07:24:42



Hi,
I have created a linked server to my SS 2000 using the OLEDB provider for
ODBC. I am using a DSN that uses an ODBC driver to access an old DB system.
I can see the tables in the remote system from the EM under the Linked
Server that I am refering to but I don't know How to query that DB.

Note: I have already tryed :

ServerName...Tablename

and it does NOT work, there are no such thing as catalogs or owner in that
DB and the answer I get is:

" invalid schema or catalog specified for provider 'MSDASQL'  "

any thoughts?

by the way in the BOL there are samples on how to deal  with the other OLEDB
providers but NOT for the one that uses ODBC
--
Noel Diaz
Implex Corp

 
 
 

How to reference a Linked Server that use ODBC driver

Post by Mary Chipma » Mon, 29 Jan 2001 02:04:43


Using the Query Analyzer, run the following stored procedure:

EXEC sp_tables_ex 'linkedservername'

If there's something wrong with the way you set up your link, you'll
get the error message. just typing in the linked server name and table
won't work, you need a SELECT statement:

SELECT * FROM linkedservername...tablename

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446/mcwtechnologies/



Quote:>Hi,
>I have created a linked server to my SS 2000 using the OLEDB provider for
>ODBC. I am using a DSN that uses an ODBC driver to access an old DB system.
>I can see the tables in the remote system from the EM under the Linked
>Server that I am refering to but I don't know How to query that DB.

>Note: I have already tryed :

>ServerName...Tablename

>and it does NOT work, there are no such thing as catalogs or owner in that
>DB and the answer I get is:

>" invalid schema or catalog specified for provider 'MSDASQL'  "

>any thoughts?

>by the way in the BOL there are samples on how to deal  with the other OLEDB
>providers but NOT for the one that uses ODBC


 
 
 

How to reference a Linked Server that use ODBC driver

Post by Noel » Tue, 30 Jan 2001 23:13:21


Hi,

I did  Excecute the sp_table_ex 'LinkedServer' and the reply was correct:

TABLE_CAT  TABLE_SCHEM   TABLE_NAME  TABLE_TYPE  REMARKS
NULL        NULL          Name1       TABLE      NULL
NULL        NULL          Name2       TABLE      NULL
.          ..            ...          ..        ..
NULL        NULL          NameN       TABLE      ..

I can even see the table names from the EM under the Linked Server Name, but when I query the tables using

Select * From LinkedServerName...TableName

Still get the error message:

OLEDB Provider 'MSDASQL' returned an invalid schema definition

*** Sent via Developersdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

How to reference a Linked Server that use ODBC driver

Post by Derryl Cock » Tue, 30 Jan 2001 23:48:07


Hi Noel

I linked Foxpro tables to SQL Server 7 and I wasn't able to query the the
tables directly. I had to use the openquery comman that sends a pass through
query to the odbc driver.




select * from openquery(HRNewPer,"select * from Name")

Name is one of the foxpro tables I use.

I hope this helps

Derryl

 
 
 

1. How to reference a Linked Server that uses the OLEDB for ODBC Drivers

Hi,
I have created a linked server to my SS 2000 using the OLEDB provider for
ODBC. I am using a DSN that uses an ODBC driver to access an old DB system.
I can see the tables in the remote system from the EM under the Linked
Server that I am refering to but I don't know How to query that DB.

Note: I have already tryed :

ServerName...Tablename

and it does NOT work, there are no such thing as catalogs or owner in that
DB and the answer I get is:

" invalid schema or catalog specified for provider 'MSDASQL'  "

any thoughts?

by the way in the BOL there are samples on how to deal  with the other OLEDB
providers but NOT for the one that uses ODBC

Thanxs
--
Noel Diaz
Implex Corp

2. ado db connection, mts questions??

3. link server on sql server using ole db provider for odbc driver

4. ASP & Sybase 11 unter W2k ├╝ber ODBC

5. Requirements on ODBC Drivers for using 4-part names with Linked Server

6. xml in text field

7. Problems querying SQL 7.0 Linked Server using ODBC Text Driver

8. Calling COM DLL from TSQL? Possible?

9. MS SQL Server Linked Server And Sybase ODBC Driver

10. Problems Linking Oracle Tables using ODBC Driver

11. jdbc-odbc bridge: and active hstmt using SQL Server ODBC Driver

12. SQL server 7.0 using ODBC link to Ingres server

13. Update SQL Server ODBC driver from 3.50 using NT Terminal Server