SQL Distinct by Default ?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Distinct by Default ??

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-07-04 : 11:47:00

Hiya,
I've got a strange one for you guys..

I Have a linked server from SQL to DB2 (as400)
The link works fine and I can see all tables..

However the distributed query below only returns 89 records when I know it should return in th region of 7,000

movex_mvxamodsfc is the linked server
mvxamodsfc is a table from the server
oxdtrk is from the table

The query I run is

SELECT *
From OPENQUERY(movex_mvxamodsfc, 'select * from mvxamodsfc.oxdtrk')

The funny thing is ..If I run a distinct query then I also get 89 results..so it looks like SQL is treating my select Statement just like a select distinct statement??

ie:

SELECT *
From OPENQUERY(movex_mvxamodsfc, 'select * from mvxamodsfc.oxdtrk')

Has anyone ever seen this before ??

Ray..

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-04 : 12:29:26

?? What Service Pack are you on for SQL Server? Where did you get the DB2 driver?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-07-04 : 12:53:37

Hiya,
I am using Microsoft 2000 SP3
I have the IBM Client Access Express ODBC data source driver 8.00.03
setup as a System DSN on the server and then linked to in SQL

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-04 : 12:57:42

Ok. That's weird. If you select a count of the records in the OPENQUERY, I'm assuming you get the right amount?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-04 : 15:58:14

is there some funky setting in your ODBC DSN that might cause the distinct query?

Are you running the latest ODBC for DB2? Is the version of the IBM driver compatible with your Windows OS release?

Have you called 800-IBMSERV?

-ec

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-05 : 06:37:19

Is it possible you are linking to a DB2 VIEW...which has distinct already in it???

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-07-05 : 07:29:16

Hi Guys thx for your replys...
I figured out what it was...

The IBM Client Access Express ODBC data source driver
needed to be updated with the latest Patch from IBM
Driver is now at 8.00.08

All 7000 records now get returned
Ray..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources