December 17, 2010 at 2:34 am
I am trying to import MsAccess 2007 data into an SQL Server database. This worked earlier, same code, same machine, except SQL Server Express 2005. After some unrelated problems, I converted to 2008 and now I am having trouble loading the data.
It's VBA code running in Access, first executes a stored procedure named ClearDB, which wipes every table. That still works. Then it loops around through all the tables, maintaining a specific order due to relationship constraints. For each table, the code executes the following three queries:
SET IDENTITY_INSERT TableOfZones ON
INSERT INTO dbo_TableOfZones SELECT Jet_TableOfZones.* FROM Jet_TableOfZones
SET IDENTITY_INSERT TableOfZones OFF
The IDENTITY_INSERT queries are pass-through, the Insert is local. This has recently started working only for the first table, then it crashes claiming that none of the records were inserted due to key violations. It's always the first table that works, regardless of which table that is, as long as it isn't one that requires matching records from another table to already be in place. Sounds like a perfectly normal screw-up in indexing or such, except that if I let it sit for a while (10-15 minutes) it suddenly works again, for one table. That Insert works, then it bombs on the next one. Again, if I let it sit for a while, that next one will suddenly work and crash on the one after that.
I have to use the IDENTITY_INSERT option, since I am importing existing data with relationships already created, else I would simply let SQL Server generate its own ID
columns.
Someone on Stack Overflow suggested that it might be because the fields are not explicitly named, but I modified the queires to list the fields and the results were the same.
December 17, 2010 at 3:17 am
This was removed by the editor as SPAM
December 17, 2010 at 4:10 am
stewartc-708166 (12/17/2010)
Have you tried disabling the foreign key constraints before begining the insert, then re-enabling them thereafter?e.g.
-- Disable the constraint.ALTER TABLE mySampleTable NOCHECK CONSTRAINT FK_SampleTableDetail_SampleTable;
-- Reenable the constraint.
ALTER TABLE mySampleTable WITH CHECK CHECK CONSTRAINT FK_SampleTableDetail_SampleTable;
Just tried it and got an error. Actually, these links are defined as relationships, not constraints. Did I do that incorrectly?
December 17, 2010 at 4:15 am
This was removed by the editor as SPAM
December 17, 2010 at 4:26 am
Sorry, my screw-up - I had a typo in the SQL. But the results are the same. It does drop the constraint now, but the INSERT INTO still works only on the first table, then bombs.
December 17, 2010 at 4:34 am
This was removed by the editor as SPAM
December 17, 2010 at 5:10 am
stewartc-708166 (12/17/2010)
Did you disable all related foreign keys?
No, would that make a difference? There are about twenty auxilliary tables, each with single one-to-many relationship to the main records table. The VBA code I wrote is reproduced below:
SET IDENTITY_INSERT TableOfZones ON
ALTER TABLE Podrobnosti NOCHECK CONSTRAINT FK_Podrobnosti_Zone
INSERT INTO dbo_TableOfZones(ZoneAutoID, Zone) SELECT Jet_TableOfZones.ZoneAutoID, Jet_TableOfZones.Zone FROM Jet_TableOfZones 119
ALTER TABLE Podrobnosti WITH CHECK CHECK CONSTRAINT FK_Podrobnosti_Zone
SET IDENTITY_INSERT TableOfZones OFF
119 119 0
SET IDENTITY_INSERT TableOfSystems ON
ALTER TABLE Podrobnosti NOCHECK CONSTRAINT FK_Podrobnosti_System
INSERT INTO dbo_TableOfSystems(SystemAutoID, System) SELECT Jet_TableOfSystems.SystemAutoID, Jet_TableOfSystems.System FROM Jet_TableOfSystems 0
ALTER TABLE Podrobnosti WITH CHECK CHECK CONSTRAINT FK_Podrobnosti_System
SET IDENTITY_INSERT TableOfSystems OFF
221 0 221
It turns on IDENTITY_INSERT, disables the constraint like you suggested, does the insert, re-enables the constraint and turns off IDENTITY_INSERT, in that order.
The three numbers below the last line for each table are record counts in source table, destination table and simple difference between the two. As you can see, I'm still in the same boat. The first INSERT runs fine, the second does absolutely nothing, with the same commands. And yes, the tables are truly empty - I went in and checked with SSMSE.
December 17, 2010 at 7:46 am
I had indexes on the auxilliary fields. Tried removing those, so that there is only the autonumber primary key, no luck. It's difficult to experiment, because everything I do to the database makes the next INSERT successful, so it often looks like I've made some progress, but it always bombs on the next query. It's always the first one in a set, or the first one after some manipulations with SSMSE, that works. But the next one, and all subsequent ones, fail.
December 17, 2010 at 8:05 am
It sounds to me like the either the PK or a UNIQUE constraint is firing. The first runs fine because there are no duplicates; the second+ fails because of a duplicate in one of the keys.
Can you post the exact error message that you're getting?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 17, 2010 at 8:49 am
WayneS (12/17/2010)
It sounds to me like the either the PK or a UNIQUE constraint is firing. The first runs fine because there are no duplicates; the second+ fails because of a duplicate in one of the keys.Can you post the exact error message that you're getting?
Not completely exactly, it's in Czech. It's the standard MsAccess text when it is unable to perform an Insert query. Here's a translation:
Application Akcesit was unable to add all the records in append query.
Application Akcesit set 0 value(s) to Null due to type conversion failure and did not add 221 records for key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
I am unable to get a SQL Server error message, since the application trying to do this insert is Access.
Each Insert is to a different table, which is what's driving me batty. And it doesn't matter which table; if I swap around the order in which I do the tables, it's still always the first one that works and subsequent ones bomb, regardless of which tables are actually addressed in the query.
It sort of seems to me like the IDENTITY_INSERT OFF command is not working. I can turn it on, do one insert, then the next one craps out because the IDENTITY_INSERT is still on for the previous table. But the query is executing - I've even stepped the code by hand and run the OFF query manually. No error message, but no way to tell if it has actually done anything. I've read up on it, and the consensus seems to be that the current value of IDENTITY_INSERT is not exposed. Maybe that's why it works when I fiddle with it manually in SSMSE, maybe that resets the value automatically?
December 19, 2010 at 5:12 am
No luck with anything, so I decided to go back to 2005. Did a fresh install of SSE 2005, re-attached the old database files and now I'm getting the same behavior there! First INSERT query works fine, second and subsequent bomb. Can ANYONE suggest something, or should I just find a pistol and blow my brains out?
December 20, 2010 at 12:27 am
This was removed by the editor as SPAM
December 20, 2010 at 2:10 am
stewartc-708166 (12/20/2010)
Have you tried using a batch delimiter between the statements?
Access doesn't allow more than one SQL statement at a time. Each INSERT is a separate call. I have even tried completely severing the connection between calls and creating a new database object for each call, in case there was something cached in the connection. No help.
The only thing that has made any difference, finally, is making the ID field not an identity, loading the data and turning it back into an identity field. Completely retarded and I don't understand why the IDENTITY_INSERT command used to work and now doesn't, but that's the only way I can now get data into my tables.
December 20, 2010 at 5:39 pm
Rather than do all 3 tables in the same batch, try doing each table in a separate batch, which is to say a separate set of code in Access each one executed with a different statement from the VBA code.
Todd Fifield
December 20, 2010 at 10:00 pm
I'd suggest that you Profile it and see what's really going on.
However, my suspicions would be that your Access engine is not executing all of your Statements / Batches in the same Session, which is going jam up your SET IDENTITY_INSERTS...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply