Openquery returning only 1 row result from AS400

• Jordan Chan

  SSC Veteran

  Points: 242

  More actions

  October 24, 2007 at 8:02 pm

  #178789

  Hi,

  I have a strange problem with a simple openquery statement to an AS400 linked server.

  my statement is:

  select * from openquery(AS400LinkedServer, ' select * from OrderDetail where OrderNo = ''ABC123'' ')

  the query result returns only 1 row, but I know this particular order has 4 order lines. this is confirmed with:

  select * from openquery(AS400LinkedServer, 'select count(*) from OrderDetail where OrderNo = ''ABC123'' ')

  Also, I don't get this problem if I'm executing the statement from Crystal Reports (via ODBC).

  I've checked my Linked Server setting and everything looks ok.

  Has anyone encounter this problem? or have any ideas what could be wrong with the linked server setting?

  Thanks in advance,

  Jordan

• Daniel Crowther

  Valued Member

  Points: 61

  More actions

  October 26, 2007 at 9:25 am

  #745786

  This was a really bizarre problem in that if I issued select top 20 * from openquery(blah blah blah) it would work up to a certain threshold, in my case top 62! Also, most other tables on the AS400 seemed to work with no problems.

  Changing the following setting seems to make it work regardless:

  1. Navigate to "iSeries Access for Windows ODBC" / Performance tab

  2. Click on the Advanced button

  3. Uncheck "Use blocking with a fetch of 1 row"

  Hope it works for you!

  Kind Regards, Daniel Crowther (Keystone Solutions Ltd)

  [font="Arial Narrow"]The information is provided "as is" without warranty of any kind, whether expressed or implied, including, but not limited to, implied warranties as to quality and non-infringement. Keystone Solutions assumes no responsibility for errors or omissions for the information provided.[/font]

  Jordan Chan (10/24/2007)

  ---

  Hi,

  I have a strange problem with a simple openquery statement to an AS400 linked server.

  my statement is:

  select * from openquery(AS400LinkedServer, ' select * from OrderDetail where OrderNo = ''ABC123'' ')

  the query result returns only 1 row, but I know this particular order has 4 order lines. this is confirmed with:

  select * from openquery(AS400LinkedServer, 'select count(*) from OrderDetail where OrderNo = ''ABC123'' ')

  Also, I don't get this problem if I'm executing the statement from Crystal Reports (via ODBC).

  I've checked my Linked Server setting and everything looks ok.

  Has anyone encounter this problem? or have any ideas what could be wrong with the linked server setting?

  Thanks in advance,

  Jordan

• Jordan Chan

  SSC Veteran

  Points: 242

  More actions

  October 28, 2007 at 3:51 pm

  #746067

  Hi Daniel,

  Thanks for the response. Your suggestion certainly fixed the problem.

  Cheers,

  Jordan

  Daniel Crowther (10/26/2007)

  ---

  This was a really bizarre problem in that if I issued select top 20 * from openquery(blah blah blah) it would work up to a certain threshold, in my case top 62! Also, most other tables on the AS400 seemed to work with no problems.

  Changing the following setting seems to make it work regardless:

  1. Navigate to "iSeries Access for Windows ODBC" / Performance tab

  2. Click on the Advanced button

  3. Uncheck "Use blocking with a fetch of 1 row"

  Hope it works for you!

  Kind Regards, Daniel Crowther (Keystone Solutions Ltd)

• kevinwils

  Newbie

  Points: 3

  More actions

  January 27, 2011 at 8:28 am

  #1279114

  Great! Thx for this usefull information!