August 28, 2008 at 3:38 pm
Hey guys,
Trying to help out my cousin ... he knows absolutely nothing about SQL and I want to get him started with some basics. Their application runs on a progress database, and I know absolutely nothing about progress ... and not having access to their network without being there, it's hard for me to tell him what to do.
What all do I need to do to set him up to use SSMS to talk to his progress database? I'm just going to get him setup with Express for now and take it from there. Is the query language relatively similar to that of SQL Server? Any specific gotcha's or good to knows that I should be prepared for?
Any help would be greatly appreciated.
Thanks
August 29, 2008 at 11:23 am
Anyone?
August 29, 2008 at 1:43 pm
SSMS is short for SQL Server Management Studio and only works with SQL Server.
Are you sure that the product name is Progress?
Progress is a company, and their DBMS is ObjectStore.
Postgres is an Relational DBMS and was originally named Ingress.
SQL = Scarcely Qualifies as a Language
August 29, 2008 at 2:10 pm
Carl Federl (8/29/2008)
SSMS is short for SQL Server Management Studio and only works with SQL Server.Are you sure that the product name is Progress?
Progress is a company, and their DBMS is ObjectStore.
Postgres is an Relational DBMS and was originally named Ingress.
Yes, I know what SSMS is ... and you can use it to communicate with multiple different database systems.
I know absolutely nothing about Progress, I was simply told that was their database back end. After Googling, it is indeed a database platform. I am simply trying to determine whether or not I can communicate between the two, and if so, how. I'm assuming I can use opendatasource and/or create a linked server, but with what driver? ODBC? Can I authenticate with integrated?
September 3, 2008 at 8:52 am
Hopefully going to be able to work on this tomorrow, just trying to be ready for it.
I haven't been able to find much, except for a post saying to use a Merant 3.60 ODBC driver when using a linked server between SQL and Progress.
Any information would be much appreciated.
Thanks all
September 9, 2008 at 9:50 am
I had a look at connecting a Progress db but as you found out the only method seems to be that ODBC driver and IIRC it cost a small fortune.
September 10, 2008 at 4:50 am
Hi,
We work with progress also, and are in the middle of changing our progress application env. to a SQL-Server backend with a windev application on the foreground.
We also have got the need to extract data from our progress DB into our sql db and have done that on the following way.
Progress db can be "started" with multiple brokers where the standard broker uses the 4GL engine you can add an extra broker for SQL ,
When this broker is up and running then you can connect to progress via a ODBC driver from progress itself (we are using version 10.1B)
a little annoying thing we have figured out was that you hav eto create the user "sysadmin" to your progress database before the odbc would want to connect.
If you need info on how to setup the secundairie broker as well, just give me a call.
PS: in progress data administration, you can also easily export your data to different formats;, plain text, comma separated, ....
these data can then being imported in sql server via SSIS
although because progress is having some datatype that are not recognized by SQL-Server they dont work always that simple.
For example: progress can have a integer filed with indexes more likely recognized as array fields
and for sting fields you have the datafields string with a certain lenght but this lenght only matters for the lenght of data that you want to display, and not on the lenght of data that can be inserted.
Hope this helps.
Wkr,
Eddy
September 10, 2008 at 4:53 am
PS: i would NOT suggest the linked server method
Have tried that for myself but was a little disaster
PS: dont forget that progress is NOT only a database it is also a development platform where the 4GL code is compiled WITH the database, so creating a robuust environment but making it a disaster to make changes on one side of the 2
Wkr,
Eddy
September 10, 2008 at 6:58 am
I also work with Progress and MS SQL environments.
Progress was not my choice but part of the job;)
We have had issues connecting directly between the Progress- Openedge servers and the MS sql servers -
There is a product called winsql which has the odbc drivers for both databases as a part of their offering.
I actually found it faster to use this product to port data between the two than using the linked server functionality. You can aslso check the progress web site or the PSDN(Progress software Development Network).
September 10, 2008 at 8:15 am
Eddy does know something about what he's talking about. I worked for an ODBC driver vendor that supported Progress. It does have some quirks. To re-iterate:
You can't trust the datatypes that ODBC returns. This means recordsets might format data incorrectly, change the data, or exclude data. This is primarily due to the structure of Progress, which was not designed to be an SQL database.
1) The length of the strings is based off of the databases configured display length, not amount of data in the field. Might need to fiddle with the driver settings to get it all. This is used sometimes as a "feature" by Progress programs.
2) Progress has an array type, which shows in odbc as pipe(|) seperated strings. Progress treats this as independent values, so the driver must support this correctly if you want to update these, or query on them.
3) Progress makes some weird choices on how it reports PK and indexes. It sometimes depended on the order of index creation, rather than the appropriateness on an index.
4) As far as I could tell, the number occasionally acted like they were stored as strings, and had special rules depending on the progress configured data types. Sometimes that would bite you in ODBC.
It's been a few years for me. The upshot is, test everything 10 times. Be careful about using a "live" link, use batch imports if possible.
September 10, 2008 at 8:21 am
Wow, I really appreciate the information all.
So minus an export/import process and/or buying a driver/software, there is no way to opendatasource/linked server to a progress db from SSMS then eh?
September 10, 2008 at 8:33 am
You should be able to use a linked server, or any application that uses an appropriate ODBC/OleDB/ADO.Net connection. There are options. The kicker here is that this is not the same paradigm as SQL Server. There are some differences, and you need to be aware.
IIRC, the Progress drivers required the Progress client installed (and licensed) for a direct connection. The 2 main vendors were DataDirect (now owned by Progress) and Openlink Software (openlinksw.com, where I worked). Both had an option for a light client using an intermediate request broker.
This was a finicky system. For example, many progress installations did not have any sort of login or security. They had multiple SQL version modes. Some of these modes required setting up security.
Progress was designed to be a "fire and forget" dbms with little maintenance involved, so there may not be any DBA supporting it.
September 10, 2008 at 11:06 am
It is indeed just like jgrubb said, verry hard to find a DBA for progress or even a developper,
As progress said to us, we are a fairly big player in America but here with us in Belgium they are rather very small and unknown,
On the other hand i have to say, until know we did not have any issues with our progress RDBMS, it is a verry stable environment but if you would like to use al kinda neeth features, like webdevelopment, remote applications etc.. , progress can offer it but in separated apllications "read licenses"
We are mainly changing from environment because of the lack off support for progress in our region and for the licenses problematic, progress is fairly pricey compared to other rdbms.
Again, i would really not suggest Linked server, the differences between the 2 are to big an can lead to unpredictable results..
Best way to go is export/import
or wxhat we do now while setting up a new env. and still working operational with progress for keeping data equal is
working with windows services,
any change in a customer in progress results in an xml file that is been picked up by our windows service (vb.net program) and that way sorted, checked and inserted in sql server.
works verry well.
Ps: if you have a license for openedge 10.1A studio or higher you can use this odbc driver to connect to progress from withing access, excell whatever application if you set you progress db up with a sql broker (mainly or secundairie)
wkr,
Eddy
September 11, 2008 at 9:58 am
HI,
Nope, this does not require progress programming as such..
altough you should have a little basic knowledge about progress 4GL.
Because the way we did it for certain data that has to be in sync while the old env is running and the new env. is building up.
steps : Detect where the correct data is saved to the progress database (mostly Post_update routine)
Create a little progress procedure that also puts this data to an XML file (is builtin in progress)
al the rest is .net programming and some stored procs on sql server
But for the ease and because the updates have to be in the sql server database withing seconds we have transformed our vb.net "import" programma into a windows service
Works great and until now the errorcount is still at 0, meaning al changes in progress are correctly imported in sqlserver
if you need some help on any of these subjects just give me a sign (altough i guess concerning the stored procs you can find bether advice here, i have found mine 2 in these forums, for witch thanks to all contributors..)
wkr,
Eddy
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply