Posted: 14 April 2009 at 9:05pm | IP Logged

I am trying to set up a linked server from SQL Server 2005 to Pervasive SQL V10. I would like to know what the values for each entity within the "New Linked Server"  screen are. I am using the Server Management Studio to set up the link. I am trying to set up the best link to the "demodata" source installed by Pervasive. From the information I've come across, I can use the PervasiveOLEDB or Microsoft OLE DB Provider for ODBC Drivers. I am looking for the values for Product name, Data source, Provider string, Location and Catalog for each of the two Providers.

My potential client is a Microsoft SQL Server 2005 shop, while I am a long-time Btrieve \ Pervasive SQL developer. I have set up a test server with Windows Server 2003 and Pervasive SQL V10 as well as Microsoft SQL Server 2005 Standard Edition installed. I was able to set up a linked server using the Microsoft for ODBC setup and able to query the "billing" table and get results. I'm pretty sure that I don't have the ideal setup of the linked server as I can't see any method of displaying all of the tables in demodata. Since I know the table names via the Pervasive Control Center, I was able to issue select * from hcsi.demodata.."billing" and display results.

My potential client has a setup of SQL Server 2005 Enterprise Edition set up on a Windows Vista Ultimate workstation. He as numerous linked servers to other SQL 2005 instances on other servers. We ultimately would like to create a linked server on his workstation to a Pervasive SQL Server. I had my test server up in his network. He was using sp_addlinkedserver to attempt to create the linked server. While we received a successful linked server execution using the MSDASQL setup to an ODBC entry on his workstation via the Pervasive V10 Client install and an ODBC entry to demodata, we were not able to execute a select statement without an error. This is why I'm going back to square one to set this up properly. I have opened a support ticked with Pervasive, but have not yet received a response. I have seen Mirtheil's answers on every other forum I've been on, so I'm coming to the horse's mouth for an answer. 

Posted: 17 April 2009 at 2:49am | IP Logged

Spent all day and night applying service packs to get my environment set up to test linked server. I have a Vista Ultimate Virtual PC with SQL Server 2005 and the Pervasive Client V10.10 installed. I set up an ODBC entry using the Pervasive ODBC Client driver named EdTest pointing to DEMODATA on my Pervasive server. I received a "command completed successfully" when executing the sp_addlinkedserver. My linked server name is 'hcsi' and my ODBC entry is 'EdTest' I executed  select * from hcsi.EdTest.."Billing" and received the following:

OLE DB provider "MSDASQL" for linked server "hcsi" returned message "Unspecified error:.

OLE DB provider "MSDASQL" for linked server "hcsi" 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 "hcsi". The provider supports the interface, but returns a failure code when it is used

Posted: 17 April 2009 at 3:13am | IP Logged

Very strange! Although I have an ODBC entry named EdTest defined and I created the linked server with EdTest as the @datasrc I issued the following and retrieved data:

select * from OPENQUERY (hcsi, 'select * from demodata.billing')

I received the 2301 error when attempting to use EdTest.billing in the above statement.

Posted: 17 April 2009 at 4:01am | IP Logged

Finally got this to work! I removed the EdTest ODBC entry and created a new one named "demodata" which pointed to demodata on the server. I created the linked server and was able to execute select * from hcsi.demodata..billing to get results. Seems like there's something lost in the translation.

I just tried setting up the linked server via the Management Studio right-click "new linked server" method. I used the "Microsoft OLE DB Provider for ODBC" , Product Name = Pervasive,  and Data source demodata. The linked server appears and actually expands to show all of the DB Names (DEFAULTDB, DEMODATA, PERVASIVESYSDB, and TEMPDB). DEMODATA further expands to show Tables and Views. Further expanding the Tables shows the results I was expecting.

Since I was on a roll, I started playing with setting up the linked server in the Management Studio using the Pervasive OLE DB Provider. While it creates the linked server, expanding the Catalogs aborts with the cannot locate "DBSCHEMA_TABLES" error code 7399. Probably need a correct entry for Catalog when setting up the linked server. I had to use my server name in the Location field. I tried demodata in the Catalog field, but no luck.