This project has moved. For the latest updates, please go here.
1

Closed

How to copy multiple tables in single transaction?

description

We recently upgraded from 2.1.6.4 to 2.1.6.11. Now we are getting the following error:

"You cannot use your own transaction with the KeepIdentity option"

We traced the change to changeset 75571, where you added this to the SqlCeBulkCopy constructor:
if (keepIdentity && trans != null)
{
  throw new Exception("You cannot use your own transaction with the KeepIdentity option");
}
Your comment on the checkin was "Fix keepIdentity issue" so we assume it was for a good reason. However, we are now not sure how to copy more than one table within a single transaction. Is this possible? My understanding is that each call to WriteToServer() runs as an independent transaction.

We are copying multiple tables, which we want to succeed or fail as a unit. Our code looks like this (simplified for brevity):
using (var bulkCopy = new SqlCeBulkCopy(cn, SqlCeBulkCopyOptions.KeepIdentity, tran))
{
  foreach (var tableName in backupFile.MembershipTables)
  {
    bulkCopy.DestinationTableName = Utils.GetSqlName(tableName, backupFile.GalleryDataStore, "dbo");
    bulkCopy.WriteToServer(ds.Tables[tableName]); 
  }
}
Thanks,
Roger
Closed Apr 25, 2016 at 9:06 PM by ErikEJ

comments

ErikEJ wrote Apr 23, 2016 at 6:54 AM

Hi Roger,
This is the story as I recall it:
1: Introduction of the KeepIdentity option (before and in build 2.1.6.4) - but this new feature had a flaw, that caused the bulkcopied tables to be come unusable unless you manually reset the seed like this:

ALTER TABLE [{0}] ALTER COLUMN [{1}] IDENTITY ({2},1);", DestinationTableName, idCol, maxVal + 1)))

(This would not become a problem if you never added rows to the bulkcopy populated table)

Then a fix that added reseed was introduced, but this fix reqiúired it's own transaction, which in turn prevented use of transactions from the "outside".

I suggest the following: Add a new option: KeepIdentityWithoutReseeding - which does not invoke the "reseed" code, and which would then Work with a transaction from the "outside" - what do you think?

ErikEJ wrote Apr 23, 2016 at 6:55 AM

Can I also ask what feature you are missing from .4 build, that you need from the .11 build?

rdmartin wrote Apr 23, 2016 at 2:32 PM

Thanks for your quick response. I didn't need anything in 2.1.6.11 - just wanted to get on the latest.

Your description and suggestion of a resolution make sense. After we bulk copy the tables and complete the transaction, we compact the DB, which also resets the identity columns of the tables. So we effectively had a workaround.

Adding the option KeepIdentityWithoutReseeding would work, but it has a slight odor. It's only useful when using transactions and it's not obvious to your API consumer why one would want it. That said, I've sat here 10 minutes trying to think of something better and came up short.

So yes, I support adding KeepIdentityWithoutReseeding. You may want to note in the XML comments for the option that compacting the DB works in addition to the ALTER TABLE statement.

ErikEJ wrote Apr 23, 2016 at 5:44 PM

Maybe; KeepIdentityWithUserTransaction?

rdmartin wrote Apr 23, 2016 at 6:18 PM

Yeah, I thought of something like that, but I'm not sure it's better (maybe about the same). I also considered not adding a new option; instead, you detect when a transaction is supplied and automatically avoid reseeding. That would allow our existing code to work without modification but introduces non-obvious behavior differences between the constructor overloads.

wrote Apr 24, 2016 at 7:23 AM

rdmartin wrote Apr 25, 2016 at 5:01 PM

I think you need one more change. The function GetAutoIncNext() doesn't use the transaction, resulting in the following error when it executes the SQL:

"SQL Server Compact timed out waiting for a lock. The default lock time is 2000ms for devices and 5000ms for desktops. The default lock timeout can be increased in the connection string using the ssce: default lock timeout property."

You need to either modify the function so that it uses the transaction that was passed to the calling function (IdInsertOn) or add a conditional so that GetAutoIncNext never executes when an external transaction is used. I vote for the latter due to the slight perf benefit of fewer SQL statements being executed:
private void IdInsertOn(SqlCeTransaction localTrans, int idOrdinal)
{
  if (keepIdentity && idOrdinal >= 0)
  {
    using (var idCmd = AdoNetUtils.CreateCommand(conn, localTrans, string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT [{0}] ON", DestinationTableName)))
    {
      idCmd.ExecuteNonQuery();
    }
    if (trans == null) // <== THIS LINE ADDED: trans is null when an external transaction is being used
    {
      autoIncNext = GetAutoIncNext();
    }
  }
}
After making this change, our code is once again working on the latest version of SqlBulkCopy. Cheers.

ErikEJ wrote Apr 25, 2016 at 6:33 PM

** Closed by ErikEJ 24-04-2016 00:23

wrote Apr 25, 2016 at 6:33 PM

ErikEJ wrote Apr 25, 2016 at 6:34 PM

I think I can get rid of the call to getautoincnext all together!

ErikEJ wrote Apr 25, 2016 at 7:01 PM

Sorry, I cannot... I will use your suggestion (or ownsTranscation)

rdmartin wrote Apr 25, 2016 at 8:21 PM

Again I thank you for your speedy response. Roughly how long will it be until the NuGet package is updated with this fix? (I find myself jumping through a number of hoops trying to apply this fix outside of the NuGet release)

ErikEJ wrote Apr 25, 2016 at 9:06 PM

https://www.nuget.org/packages/ErikEJ.SqlCeBulkCopy/2.1.6.14 is now available on nuget - thanks for helping improving this small library!

If you use my tools, I would be very grateful for a rating or review here:

https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/view/Reviews

wrote Apr 25, 2016 at 9:06 PM

rdmartin wrote Apr 25, 2016 at 11:31 PM

I think something went wrong with your NuGet deployment. I'm still getting version 2.1.6.11 and the same behavior as that version.

ErikEJ wrote Apr 26, 2016 at 8:32 AM

I have uploaded 2.1.6.15 here: https://www.nuget.org/packages/ErikEJ.SqlCeBulkCopy/ - sorry about the bad QA process

rdmartin wrote May 11, 2016 at 4:43 PM

I finally got around to testing the NuGet 2.1.6.15 release and can confirm the issue is resolved. Thanks so much for your help getting to the bottom of this.

Cheers,
Roger