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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
mirtheilSoftware DeveloperCommented:
Try creating a System DSN rather than a User DSN to the Pervasive Database.  
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
mirtheilSoftware DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.