SQL Server 2008 linked server to Pervasive

[parsethis]

Can anyone create a linked server in 2008 SQL Server Express to Pervasive? I did it in 2005, using "Microsoft OLE DB Provider for ODBC Drivers", due to having no luck with the PervasiveOLEDB provider installed with Pervasive 10.10.

Trying same setup in 2008, I get...

<using ODBC>
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "myLinkedServer".
OLE DB provider "MSDASQL" for linked server "myLinkedServer" returned message "[Pervasive][ODBC Client Interface][Client LNA]Connection attempt timed out. The SQL Connection Manager may be inactive or using a different transport protocol from the client.". (Microsoft SQL Server, Error: 7303)

<using PervasiveOLEDB>
Cannot initialize the data source object of OLE DB provider "PervasiveOLEDB." for linked server "myLinkedServer".
OLE DB provider "PervasiveOLEDB." for linked server "myLinkedServer" returned message "Unable to connect to the relational engine (is Database Name and Server available?)". (.Net SqlClient Data Provider)

 

[mirtheil]

The error you're getting indicates a problem connecting to the engine. A few questions:
- Are PSQL and SQL Server on the same machine?
- If they are on different machines, can you connect to the server from the SQL Server machine with the Pervasive Control Center?
- Is the SQL Server 32 bit or 64 bit?

A few comments:
- Make sure that port 1583 and 3351 are open on the PSQL server.
- Make sure that both services (Transactional and Relational) are running

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[parsethis]

mirtheil...when do you sleep? I've been reading a lot of google results, and you are behind most of them regarding pervasive!
Anyway, PSQL and SQL Server are on same machine. This is currently proof-of-concept stage. We want to get data out of pervasive and into sql server without having the app developers involved. I assumed I could create a linked server and pull what I need.
netstat -an shows both those ports listening.
this is all 32-bit
as for the services, not sure this applies, since this is on my dev box and I see no services related to pervasive...but Control Center is up and running.

thanks

 

[mirtheil]

Sleep? What's that?
If you don't have any PSQL services, I'm guessing you're running the Workgroup Engine as an app (rather than as a service). That shouldn't be a problem.

I haven't tried it with OLEDB but using ODBC, the following works for me:

EXEC sp_addlinkedserver 
   @server = 'pvswdemodata', 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @datasrc = 'DEMODATA';
select * from pvswdemodata.demodata..course;

where "datasrc" is the ODBC System DSN (in my case, I used DEMODATA).

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[parsethis]

ok...sorry for delay
First, I think that a System DSN called "demodata" gets created during Pervasive install. On my box, it uses the engine driver. I've tried several tests, and results seem to say that I cannot use the engine driver. I can only connect via linked server when I use the client driver.

w3odbcci.dll (client interface)
w3odbcei.dll (engine interface)

any thoughts?
thanks again

 

[mirtheil]

DEMODATA is a System DSN created during the PSQL install.

I was able to connect to both an "engine" and "client" DSN through a linked server.

Are you getting the error on sp_addlinkedserver or the select? Is it the same error as before?

Can you post how you're adding the linked server?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com