General information
Forum:
Microsoft SQL Server
I think it turns out that EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
Will only work for adds and updates but NOT for truncate table. So the error is the same if you specify NOCHECK or not (prevented by constraints from other files).
Query Analizer Results
Run:
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
TRUNCATE TABLE dbo.QM_EQUIPMENT
Results Window:
Server: Msg 4712, Level 16, State 1, Line 3
Cannot truncate table 'dbo.QM_EQUIPMENT' because it is being referenced by a FOREIGN KEY constraint.
Are you aware of a method of storing all constraints to a file? Then remove all constraints and later reinstall all constraints?
What I am trying to do is set all of the data input tables back to a New System condition. I need to get rid of all of my test data.
THANKS for your help on this (and MANY other issues).
Joel
>What is actual error is returned when TRUNCATE TABLE is run?
>Yes, I thought it would work from SQLEXEC but I am not having any success.
>
>
>hsql=SQLSTRINGCONNECT('dsn=ABC_NEW;uid=sa;pwd=xray')
>returns a positive connection number
>
>xEXEC= 'EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'
>xxx=SQLEXEC(hsql,xEXEC)
>? xxx
>
>Works well and has a return XXX value of +1
>So NOCHECK is being activated as expected
>
>
>
>
>STORE UPPER(ALLTRIM(kill_tables.table)) TO m.table
>xaction="TRUNCATE TABLE "+m.table
>? xaction
>xx=SQLEXEC(hsql,xaction)
>? xx
>Always return -1 because of constraints defined in other user tables
>
>It seems that the nocheck on constraints just lasts for the one command that turns it off, but the
>constraints are BACK ON for next commaand.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only