SQL-Server Linked Server to Pervasive

[Norris Harrell]

I'm new to this, but I need to setup a Linked Server within SQL-Server
2000 to a Pervasive.SQL 2000i database. I can do this using a sytem
DSN and using the "Microsoft OLE DB Provider for ODBC Drivers" for
server type. However, I am uncertain how to setup/define the linked
server using the "Pervasive OLE DB Provider" server type. Actually,
I'm not certain that I necessarily should, but suspect that I might
benefit from a performance boost.

Specifically, I would like to know how to fill out the following
fields (what kinds of information to use) when setting up the linked
server within SQL-Server's Enterprise Manager using Pervasive's
driver:

1) Product Name: I'm assuming that this is just a simple text field
and I can fill it out with "Pervasive" or perhaps something more
informative if I want to.

2) Data Source: the help text says "the data source is usually the
name of the server or filename." Do I put in the name of the server?
The name of some file-based DSN? Something else entirely?

3) Location: the help text says "Datasource location." Would this be
the location of some file-based DSN? If not, then I'm not sure which
file location they would be referring to.

[Ted G Williams]

You are not the first one to attempt to use MS SQL Server Linked
Servers with Pervasive tables. Unfortunately, the OLE DB Provider
does not support the necessary features to be used as a linked server.
You are stuck with ODBC. You will also discover ODBC only works on
fairly small tables, since indexes are not used.

Following is a copy of a post from Ken White a Pervasive, explaining
the OLE DB issue.

>The Pervasive OLE DB Provider shipped with the Pervasive.SQL 2000
SDK,
>but it is an rowset-only based provider (in other words, it does not
>yet allow commands). There's a white paper on it in the component
zone
>(componentzone.pervasive.com).

>Your best bet might be to go to the ODBC driver for Pervasive.SQL
>2000. I don't know if this will solve your specific problem, but in
>general it works much better with MSDASQL than prior versions. It's
>backward compatible with your 7.0 data as well.

>Thanks,

>Ken White
>Senior Lead Software Engineer
>Pervasive Software

[norris harrell]

Thanks for the feedback. I currently have the "Pervasive ODBC Client
Interface" driver loaded (version 7.90.230.36). I can use this to create
a system DSN but am unsuccessful in creating a file dsn.

Is this the correct driver (the one referred to in Ken's message)? If
not then how/where do I get the ODBC driver for Pervasive.SQL 2000? I
have the Pervasive.SQL 2000i
Service Pack 3 General Release installed.

Thanks again,
Norris

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[LHarvey]

The quote below is for Pervasive.SQL 2000, not the current
Pervasive.SQL 2000i product.
Pervasive did update the OleDB provider for the newer version.
It should work with SP4 based on this listed as an enhancement:
33903 OLE DB fails when used as Linked Server with MS SQL Server

The Pervasive.SQL 2000i engine should use indices.
If the engine is not using indices then:

1) there may not be an index to use - check your query against
available indices

2) the SQL schema may not match the data files causing the indices to
not be used - check the SQL schema to data file consistency using the
CheckDB wizard, available through the Pervasive Control Center -
databases, right click on the database in question and select Check
Database. Select all tables to check. Any errors should be
addressed.

3) the SQL schema is in an old format that defined indices differently
that is ignored by the new engine. - Run a check database as above,
if no indices show up, then this is probably the issue and there is a
conversion utility at:
http://www.pervasive.com/support/toolbox.asp
If still none show up, it is possible none were defined for the SQL
schema. The type and location is defined in the data files and can be
added to the SQL schema.

Some of the data sets in existence pre-date both the SQL and ODBC
standards, and / or the application was developed using the
transactional interface instead and the SQL schema was added on after
the fact.

Leonard

On 30 Jul 2002 05:50:53 -0700, twil...@rsmedical.com (Ted G Williams)
wrote:

[Ted G Williams]

One of us has misread the release notes
(http://www.pervasive.com/support/updates/readmeoledb.txt) for SP4.
Under the section "Known Issues in this release" it lists the defect
you sited. I think that means it's still an issue.

It's a bummer too. I am looking forward to being able to use the
OLEDB provider as a Linked Server.