Thanks.....

In the situation I'm in, it's not as simple as a primary key deletion via stored proc. There *is* a stored proc for deletions that accepts the PK. Getting to that point is the issue.

It's an incoming text file with about 70 columns that has to be validated and pumped over to the server db (or delete or change existing records)....since the server db generates the PK via an identity, the text files to not have a PK. So I have to go looking for the PK using a composite key that should be unique. Then I can take advantage of the SP and PK.

Now, in the past, everything was SPT. The major problem there was that UPDATEs had to be done via multiple UPDATE statements because these 70 columns have long field names and there's that old ODBC 254 character limitation. And they don't have an SP for update because of the huge number of parms that would be required.

So, what's going to make this work for me on this conversion to ADO is this simple question: Is there any limit to the size of the Command.CommandText property? If not, then I can collapse all those SQLEXEC UPDATE statements into a single Command objects CommandText and Execute and that is bound to be much more efficient.

TIA, John.



>For deletion, all you need is a primary key. If you use stored procs, you could pass a string of delimited keys to the proc. The proc could then parse through the string and delete the records. Again, if you are rendering this sort of thing from the client, you have different issues standing in your way.
>
>Updating is a bit more of a challenge, but one that is doable. Depending on the complexity of the updates, you can still take the same approach as deleting records. There are other ways. In any case, the best and the only truly efficient way of doing this is with stored procedures. If you are not doing that, you are behind the 8-ball. And, as I seem to recall, the DBA you are working with is shall we say, not the sharpest knife in the drawer when it comes to this sort of thing...< s >...
>
>Good luck!!
>
>
>
>>Hey all,
>>
>>Does anyone know what the difference is in speed and ease-of-use between ADO Command objects and SQL Passthrough when it comes to bulk operations, such as updating or deleting several hundred records?