Linked Server

[dkirk]

Hey,

I saw the previous thread about Linked Servers, but I haven't had any luck setting up a Linked Server from our SQL Server 2005 to our Progress 9.1D database.

I've also seen Knowledge Base article P120484 which says that I don't need to bother with a Provider String, but I'm still not having any success.

If I right click on my Linked Server and select "Test Connection" it says that it connects successfully. When I try to expand "Catalogs" I get this error:

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: 7311)

This is how I've set it up:

I've got an ODBC DSN called "Mizzisoft". I can connect and see data all right.

Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: MERANT 3.60 32-BIT Progress SQL92 v9.1D
Datasource Name: Mizzisoft

Provider String, Location and Catalog are all blank.

Under Security, I have:

"Be made using this security context" with the same login and password that I can use to connect with my ODBC DSN using any Excel and MS Query.

I'm stumped. I've been searching for days and I can't figure out why it's not working. Does anyone have any ideas?

--
Thanks

David Kirk

 

[RandMan57]

I am having a similar problem with SQL Sever 2005 Management Studio. I am using SQL Server 2005 with SP2 installed.

I have a linked server setup in SQL 2000 using the OE10.1B ODBC driver and it works fine.

I try to do the same in SQL 2005 it seems to work fine as testing the connection succeeds.
But when I try to expand the catalogs in the linked server in SSMS I get a beep, then it locks up. I get the message that SQL Server is busy with an internal operation and never comes back. I have to kill the process with task manager.
I use the "connections will be made using this security context" and specify the same logon and password as I have before.

I have searched and searched online for an answer but have not found one.

PLEASE HELP:errr:

 

[RandMan57]

I think I have found the answer!

I found this and it works!

The "DataDirect 4.20 32-BIT OpenEdge SQL v10.0B" ODBC driver that we are using comes with the Progress 10.0B CD that we have purchased, for the ODBC drivers that come with Progress 10.0B, they are not Unicode compliant yet (noted from the Progress KB that ODBC drivers that come with Progress 10.1A starting to be Unicode compliant that you don't need to set the PC's Window Locale to, eg. "PRC" also requiring to set the "SQL_CLIENT_CHARSET" System Variable from Windows)

That means after we set the System Environment Variables for the codepage 'GB2312'

Start Button -> Settings -> Control Panel -> System -> Advanced tab -> Environment Variables button -> System Variables

Add... -> "SQL_CLIENT_CHARSET" with value "GB2312"

The C#.net program just work all okay for us

Please refer to the following Progress KBs for more info:

Solution ID: p120558
Solution ID: p112404

 

[RandMan57]

Well it worked right after I added the environment variable. I then tried restarting SSMS and the same problem is back.

Go figure...... :furious:

 

[RandMan57]

UPDATE

I have found that if right after opening SSMS I run a select query that references the linked server, it runs and returns results. Then if I try to expand the linked server node, everything works fine.

If I try to expand the linked server node before doing the select query, it locks up.

 

[joey.jeremiah]

or replicate the data and in real time to sql server, mysql or other.

 

[heindhoven]

Hello,
Is it possible what you suggest, to set up a linked server (linked to progress, and it works fine) as a publisher and distributor to a MS SQL subscriber?

 

[andi7171]

Hello,
I had (YESS PAST :biggrin the same problem - with OpenEdge 101b SP3 Fix17, and the SQL-Server ManagementStudio - for a linked server.
I read several threads in the internet - without any hint or solution - but my luck was, that I made a link to an openedge-database - about 2 years before - and this link worked from the first time I tried.
When I made a link to an other openedge-database (for the same openedge-server - and with the same sql-server) I lost my patiente.
The last thing I tried was to compare the registry-settings - between the working ODBC-Connection and the other.
The solution I found is, that the value in the DefaultIsolationLevel is COMPLET USELESS. If you config the ODBC-Data-Source within the GUI - you only can select between four values. But in the working ODBC-Connection there was a value like SQL_TXN_REPEATABLE_READ. So I tried it with the not working linked server - and it worked. When I completly removed the value within the regedit - it worked also.
kind regards
andi

 

[nullpointer]

Hello everybody.

I have similar problem.
I'm using MS SQL Server 2005 to read data from Progress 10 DB using OpenEdge 10.2b ODBC driver.
I've created a DSN (name 'pp'), the test connection is successful, and it works with Excel, but not in SQLServer.

I've set up a Linked Server with this data:
provider: Microsoft OLE DB Provider for ODBC Drivers
data source: pp
provider String: MSDASQL

Security settings:
'Be made using this security context', with user + pwd.

If I try to query data using OPENQUERY:
SELECT * FROM OPENQUERY(LINKED,
'SELECT *
FROM jtdsvol.jmf_broadcast ')

then I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT *
FROM jtdsvol.jmf_broadcast " for execution against OLE DB provider "MSDASQL" for linked server "LINKED".

What am I doing wrong??
Please help me, I'm frustrated !!!
Thx.