Log Shipping – NORECOVERY vs. STANDBY mode

Whenever you create or recreate an SP, make sure QUOTED_IDENTIFIER is set to ON. The default value of OFF is there for backward compatibility only. Many modern features of SQL Server (such as filtered indexes, computed persisted columns, xml columns ) require Quoted Identifiers enabled.

Here’s is the script to list all objects with QUOTED_IDENTIFIER set to OFF

  1: SELECT  SCHEMA_NAME(s.[schema_id]) + '.' + s.name AS name
  2:        ,s.create_date
  3:        ,s.modify_date
  4:        ,OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
  5:        ,s.type_desc AS object_type
  6: FROM    sys.objects s
  7: WHERE   s.type IN ( 'P', 'TR', 'V', 'IF', 'FN', 'TF' )
  8:         AND OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') = 0
  9: ORDER BY SCHEMA_NAME(s.[schema_id]) + '.' + s.name DESC 
 10: 
 11: