OLE DB Connection to Pervasive

[JohnEfford]

I have the following connection string in VB which works OK.

Cn.ConnectionString = "Data Source=Fops;Location=Fops1"

I want my users to be able to be able to query the the Parvasive db from within SQL server (version 7), they need to have access two tables stored on both db systems. I have tried the following two methods;

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

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]

or by setting up a link on the server

EXEC sp_addlinkedserver 
	@server = 'FOPS1', 
	@srvproduct = 'Pervasive',
	@provider = 'PervasiveOLEDB', 
	@datasrc = 'FOPS'

Select * from openquery(Fops1,'Select * from Order_Header')

Which gives the following error.

Server: Msg 7413, Level 16, State 2, Line 1
Could not perform a Windows NT authenticated login because delegation is not available.
[code]

I have seen a post for doing this using an ODBC connection but I wold prefer to use OLE DB as pervasives documentation states that this is the faster of the two access methods. 
Any Comments appreciated.

 

[TomKane]

I'm not sure if this will help as we've only accessed pervasive from a DSN via VB however this is the connection string that we use:

"Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & strDSNName

I hope this is some help

 

[JohnEfford]

Does anybody have a connection string to pervasive that works from SQL sever? Even using ODBC as this one is really stumping me.

Cheers

 

[SDELISLE]

I'm looking for a correct connection string working from Sql Server...

 

[TomKane]

Try using microsoft data link - it will build you a string based on what you want to do

 

[SDELISLE]

Yes it's done but how have the string based ??

 

[TomKane]

When I use microsoft data link to create a connection string and then open the file in notepad I get the following:


[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Data Source=Commercial

The line that begins with 'Provider' is what I use for the connection string.

Hope it helps....