469,076 Members | 1,538 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

problem using stored procedure to insert records into table

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!!!
Oct 29 '07 #1
3 4293
ck9663
2,878 Expert 2GB
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.
Oct 30 '07 #2
amitpatel66
2,367 Expert 2GB
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.
Oct 30 '07 #3
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.
Oct 30 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by thoidi | last post: by
7 posts views Thread by jennifer1970 | last post: by
6 posts views Thread by rn5a | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.