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 |
|
JeReLo
Starting Member
16 Posts |
 Posted - 2008-03-12 : 02:44:07
|
| I recently learned to use SSIS to import a database from a SQL 2005 server to a local SQL 2005 development server. It was working fine for a custom database, but now I'm trying to use it on the membership tables that ASP.NET creates to manage login, profile, and so on.I created the package, specifying that I wanted to delete data in the destination, and turning on identity insert. I only need the data in a few of the tables, so I am not copying empty tables. When I run it, I get the error:[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [aspnetdb].[dbo].[aspnet_Roles] " failed with the following error: "Cannot truncate table 'aspnetdb.dbo.aspnet_Roles' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. How can I safely allow it to overwrite the foreign key constraints? Is there a special procedure I need to use with membership (aspnetdb) data? I am just beginning with this, so this is probably an elementary question. Thanks for any links or explanations you might know of. |
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2008-03-13 : 22:29:30
|
| You can't truncate a table that is being reference by a foreign key in another table; you can DELETE data, but TRUNCATE will cause an error. You can delete the foreign keys and reinsert the data since you are using identity insert ON, assuming that your source data is good.hth,Tim---------------------Tim Mitchellwww.BucketOfBits.com |
 |
|
|
steve.block
Starting Member
1 Post |
Posted - 2010-05-27 : 14:56:51
|
| You have to do one of two things.1) disable foreign key contraints. (sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all") disables all constraints in a database use DELETE FROM instead of TRUNCATE TABLE re-enable foreign key contraints. (sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all") enables all constraints.2) use DELETE FROM instead of TRUNCATE TABLE and make sure that the tables are deleted in an order that will not violate the foreign key constraintMethod 1 may be easier but not recommended if you are loading a production database that is in use while running the package.It annoys me that the wizard creates TRUNCATE TABLE when it has the information needed know it will not work.Steve Block |
|
|
|
|
|
|
|
|
SSIS import of membership table foreign key issue