MS SQL stored proc., linked server, DB2, incomplete txfrs

[mxd]

We have server 2003 with sql 2000. as a client getting data from an IBM
iseries server with DB2 UDB. The connection is through a linked sever with
ODBC connection to DB2. If we invoke a SQL stored procedure to txfr data
from DB2 to SQL 2000 the stored procedure completes fine but only about 128k
of data is txfred out of 6 megs. No errors occur. If we use DTS and import
from DB2 to SQL 2000 everything works fine and all the data is moved. Any
clues as to why the SQL stored procedure will not move all the data ? Assume
the SQL stored procedure is programmed correctly ( it was tested without
going to linked system and worked fine). This is killing us, any help would
be appreciated !!!

[Claus Samuelsen]

mxd wrote:

We have server 2003 with sql 2000. as a client getting data from an IBM
iseries server with DB2 UDB. The connection is through a linked sever with
ODBC connection to DB2. If we invoke a SQL stored procedure to txfr data
from DB2 to SQL 2000 the stored procedure completes fine but only about 128k
of data is txfred out of 6 megs. No errors occur. If we use DTS and import
from DB2 to SQL 2000 everything works fine and all the data is moved. Any
clues as to why the SQL stored procedure will not move all the data ? Assume
the SQL stored procedure is programmed correctly ( it was tested without
going to linked system and worked fine). This is killing us, any help would
be appreciated !!!

Using a cursor without WITH HOLD?
If a cursor is declared without WITH HOLD then a commit will
automatically close the cursor. This problem typically arises when
trying to aviod long transaction like:

declare mycursor cursor for select ...
define counter
while fetch mycursor
do whatever
counter = counter + 1
if counter mod 1000 = 0 then commit
end while

the problem is solved with:

declare mycursor cursor with hold for select ...