create a linked server on SQL Server to Pervasive DB

Hi,

  I need to create a linked server on a SQL Server 2008 Express with a Pervasive database. On the SQL Server PC, I have 2 User's DSN configured (ODBC driver) to the Pervasive server and the connection is good. I have 2 ODBC drivers: PERVA1 and PERVA2. Pervasive database is located on another server (192.168.1.253). IP of the SQL server is 192.168.1.250

 Now, I need to setup the linked server. Provider name would be "Microsoft OLE DB Provider for ODBC Drivers". What are the parameters I have to enter in :
Product Name
Data source
Provider string
Location
Catalog

thanks for your time and help
Dominic34Asked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
After creating DSN for Pervasive DB, try this one:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23989652.html

Make sure that you have installed Pervasive driver
0
 
Dominic34Author Commented:
Unfortunatly, it doens't work. Pervasive client is installed on the server, ODBC driver correctly setup. I alwasy get an error message when creating the linked server:

The OLE DB provider "MSDASQL" for linked server "GPAO" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "GPAO". (Microsoft SQL Server, Error: 7399)
0
 
mirtheilCommented:
Try creating a System DSN rather than a User DSN to the Pervasive Database.  
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes.. It should be System DSN and should have explained it out earlier..
Make sure you are able to successfully test it using your DSN and then this linked server creation should work..
0
 
Dominic34Author Commented:
thanks for the replies

I moved the User's DSN to System DSN and still don't work. have the same error message than above
0
 
mirtheilConnect With a Mentor Commented:
What exactly are you using for options when adding the linked server?  I've used the following before:
EXEC sp_addlinkedserver
   @server = 'pvswdemodata',
   @srvproduct = '',
   @provider = 'MSDASQL',
   @datasrc = 'DEMODATA';

@server is the name you want to call the linked server
@provider is the provider's name (MSDASQL is the ODBC driver)
@datasrc is the ODBC DSN.  
0
 
Dominic34Author Commented:
I'm trying:
@server = 'GPAO'
@provider = 'MSDASQL'
@datasrc = 'ExactSQL'

where ExactSQL is the System's DSN name configured.
I still get the same error message.
0
 
Dominic34Author Commented:
Ok, I figured it out finally! that was a tough one.

I had to change the log on account for the SQL Server service from NT AUTHORITY\NetworkService to "Local System Account". After restarting the SQL service, it works!

thanks
0
All Courses

From novice to tech pro — start learning today.