SQL 2005 and Pervasive Linked Server

Steve Z

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?

Piotr Lipski

Steve Z wrote:
> 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?

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

Steve Z

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:


------------------------------
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:


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

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

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

> 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
>

TheSQLGuru

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" <> wrote in message
news:...
> 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:
>
>
> ------------------------------
> 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:
>
>
> ------------------------------
> BUTTONS:
>
> OK
> ------------------------------
>
> Searches on the internet for this error return very little info.
>
>> 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
>>

Steve Z

it's all 32 bit hardware and os.

"TheSQLGuru" wrote:

> 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" <> wrote in message
> news:...
> > 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:
> >
> >
> > ------------------------------
> > 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:
> >
> >
> > ------------------------------
> > BUTTONS:
> >
> > OK
> > ------------------------------
> >
> > Searches on the internet for this error return very little info.
> >
> >> 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
> >>
>
>
>

Hope

Hi Steve Z.

Did you ever get this issue resolved. We just performed a similar migration and also cannot see the Pervasive tables via the linked server even though they test okay.

Warm regards,
Hope