E aí pessoas,
Nesse post vamos resolver mais um Caso do Dia a Dia com a mensagem de erro abaixo:
UPDATE failed because the following SET options have incorrect settings ‘QUOTED_IDENTIFIER’
Recomendo que você leia também o post anterior, onde falei com mais detalhes sobre o ‘QUOTED_IDENTIFIER’. #gogogo
https://luizlima.net/dicas-t-sql-pra-que-serve-a-opcao-set-quoted-identifier-on/
Explicando o cenário:
Um determinado cliente fez uma Atualização da Aplicação. Nessa nova versão foram incluídos alguns ÍNDICES FILTRADOS. Agora vamos iniciar o nosso exemplo para simular o problema.
Vou criar uma tabela, inserir um registro e criar um índice filtrado.
IMPORTANTE: Aqui a opção “QUOTED_IDENTIFIER” está habilitada.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE Traces SET QUOTED_IDENTIFIER ON create table testeIndiceFiltrado ( id int, Versao int ) INSERT INTO testeIndiceFiltrado VALUES(1,1) CREATE NONCLUSTERED INDEX ix_testeIndiceFiltrado ON dbo.testeIndiceFiltrado ( Versao ASC ) WHERE (Versao=(0)) |
Agora vou fazer um UPDATE e ele será executado normalmente. Até aqui nada demais.
|
1 2 3 4 |
USE Traces update testeIndiceFiltrado set id = 1 |
SET QUOTED_IDENTIFIER OFF – Simulando o Erro:
Agora vou desabilitar a opção “QUOTED_IDENTIFIER” e executar novamente o UPDATE.
|
1 2 3 4 5 6 |
USE Traces SET QUOTED_IDENTIFIER OFF update testeIndiceFiltrado set id = 1 |
UPDATE 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.
O mesmo problema vai acontecer se tentarmos fazer um INSERT / DELETE com a opção “QUOTED_IDENTIFIER” desabilitada.
|
1 2 |
delete testeIndiceFiltrado where id = 1 |
Msg 1934, Level 16, State 1, Line 39
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.
|
1 2 |
INSERT INTO testeIndiceFiltrado VALUES(2,2) |
Msg 1934, Level 16, State 1, Line 52
INSERT 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.
No caso do cliente que comentei no início do post, eu encontrei essa mensagem de erro em um JOB que começou a falhar justamente após a Atualização da Aplicação devido a criação de alguns índices filtrados. Agora vamos simular o problema com o JOB também.
Coloquei o UPDATE abaixo dentro de um JOB. Lembrando que essa tabela possui um índice filtrado.
|
1 2 3 4 |
USE Traces update testeIndiceFiltrado set id = 1 |
Ao executar o JOB, ele vai falhar:
Para resolver, basta você habilitar explicitamente o QUOTED_IDENTIFIER no início do script e o JOB será executado com sucesso.
|
1 2 3 4 5 6 |
SET QUOTED_IDENTIFIER ON USE Traces update testeIndiceFiltrado set id = 1 |
Índices Filtrados:
Se você quiser mais detalhes sobre os índices filtrados, dê uma olhada no link abaixo da Documentação Oficial da Microsoft:
Nesse outro link que fala sobre o QUOTED_IDENTIFIER, ele informa que essa opção precisa estar ON quando trabalharmos com índices filtrados OK.
Portanto, minha DICA FINAL é:
- CUIDADO ao criar índices filtrados, pois como mostrei nos exemplos anteriores, eles podem gerar alguns efeitos colaterais em outras rotinas / JOBs.
- Se você tiver algum problema com as mensagens de erros anteriores, basta habilitar a opção “SET QUOTED_IDENTIFIER ON” no início do script que o problema será resolvido.
Referências:
Segue mais alguns posts como referência sobre o assunto:
Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!
Me siga no LinkedIn e YouTube para ficar por dentro das novidades.
Abraço,
Luiz Vitor França Lima
Consultor SQL Server
Resolveu a minha vida! Muito obrigado!
Show Alberto!! Fico feliz em ter ajudado! =)
Abraço,
Luiz Vitor