Linked Server To As400 Strange Links 1

[Skittle]

This is a long shot but here goes...

I have created a linked server definition on my SQL 2000 server to an AS400.

The strange thing is that when I use a simple LEFT OUTER JOIN query on SQL Server on the two tables via the linked server, I get a smaller returned set of rows that the same statement executed within the AS400 SQL environment. I am completely at a loss to why this is. Is there some sort of subtle difference the way linked servers or the AS400 works?.


Example:-

On The AS400

SELECT count(Field1) FROM Library1/File1 C1
LEFT OUTER JOIN Library1/File2 C2 ON
C1.Field1 = C2.Field1


On SQl Server

SELECT count(Field1) FROM AS400ALL.AS400.Library1.File1 C1
LEFT OUTER JOIN AS400ALL.AS400.Library1.File2 C2 ON C1.Field1 = C2.Field1;




Dazed and confused

 

[onpnt]

I would also use OPENQUERY or OPENROWSET instead of entering your query directly. The statements will usually get interpreted well but there will be issues going from one sql construct to the other.

It's been year’s sense I worked on DB2 but I would bet support for joins has not caught up to the full features of other constructs. I would start looking into DB2's support on joins etc. If you are going to be doing complex statements from SQL Server to DB2 I would recommend pulling data down and then doing your statements on it so there is no relying on the provider to return the results from your TSQL written statements on DB2


_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[onpnt]

Comparison of different SQL implementations

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[Skittle]

Handy link, many thanks.

Dazed and confused