Drammy
Member
Hi guys,
I am new to the forums so; hello everyone.
I have created a Linked Server in SQL 2005 linking to an Openedge 10.0B database.
I am having to do a query like:
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.
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.
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
I am new to the forums so; hello everyone.
I have created a Linked Server in SQL 2005 linking to an Openedge 10.0B database.
I am having to do a query like:
Code:
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.
Code:
SELECT [ENTITY-INT-CODE], [NAME], [ENTITY-TYPE-CODE] FROM BUGFIX..pub.Entity WHERE [ENTITY-INT-CODE] = 9386Hey 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.
Code:
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