|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL 2005 and Pervasive Linked ServerWe 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:
> We are migrating from SQL 2000 to SQL 2005. What exactly do you mean by "we cannot get it to work."? I don't know > > 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? 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?ProdName=Microsoft+SQL+Server&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?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Searches on the internet for this error return very little info. Show quote > 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 > 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!! -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "Steve Z" <Ste***@discussions.microsoft.com> wrote in message news:24F8E70A-2BAD-4CBB-926F-CFD01185B93B@microsoft.com... > 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?ProdName=Microsoft+SQL+Server&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?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476 > > ------------------------------ > 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 >> it's all 32 bit hardware and os.
Show quote "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" <Ste***@discussions.microsoft.com> wrote in message > news:24F8E70A-2BAD-4CBB-926F-CFD01185B93B@microsoft.com... > > 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?ProdName=Microsoft+SQL+Server&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?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476 > > > > ------------------------------ > > 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 > >> > > >  |
|||||||||||||||||||||||
Other interesting topics