We are migrating from SQL 2000 to SQL 2005.

One of the requirements we have is to link to a Pervasive DB. We did this
successfully under SQL 2000 with a SYSTEM DSN using ODBC. The LINKED SERVER
using this technique worked fine.

Under SQL 2005 we cannot get it to work.

Is there a SQL 2005 issue with PERVASIVE?

Steve Z wrote:
What exactly do you mean by "we cannot get it to work."? I don't know
Pervasive but I had analogical issue with iSeries. After few weeks I
discovered several odbc driver settings that had to be changed to make
it work with sql2005. Maybe your issue is similar?

--
PL

Ok - you asked - so here it is...

SQL box that worked - SQL 2000 on Windows 2000. System DSN setup as Client
name "asmddf" / Pervasive ODBC Client Interface. Server address is GIMEL.
Data Source Name on Server is "ams ddf". TEST of the DSN asks for un/pw -
that is entered - test successful.

On that same box - SQL 2000 - in ENTERPRISE MANAGER - setup for linked
server - name is AMSDDF1. The properties for this are PROVIDER
NAME=Microsoft OLE DB Provider for ODBC Driver. Product name=Pervasive.
Data Source=amsddf. Under security "Be made using this security context" and
the same un/pw is entered as was used for "test successful" above.

Expand the LINKED SERVER - click on TABLES - and you see all 39 objects from
the Pervasive DB.

Sorry for all - just wanted to tell you what has been working for 3 years
now on the old SQL 2000/Win 2000 server.

Now - new server. Win 2003 box. Running SQL 2005.

It has the exact same DSN setup. And that DSN works fine - "test successful"

Now - going into Mgt Studio - setup exact same Linked Server - with exact
same settings - filling the Provider, Product name and Data Source as the
System DSN.

When you try to expand the Catalog you get

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "SQL Server" for linked server "(null)" reported an
error. One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL
Server" for linked server "(null)". The provider supports the interface, but
returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)

For help, click:
http://go.microsoft.com/fwlink?ProdN...9&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Searches on the internet for this error return very little info.

In all of that detail you missed a very critical piece of information: 32
bit or 64 bit hardware/OS/SQL Server? There are some major driver
differences/deficiencies between 32 and 64 bit stuff!!

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.


"Steve Z" <SteveZ (AT) discussions (DOT) microsoft.com> wrote

it's all 32 bit hardware and os.

"TheSQLGuru" wrote:

Is there no solution to this priblem, i have the problem as discussed above. running hardware 32 and os 32 bits..Pls help

Thanks

Is there no solution to this priblem, i have the problem as discussed above. running hardware 32 and os 32 bits..Pls help

Thanks

Is there no solution to this priblem, i have the problem as discussed above. running hardware 32 and os 32 bits..Pls help

Thanks