I hope someone can help me out with connecting to a Progress Database.
Here is what is going on; I need to create a connection from SQL Server to a Progress database. I need to pull data down regularly to perform a comparison between multiple data sources including this Progress server.
I have configured the ODBC Data source as a name of ‘ProgressODBC’ to point to the Progress database server (ProgressServer) that I need. I can access this server through MS Excel by setting the data source to this ODBC. Progress driver is 'DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E' PGPR0918.DLL
However, I have tried to create a Linked Server to no avail. I seem to be able to create the Linked Server but when I try to run a query I get errors. If I follow the idea that the path to get to the tables are {linked-server-name.catalog.schema.table-name} I still get errors.
Here are the queries that I have tried:
- select RCN from lsProgress.RA.SYSPROGRESS.RESIDENT
- select RCN from lsProgress.PUB.SYSPROGRESS.RESIDENT
- select RCN from lsProgress.RA.dbo.RESIDENT
- select RCN from lsProgress.PUB.dbo.RESIDENT
- select * from openquery(lsProgress, 'SELECT RCN FROM PUB.RESIDENT')
This is the error I get: 'OLE DB provider MSDASQL reported an error. Invalid connection Data source name not found and no default driver specified. OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].'
What I choose to use for the Linked Server is this:
- Linked Server Name: lsProgress
- Server Type: MS OLE DB Provider for ODBC Driver
- Product Name : 4.10 32-BIT Progress SQL92 v9.1E (I have even used the DLL name)
- Data Source: ProgressODBC (I have even used the DLL name and the server name)
- Provider String: ProgressServer
- Location: I used the port number that was defined on the ProgressServer. I even tried to use the full location of where the database is located.
- Catalog: I used the name of the database that I want to connect to
On the Security tab:
- Login should be made using this security context: I used the system userid that was supplied. However, there is no password. So, I used a 'space' as I found from Goggling this should work. Plus, that is the way the ODBC is set up too.
On the Server Options tab:
- I choose RPC and Use Remote Collation
I have gone as far as leaving the following enteries blank Product Name, Datasource Name, Location, and Catalog. Then filling in the Provider String with any of the following:
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=<BLANK>;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=’’;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=””;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
I even left everything blank and just filled in the Datasource name with the ODBC DSN that I created: ProgressODBC
I am at a loss for what I can do. Hopefully someone has encountered this and can help me.
Thanks!
Don
Here is what is going on; I need to create a connection from SQL Server to a Progress database. I need to pull data down regularly to perform a comparison between multiple data sources including this Progress server.
I have configured the ODBC Data source as a name of ‘ProgressODBC’ to point to the Progress database server (ProgressServer) that I need. I can access this server through MS Excel by setting the data source to this ODBC. Progress driver is 'DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E' PGPR0918.DLL
However, I have tried to create a Linked Server to no avail. I seem to be able to create the Linked Server but when I try to run a query I get errors. If I follow the idea that the path to get to the tables are {linked-server-name.catalog.schema.table-name} I still get errors.
Here are the queries that I have tried:
- select RCN from lsProgress.RA.SYSPROGRESS.RESIDENT
- select RCN from lsProgress.PUB.SYSPROGRESS.RESIDENT
- select RCN from lsProgress.RA.dbo.RESIDENT
- select RCN from lsProgress.PUB.dbo.RESIDENT
- select * from openquery(lsProgress, 'SELECT RCN FROM PUB.RESIDENT')
This is the error I get: 'OLE DB provider MSDASQL reported an error. Invalid connection Data source name not found and no default driver specified. OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].'
What I choose to use for the Linked Server is this:
- Linked Server Name: lsProgress
- Server Type: MS OLE DB Provider for ODBC Driver
- Product Name : 4.10 32-BIT Progress SQL92 v9.1E (I have even used the DLL name)
- Data Source: ProgressODBC (I have even used the DLL name and the server name)
- Provider String: ProgressServer
- Location: I used the port number that was defined on the ProgressServer. I even tried to use the full location of where the database is located.
- Catalog: I used the name of the database that I want to connect to
On the Security tab:
- Login should be made using this security context: I used the system userid that was supplied. However, there is no password. So, I used a 'space' as I found from Goggling this should work. Plus, that is the way the ODBC is set up too.
On the Server Options tab:
- I choose RPC and Use Remote Collation
I have gone as far as leaving the following enteries blank Product Name, Datasource Name, Location, and Catalog. Then filling in the Provider String with any of the following:
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=<BLANK>;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=’’;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
- DRIVER={DATADIRECT 4.10 32-BIT Progress SQL92 V9.1E};UID=SYSPROGRESS;PWD=””;DatabaseName=RA;PORTNUMBER=111111;HOSTNAME=ProgressServer
I even left everything blank and just filled in the Datasource name with the ODBC DSN that I created: ProgressODBC
I am at a loss for what I can do. Hopefully someone has encountered this and can help me.
Thanks!
Don
). The field widths of the character fields in the database need to be adjusted on a daily basis and security must been set up to provide enough priviliges. (I think it is a bad habbit to query with sysprogress).