Hi Everyone,
I have a need to truncate all tables in advwrks2008 /r2 in a single go using scripts or commands.
I have tried :
"
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"
-- enable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
--some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)""
it executes only uptil halfway and shows many errors :
"Msg 1934, Level 16, State 1, Line 1
DELETE failed because the following SET options have incorrect settings:
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Vendors cannot be deleted. They can only be marked as not active.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted."
Please help me out. Seeking help asap.
Regards
Abhishek