Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    20

    Unanswered: Pervasive DB as Linked Server to SQL Server

    Hi All,

    Does anyone know how to set Pervasive DB as Linked Server in SQL Server? What values do I put in the Linked Server Properties dialog?

    For example, I tried to use DEMODATA and entered the following in SQL Server's Linked Server Properties dialog:

    Code:
    Linked Server: DEMODATA_LS
    Provider Name: Pervasive OLE DB Provider
    Product Name: <blank>
    Data Source: DEMODATA
    Provider String: <blank>
    Location: <I put server machine's name where DEMODATA is installed, e.g. SERVER01 w/c is the same where SQL Server DB is>
    Catalog: <blank>
    I seem to be able to create the Linked Server successfully, but when I tried to access DEMODATA_LS (i.e. Security > Linked Servers > DEMODATA_LS > Tables), I get an error saying Error 7302: Could not create an instance of OLE DB Provider 'PervasiveOLEDB'

    Any clues?

    Many thanks in advance.

    _msd_

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    WHat version of Pervasive are you using? Pervasive.SQL 2000i did not work in a Linked Server using the Pervasive OLEDB Provider. YOu have to use ODBC.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Aug 2004
    Posts
    20
    Hi Mirtheil,

    You suggestion works. Thanks.

    Anyway, this is my environment:

    My PC (the Client) : WinXP, Pervasive.SQL 2000i, SQL Server 2000
    Server (SERVER01): WinNT 4.0, Pervasive.SQL 2000i, SQL Server 7.0

    Both the SQL Server DB and Pervasive DB are located in the Server machine.

    I create the Linked Server through my PC using Enterprise Manager (SQL Server 2000).

    I used your suggested ODBC driver as follows:

    Linked Server: DEMODATA_LS
    Provider Name: Microsoft OLE DB Provider for ODBC Drivers
    Product Name: Pervasive
    Data Source: DEMODATA
    Provider String: <blank>
    Location: SERVER01
    Catalog: <blank>

    It works. I can see the tables of DEMODATA now.

    Query Analyzer:
    1. SELECT * FROM OPENQUERY(DEMODATA_LS, 'SELECT * FROM Billing')
    Result: ok. It returns the records.

    2. SELECT * FROM DEMODATA_LS...Billing
    Result:
    Server: Msg 7313, Level 16, State 1, Line 1
    Invalid schema or catalog specified for provider 'MSDASQL'.


    Another problem I have is that when I tried to defined the Linked Server for the actual DB, I get the following error when accessing the tables:

    Error 7399: OLE DB Provider 'MSDASQL' reported an error.
    Data source name not found and no default driver is specified.


    I'm pretty sure I got the DSN name right from ODBC Data Source Admin. Any ideas?

    _msd_

  4. #4
    Join Date
    Aug 2004
    Posts
    20
    Quote Originally Posted by _msd_
    2. SELECT * FROM DEMODATA_LS...Billing
    Result:
    Server: Msg 7313, Level 16, State 1, Line 1
    Invalid schema or catalog specified for provider 'MSDASQL'.
    I've fixed this with the following statement:
    Code:
    SELECT * FROM DEMODATA_LS.DEMODATA..Billing
    Quote Originally Posted by _msd_
    Another problem I have is that when I tried to defined the Linked Server for the actual DB, I get the following error when accessing the tables:

    Error 7399: OLE DB Provider 'MSDASQL' reported an error.
    Data source name not found and no default driver is specified.


    I'm pretty sure I got the DSN name right from ODBC Data Source Admin. Any ideas?
    I've also fixed this. Maybe I had a typo error earlier or something. I used the name under the Databases on the Pervasive Control Center and it worked ok in my development Pervasive.SQL 2000i server.

    FYI, Error 7399 also relates to trying to create a Linked Server to a third server which is true for my second connection. Wherein I am trying to create a Linked Server to another server (say SERVER02 -- the production Pervasive.SQL 2000i server). So, my second linked server is

    my PC --- SERVER01 SQL Server --- SERVER02 Pervasive.SQL 2000i production.

    This one, I cannot do anything. I do not have control over this case. So, I might have to find other means to connect to production without physically downloading the data. The best that worked for me so far is Linked Table using MS Access.

    Thanks again for your help.

    _msd_

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •