March 20, 2002 at 11:57 pm
Is there a way to transfer a recordset (containing a list of numbers) to a stored procedure using ADO?
I'm currently breaking up the numbers into text strings and sending them as parameters.
Any ideas???
Bruce - Coffs Harbour
March 21, 2002 at 1:27 am
This is an intriguing question. Could you elaborate on the thinking behind passing the recordset INTO a stored procedure.
Where's the recordset coming from?
What is the stored procedure going to do with it?
One last question, what are you using to drive ADO (ASP, VB, Delphi, anything else...)?
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 21, 2002 at 5:09 am
Parameters is the usual approach. Not a great one, but it works. OpenXML offers some great alternatives, you can send over the equivalent of a shaped recordset (or a plain one) and do so with one parameter. You're just leveraging the XML parser to pull the data apart. Both are powerful techniques and are one of the ways you can reduce round trips - got a couple articles on the subject:
http://www.sqlservercentral.com/columnists/awarren/roundtrips.asp
http://www.sqlservercentral.com/columnists/awarren/reducingroundtripspart2.asp
http://www.sqlservercentral.com/columnists/awarren/reducingroundtripspart3.asp
Andy
March 21, 2002 at 5:51 am
Thanks for the replies. I'll look into the XML thing, I don't know any XML.
The process is:
In VB I create an ADO recordset of a list of numbers from a listbox the user has entered. The list can be 1 record or 20,000 records. I need to get this into a stored procedure (MSSQL7) to update tables with the selected records from the VB6 program. I do this for a number of reasons, main one is to NOT have to send each record over the network one at a time.
I hope to be able to send the recordset in one hit as a parameter.
Bruce - Coffs Harbour
March 21, 2002 at 7:32 am
20k is a lot. It would be a good sized XML doc and XML docs aren't small to start with. Shouldnt be too bad if just an int and you keep the tags small. Can't have 20,000 parameters either! If you're on a LAN you can probably afford to do it one record at a time. Going to be a little slower for the user but might end up being cleaner. Alternative might be to dump the ID's into a text file, copy up to the server, then have DTS import and run the update.
Andy
March 21, 2002 at 11:34 am
I'd go with Andy. The Text-File/DTS load method would be my preffered route. XML's great particularly with handling complex data-set structures, but yours is simple.
DTS gives you the oppurtunity to do some transformations on the data as it comes in, should you so wish and you can call it from within your stored procedure
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 21, 2002 at 3:53 pm
Thanks again.
How do I save a recordset or a list of numbers into XML in VB, the number field is an int. I can't find any documentation on it. This is OK in MSSQL 7?
If I can't do that I'll stick with using a parameter with 1000 records (numbers can be 7 digits) made up of CSV's and send this for as long as it takes.
Bruce - Coffs Harbour
March 22, 2002 at 3:36 am
Check out ADO 2.6 there is a flag you can set on the recordset object(PersistXML from memory) which will return your recordset in XML. Neat huh?
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 22, 2002 at 9:19 pm
Very good, so easy. 🙂
MSSQL 7 doesn't support the openxml thing. :-(. There's probably a workaround but I've got the string thing happening now where I just pass a string of numbers in a variable... it works, fast too. Thanks for the feedback.
Bruce - Coffs Harbour
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply