Linked Server

itsAK

New Member
Hi all,

OK.... I wish to set up a linked server within MS SQL Server 2005 to access my PROGRESS DB for reporting purposes. The Linked Server will allow us to update the tables ect and then run our queries off of SQL Server.

Im having trouble in connecting to PROGRESS via SQL Server using ODBC and I was looking for advice

Here is what Im doing:

Right click on Server objects and select new linked server

Linked Server: PRO_SQL


Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: MERANT 3.60 32-BIT Progress SQL92 v9.1D
Data Source: Linked_test
Provider String: DSN=linked_test;DB=Data1;UID=odbc;PWD=pass1234;HOST=reporttest;PORT=23400
Location: I HAVE THIS BLANK????
Catalog: I HAVE THIS BLANK????

The two last fields I have blank, is this the problem? what should go in there??

Here are the errors Im getting:

OLE DB provider "MSDASQL.1" for linked server "PRO_SQL" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed.".
OLE DB provider "MSDASQL.1" for linked server "PRO_SQL" returned message "[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Access denied(Authorisation failed)".
OLE DB provider "MSDASQL.1" for linked server "PRO_SQL" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Optional feature not implemented.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL.1" for linked server "PRO_SQL".


I have also tried it with another STRING and I get a separate error when I try and run a querry:

DRIVER={linked_test};UID=odbc;PWD=pass1234;DatabaseName=Data1;PORTNUMBER=23400;HOSTNAME=reporttest

Error:

OLE DB provider "MSDASQL.1" for linked server "PRO_TO_SQL" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed.".
OLE DB provider "MSDASQL.1" for linked server "PRO_TO_SQL" returned message "[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Error in Network Daemon".
OLE DB provider "MSDASQL.1" for linked server "PRO_TO_SQL" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Optional feature not implemented.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL.1" for linked server "PRO_TO_SQL".

As always any help greatly appreciated!

AK
 

woyakj

New Member
Did you ever get this working? I am running into the same issue and would love to solve this! Thanks!
 

woyakj

New Member
the whole thing.. ;-)

Here is my ODBC setup:
Product Name: MERANT 3.60 32-BIT PROGRESS
Data Source: SKYWARD
Provider String: DRIVER={DataDirect 5.0 Progress SQL92};UID=sysprogress;PWD=<BLANKED>;DatabaseName=skyward;PORTNUMBER=23473;HOSTNAME=67.53.75.200
catalog: skyward

and when I connect to the linked server and run "select * from skyward..pub.name" i get this back:

OLE DB provider "MSDASQL" for linked server "skyward" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed.".
OLE DB provider "MSDASQL" for linked server "skyward" returned message "[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Access denied(Authorisation failed)".
OLE DB provider "MSDASQL" for linked server "skyward" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Optional feature not implemented.".
.Net SqlClient Data Provider: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "skyward".

I can't help but feel like I'm just missing something here. Any ideas? BTW: it says you are from Dublin. As in Ireland? I was just there for my honeymoon a few weeks ago. Ireland is my favorite place in the world! :)

thanks for any help you can provide!

--jason
 

itsAK

New Member
Here is my succesfull connection string:

DSN=name_test;DB=data1;UID=odbc;PWD=odbc1234;HOST=datatest;PORT=23400

Try and run this SELECT statement.

SELECT * FROM LINKED_SERVER.."sysprogress"."sysdbauth"

From the looks of your error messages I think the linked server is getting past SQL server but failing on PROGRESS...

And yes Dublin Ireland is where im from..... Glad you enjoyed your honey moon!!
 

woyakj

New Member
Thanks for the response! I tried this, but got the exact same error message. Here is the modified Provider string I used:

DSN=skyward;DB=skyward;UID=sysprogress;PWD=<BLANKED>;HOST=67.53.75.200;PORT=23473

and once again I get the same error. I notice that you are connecting by name and I am connecting by IP, though I'm not sure why that should matter. Note that the UID, PWD, IP and Port settings work fine if I do a "Test Connection" in the ODBC setup. I guess I'm at a loss here...

thanks for your help!

--jason
 

itsAK

New Member
Hi Jason,

If your ODBC Connection is set up correct which it is if you can run a test connect then I cant see why it is not working.

Try this....

Click properties on your linked server
under security make sure the following is ticked.

"For a login not defined in the list above, connections will be:

Be made using the logins current security context."

Then make sure you have the correct permissions set up ie: make sure you are the DB owner ect....

That should work for you!

Let me know
 

woyakj

New Member
You are THE MAN :) That was it.. I never even thought about that, but it was set to "Be made without using a security context". I switched it to "Be made using the login's current security context" and it worked! Thanks so much. You have rescued my day :)

--jason
 

klar

