Warehousing an OpenEdge 10.1C Database in SQL Server 2005

[cotton31]

Hi,

I'm looking to set up the above, but haven't found any clear instructions on the web.

Could someone point me in the right direction or give me a guide?

Sorry if I'm being quite vague!

Thanks,

 

[vinod_home]

What have you done till now, what do you expect to do.

 

[cotton31]

I have the Progress Database set up as a linked server.

I have created a new Integration Services project in Business Intelligence Development Studio and created a new ADO.net connection using an ODBC driver. I've chosen the database and given it some login credentials (I'm not sure what these should be - do OE database have the equivalent of a SQL SA user?)

I've then added a DataReader Source tool to the Data Flow tab and used my ADO.net connection, however when passing a query to it for it's SQL command I'm getting Autorization Errors.

[ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE] Access Denied (Authorization Failed)(7512)

Am I right so far, or should I be doing something differently?

 

[vinod_home]

You need to create a sql user on the Progress database side and grant access to access all the tables that you need. If you look at the Progress knowledge center, i think its in the top few frequently asked questions. Link to the knowledge center. http://progress.atgnow.com/esprogress/results.do

 

[RealHeavyDude]

The Progress database is not an SQL database in the first place. Therefore in order to use it successfully with SQL clients you must set it up:

• When you connect to a Progress database with an SQL client the first time there are only two possible login credentials that are accepted, both of which will have DBA privileges:
  • The login credentials of the OS account under which the database was created.
  • The user SYSPROGRESS which must be added manually via the data administration tool. You can specify your own password.
  • Only this two user can grant SQL privileges to other SQL users even if they already exist in the Progress user list on the 4GL side of things.

• Next, when you start the database broker it's either set as default or you must explicitely tell it so that it will accept SQL connections ( but I think you are already beyond that step ). You can do this in specifying the -ServerType both parameter for startup.

• Last, but not least, when you plan to use SQL connections to the database for more than tests or onetime acitivities then you should start a secondary login broker for the database which is dedicated to handle the SQL clients.

HTH, RealHeavyDude.