Connection to pervasive DB form MSQL

[JohnEfford]

I need to be able to execute queries that use tables from both Pervasive and Microsoft SQL databases. I have tried connecting to the pervasive database from MSQL using the following:

Select * From openrowset('PervasiveOLEDB','Provider=PervasiveOLEDB;Data Source=Fops;Location=Fops1','Select * From Order_Header') as A

Which returns the error:


Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'PervasiveOLEDB'.
[OLE/DB provider returned message: Errors occurred]
[OLE/DB provider returned message: Mode, Protection Level, or an unknown parameter has been set (incorrectly) in the connection string

The connection string works fine when used in a Visual Basic program.

Cheers

 

[TomKane]

You could try using microsoft data link to create a connection strig for you or you could try using sql server's dts to connect to your pervasive data and see what kind of a connection string it uses to do that.

 

[AaronSC]

In my experience, the Pervasive OLEDB driver is not that tolerant in such configurations (especially before v8). What I have done in the past is to create an ODBC DSN on the MS SQL Server, then either do a passthrough query, or add a linked server using the MSDASQL provider that points to the P.SQL DSN, then use OPENQUERY(lsName, 'query') from the SQL Server and that does the trick.