New Member
I am having a similar problem, but can't get the thing to connect at all. When I try to display tables (under Linked server in Enterprise Manager), it returns error message "Error 7739: OLE DB Provider 'MSDASQL' reported an error.... IDBInitialize::Initialize returned 0x80004005..."
My linked server definition is:
Product Name:TM
Data Source: TM_TEST (this passes the Test Connection test)
Provider String: MSDASQL
Location: blank
Catalog:ticket (the name of the Progress database).
On the Security tab I have selected the same remote login/password as I use in the data source (which also works with the Progress SQL Explorer Tool).
I have added the appropriate entry in C:\...\drivers\etc\services file that is needed to access the Progress database using a 4GL client program.
Any ideas?
Thanks
 

woyakj

New Member
Klar

I got this to work in a different fashion than you seem to be trying. What I did was setup (using the Progress SQL92 ODBC driver) an ODBC connection (system DSN) on the server I wanted to set it up as a linked server on, and then added the linked server in Enterprise Manager as an MS OLE DB for ODBC connection. The settings that worked for me are:

Code:
Product Name: MERANT 3.60 32-BIT PROGRESS
Data Source: (the name of your system DSN, SKYWARD in my case)
Provider String: Dsn=SKYWARD;databasename=skyward;uid=sysprogress;pwd=(PASSWORD)
Catalog: skyward
Your mileage may vary, but that is what worked for me. I hope it helps.

Jason
 

klar

New Member
Jason,
Thanks for the quick reply. I tried changing my linked server definition using yours as a pattern, but the results were the same. I wonder if there is something else that needs to be checked?

Ken
 

woyakj

New Member
It has actually been several months now since I implemented this, so I may be forgetting something, but I think that as long as the ODBC connection tests fine on the box you want to use it on, and you have your provider string and the other settings right under your linked server ODBC settings, it should be ok. Keep in mind too that i set this up using the way old version of Progress that Skyward uses (9.1D i think), and did the odbc on an SQL2000 server, so if your setup is more recent, things may work differently (though I haven't actually heard anything to this effect). I wish I was more help :-/

Jason
 

esedmak

New Member
How well does using two servers work? We are implementing Datatrax which run on Progress and are considering this same structure. Does it work well? Are you able to get near real-time reporting? How do you synchronize the tables?
 

lety3930

New Member
Hi Jason,

If your ODBC Connection is set up correct which it is if you can run a test connect then I cant see why it is not working.

Try this....

Click properties on your linked server
under security make sure the following is ticked.

"For a login not defined in the list above, connections will be:

Be made using the logins current security context."

Then make sure you have the correct permissions set up ie: make sure you are the DB owner ect....

That should work for you!

Let me know

This may sound like dumb question, user needs to be DB owner of what databases, master?
 

tomeng01

New Member
lety3930:

I believe he was refering to the user that is connecting to the database must have DBA privileges.

On a similar note, I received the same error messages:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server <<servername>>.

In my case, I had been successfully accessing the Progress DB through SQL Server 2005 and then I received the above error. It turned out the Progress DB had locked up and needed to be bounced. Thought I would add my experiences...

-Tom
 

paulsg

New Member
I have SP2 for SQL 2005 - I run select commands and all OK.

I cannot see my Progress DB tables.

I have followed all of the instructions, but no joy.

I am using Progress 9.1E.

Any help would be appreciated, thanks
 

reneejaramillo

New Member
I am trying to do a similar thing-- creating a SQL Server linked server to a Progress database. However, my driver is DataDirect 4.2 32-BIT OpenEdge SQL v10.0B. I tried setting up the linked server like this:

Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: DataDirect 4.20 32-BIT OpenEdge SQL v10.0B
Data source: MULTI (my system DSN name)
Provider string: DSN=MULTI;DB=SUBS;UID=ODBS;PWD=<PutPasswordHere>;HOST=10.133.5.6;PORT=4000

But I got the same error:

OLE DB provider "MSDASQL" for linked server "MULTI" returned message "[DataDirect][ODBC OPENEDGE driver]Driver's SQLSetConnectAttr failed.".
OLE DB provider "MSDASQL" for linked server "MULTI" returned message "[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Access denied(Authorisation failed)".
OLE DB provider "MSDASQL" for linked server "MULTI" returned message "[DataDirect][ODBC OPENEDGE driver]Optional feature not implemented.". (Microsoft SQL Server, Error: 7303)

Any insight would be greatly appreciated. Thanks, Renee
 

reneejaramillo

New Member
So, I figured out how to successfully create the linked server and I am able to run queries to it. But, I can't view the list of tables under Catalogs. What do I need to specify for Catalog when I create the linked server so that I can see the list of tables? Or, is there something else I need to do? Here's the error I get when I try to expand out the linked servers Catalogs:

The OLE DB provider "SQL Server" for linked server "(null)" reported an error. One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)
 
Top