はじめに
ストアドプロシージャを新規に作成してDBに適用したところエラーになりました。
そのときの対応内容をメモしておきます。
環境
Windows10
SQLServer 2017
Management Studio 17
再現手順
新規のストアドプロシージャを作成して開発環境にリリース。
ストアドプロシージャにはXML型の記述が含まれています。
DBモジュールリリース用のバッチがあるので実行したところ下記のエラーがでました。
「SELECT INTOが失敗しました。
次のSETオプションには不適切な設定'QUOTED_IDENTIFIER'があります。
SETオプションが、インデックス付きビュー、計算列のインデックス、フィルター選択されたインデックス、クエリ通知、XML型データ型のメソッド、空間インデックスの操作で使用するのに適切であることを確認してください。」
なお、リリースバッチを使用せずにプログラムを直接SSMSで開いて適用したところ正常に適用されました。
原因
エラー内容に記載のとおり、QUOTED_IDENTIFIERというSETオプションの設定が不足していました。
ストアドプロシージャ内で一時テーブルへデータをSELECT INTOしているのですが、
一時テーブルのQUOTED_IDENTIFIERがONの設定になっていないためエラーになったと思われます。
QUOTED_IDENTIFIER
予約済キーワードを使用できるようにするオプションのようです。
"SELECT"や"ORDER"といった予約済キーワードをオブジェクト識別子(テーブル名、カラム名等)として使用できるようになります。
そして、XML型を使用する場合はこのオプションをONにする必要があります。
以下、公式からの引用です。
XML データ型のメソッドを呼び出す場合は、SET QUOTED_IDENTIFIER を ON に設定する必要があります。
おわりに
QUOTED_IDENTIFIERオプションはONが規定値のようです。
SSMSから直接ファイルを開いて単独実行した場合には成功したのはこのためと思われます。
外部からsqlcmd実行する際は明示してあげる必要があるのでしょうか。
QUOTED_IDENTIFIERオプションはインデックス付きビューで効果を発揮するらしいです。
インデックス付きビューの効果についてもう少し知りたくなりました。
参考
https://docs.microsoft.com/ja-jp/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver15
https://social.msdn.microsoft.com/Forums/ja-JP/30ffa085-04fd-4e67-8466-05b2fc65e852/microsoft-sql-server-management?forum=sqlserverja
https://qiita.com/maaaaaaaa/items/4653d610c0925504b10a