problem using stored procedure to insert records into table

[dbanalyst]

I have a VB6 app that executes an SQL stored procedure that returns a recordset that I loop through 1 record at a time. During the looping process, I am attempting to insert contents from the recordset to another SQL table. This seems to work with small recordsets, but hangs the app if the recordset is large. When I use the SQL Analyzer tool and execute the "insert" command it works fine, so the syntax is correct. Also, if the VB6 calls a "select" for the recordset instead of a stored procedure, it also works just fine. Any help would be greatly appreciated!!!

[ck9663]

I have a VB6 app that executes an SQL stored procedure that returns a recordset that I loop through 1 record at a time. During the looping process, I am attempting to insert contents from the recordset to another SQL table. This seems to work with small recordsets, but hangs the app if the recordset is large. When I use the SQL Analyzer tool and execute the "insert" command it works fine, so the syntax is correct. Also, if the VB6 calls a "select" for the recordset instead of a stored procedure, it also works just fine. Any help would be greatly appreciated!!!

try to create a stored proc that will run this. i think this will really be slow in RS processing. do it on the sql server side.

[amitpatel66]

I have a VB6 app that executes an SQL stored procedure that returns a recordset that I loop through 1 record at a time. During the looping process, I am attempting to insert contents from the recordset to another SQL table. This seems to work with small recordsets, but hangs the app if the recordset is large. When I use the SQL Analyzer tool and execute the "insert" command it works fine, so the syntax is correct. Also, if the VB6 calls a "select" for the recordset instead of a stored procedure, it also works just fine. Any help would be greatly appreciated!!!

Why you have the procedure that returns the recordset to your VB app and then insert in to some other SQL table.

Why dont you make your procedure to insert the data directly in to another table and return you the status whether the insert opetration has been completed successfully? This will improve the performance.

[dbanalyst]

Think I figured it out. Remember when I said:
"During the looping process, I am attempting to insert contents from the recordset to another SQL table."
Well, the change I made was in doing the insert into the other SQL table, I changed the rdo connection to ado connection, and it works just fine now. So I'm guessing there's a limit on the size of a recordset for an rdo connection, or something? Anyway, thanks for helping me think through this.