ODBC -> Progress V9.1B (For Crystal Reports v8P)

[Matt Russell]

Hello,

We are using a Progress v9.1B database and 4GL app, developed by a third-party. My only experience with Progress is by using this app - I've been raised on SQL. Unfortunalty, the entire firm is made possible by this app, so I've got a bit of learning to do.

What we need to do is run a number of Crystal Reports over the underlying Progress database. How this is done at the moment is nothing short of ridiculous (Dump the contents of a table using a function of the app into TSV files, Format TSV files, load TSV files into MS Access, format tables, run Crystal via ODBC to Access), mainly because no-one knows Progress well enough to do it any different.

What makes sense is to be able to drill straight into the Progress database via ODBC with Crystal, and report off the live data. The big questions are :
1 - Can it be done?
2 - How?

I've so far downloaded and installed the Merant DataDirect SequeLink server for Progress (which, despite their assurances, doesn't support 9.1B), as well as the SequeLink server for ODBC Sockets, both to no avail. I'd rather NOT use these, since these are slightly overkill for what we need (as well as being very expensive).

I've since discovered the wonders of the Progress Client Networking, which I installed this morning, and now have an ODBC driver. This is great - it lets me create an ODBC link to the database, and all is well. I fire up Crystal, and open the ODBC link, I can see all the tables, and I can add fields from those tables into my report.

However, as soon as I try and browse the data (refresh the report, etc), I get ODBC Error -20217 (which I believe to be Access Denied, or similar??).

On the 2/9/01, Nick Williamson posted to this board regarding ODBC, v9.1, and a user called "sysprogress", which he believed to be a kind of SQL92 DBA account, to allow reading of the data. Do I need to add this user? If so, how? (Keeping in mind I have no idea what I'm doing...)

Any help would be muchly appreciated, I've been workig on this for a while, and feel that I'm quite close. The third party who have supplied the Progress DB and app have been, shall we say, less than helpfull in this regard.

Thankyou all for your time and patience!

-Matt Russell
IT Development
Shine Roche McGowan Solicitors
Queensland, Australia.

 

[mra]

Hi Matt!

Have you checked the access rights for the user you're using to connect to Progress?
If you're using blank userid, check that this is not disallowed for your tables.


Regards
Mike


Queensland, Australia?!?!?
In Denmark it is -3 below, and windy - - want to swap??

 

[Matt Russell]

Hi Mike!

Thanks for your response.

How do I go about checking the permissions? I fire up the Progress Data Administration utility on the server, and connect to the (stopped) database, but the Admin, DataServer and Utilities menus are disabled.

Can't seem to use a blank User ID - I'm using a userID which I've discovered by reading through the properties for the database / appservers, called "proadm". Is this a generic Progress dba account? Or am I on the wrong track?

Thanks heaps!

-Matt

((It's monday morning - Saturday I went swimming at the beach, Sunday, more swimming at a mate's pool. At the moment it's probably 25oC, and not a cloud in the sky... Let me think about it... ))

 

[mra]

Uhm! I'm getting into deep water here!

My guess is, that the user you are using is not a DBA, or you have not performed a full Progress installation
Are you using a Windows version?

(( It's snowing here - enough make it slippery, but not enough to make it fun ))

Regards
Mike

 

[Matt Russell]

No need to panic - it's working!

Trolling through the Progress documentation, I discovered a little snippet of valuable info...

"...you must use the 4GL DBA permissions to create a "sysprogress" user ID and password. Only then can you access the database from SQL-92..."

Using the proadm userid, I was able to fire up the Data Dictionary, and add the mystical "sysprogress" user. It was then a simple matter of setting the client ODBC userid and password to this. Interestingly, if the password was blank/null, it wouldn't work... Also, since I was on a bit of a roll creating users, I added a specialised "Reporting" user, which failed to work as well. Something special with the "sysprogress" user, I suppose.

Anyway, it's all working very well. Spent the rest of the day writing Crystal reports with my new-found live data. Tomorrow, I'll throw them up on the intranet, and we should have a whole bunch of happy (well, as happy as they ever get) managers! I feel like a real Progress dba now!!!

Thanks Mike for all your help! Feel free to now add the qualification of "Guru" to your business cards!

-Matt

(( It may come as a shock, but I've never actually had the pleasure of experiencing real snow... I imagine it would be pretty <i>cool</i> ))