Using a SQL view to pull from AS400 in SQL2000

• Mitch2007

  SSC Eights!

  Points: 830

  More actions

  May 5, 2010 at 11:12 am

  #224199

  Good day all,

  I am using SQL 2000 to pull Data from a AS400 DB and have a DTS package that

  pulls in the required data and all works well enough. not really as the Data is pulls in is dictated from a view using Global parameters, it takes the lowest acct number and the highest and brings in the desired records, but when this procedure is run during the end of the month the Account numbers are scattered in range and bringing everything in is both unnecessary and very time consuming, so I decided to base the range from a view, and will use that dataset as a global variable, however this pulls in only the first account of course. How do I get the AS400 connection SQL to read every account and action the SQL code for each and every Account from the View? The code for the Global variable is as follows:

  SELECT Account FROM vwEndmonthAccts

  and the code for the connection between AS400 and the SQL destination is:

  select *

  from "Awkward"."Fussy"."AS400tbl"

  where Account = ?

  Can anyone assist with this problem? I did think about a do loop in the code above but not sure how to go about coding or if it will work since the connection is based on AS400.

  Thanks in advance,

  Craig.....

• Neal Sivley

  SSCommitted

  Points: 1766

  More actions

  May 17, 2010 at 9:05 am

  #1168311

  You can do it in a SQL cursor. But a warning to you, cursors have bad performance but are useful in specific instances when you have no other option.

  Thanks,

  Neal

• Mitch2007

  SSC Eights!

  Points: 830

  More actions

  May 17, 2010 at 10:14 am

  #1168347

  Thanks Neal.

  Never used SQL cursor before, but shall look into it.