SqlDependency Notification database Options troubleshooting

For SQL notifications we followed the steps described in http://dimarzionist.wordpress.com/2009/04/01/how-to-make-sql-server-notifications-work.

However it didn’t work for our database. The instructions missed a step to ensure that database options are set for SqlDependency Notification

I’ve created a function to Follow recommendation to always check the notification source, info and type, it returned
 
SqlNotificationEventArgs, Type : Subscribe, Source : Statement, Info : Options 

    //TODO: pass  SQL string
      /// <summary>
               ///
               /// </summary>
               /// <param name=”logger”></param>
               /// <param name=”e”></param>
               public static void LogSqlNotificationEventArgs( Logger logger, SqlNotificationEventArgs e,DbConnection dbConnection=null)
              {
                      LogLevel level = LogLevel .Warn;
                      //You’re supposed to check the values of the SqlNotificationEventArgs argument. Only if Type is Change and Source is Data where you notified for a data change.
                      if ((e.Type == SqlNotificationType .Change) && e.Source == SqlNotificationSource.Data)
                     {
                           level = LogLevel.Info;
                     }
                      if (e.Info == SqlNotificationInfo .Options)
                     {
                            if (dbConnection != null )
                           {
                                   //TODO http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/. Make SP
                                   //check DB set options,
                                   // Execute SqlDependencies_CheckDBSetOptions
                                   // Run Alter DB

                           }
                     }
               //http://msdn.microsoft.com/en-us/library/ms189308%28SQL.105%29.aspx Insert/update/delete/truncate are valid
                     logger.Log(level, “SqlNotificationEventArgs, Type : {0}, Source : {1}, Info : {2} “, e.Type, e.Source, e.Info);
                     
              }

Based on TROUBLESHOOTING QUERY NOTIFICATIONS in http://rusanu.com/2006/06/17/the-mysterious-notification/ 

and Troubleshooting Query Notifications I’ve created Stored Procedure


/*
— RUN manually for every DB that required change

ALTER DATABASE MyDB
set ANSI_NULLS ON
       , ANSI_PADDING ON
, ANSI_WARNINGS ON
, CONCAT_NULL_YIELDS_NULL ON
, QUOTED_IDENTIFIER ON
, NUMERIC_ROUNDABORT OFF
,ARITHABORT ON
*/
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— Drop stored procedure if it already exists
IF EXISTS (  SELECT *     FROM INFORMATION_SCHEMA.ROUTINES     WHERE SPECIFIC_SCHEMA = N’dbo’     AND SPECIFIC_NAME = N’SqlDependencies_CheckDBSetOptions’ )
   DROP PROCEDURE dbo.SqlDependencies_CheckDBSetOptions
GO

— =============================================
— Author:           MNF
— Create date:
— Description:      
— =============================================
CREATE PROCEDURE SqlDependencies_CheckDBSetOptions
       @dbName sysname 
AS
BEGIN
        — SET NOCOUNT ON added to prevent extra result sets from
        — interfering with SELECT statements.
        SET NOCOUNT ON;
select –*
       is_ANSI_NULLS_ON
        ,is_ANSI_PADDING_ON
        ,is_ANSI_WARNINGS_ON
        , is_CONCAT_NULL_YIELDS_NULL_ON
, is_QUOTED_IDENTIFIER_ON
, is_NUMERIC_ROUNDABORT_ON–OFF
,is_ARITHABORT_ON
–select *
From sys .databases where name =@dbName 
and (
       is_ANSI_NULLS_ON =0
        or is_ANSI_PADDING_ON =0
        or is_ANSI_WARNINGS_ON =0
        or is_CONCAT_NULL_YIELDS_NULL_ON =0
or is_QUOTED_IDENTIFIER_ON= 0
or is_NUMERIC_ROUNDABORT_ON = 1–OFF
or is_ARITHABORT_ON= 0)

END
— SqlDependencies_CheckDBSetOptions ‘MyDB’
GO