Ok, i've got a really hard question for you guys!

[mas_oyama]

Ok, it may be simple, but i really dont find it! lolso, We have a AS400, wich contains live data from our stores. Here we work on SQL Server, but we need access to the live data. So we want to transfer data every night from the 400 to one of our databases in SQL Server. So we made a Linked Server, wich works fine. But there is, somewhere, a buffer that wont let us transfer a big load of data at the same time. for example

SELECT * FROM OpenQuery(MyLinkServer, 'SELECT * FROM SYNTAXPF."NU.POHDR"')

this returns 862 rows.

SELECT * FROM OpenQuery(MyLinkServer, 'SELECT PHDIVX FROM SYNTAXPF."NU.POHDR"')

this returns all 1719 rows from the table.So there must be a buffer between the 2 servers, but i cant seem to fin how to remove it, or at least make it bigger. I had to create a stored wich 5 tables from the 400 and put the information into 1 table in SQL Server, but there is an awsome load of records in these 5 tables, so i was forced to make a cursor wich loops on suppliers, and this takes aver a half hour to execute, since we have close to 5000 suppliers, but we only receive stock from like 2-3% of them, so my loop execute a heck of a lot of select statement that are actually useless, and that takes a lot of time to execute. i would prefer to remove that stupid buffer, and simply make one select that will retur all the rows from the 5 tables. it would take like 3 minutes to execute...