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 )
{
//check DB set options,
// Execute SqlDependencies_CheckDBSetOptions
// Run Alter DB
}
}
logger.Log(level, "SqlNotificationEventArgs, Type : {0}, Source : {1}, Info : {2} ", e.Type, e.Source, e.Info);
}
/*
-- 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