By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

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

P: n/a
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 !!!
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 ...
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.