0

I may have figured out why I cannot establish a linked server on sql server 2012 using ODBC/OLEDB into a pervasive sql.

I have another sql server 2005 that is able to connect to the pervasive sql because it has a provider called PervasiveOLEDB.11.0.

I would like to use the same provider, and I am wondering if it is possible to get my sql server 2012 to use this provider (which resides on a different server)?\

If not, would there be a different way I could export/import this provider?

I am trying to establish a connection this way, but cannot do it with the provider:

EXEC sp_addlinkedserver 
   @server = 'kslap208', 
   @provider = 'MSDASQL', 
   @datasrc = 'C003',
   @srvproduct = 'Pervasive'
GO

update: although i am able to connect succesfully, i am getting this error when i try to select from tables:

OLE DB provider "MSDASQL" for linked server "kslap208" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "kslap208" returned message "[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Cannot locate the named database you specified(Btrieve Error 2301)".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "kslap208". The provider supports the interface, but returns a failure code when it is used.
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062

1 Answers1

1

I think you need to download Pervasive SQL 11 client from here. This should install all drivers

cha
  • 10,301
  • 1
  • 18
  • 26
  • do you tink i need to restart the server after im done installing? because i dont see the new provider that i needed in ssms – Alex Gordon Feb 07 '13 at 23:20
  • test if the ole db provider has been registered. Create an empty file anywhere on your system with an extension udl, e.g. c:\test.udl. Open it. It will allow you to see all the OLE DB providers installed in the system. If the provider is not there, check if there are any Pervasive ODBC drivers in the system. Thy to connect to you server via ODBC – cha Feb 07 '13 at 23:25
  • thank you very much. i dont see the OLEDB there. how can i check if the drivers are in the system? – Alex Gordon Feb 07 '13 at 23:28
  • here are the errors that i am getting when i try to test connection http://stackoverflow.com/questions/14744012/data-source-name-not-found-when-adding-linked-server – Alex Gordon Feb 07 '13 at 23:38
  • according to the above you are connecting via ODBC – cha Feb 07 '13 at 23:42
  • yes but its called microsoft ole db provider for odbc ( in the server that connects to it without problems) – Alex Gordon Feb 07 '13 at 23:49
  • i apologize for bothering you again but although the connection works, i am unable to select anything from tables! i get this error ( please see revised question) – Alex Gordon Feb 08 '13 at 03:43
  • are you sure you have a database called C003 on your pervasive database. According to your DSN setup (in the separate question) the database name will be C003. Please set the proper database name in your ODBC settings – cha Feb 08 '13 at 04:31
  • I think the connection is now OK. The problem is with the query. Can you show the query you use to read data from a table on the linked server? – cha Feb 10 '13 at 22:33
  • its not the query. i tried the query on a diff system and worked fine – Alex Gordon Feb 11 '13 at 02:38