linked server, invalid schema on fvp odbc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

linked server, invalid schema on fvp odbc

Author

Topic

wolfgam
Starting Member

7 Posts

Posted - 2010-11-16 : 17:13:59

Hello,

i have a system dsn 'OMS_CSD' with Visual Vox Pro ODBC driver. This works fine with several database query products (e.g. WINSQL).

Now i setup a linked server to my vfp database in SQL Server 2008 R2 using MSDASQL:

EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'test', @provider=N'MSDASQL', @datasrc=N'OMS_CSD'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

Test connection to my database works fine.

select * from TEST...Kunde

Msg 7313, Level 16
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "TEST"

The same query with openquery works fine!
SELECT * FROM openquery (test , 'SELECT * FROM Kunde')

Using vfpoledb drivers works fine too. But it's not supporting indexes!

Any idea?

kindly regards
Wolfgang

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-17 : 09:09:59

quote:
Originally posted by wolfgam

select * from ~~TEST...~~Kunde

wolfgam
Starting Member

7 Posts

Posted - 2010-11-18 : 11:31:44

Hello,

~~TEST...~~Kunde ???

the anser is unfortunately not correct.

When you execute a distributed query against a linked server, a fully qualified, four-part table name for each data source to query must be specified. This four-part name should be in the form linked_server_name.catalog.schema.object_name.
On some databases catalog and schema can be blank.

Wolfgam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-19 : 10:47:48

... is not fully qualified. every part needs to be specified in a distributed query

wolfgam
Starting Member

7 Posts

Posted - 2010-11-19 : 12:55:38

Hello,

i am trying to connect to vfp database using a linked server.
There are 2 methods:

1) OLE DB using vfpoledb driver. This works fine, but unfortunately vfpoledb is not using indexes. So its working very slow...

!! This examples works fine !!
EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'VFPOLEDB', @provider=N'VFPOLEDB', @datasrc=N'\\T61P\E\OMS-Produktion\BCC\DAT\OMS.dbc', @provstr=N'Provider=vfpoledb;Collating Sequence=machine'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

select * from TEST...Kunde
(catalog and schema are left empty)

2) ODBC using vfpodbc driver with MS MSDASQL as i described above earlier.
select * from TEST...Kunde does not work.
(invalid schema or catalog)

So, on a vfp database with OLE DB linked server you do not need catalog + schema.
I guess this does not even exist in vfp?!

The same database with ODBC using MSDASQL does not work.
Why?

If i am not right and there is a catalog and schema on vfp database:
Where to find those?

With MSDASQL (ODBC) i can even browse the database in management studio. But when accessing a single table, it says the table has no columns....

Wolfgam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-19 : 13:57:09

select * from test.catalogName.Kunde

catalogName might be "default" if you didn't specify anything

wolfgam
Starting Member

7 Posts

Posted - 2011-01-27 : 13:36:25

!! Found the solution !!

On Linked Server Properties (Provider Options Page) you have to set the following options for MSDASQL provider:
- Dynamic Parameter
- Level zero only
- Allow inprocess

So the following SQl statement works:
select * from TEST...Kunde
No schema or catalog is required for Visual FoxPro ODBC driver

wolfgam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources