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
Advertisements

Links for RazorEngine development.

We are using RazorEngine to generate emails based on .cshtml templates.
 
Below are a few links that I used when develop it



http://weblog.west-wind.com/posts/2010/Dec/27/Hosting-the-Razor-Engine-for-Templating-in-NonWeb-Applications

How to set iOS Pocket to open in Web View.

I prefer to open pages in Web View, because Article View often doesn’t show text (in particular source code) properly.
However option “Open Best View” usually opens Article View and I needed to change it manually.
Pocket support provided me with solution
          To force the app to open all the articles in web view in your iPad you can follow these steps:
  1. Tap the Profile option
  2. Tap Settings, in the top right corner
  3. Under the headline Offline Downloading, disable “Download Best View”, “Always Fetch Article”
  4. Enable “Always Fetch Web View”
However it will be easier for user to have in settings section “select view” with options Web, Article,View.
Also if Pocket would have support forums, they will not need to repeat the same answer for other users and I would not create this post.

Insert new line in multiple xml files using Visual Studio

I needed to insert new line after existing one in multiple similar xml files.
In Visual Studio open “replace in files” dialog and tick “use regular expression”
In “find” escape “/” as “\/”
     <param name=”ExistingKey” value=”(.*)” \/>
In “Replace” repeat original string, then add n and add a new string
     <param name=”ExistingKey” value=”$1″ />\n  <param name=”NewKey” value=”new value” />
Related links:

Apple to create Feedback Forum.

Recently I’ve submitted a few suggestions to iTunes support forum. But I was informed, that for suggestions I have to use http://www.apple.com/feedback/itunesapp.html form.
I feel slightly uncomfortable, that I can’t check later, what I submitted. 

So I proposed to Apple to create Feedback Forum.

    

 It will be good to have a forum for feedback similar to what you have for support.
 1.It will allow me as a customer  to check what I suggested and edit/append new considerations. 
 2.It will allow othe customers to read my suggestions and vote if they like/ dislike the idea, as well as add more details to the idea.
 3.It will allow you to provide information about status the idea, is it accepted and when it will be implemented.

Case insensitive StringHelper.EnsureEndsWith

I’ve created an extension  method for string 
     
public static string EnsureEndsWith(this string str,string sEndValue,bool ignoreCase=true)
        {
            if (!str.EndsWith(sEndValue, ignoreCase, CultureInfo.InvariantCulture))
            {
                str = str + sEndValue;
            }
            return str;
        }

ScrollToControl helper method for ASP.Net web forms to move position to particular control

I’ve created a helper method for ASP.Net web forms to move position to particular control
            /// <summary>
            ///
            /// </summary>
            /// <param name=”page”></param>
            /// <param name=”clientId”></param>
            /// <param name=”alignToTop”></param>
            public static void ScrollToControl( Page page, string clientId, bool alignToTop)
            {
                //NOTE: if there are more than one call on the page, first one will take preference
                //If we want that last will take  preference, change key from MethodBase.GetCurrentMethod().Name to anchorName
                //recommended in http://gnidesign.blogspot.com.au/2011/06/how-to-maintain-page-scroll-on-postback.html               
                String script = ” window.scrollTo = function () { };” + Environment.NewLine;
                script += String.Format(“document.getElementById(‘{0}’).scrollIntoView({1});” , clientId, alignToTop.JSToString());
                page.ClientScript.RegisterStartupScript(TypeForClientScript(), MethodBase.GetCurrentMethod().Name, script, true );
                //return script;
            }
            public static string JSToString(this bool bValue)
            {
                return bValue.ToString().ToLower();
            }

Use getElementById(‘{0}’).scrollIntoView is simpler than location.hash , because you don’t need to add extra anchor element.
Parameter alignToTop is very convenient to specify do you want to show control at the top or bottom of the screen.

It’s posted as an answer http://stackoverflow.com/a/34823392/52277
There is more advance jQuery plug-in http://erraticdev.blogspot.com.au/2011/02/jquery-scroll-into-view-plugin-with.html