locked
Table Truncate Issue RRS feed

  • Question

  • Hello,

    I have written the following SQL in a Script Task
    ALTER TABLE dbo.tag_data NOCHECK CONSTRAINT FK_TAG_DATA_BLOCK
    truncate table dbo.tag_data;
    DBCC CHECKIDENT ("dbo.TAG_DATA", RESEED, 1);
    truncate table dbo.block;
    DBCC CHECKIDENT ("dbo.Block", RESEED, 1);
    ALTER TABLE dbo.tag_data WITH CHECK CHECK CONSTRAINT FK_TAG_DATA_BLOCK;
    truncate table dbo.management;
    truncate table dbo.base;
    here tag_data has a foreign key and block has a primary key.

    However I keep getting the error message

    [Execute SQL Task] Error: Executing the query "ALTER TABLE dbo.tag_data NOCHECK CONSTRAINT FK_TAG..." failed with the following error: "Cannot truncate table 'dbo.block' because it is being referenced by a FOREIGN KEY constraint.
    Checking identity information: current identity value 'NULL', current column value '1'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    Saturday, January 30, 2010 9:00 PM

Answers

  • Hi,

    Following Restrictions are apply when you run Truncate Table command.

    Restrictions

    You cannot use TRUNCATE TABLE on tables that:

    • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
    • Participate in an indexed view.
    • Are published by using transactional replication or merge replication.

    See help on following link

    http://msdn.microsoft.com/en-us/library/ms177570.aspx

    • Marked as answer by MSDN Student Saturday, January 30, 2010 10:41 PM
    Saturday, January 30, 2010 9:53 PM