Need some help with ODBC Connect progress

[jason.jia]

hi,all,about odbc connect progress,i have some problem!

Progress Ver:9.1D
Progress Server:HPUX 11.11

Client ODBC Driver:MERANT 3.60 32-BIT Progress SQL92 v9.1D
Client Server:WinXP
Client DataBase:SQL Server 2000

i have create linked server in SQL Server 2000 to user Microsoft OLE DB Provide for ODBC Driver
my connect string:
Product name:MERANT 3.60 32-BIT Progress SQL92 v9.1D
DateBase: progress
Connect String DSN=progress;DB=sst1shbj;UID=aaaabshb;PWD=1qazxsw2;HOST=10.38.194.26;PORT=10400

i can view table in linked server,but when i query in Query Analyzer,i have recevie error message:

[OLE/DB provider returned message:The provider does not support the necessary method.
[OLE/DB provider returned message: [DataDirect-Technologies][ODBC PROGRESS driver]Optional feature not implemented.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

start sh of database is:

/progress/V9/dlc/bin/proserve /data/shbj_database/sst1 -S sst1shbj -N tcp -n 10 -d dmy -B 200000 -bibufs 20 -L 2000000 -spin 2
0000 -cpinternal GB2312 -cpstream GB2312 -Mn 4 -Ma 5 -Mi 2
proapw sst1
probiw sst1
prowdog sst1

Services:
#
# Premium Services
#
sst1test 10300/tcp # Premium Test
sst1shbj 10400/tcp #Premium Beijing
sst1v8 10500/tcp # Premive Live DB V8
sst1v9 10600/tcp # Premive Live DB V9
sst1dev8 10700/tcp # Develop DB V8
sst1dev9 10800/tcp # Develop DB V9
poib 10900/tcp # ODBC

i suspect whether not to start servertype is 4GL and SQL-92 or not,
if it is true,some body can help me to fix it,i don't know hot to edit servergroup

 

[TomBascom]

0) 9.1D is ancient, obsolete and unsupported. You should have upgraded 7 or 8 years ago. Your db startup command suggests a database that is clearly large enough to benefit significantly from an upgrade.

1) There is a forum specifically for SQL & ODBC issues

2) What service pack of 9.1D? It's important -- there were major changes to ODBC in the 9.1D service packs. One change was from Merant to DataDirect.

 

[OpenLink]

0) 9.1D is ancient, obsolete and unsupported. You should have upgraded 7 or 8 years ago. Your db startup command suggests a database that is clearly large enough to benefit significantly from an upgrade.

I'd hazard a guess that some existing enterprise applications were written for SQL-89, and possibly catering for specific behaviors of 9.1D -- which behaviors may well change in later versions, making an upgrade of questionable benefit. Of course, this is a guess, and it is possible that an upgrade *would* be of value.

2) What service pack of 9.1D? It's important -- there were major changes to ODBC in the 9.1D service packs. One change was from Merant to DataDirect.

That particular change was largely cosmetic, as that naming reflected an ownership change, not a technology change. The Merant codebase was/is the DataDirect codebase. This is not to say that there haven't been updates and improvements made to that codebase -- but the change wasn't as drastic as the above might suggest.


That said... Microsoft SQL Server has particular expectations about the abilities and behaviors of target Linked Servers, and Progress 9.x (among many other ODBC-accessible DBMS) doesn't generally satisfy these, regardless of the ODBC Driver in use. (OpenLink also makes ODBC Drivers for Progress 9, and they can't support all activities in your attempted architecture, either.)

However, if you are locked in to this version of Progress for any reason, for any length of time, you can make things work fairly well by adding another piece of middleware to the mix.

OpenLink Virtuoso Universal Server, which has a native OLE DB Provider (eliminating one common layer of incompatibility -- the OLE DB Provider for ODBC Data Sources), behaves as Microsoft SQL Server expects and demands. One of Virtuoso's key features is its Virtual Database Engine, which can be used to link Progress tables into itself -- making those tables appear to be Virtuoso tables, from the perspective of data consumers targeting Virtuoso -- such as SQL Server. Virtuoso knows how to work within the limitations of the target data source (i.e., Progress 9.x), and this architecture has been successfully used by many.

