warning re ADO .UpdateBatch method

[Ellen K.]

Awhile back there was some discussion about ways of sending multiple
rows of data to SQL Server. The ADO .UpdateBatch method was one of
the possibilities mentioned, and Erland said he thought that probably
once the data get to SQL Server they are probably actually inserted
one row at a time. I just want to say that based on an experience I
had a couple of days ago I am now sure that is the case. I was using
..UpdateBatch for something quick and dirty I had to do just once and
was too lazy to use XML for. An error was generated during the
..UpdateBatch process because of invalid data in one row. However, a
large number of the rows in the batch WERE written to the database.

Until now I haven't used .UpdateBatch in a production environment only
because this implies granting INSERT or UPDATE privileges to the user.
But based on this experience I would NEVER use .UpdateBatch in a
production environment, even for a user like a batch job where the
permissions would not be an issue.

FWIW.

[Ellen K.]

Have you actually tried this and verified that it works?

On Wed, 21 Apr 2004 17:10:52 -0400, "KA" <ab***@aol.com> wrote:

Do it in a transaction...

[KA]

This is the whole purpose of transactions. You commit/roll back all changes
that was done in the transaction.

[KA]

Here's a good starting place:
http://msdn.microsoft.com/library/en...md_06_2it2.asp

[KA]

Do it in a transaction...

[Ellen K.]

Why do you assume I don't know what a transaction is? My question is
whether the ADO .UpdateBatch method respects that.

On Thu, 22 Apr 2004 16:27:45 -0400, "KA" <ab***@aol.com> wrote:

This is the whole purpose of transactions. You commit/roll back all changes
that was done in the transaction.

[KA]

Of course it does.

UpdateBatch will use whatever connection is associated with the RS and will
simply translate your changes into a bunch of insert, update and delete
statements wrapped by sp_executesql.

If you are in a transaction, your UpdateBatch calls will be part of that
transaction.

[Ellen K.]

Have you actually tried this and verified that it works?

On Wed, 21 Apr 2004 17:10:52 -0400, "KA" <ab***@aol.com> wrote:

Do it in a transaction...

[KA]

This is the whole purpose of transactions. You commit/roll back all changes
that was done in the transaction.

[KA]

Here's a good starting place:
http://msdn.microsoft.com/library/en...md_06_2it2.asp

[Ellen K.]

Why do you assume I don't know what a transaction is? My question is
whether the ADO .UpdateBatch method respects that.

On Thu, 22 Apr 2004 16:27:45 -0400, "KA" <ab***@aol.com> wrote:

This is the whole purpose of transactions. You commit/roll back all changes
that was done in the transaction.

[KA]

Of course it does.

UpdateBatch will use whatever connection is associated with the RS and will
simply translate your changes into a bunch of insert, update and delete
statements wrapped by sp_executesql.

If you are in a transaction, your UpdateBatch calls will be part of that
transaction.