問題描述
我有一個腳本來刪除我的數據庫中的所有表，如下所示:
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
當它碰到 DELETE 行時，我收到一些表的錯誤:
When it hits the DELETE line I get this error for a few of the tables:
DELETE 失敗，因為以下 SET 選項不正確設置:'QUOTED_IDENTIFIER'.驗證 SET 選項是否正確與索引視圖和/或計算列和/或索引一起使用過濾索引和/或查詢通知和/或 XML 數據類型方法和/或空間索引操作.
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?
推薦答案
將 SET 選項添加到刪除調用中.
Add the SET options to the delete call.
這些仍然適用于錯誤中提到的其他項目，即使您禁用了 FK.
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
評論后編輯
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
這篇關于刪除所有表時出錯“刪除失敗，因為以下 SET 選項的設置不正確:‘QUOTED_IDENTIFIER’"；的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！