The connection architecture you'd wind up with is something like this, from data consumer to data source --

Microsoft SQL Server Linked Servers
>> OLE DB Provider for OpenLink Virtuoso
   >> OpenLink Virtuoso
      >> ODBC Driver(s) for Progress 9.1D (and/or other DBMS)
         >> Progress 9.1D (and/or other DBMS)

All data remains resident where it is today -- there's no replication involved. You'll be effectively creating Remote VIEWs (read/writeable, at your option) of the Progress TABLEs (and VIEWs, and Stored Procedures) you want SQL Server to get hold of.

I hope this information is helpful.

 

[TomBascom]

I'd hazard a guess that some existing enterprise applications were written for SQL-89, and possibly catering for specific behaviors of 9.1D -- which behaviors may well change in later versions,

Very unlikely. Progress' backward compatibility is pretty darned good. Progress v8 used SQL-89 with ODBC and it sucked.
Progress v9 ODBC is SQL-92 not SQL-89. SQL-89 is via the quasi-SQL inside the 4GL or the embedded SQL product that you had to re-link executables to use (or at least that's how my foggy memory remembers it).

making an upgrade of questionable benefit. Of course, this is a guess, and it is possible that an upgrade *would* be of value.

There is no question but what an upgrade would be of benefit. The list of bug fixes and performance improvements is dramatic.

That particular change was largely cosmetic, as that naming reflected an ownership change, not a technology change. The Merant codebase was/is the DataDirect codebase.

You're right. Actually I think I was thinking of the change from OpenLink to Merant/DataDirect?

In any event SQL-92 got a whole lot better as v9 patches were released.

This is not to say that there haven't been updates and improvements made to that codebase -- but the change wasn't as drastic as the above might suggest.

That said... Microsoft SQL Server has particular expectations about the abilities and behaviors of target Linked Servers, and Progress 9.x (among many other ODBC-accessible DBMS) doesn't generally satisfy these, regardless of the ODBC Driver in use. (OpenLink also makes ODBC Drivers for Progress 9, and they can't support all activities in your attempted architecture, either.)

However, if you are locked in to this version of Progress for any reason, for any length of time, you can make things work fairly well by adding another piece of middleware to the mix.

OpenLink Virtuoso Universal Server, which has a native OLE DB Provider (eliminating one common layer of incompatibility -- the OLE DB Provider for ODBC Data Sources), behaves as Microsoft SQL Server expects and demands. One of Virtuoso's key features is its Virtual Database Engine, which can be used to link Progress tables into itself -- making those tables appear to be Virtuoso tables, from the perspective of data consumers targeting Virtuoso -- such as SQL Server. Virtuoso knows how to work within the limitations of the target data source (i.e., Progress 9.x), and this architecture has been successfully used by many.

The connection architecture you'd wind up with is something like this, from data consumer to data source --

Microsoft SQL Server Linked Servers
>> OLE DB Provider for OpenLink Virtuoso
   >> OpenLink Virtuoso
      >> ODBC Driver(s) for Progress 9.1D (and/or other DBMS)
         >> Progress 9.1D (and/or other DBMS)

All data remains resident where it is today -- there's no replication involved. You'll be effectively creating Remote VIEWs (read/writeable, at your option) of the Progress TABLEs (and VIEWs, and Stored Procedures) you want SQL Server to get hold of.

I hope this information is helpful.

 

[OpenLink]

