Export Progress 10.1c to SQL on schedule?

[sentinelace]

I want to dump paticular tables from Progress to SQL so I can grab data from SQL and pull it into my sharepoint site. This seems easier than trying to create a different web service for everything. What is the best way to do this? I am running 2008 R2 SQL server with R2 SQL.

 

[TomBascom]

I don't understand the reference to creating a different web service for everything.

Why wouldn't you just connect to the Progress db using the SQL-92 interface?

 

[sentinelace]

In the end I just want to pull data from progress to share point. Is BCS better or your method? How do I set this up? Sharepoint doesn't support an ODBC connection but it supports a connection to SQL. I cannot pull data into sharepoint via ODBC that I know of or infopath? Where do I get the driver since I actually own Progress.

 

[TomBascom]

I take it that by "SQL" you mean Microsoft SQL Server?

I have no idea what BCS is.

About the only thing I know about Sharepoint is that it is something that Microsoft sells. I find it amazing that it cannot make an ODBC connection. That doesn't pass the sniff test but I suppose that MS knows no shame so maybe I shouldn't be surprised after all.

If you want to dump the data on a schedule you can certainly do that. Create a "scheduled task" and run some data extracts. If you need us to write the extracts for you then you will have to be a bit more specific about what data you need and what format you would like to see it in.

 

[sentinelace]

I have tried connecting via ODBC with the progress driver. I use infopath to try and connect to the database, it comes up and I can see all the tables, but then I get an error that it failed to connnect. So close. From what I read, it's best practice to use a web service. How would I go about creating a web service for a paticular table in progress?

 

[TomBascom]

So you are saying that, actually, Sharepoint can connect to a db via ODBC?

I don't know anything about "infopath" except that I just read via Google that it is some sort of Sharepoint related forms software -- which would seem to be totally unrelated to extracting data from Progress to Sharepoint. Or maybe your real issue is that you want to use a Progress database as the data source for an infopath form? In which case ODBC is, once again, the simplest way forward and should be perfectly supported.

Anyhow... back to ODBC. You got a connection and you could see the tables. Then you got an error? Errors usually come with some descriptive text and a helpful error number. Maybe you could share those? (In their entirety please.)

Since you have not shared the actual error I'm just guessing, but it seems likely that the DBA has not granted you access to the tables. The userid that you used to connect needs to have permission to query data. Your local DBA should be able to help you with that. It is definitely a "worst practice" but you could also connect as "sysprogress" if this is some sort of wild west operation where everyone has "administrator" level permissions on everything.

Where did you read that it is best practice to use a web service? It might be -- but there is a lack of context within which to make that judgment.

Web services are not typically constructed as "for a particular table". They usually provide a more functionally oriented "service" of some kind like "order listing" or "currency conversion" or the ilk. Writing a web service is not quite trivial and would be better discussed in the "development" forum.

 

[sentinelace]

Yes I am trying to get an ODBC connection so that I can pull data from the progress database into my form to avoid manual entry. There is no error code. I do login with sysprogress and see all the tables but then I get the error that Infopath has encoutered and error and the operation has failed. My idea about SQL (M$) is that all the sharepoint applications intergrate easlily with SQL. If I could some how get the data over to SQL, I could easily pull it onto my infopath form from there. Every google I run says I need to use a web service, I just don't understand why I get this error if I can see the tables?

 

[TomBascom]

You are going to need to find a way to get more detailed about the error before anyone will be able to help you.

If info path insists on not describing the error then try using some other SQL query tool. There are thousands of them available for Windows.

 

[tamhas]

Are you using a Progress SQL client? If not, that is your first problem. This is included in a Progress Client Networking license, though not installed by default, as I recall. There is also a separate SQL Client product which is a free download in versions 10+, but does require talking to your salesrep to get it on the ESD site. If you don't have that, then you really need to get it before you can have claimed to tried ODBC.

If you have it, then see the second and third child pages here http://www.oehive.org/node/952 for some setup ideas. There is another more general reference on PSDN, but I don't have a URL handy. Go though that process and report any errors. There are some standard issues people run into with permissions, not having SQL access enabled on the DB, etc. None of it is very complicated.

If you can setup and use ODBC, I would suggest that this was a far superior approach because it is general purpose and flexible and performant.

If, for some reason, this is not possible, then you have a couple of options.

One is just to have a 4GL procedure which you run periodically that dumps out the data you want in ASCII and then create a task on the SQL side to import this data. Works, but is so 1980s!

Creating the web service is possible, but involves a lot more issues. E.g., are you currently running Webspeed or AppServer?