Actually you could use XML to send multiple rows in SQL Server 2000 too. The only catch is if the database is run on Fibers instead of threads OpenXML would not work.
Thanks for your update. Actually it is based upon SQL 2005.
Hi, good article.
I'm not sure that passing a long string is the best way of doing this. There are some limitations such as max length, sql escaping, performance, delimiter escaping, and the effort to program all this !
Yes, we have limitation of string length, which I mentioned at the end in Pros
I would prefer the combination of BCP + bulk updates:
bcp myfile.txt aux_table
update realtable with_values_in aux_table
even in 3-tier applications you can always put the file "near" the database server.
In the scenario, where this code has been implemented, we cannot use external resource like Disk etc. from the application. Our client has well defined code security policy.
Moreover, we are dealing around 500 records at max at once.