異質性查詢需要為連線設定 ANSI_NULLS 和 ANSI_WARNINGS 選項。這樣才能確保一致的查詢語意。請啟用這些選項再重新發出您的查詢。 [SQLSTATE 42000] (錯誤 7405). 步驟失敗。
公司維護 sp 都是放在 master 底下，script 會指定 dbname，但沒有使用到 Linked Server 才是，且是某天突然收到執行失敗，打開 sp 來觀察，發現有明確關閉 ANSI_WARNINGS，該設定效果如下
- When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.
- When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs
- if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the ISO standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.
該 sp 是彙總統計整理相關，可能是為了避免彙總時有 null 出現發出警告才把 ANSI_WARNINGS 關閉。
仔細閱讀後發現到真正原因在於一個 view，它最近變成 Linked Server 必須跨 Server 去抓資料，所以才導致該錯誤訊息發生，現況去檢查也看不出會彙總時會有 null 發生，把 ANSI_WARNINGS ON 起來結案
沒有留言:
張貼留言