September 12, 2008 at 7:13 am
A few comments...
I haven't really tried linked server, but I don't think there would be a big issue. The ODBC driver has improved immensely in the last few versions. The ODBC driver usually ships with the client drivers, so it shouldn't be hard to get a hold of.
The main issue, as noted above, is that there is no hard limit on variable charcter lengths, so you could have data in a char(30) field that is 50 characters long. The ODBC driver doesn't like that. There are a couple of utilities that come with Progress that enable you to identify the longest data value you have, and you can change the setting within the Database management tool pretty easily.
OpenEdge is the typical Progress database, not ObjectStore. ObjectStore is a separate, object oriented database that isn't that widely used.
September 12, 2008 at 7:23 am
Eddy,
Thanks. I'm the SQL Server guy at our company. I will need as much information as I can get in order get (spoon feed) our Progress "dba". I have a method in place driven by SQL Server DTS and linked servers to Progress, but am always looking for a better way that peraps could be done in Proress, pushing data to me. Can you please describe the following step you mentioned in more detail or with a simple example, referring to a table:
"steps : Detect where the correct data is saved to the progress database (mostly Post_update routine)"
Thanks!
smv929
September 12, 2008 at 7:37 am
To expand on this again......
There are 2 things that can really complicate connections to Progress db's.
The fact that reported length on string values is a display value, not a hard limit, and they have a multivalue field.
The choice of odbc might help. I used to work to for Openlink Software in support, and they did significant business because they had driver that could these. It can on a connection by connection based granularity set the length of the character data brought back, effectively ignoring the reported length.
It can also take the multivalue fields and pivot them to be columns, instead of a list that can also be updated through the driver. They could work with either SQL89 (or Progress native) formats or SQL92, an attempt by Progress to more SQL like, but sacrificing some features.
The downside is that the driver was often finicky to configure and required the Progress client. Stable in production, but a pain to get running without help.
I often had people coming in because they Data Direct driver was inadequate for their needs. This is from about 2.5 years ago.
September 12, 2008 at 8:08 am
It depends on what you exactly wish to export/import ..
For us its just as simple as keeping the customers in sync so we chose to put our progress developer at work
and he created a little 4GL procedure that puts the data that he write to the progress db for each update or creation also to an xml file.
My part existed in writing a windows service that picked up the xml file, read it out and put it in sqlserver.
If it is to put selected data from progress into sqlserver you can accomplish that also with setting up the progress database with 2 brokers of witch 1 is for Ansi SQL.
That way you can connect to progress via ODBC and issue just plain sql statements to the database.
Commando's for starting progress with 2 brokers,
1st as 4GL and second broker for SQL
proserve /ext3/testdb/testdev -H ddy -S 3006 -N tcp -n 30 -Mn 3 -Ma 5 -Mpb 10 -ServerType 4GL -SQLStmtCache 500 -TM 32 -TB 32
proserve /ext3/testdb/testdev -H ddy -S 3005 -N tcp -m3 -Mpb 4 -ServerType SQL
-H ddy = -H hostname of the machien running the database
-S = Tcp port on with the broker should listining
In the second line is the most important one the -m3 with means secondairie broker.
if you have a progress cliet you should be able to use the progress 10.1A or other version driver to create an odbc that points to host ddy on port 3005 in this example
1 thing to note is, if your progress database does not have any users defined already you should create one called sysprogress.
wkr
Eddy
September 12, 2008 at 8:16 am
jgrubb (9/12/2008)
There are 2 things that can really complicate connections to Progress db's.
The method i just described can also be used for SSIS via odbc
i have tested that and works verry well:
offcourse i have to set my string fields to varchar(max) and afterwords look in sqlserver on how long the longest string found was.
then setting my collumn lenght acording to this.
SSIS was also smart enough to put the multivalue field in pipe "|" separated columns, but still this can be a pain in the *ss
i should say, give it a try in a development env. and see what best suits youre needs
but as i understand what you want i guess odbc and SSIS can help you for 95% out.
Wkr,
Eddy
Ps: DataDirect still have issues with prodb V. 10.1B :crying:
January 29, 2009 at 12:34 pm
Hello. I'm using the MERANT 3.60 32-BIT Progress SQL92 v9.1D, I try to connect a Progress DB as a Linked Server in SQL Server 2005, I make the test conection and respond... "The test connection to the linked server succeeded"
But when I enter a simple select statement like:
SELECT *
FROM OPENQUERY(DB_TRAIN,'select tb_site from scdb.tb_mstr')
GO
I get the next error message:
OLE DB provider "MSDASQL" for linked server "DB_TRAIN" returned message "[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Table/View/Synonym not found (7519)".
Anyone have any experience in this area?
January 29, 2009 at 1:58 pm
Be sure the tables are SQL92 tables. If they are SQL89 tables, you won't be able to use the SQL92 drivers, and Merant no longer makes those. As of ~3 yaers ago, Openlink (openlinksw.com) made SQL89 drivers.
There's also the possibility that the login security has not been enabled on the progress DB
January 30, 2009 at 2:53 am
is the security is the failure,
Then make sure you create a user called "sysprogress" in de prodb
any other user will fail if this one does not exists,
Wkr,
Eddy
January 30, 2009 at 6:36 am
I'm a little rusty on progress. Couldn't remember the system user name. It's a quirky DB, and you can get bit by a bunch of things. One of the DB "features" is multi-value fields, which are completely alien to an RDBMS. Also, in some cases the field "size" only deals with the display, and not the data stored. You can have a field defined as 20 chars, and store 50, if you feel like.
January 30, 2009 at 6:47 am
Also, once again, be sure this is an SQL92 format table you are going after. Progress can have a different type (SQL89) which is being phased out but is common in prepacked applications built on Progress. If it's SQL89, The driver you are using will not be able to get the data. I can't remember off hand how to tell.
January 30, 2009 at 11:36 am
jgrubb (1/30/2009)
I'm a little rusty on progress. Couldn't remember the system user name. It's a quirky DB, and you can get bit by a bunch of things. One of the DB "features" is multi-value fields, which are completely alien to an RDBMS. Also, in some cases the field "size" only deals with the display, and not the data stored. You can have a field defined as 20 chars, and store 50, if you feel like.
And i will be happy when are new application is going live and we can get rid of Progress (because it ain't progress but going back in time..) 😀
Idd the multivalue integer fileds will bite you in SqlServer, they get split up in multiple columns,
+ the fact that progress as such aint really a relational db is a pain if you have to find the links between tables, like in our current prodb,
Would never recommend prodb to anyone, plus its also quit hard to find some assistance in case of emergency.
Eddy
January 30, 2009 at 2:21 pm
I spent 3 years as Tech Support for an ODBC driver company (Openlinksw). A bunch of their business was replacing the merant/data direct drivers. Openlink had tools that could deal with the fast-and-loose nature of Progress a little. As a product, Progress wasn't bad. As an RDBMS.....a nightmare.
Bastard stepchild of a relational and a multivalue DB, not really either. Integrating with another db was serious pain.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply