[_msd_]

Hi All,

Does anyone know how to set Pervasive DB as Linked Server in SQL Server? What values do I put in the Linked Server Properties dialog?

For example, I tried to use DEMODATA and entered the following in SQL Server's Linked Server Properties dialog:

Linked Server: DEMODATA_LS
Provider Name: Pervasive OLE DB Provider
Product Name: <blank>
Data Source: DEMODATA
Provider String: <blank>
Location: <I put server machine's name where DEMODATA is installed, e.g. SERVER01 w/c is the same where SQL Server DB is>
Catalog: <blank>

I seem to be able to create the Linked Server successfully, but when I tried to access DEMODATA_LS (i.e. Security > Linked Servers > DEMODATA_LS > Tables), I get an error saying Error 7302: Could not create an instance of OLE DB Provider 'PervasiveOLEDB'

Any clues?

Many thanks in advance.

_msd_

[mirtheil]

WHat version of Pervasive are you using? Pervasive.SQL 2000i did not work in a Linked Server using the Pervasive OLEDB Provider. YOu have to use ODBC.

[_msd_]

Hi Mirtheil,

You suggestion works. Thanks.

Anyway, this is my environment:

My PC (the Client) : WinXP, Pervasive.SQL 2000i, SQL Server 2000
Server (SERVER01): WinNT 4.0, Pervasive.SQL 2000i, SQL Server 7.0

Both the SQL Server DB and Pervasive DB are located in the Server machine.

I create the Linked Server through my PC using Enterprise Manager (SQL Server 2000).

I used your suggested ODBC driver as follows:

Linked Server: DEMODATA_LS
Provider Name: Microsoft OLE DB Provider for ODBC Drivers
Product Name: Pervasive
Data Source: DEMODATA
Provider String: <blank>
Location: SERVER01
Catalog: <blank>

It works. I can see the tables of DEMODATA now.

Query Analyzer:
1. SELECT * FROM OPENQUERY(DEMODATA_LS, 'SELECT * FROM Billing')
Result: ok. It returns the records.

2. SELECT * FROM DEMODATA_LS...Billing
Result:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.


Another problem I have is that when I tried to defined the Linked Server for the actual DB, I get the following error when accessing the tables:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data source name not found and no default driver is specified.


I'm pretty sure I got the DSN name right from ODBC Data Source Admin. Any ideas?

_msd_

[_msd_]

Originally Posted by _msd_

2. SELECT * FROM DEMODATA_LS...Billing
Result:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.

I've fixed this with the following statement:

SELECT * FROM DEMODATA_LS.DEMODATA..Billing

Originally Posted by _msd_

Another problem I have is that when I tried to defined the Linked Server for the actual DB, I get the following error when accessing the tables:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data source name not found and no default driver is specified.


I'm pretty sure I got the DSN name right from ODBC Data Source Admin. Any ideas?

I've also fixed this. Maybe I had a typo error earlier or something. I used the name under the Databases on the Pervasive Control Center and it worked ok in my development Pervasive.SQL 2000i server.

FYI, Error 7399 also relates to trying to create a Linked Server to a third server which is true for my second connection. Wherein I am trying to create a Linked Server to another server (say SERVER02 -- the production Pervasive.SQL 2000i server). So, my second linked server is

my PC --- SERVER01 SQL Server --- SERVER02 Pervasive.SQL 2000i production.

This one, I cannot do anything. I do not have control over this case. So, I might have to find other means to connect to production without physically downloading the data. The best that worked for me so far is Linked Table using MS Access.

Thanks again for your help.

_msd_