Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ron2112
Starting Member
44 Posts |
 Posted - 2008-05-09 : 16:03:19
|
| Sorry if this isn't the forum for this, it was the closest I could find. Anyway, here's the scenario: We're transferring data from an Access database to SQL Server 2005. The Access db is a mess -- tons of redundancy, de-normalized data all over the place -- it looks like it was built by a third-grader. My new SQL Server design fixes all those problems, so a straight bulk import isn't an option. We have to put the data through a few gyrations in order to put it into the improved schema.We're doing all this within an ADO.NET transaction so we can roll it back easily on an error. I'm going to spare you the specific code unless you want it. What's happening is this, in brief:1) Begin the transaction.2) Populate SQL Table_A from Access Table_A.3) Try to populate SQL Table_B from Access Table_B. Table_B has a primary key, and a FK relationship with Table_A. When we attempt to do this, we get a PK violation, duplicate record error.That's the symptom, in a nutshell. Here's where it gets weird. If we drop the PK on Table_B and run the script, Table_B gets populated, as you'd expect. But... there are only a half-dozen records involved, and it's obvious from looking at the data that there is no duplication in the fields that made up the PK. In fact, if we recreate the PK with that data in the table, we get no complaints at all. It's clear that the resulting data does not represent a PK violation.Next weird thing: If, between steps two and three, we commit the transaction and start a new one, we get no PK violation on Table_B.So in brief, ADO.NET is giving us a PK violation on an INSERT that, as far as we can tell, should not generate such an error -- and doesn't if we commit the transaction up to that point first. There are no triggers at play here, so that's ruled out.If anyone has a clue what might cause this, or maybe needs more information, please let me know. We're pulling our hair out on this one.Thanks!Ron Moses |
|
|
|
|
|
|
|
ADO.NET transaction problem