Progress v8 used SQL-89 with ODBC and it sucked.
Progress v9 ODBC is SQL-92 not SQL-89. SQL-89 is via the quasi-SQL inside the 4GL or the embedded SQL product that you had to re-link executables to use (or at least that's how my foggy memory remembers it).

Well... Progress v8 (and previous) SQL-89 was very much not ANSI SQL-89, and that certainly let to difficulties, but they weren't tied to ODBC, per se.

Progress v9.1 brought a hybrid engine, and supports both the old SQL-89 and the newer SQL-92 by default -- but it can be launched with flags that disable either of these.

Progress OpenEdge 10.x fully deprecates the SQL-89 (but retains the even older 4GL) -- but the functionality appears to still be in the engine, although it's challenging (at best) to reach.

There is no question but what an upgrade would be of benefit. The list of bug fixes and performance improvements is dramatic.

Lots of bug fixes and performance improvements, yes -- and many users/installations would indeed benefit by them. However, "bug fixes" can also be read "behavior changes" -- and if you've coded an application to rely on a nominally buggy behavior, which changes with an upgrade -- you must re-code your application. That can be a deal-breaker for an upgrade, depending on the application's distribution, mission-criticality, ease-of-update, etc. It doesn't matter how fast the engine runs if it can't be connected to the driveshaft, and thus the tires...

You're right. Actually I think I was thinking of the change from OpenLink to Merant/DataDirect?

So far as I know, the ODBC drivers PROGRESS Corp has chosen to ship as OEM have always been DataDirect/Merant/Visigenic/other-names (same primary codebase). They've definitely never been from OpenLink Software. OpenLink did produce the first ODBC driver available for any version of the Progress DBMS, and we've supported every version since (still do), but always as a third-party.

In any event SQL-92 got a whole lot better as v9 patches were released.

I can't argue with that. Progress SQL-92 is a lot closer to ANSI SQL-92, but it still has a fair bit of dialect specificity, and it doesn't conform to the behavior nor feature expectations of SQL Server.

 

[TomBascom]

Well... Progress v8 (and previous) SQL-89 was very much not ANSI SQL-89, and that certainly let to difficulties, but they weren't tied to ODBC, per se.

Progress v9.1 brought a hybrid engine, and supports both the old SQL-89 and the newer SQL-92 by default -- but it can be launched with flags that disable either of these.

Progress OpenEdge 10.x fully deprecates the SQL-89 (but retains the even older 4GL) -- but the functionality appears to still be in the engine, although it's challenging (at best) to reach.

There are 2 "languages" that form alternate "layers" over the storage engine. One is the traditional 4GL language which contains SQL-89 as a subset. It's really only there for marketing purposes (and if you know anything about PSC marketing that should tell you everything you need to know about that "feature"). Nobody should use it. Except maybe to scare junior programmers.

The other is SQL-92.

This division was new with v9. The v9 SQL-92 language engine was purchased (from "Dharma" I believe) and, as I understand it, needed a lot more work than anticipated to be made functional.

Lots of bug fixes and performance improvements, yes -- and many users/installations would indeed benefit by them. However, "bug fixes" can also be read "behavior changes" -- and if you've coded an application to rely on a nominally buggy behavior, which changes with an upgrade -- you must re-code your application. That can be a deal-breaker for an upgrade, depending on the application's distribution, mission-criticality, ease-of-update, etc. It doesn't matter how fast the engine runs if it can't be connected to the driveshaft, and thus the tires...

Which is why Progress is fanatical about making sure that doesn't happen.

I defy anyone to provide an real example of Progress making such a change that would prevent an upgrade. The vendors that are most infamous for cowing their customers certainly cannot cite any actual examples. When pressed for specifics they always come up empty. (I personally have in fact come across a very few behavior changes -- all of which were very easily corrected.)

So far as I know, the ODBC drivers PROGRESS Corp has chosen to ship as OEM have always been...

I defer to your superior knowledge.

 

[tamhas]

I defy anyone to provide an real example of Progress making such a change that would prevent an upgrade.

The only one I have seen that is at all common is having the misfortune to use a name for a field or table which becomes a reserved keyword in a later release. Even then, an entry in the keyword forget list will get you running and, with a few tools, making the change is rarely that difficult ... unless one doesn't have source, of course.

 

[Casper]

Using vanilla 9.1D with sql is a bad idea. Although it is suggested here that the differences wheren't that big, I can assure that there where many changes made to the sql engine with regard to performance and bugs. If you really have to use 9.1D then at least go to SP9 and use the data direct 4.10 driver. This combinations solved many bugs and performance problems and makes it even somewhat workable using sql.
I can't comment on the linked server part. I tried it a few times with 9.1D but didn't experience problems with it. But then again, I only tested it to see if I could get it to work.
The problem Progress still haves (up to 10.2A) is that using not well formed queries on database > 10 GB causes the sql engine to retain the connection after the query has stopped (in essence dies of) which in time causes filling of the server with stale processes.
So you better be sure you use proper queries, which in esence means that you should run update statistics on a regular basis and you must check the query plan of all queries being used on large sets of data.

Casper.