Post reply

DTS Headaches - Copying over table

  • September 29, 2006 at 7:13 am

    #115867

    I hate this. I swear even though I've done this quite a bit, I always come across the same sh** errors from DTS. (can you tell I just woke up?)

    I'm trying to copy over data from a table from serverA to serverB using SQL 2005 DTS.  ServerB is using SQL 2000 which shouldn't matter, just a side note.  

     
    The table exists in the destination server, I just want to copy over its data.  I need to not only copy but also ensure the identity is also the same by doing an identity insert and reseed. 
     
    I cannot do a linked server as we will be moving over to different domains eventually and they don't want to use linked servers here so this is why I'm using DTS

    Here is the damn error that I know what it is but how to get around it in DTS!:

    Error 0xc002f210: Preparation SQL Task: Executing the query "TRUNCATE TABLE [InstantForum414].[dbo].[InstantASP_Roles]

    " failed with the following error: "Cannot truncate table 'InstantForum414.dbo.InstantASP_Roles' because it is being referenced by a FOREIGN KEY constraint.".

    I understand the error, but how do I get around this without having to manually undo the constraint on the table?  I am able to undo the constraints programically through SQL but cannot get my SQL to work via the SQL route in this wizard.  I'd like to just use the

    How the hell can I do a ALTER TABLE jdvqasql1.InstantForum414.dbo.InstantASP_Roles NOCHECK CONSTRAINT

    and when I look at constraints in SQL 2005 for this table, the names are all abbreviated so how do I know the exact name of the constraint that's causing the issue unless I actually go to SQL to run this script below..which I cannot do unless I have a linked server in place which is something we cannot do at this moment?

    DBCC CHECKIDENT ('qaservername.InstantForum414.dbo.InstantASP_Roles', RESEED, 0)

    SET IDENTITY_INSERT qaservername.InstantForum414.dbo.InstantASP_Roles ON

    ALTER TABLE qaservername.InstantForum414.dbo.InstantASP_Roles NOCHECK CONSTRAINT PK_InstantForum_Forums_ForumID

    INSERT INTO qaservername.InstantForum414.dbo.InstantASP_Roles SELECT * FROM devservername.InstantForum414.dbo.InstantASP_Roles

    SET IDENTITY_INSERT 'qaservername.InstantForum414.dbo.InstantASP_Roles OFF

    I tried pasting this using the SQL option in the wizard instead of letting DTS handle all this, either way, I cannot get my data moved over via DTS!

  • October 2, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • October 2, 2006 at 11:05 am

    #663685

    you might try looking at SQL Server 2005 transaction isolation levels.  In oracle you can violate the RI within a transaction (i.e., between begin/commit) as long as with the commit (with the isolation level appropriately set) all data are present to satisfy the RI.  Not sure whether this is done the same way in SS 2005; but it would be worth looking into as a solution.

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply