December 18, 2006 at 3:49 pm
While trying to create the DTS package that will clear all my SQL tables and replace the data by loading the current production data from Access, I have encounter the Indentity Column MONSTER. I have done a lot of searching and reading off the web, and Microsoft's "stuff" on their web site, but still have not found, or figured out for myself, just how to be able to delete all the records from every kind of table, and reload the data from Access. "Every kind of table" is tables without identity columns, tables with identity columns, and tables with identity columns where it (identity) has been turned off, just how to use Identity_Insert = Yes.
Some tables, and I have not seen the common thread amongst them, don't allow the records to be deleted, and other tables don't allow records to be added, and again, I have not found the common thread in these tables either. When I try to set Identity_Insert = Yes, I get an ODBC error. Even on the first try within the session.
Any help would be VERY nice.
Thanks,
Vic
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
December 19, 2006 at 3:16 am
This may be due to integrity between the tables. To make sure records are not left "orphaned" in a related table when deletions occur, you can set up integrity between two tables. The way to get round this is to set cascading detions on when setting up the integrity.
December 19, 2006 at 6:53 am
Have you tried truncating the table instead of deleting the rows. If you try to delete a row without a primary key you may experience problems.
December 19, 2006 at 8:21 am
I'm not sure truncating will work on tables linked by referential integrity. You can either drop all constraints first, or delete the records from child tables first, then move up the chain. Make sure your "identity" columns are compatible between Access and SQL Server as well. Load tables in the opposite order from deletion i.e. parents first, then children.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply