Ошибка удаления/вставки с фильтрованным индексом в SQL Server 2008?

Я создаю хранимую процедуру с настройкой по умолчанию, например:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Create  PROCEDURE [dbo].[TestSP] 
AS
BEGIN
   set arithabort off
   set arithignore on
   --.....
END

Это нормально. Затем я создал отфильтрованный уникальный индекс в таблице для ненулевого значения в БД, после чего я больше не могу запускать какую-либо хранимую процедуру в SQL Server Management Studio. Ошибка, которую я получаю, выглядит примерно так:

УДАЛИТЬ (или ВСТАВИТЬ) не удалось, поскольку следующие параметры SET имеют неверные настройки: «ANSI_NULLS, QUOTED_IDENTIFIER». Убедитесь, что параметры SET подходят для использования с индексированными представлениями и/или индексами вычисляемых столбцов, и/или отфильтрованными индексами, и/или уведомлениями о запросах, и/или методами типа данных XML, и/или операциями пространственного индекса.

Я пытаюсь запустить хранимую процедуру, например:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
EXEC TestSP

Or 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC TestSP

Я все еще получаю ошибку выше. Я очень смущен. Как решить эту проблему?


sql-server-2008 filtered-index
person KentZhou    schedule 23.04.2013    source источник
comment
что произойдет, если ansi_nulls будет включен при создании хранимой процедуры?   -  person Ian Kenney    schedule 23.04.2013
comment
Почему вы создали процедуру с этими двумя настройками OFF? Вы знаете, что SET ANSI_NULLS OFF устарела, начиная с SQL Server 2005. , верно? Прекратите использовать его.   -  person Aaron Bertrand    schedule 23.04.2013

Ответы (4)


arrow_upward
2
arrow_downward

Параметры для ANSI_NULLS и QUOTED_IDENTIFIER основаны на том, какие эти параметры используются при создании/изменении процедуры, а не на параметрах экземпляра, в котором вы запускаете процедуру.

Вы хотите запустить ALTER PROC для своего TestSP, который устанавливает как минимум QUOTED_IDENTIFIER в ON при его запуске.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TestSP] 

AS
BEGIN
   set arithabort off
   set arithignore on
   --.....
END
person Jason Whitish    schedule 23.04.2013

arrow_upward
1
arrow_downward

Только мой 2с.

Я часто помещаю SET ANSI_WARNINGS OFF внутри своих процессов, потому что сообщения меня раздражают, и иногда эти предупреждения неправильно всплывают через пакеты SSIS к заданиям агента SQL и неправильно сообщают о сбое пакета SSIS.

Я получил эту ошибку после добавления отфильтрованного индекса. Теперь я удалил SET ANSI_WARNINGS OFF. Мой процесс теперь работает, и я посмотрю, была ли эта проблема SSIS реальной или нет.

person Nick.McDermaid    schedule 11.01.2019

arrow_upward
0
arrow_downward

Похоже, это проблема SQL Server (несогласованность?). Ошибка «ВСТАВИТЬ не удалось, поскольку следующие параметры SET имеют неверные настройки: «QUOTED_IDENTIFIER» будет сгенерировано, если выполняются следующие условия:

  • в таблице есть столбец, допускающий значения NULL, но значение по умолчанию не определено, например:

[LookupID] [int] NULL

  • для этого столбца определен уникальный отфильтрованный индекс

    Create Unique nonclustered index [UniqueExceptNulls] on [tablename]
  ( [lookupID] asc)  where ([LookupID] is not NULL)

Решение: добавьте ограничение по умолчанию в столбец [LookupID]:

ALTER TABLE [tablename] add CONSTRAINT [DF_TableName_lookupID] DEFAULT (NULL) FOR [lookupID]
person ZJA    schedule 23.05.2013

arrow_upward
0
arrow_downward

Я только что столкнулся с этой ситуацией, и причина была связана с свойством файла и процессом развертывания моего проекта базы данных в Visual Studio (т.е. myDatabase.dbproj).

Основной причиной ошибки является контекст, в котором должна быть создана хранимая процедура. Параметры базы данныхANSI_NULLS и QUOTED_IDENTIFIER должны быть установлены до создания хранимой процедуры (установка этих параметров в sp-коде не является ошибкой, но бесполезна).

Хранимая процедура запустится, если я скопирую и вставлю ее исходный код в SQL Server Management Studio, поэтому я был почти уверен, что проблема вызвана разницей в параметрах SET в контексте развертывания. Затем я проверил эти настройки (Database.sqlsettings) и все было в порядке: ANSI_NULLS и QUOTED_IDENTIFIER были установлены правильно, но проблема осталась...

Пока я не обнаружил, что каждый объектный файл имеет запись Properties в проекте базы данных (щелкните правой кнопкой мыши имя файла, чтобы открыть диалоговое окно). Эти свойства фактически перезаписывали настройки базы данных, поэтому я изменил их на Project Default, и проблема была решена.

sql-file-properies-dialog

PS: я провел больше тестов, создавая новые хранимые процедуры из Visual Studio и импортируя созданные вручную с помощью сравнения схем, а файлы создаются с помощью Project Default, поэтому я думаю, что этот параметр был изменен вручную или неправильно импортирован из предыдущей версии Visual Studio. .

person Gerardo Lima    schedule 11.07.2013