SQL 2005 linked server to OpenEdge

[Drammy]

Hi guys,

New to the forum, so hello...

I have created a Linked Server in SQL 2005 linking to an Openedge 10.0B database.

I am having to do a query like:

	SELECT	[ENTITY-INT-CODE], [NAME], [ENTITY-TYPE-CODE]
	FROM	BUGFIX..pub.Entity
	WHERE	[ENTITY-TYPE-CODE] = 'INDI' and IMP=''

This returns 549 records.

I then perform a simlar query natviely in OpenEdge and get 562 records so my SQL is 13 short of the correct rowcount.

I then tested in MS Access using linked tables and the same DSN used in the SQL Server 2005 Linked Server. Access gives me 562 records, verifying what OpenEdge is telling.

I determined which records were not being returned and then queried the linked server for those records directly.

SELECT [ENTITY-INT-CODE], [NAME], [ENTITY-TYPE-CODE] FROM BUGFIX..pub.Entity WHERE [ENTITY-INT-CODE] = 9386

Hey presto all but one returned properly, yet these records don't appear in the mass select detailed above.

I was obviously quite dismayed with this so started playing around and changed the Linked Server settings in SQL 2005 so that the collation compatible setting is set to true.

EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'collation compatible', @optvalue=N'true'

I now get 532 records returned. I get the 12 of the original missing 13 records returned but now a new set of records aren't returned.

I have queried the collation in OpenEdge and it is "Basic", obviously this doesn't map to any SQL Server 2005 collation. Anyone got any ideas which collation setting I can use in order to mimic the "Basic" OpenEdge collation setting?

There are legitimate reasons why some of the records aren't returning (like the 1 in 13 not returned when setting the collation compatible setting to true). In this instance one of the fields in the SELECT clause [NAME] is too long for SQL (indicating that the SQL length assigend in OpenEdge has been passed). But I cannot account for why when specified in the WHERE clause the records appear, yet don't appear in the original mass selection detailed at the start of this thread.

If anyone has any suggestions or solution for this problem it will be very well received,


Thanks,
Drammy

[rmiao]

Tried with openquery instead of four-part name?

[Drammy]

Hey rmiao,

I'll give it a go next time I'm in the office.

I did try SSIS to draw the data but got the same results.


Thanks for the suggestion. I'll give it a go Monday morning...
Drammy

[Drammy]

Just VPN'ed in and tried it - no joy.

SELECT [NAME] FROM OPENQUERY (BUGFIX, 'SELECT * FROM pub.Entity')
WHERE [ENTITY-TYPE-CODE] = 'INDI'

This fails to retrieve all the records.

One other thing, I couldn't place my WHERE clause in the OPENQUERY query as the DataDirect drivers seemed to reject it, had to do alter a la above.


Any other ideas?
Drammy

[rmiao]

Which provider did you use to create linked server? Has row level permission on Oracle? Used same Oracle id in your test?

[Drammy]

Oracle?

Here's the create script for the Linked Server:

/****** Object:  LinkedServer [BUGFIX]    Script Date: 05/29/2007 09:25:21 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'BUGFIX', @srvproduct=N'DataDirect', @provider=N'MSDASQL', @datasrc=N'5110'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BUGFIX',@useself=N'False',@locallogin=NULL,@rmtuser=N'sysprogress',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'BUGFIX', @optname=N'use remote collation', @optvalue=N'true'

[rmiao]

Should be OpenEdge. May need to check with vendor which version of mdac OpenEdge supports.

[Drammy]

Sorry, tbh the provider stuff confuses me a bit.

Who is the vendor in my case?

We are a software house that gets the software straight from Progress.

[rmiao]

What's OpenEdge then? Who made it? Who makes OpenEdge odbc driver or you just use Progress odbc driver? I'm confused.

[Drammy]

OpenEdge is the name for Progress v10. It really is the same database engine as Progress v9.

The ODBC driver is made by a company called DataDirect, although they too are Progress (or so I believe).

I guess I'll just have to get in touch with Progress then?


Thanks for your help,
Drammy

[rmiao]

Yes, find out which version of mdac they support.