44

I have a script to delete all tables in my database that looks like this:

-- Disable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Disable all triggers 
EXEC EnableAllTriggers @Enable = 0

-- Delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'
 
-- Dnable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
 
-- Reseed identity columns
EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'', RESEED, 0)'

-- Enable all triggers
EXEC EnableAllTriggers @Enable = 1

When it hits the DELETE line I get this error for a few of the tables:

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.

I don't have any indexed views, all foreign keys and triggers are disabled, so I don't know what is causing this error. Any ideas?

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
Jeff Stock
  • 3,796
  • 12
  • 46
  • 61
  • Do you know which sp is causing the issue by running them one at a time? – Matt Seymour Dec 05 '11 at 16:16
  • As a side note, if you want to delete everything from a table, use `TRUNCATE` since it is much faster. – JNK Dec 05 '11 at 16:38
  • 7
    @JNK You can't truncate a table that is referenced by a foreign key (even if the constraints have been set to `NOCHECK` per the question). – Iridium Jun 21 '13 at 12:46

1 Answers1

80

Add the SET options to the delete call.

These still apply to the other items mentioned in the error, even though you disabled FKs.

This will work around any saved or environment settings

Edit, after comment

EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 3
    @Rob in my case it was simply adding a line on top of the script SET 'QUOTED_IDENTIFIER' ON(e.g. SET ANSI_WARNINGS ON) – Tomek Jun 22 '17 at 09:55