How to use your library

[tridip-bba]

i am interested to know how to use your library instead of MS provided. it would be nice for all if you come with a tutorial like how to use this in any application and where it is different from built-in sql dependency.

thanks

[dyatchenko]

Hello!

Thanks for your issue. I'm going to prepare a fresh documentation for the component soon, but if you want to know how to use it now - it is pretty simple. Just copy the SqlDependencyEx class to your project and you can use it like this:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

The component uses DML trigger and the Service Broker notification (the same as native SqlDependency) for getting changes in a table. Differences between SqlDependencyEx and SqlDependency are the following:

1. SqlDependency leaves trash in a database (it can cause memory leaks). SqlDependencyEx - doesn't.
2. SqlDependency doesn't provide actual information about the changes are being made. But SqlDependencyEx has event args with XML-properties which give you info about actual changed data.
3. SqlDependencyEx uses DML trigger meanwhile SqDependency doesn't.
4. SqlDependencyEx encapsulates all the notification process. Thus you don't need to think about how notification happens - you just create an object and receive events meanwhile you have to make a wrapper around the native SqlDependency.

Thanks!

[tridip-bba]

HI,

Thanks for your reply. i need the code in follow way just like below. the below code is related to sql dependency classes.

// this code stop and start the sql dependency 
// here i point a table whose data change i want to capture
// when data will be change then OnDataChange() event will be called automatically

private void RegisterNotification()
{
        string tmpdata = "";
        System.Data.SqlClient.SqlDependency.Stop(connectionString);
        System.Data.SqlClient.SqlDependency.Start(connectionString);
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT ActivityDate FROM [bba-reman].ContentChangeLog";
                dep = new SqlDependency(cmd);
                dep.OnChange += new OnChangeEventHandler(OnDataChange);
                SqlDataReader dr = cmd.ExecuteReader();
                {
                    while (dr.Read())
                    {
                        if (dr[0] != DBNull.Value)
                        {
                            tmpdata = dr[0].ToString();
                        }
                    }
                }
                dr.Dispose();
                cmd.Dispose();
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
        }
    }

void OnDataChange(object sender, SqlNotificationEventArgs e)
    {
        ((SqlDependency)sender).OnChange -= OnDataChange;
        if (e.Source == SqlNotificationSource.Timeout)
        {
                    // capture time out error
                    Environment.Exit(1);
        }
        else if (e.Source != SqlNotificationSource.Data)
        {
                  //  capture another error
                  Environment.Exit(1);
        }
        else if (e.Type == SqlNotificationType.Change)
        {
                //capture data change
               CaptureChangeData();
        }
        else
        {
        }

        // calling register notification again from where to monitor the table
        RegisterNotification();
    }

so i want this kind of code from you to know how could i use your library. if possible please give me a detail code.

thanks Tridip

[dyatchenko]

public class YourClass : IDisposable
{
     private const string CONNECTION_STRING = "????????";
     private const string DATABASE_NAME = "??????";
     private const string TABLE_NAME = "ContentChangeLog";
     private const string SCHEMA_NAME = "bba-reman";

     private SqlDependencyEx sqlDependency = new SqlDependencyEx( 
                                               CONNECTION_STRING,
                                               DATABASE_NAME, 
                                               TABLE_NAME,
                                               SCHEMA_NAME);

     // Something else is here....         

     private void RegisterNotification()
     {
            sqlDependency.TableChanged += OnDataChange;
            sqlDependency.Start();
     }

     private void UnregisterNotification()
     {
            sqlDependency.Stop();
            sqlDependency.TableChanged -= OnDataChange;
     }

     private void OnDataChange(object sender, SqlDependencyEx.TableChangedEventArgs e)
     {
              // TODO: do stuff here
              // If you want to monitor changes of `ActivityDate` field only
              // you have to use the `TableChangedEventArgs` object.
              // It has the `NotificationType` field and the `Data` field.
              // The `Data` field contains information about the changes being made,
              // so you can filter it.
     }

     private void Dispose() 
     {
            // Don't forget to clean up the resources!
            UnregisterNotification();
     }
}

As you can see, using of SqlDependencyEx doesn't require writing ADO.NET wrapper above. All you need to do is to create an instance and subscribe for notifications. I expected the same realization when I first used native SqlDependency. IMHO this realization much more easier than the default one.

[tridip-bba]

HI, See this code you provided

private SqlDependencyEx sqlDependency = new SqlDependencyEx( 
                                               CONNECTION_STRING,
                                               DATABASE_NAME, 
                                               TABLE_NAME,
                                               SCHEMA_NAME);

Instead of table name can we write select statement? How to write select statement please show Can we specify store procedure name instead of table name ? What SCHEMA_NAME name I need to enter ? please discuss with code

Thanks Tridip

[dyatchenko]

According to your questions:

1. Instead of table name can we write select statement? How to write select statement please show
2. Can we specify store procedure name instead of table name ?
3. What SCHEMA_NAME name I need to enter ? please discuss with code

The answers:

1. No you can't. With SqlDependencyEx you get all the notifications from a database about the INSERT or/and UPDATE or/and DELETE (At least one of them) changes. SqlDependencyEx also provides information about the actual changed data in the XML representation. You can filter it if you want to receive the changes about the specific column(s). But you can implement your own SELECT-based filtering for SqlDependencyEx also. I haven't done it yet because IMHO SELECT-based filtering looks awful.
2. What do you mean? You want to monitor changes of a stored procedure? It doesn't make sense.
3. I took SCHEMA_NAME from the SELECT statement in your code example: SELECT ActivityDate FROM [bba-reman].ContentChangeLog. In this case schema name is bba-reman.

If you don't mind, please format the questions properly in the next time. It is difficult to read.

Thanks, Dmitriy

[tridip-bba]

HI,

Hope your are fine.

i like to use your SqlDependencyEx library but before that i need to know how to use.

1) when we work with sql dependency then we need to resubscribe again and again to get the change notification. so the same is required in your case ?

see my below (edit: above) code where i am calling RegisterNotification() again from OnDataChange event.

2) how to get change data in xml format. if possible please provide code for that

3) how to what operation has taken on table like Insert or Update or delete using your library ?

4) when we work with sql dependency then timeout occur which we can capture from OnDataChange event like this way if (e.Source == SqlNotificationSource.Timeout) {} so like to know how to do the same when one will use your library ?

please provide me full code and also discuss all pros and corns of your library. thanks

On 24 April 2015 at 00:37, Dmitriy Dyatchenko notifications@github.com wrote:

According to your questions:

1. Instead of table name can we write select statement? How to write select statement please show
2. Can we specify store procedure name instead of table name ?
3. What SCHEMA_NAME name I need to enter ? please discuss with code

The answers:

1. No you can't. With SqlDependencyEx you get all the notifications from a database about the INSERT or/and UPDATE or/and DELETE (At least one of them) changes. SqlDependencyEx also provides information about the actual changed data in the XML representation. You can filter it if you want to receive the changes about the specific column(s). But you can implement your own SELECT-based filtering for SqlDependencyEx also. I haven't done it yet because IMHO SELECT-based filtering looks awful.
2. What do you mean? You want to monitor changes of a stored procedure? It doesn't make sense.
3. I took SCHEMA_NAME from the SELECT statement in your code example: SELECT ActivityDate FROM [bba-reman].ContentChangeLog. In this case schema name is bba-reman.

If you don't mind, please format the questions properly in the next time. It is difficult to read.

Thanks, Dmitriy

— Reply to this email directly or view it on GitHub https://github.com/dyatchenko/ServiceBrokerListener/issues/5#issuecomment-95649043 .

[dyatchenko]

Hi,

1. As you can see from the usage example above you don't need to resubscribe for event notifications.

2. You can find all the answers in the UnitTest project. A code example of XML data usage (from the unit tests):

   private static void DetailsTest(int insertsCount)
   {
      int elementsInDetailsCount = 0;
      int changesReceived = 0;
   
      using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
                  TEST_CONNECTION_STRING,
                  TEST_DATABASE_NAME,
                  TEST_TABLE_NAME, "temp"))
      {
          sqlDependency.TableChanged += (o, e) =>
              {
                  changesReceived++;
   
                  if (e.Data == null) return;
   
                  // All the INSERTED data
                  var inserted = e.Data.Element("inserted");
                  // All the DELETED data
                  var deleted = e.Data.Element("deleted");
   
                  elementsInDetailsCount += inserted != null
                                                ? inserted.Elements("row").Count()
                                                : 0;
                  elementsInDetailsCount += deleted != null
                                                ? deleted.Elements("row").Count()
                                                : 0;
              };
          sqlDependency.Start();
   
          MakeChunkedInsertDeleteUpdate(insertsCount);
   
          // Wait a little bit to receive all changes.
          Thread.Sleep(1000);
      }
   
      Assert.AreEqual(insertsCount * 2, elementsInDetailsCount);
      Assert.AreEqual(3, changesReceived);
   }

3. A code example of using INSERT, UPDATE, DELETE separately (constructor overload):

   // INSERT and DELETE without UPDATE.
   var notificationType = SqlDependencyEx.NotificationTypes.Delete 
                         |  SqlDependencyEx.NotificationTypes.Insert;
   SqlDependencyEx sqlDependency = new SqlDependencyEx
                        (CONN_STR, DB_NAME, TBL_NAME, SCHEMA_NAME, notificationType);

4. There is no timeout event in SqlDependencyEx - it is incapsulated inside. You will get events just only about table changes.

Thanks, Dmitriy

[cdfell]

Something we did was use Thread.Sleep(Timeout.Infinite) instead of preset amount of time (like 1000 ms in example). In our particular implementation we have requirement for a listener to run 24x7 (all day & night). This implementation is still in POC but so far no problems.

On Sat, Apr 25, 2015 at 4:47 PM, Dmitriy Dyatchenko < notifications@github.com> wrote:

Hi,

1. As you can see from the usage example above you don't need to resubscribe for event notifications. 2.

   You can find all the answers in the UnitTest project https://github.com/dyatchenko/ServiceBrokerListener/blob/master/ServiceBrokerListener/ServiceBrokerListener.UnitTests/SqlDependencyExTest.cs. A code example of XML data usage (from the unit tests):

   private static void DetailsTest(int insertsCount) { int elementsInDetailsCount = 0; int changesReceived = 0;

   using (SqlDependencyEx sqlDependency = new SqlDependencyEx( TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME, "temp")) { sqlDependency.TableChanged += (o, e) => { changesReceived++;

             if (e.Data == null) return;
   
             // All the INSERTED data
             var inserted = e.Data.Element("inserted");
             // All the DELETED data
             var deleted = e.Data.Element("deleted");
   
             elementsInDetailsCount += inserted != null
                                           ? inserted.Elements("row").Count()
                                           : 0;
             elementsInDetailsCount += deleted != null
                                           ? deleted.Elements("row").Count()
                                           : 0;
         };
     sqlDependency.Start();
   
     MakeChunkedInsertDeleteUpdate(insertsCount);
   
     // Wait a little bit to receive all changes.
     Thread.Sleep(1000);

   }

   Assert.AreEqual(insertsCount * 2, elementsInDetailsCount); Assert.AreEqual(3, changesReceived); }

   3.

   A code example of using INSERT, UPDATE, DELETE separately (constructor overload):

   // INSERT and DELETE without UPDATE. var notificationType = SqlDependencyEx.NotificationTypes.Delete | SqlDependencyEx.NotificationTypes.Insert; SqlDependencyEx sqlDependency = new SqlDependencyEx (CONN_STR, DB_NAME, TBL_NAME, SCHEMA_NAME, notificationType);

   4.

   There is no timeout event in SqlDependencyEx - it is incapsulated inside. You will get events just only about table changes.

— Reply to this email directly or view it on GitHub https://github.com/dyatchenko/ServiceBrokerListener/issues/5#issuecomment-96284037 .

[dyatchenko]

If you want to be sure that SqlDependencyEx works in the 24x7 mode you can use a new NotificationProcessStopped event which fires when the notification process stops (working thread interrupts). It might happen for example when SqlDependencyEx loses the connection with a database. If you get this notification you should call consequently (of course if the connection is established again) Stop() and then Start() and the component will continue its job.

[tridip-bba]

HI, Thanks for your reply. I saw this code you provided

1. var inserted = e.Data.Element("inserted"); what will be stored in inserted variable ? how to get updated data then ? because you did not provide way out to catch updated data.

Here you start sql dependency like this way

1. sqlDependency.Start(); but you did not provide code to sqlDependency.Stop() so like to know Stop() is not required for your library ? I found no code to work with sql connection object and reader object which is generally used when we work with sql dependency.

Looking for your guidance. Thanks Tridip

[tridip-bba]

HI,

Where is the body for this function MakeChunkedInsertDeleteUpdate(insertsCount); What this function MakeChunkedInsertDeleteUpdate(insertsCount); will do ?

Thanks Tridip

[tridip-bba]

HI,

Can you please provide sample code how to use NotificationProcessStopped() which will fire when SqlDependencyEx will be disconnected with db as you said. I like to know how to hook or attach this function to get the notification and as a result we could try to establish the connection again between SqlDependencyEx and database. Please help me.

Thanks Tridip

[dyatchenko]

Hello

According to your questions:

• An example of stored data in the inserted variable here:

<inserted>
  <row> <TestField>4</TestField> <StrField>юникод&lt;&gt;_4</StrField> </row>
  <row> <TestField>3</TestField> <StrField>юникод&lt;&gt;_3</StrField> </row>
  <row> <TestField>2</TestField> <StrField>юникод&lt;&gt;_2</StrField> </row>
</inserted>

If you want to know what does it mean inserted and deleted in this case you should check this article up. The inserted variable represents an INSERTED temp table in a DML trigger and deleted represents a DELETED temp table in a DML trigger. Having this information is more than enough to identify the UPDATE columns.

• Pay attention please on the code which I've provided before.

• According to the name of the MakeChunkedInsertDeleteUpdate it Makes Chuncked Insert then chunked Update and then chunked Delete. You can check the code in the unit tests also, but here it is:

  private static void MakeChunkedInsertDeleteUpdate(int changesCount)
  {
    const string ScriptFormat = "INSERT INTO #TmpTbl VALUES({0}, N'{1}')\r\n";
  
    // insert unicode statement
    StringBuilder scriptResult = new StringBuilder
            ("SELECT 0 AS Number, N'юникод<>_1000001' AS Str INTO #TmpTbl\r\n");
    for (int i = 1; i < changesCount / 2; i++) 
             scriptResult.Append(string.Format(ScriptFormat, i, "юникод<>_" + i));
  
    scriptResult.Append(@"INSERT INTO temp.TestTable (TestField, StrField)   
                                    SELECT * FROM #TmpTbl");
    ExecuteNonQuery(scriptResult.ToString(), TEST_CONNECTION_STRING);
    ExecuteNonQuery("UPDATE temp.TestTable SET StrField = NULL"
                                    , TEST_CONNECTION_STRING);
    ExecuteNonQuery("DELETE FROM temp.TestTable", TEST_CONNECTION_STRING);
  }

• Your behavior in the NotificationProcessStopped event handler depends completely on the business-logic. E.g. if you experienced the power off (and you lose the DB connection) in an office you should fix it first and then call consequently Stop() and then Start() and the component will continue its job. For example:

  private void NotificationProcessStopped(object o, EventArgs e)
  {
       // It is assumed that the connection is fine.
  
       this.sqlDependencyEx1.Stop();
       this.sqlDependencyEx1.Start();
  }

Regards, Dmitriy

[tridip-bba]

HI,

Thanks for your answer.

1) i use sql dependency related classes in my windows service which will run 27X7 and OnDataChange() event will run whenever data is changed in a specific table. At my end windows service is running with sql dependency code but some time problem occur like suppose when today I will start my windows service then it will notify me as many time data will change in table but may not work from tomorrow or day after tomorrow. I am getting clueless why some time it is not working. Then what I do I just restart the service and all again start working for few days.

2) I issue this query select * from sys.dm_qn_subscriptions and notice data is not populated in this system table dm_qn_subscriptions
3) Right now this below way I am dealing with error which may occur due to connection time out or data mismatch etc. here is the code

       void OnDataChange(object sender, SqlNotificationEventArgs e)
       {

        BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);

        if (e.Source == SqlNotificationSource.Timeout)
        {
            BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
            Environment.Exit(1);
        }
        else if (e.Source != SqlNotificationSource.Data)
        {
             BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
             Environment.Exit(1);
        }
        else if (e.Type == SqlNotificationType.Change)
        {
            BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
           StartIndex();
        }
        else
        {
            BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
        }
        ((SqlDependency)sender).OnChange -= OnDataChange;
        RegisterNotification();
      }

I return Environment.Exit(1); when error occur as a result my win service will restart because I have set the discovery level that way for my win service.

4) Also notice we need to unregister OnChange every time when we work with sql dependency. So do we need to do the same in your case if I use sqldependencyEX ?

5) When we work with sqldependencyEX then connetion time out will not occur ? if occur then how I will be notified ? show me to catch error when I will be working with sqldependencyEX.

Because if I use your sqldependencyEX then it will run 24X7 so connetion time out error or other kind of error may occur. I need to know how to handle those error ?

Looking for your guide line. Apologized for asking some many question because I need to clear my doubts in advance before using your library.

Thanks Tridip

[dyatchenko]

Hi,

The answers in the chaotic order:

• If an exemplar of the SqlDependencyEx doesn't fire NotificationProcessStopped be sure that everything is going fine. If you experienced this event just restarted the component with Stop() and Start() like I showed above.
• You don't need to unsubscribe from event notifications unless you dispose the object.
• The connection timeout event doesn't occur in SqlDependencyEx.

We used little modified SqlDependencyEx as part of our customer service (it is assumed that they are still use it) and we had system reboots once a week on Sundays. The component worked fine without any problems. So, it should work within at least a week.

P.S. I also have some ideas about a stability improvement. And I'm going to implement it soon as well as a NuGet package and a fresh documentation with examples. I'm waiting for critics and if people like it I'm able to make it much better.

Regards, Dmitriy

[tridip-bba]

HI,

I have few question as follows 1) is it sure that when someone work with SqlDependencyEx then connection time out will not occurred because you said last time. Why do you think connection time out will not occurred when someone will work with your libraray SqlDependencyEx ? 2) you developed SqlDependencyEx. So did you develop this libraray from scratch or develop it extending SqlDependencyEx class ? 3) see the below script I have seen many people use the below script to clean up purpose. Did you use the same ? please see and answer.

private void ClearOldSubscriptions() { using (var connection = new SqlConnection(connectionString)) { using (var command = new SqlCommand()) { string sql = ////@"DECLARE @UniqueTimeout AS int = 3586; " + @"DECLARE @SubscriptionId AS int; " + @"DECLARE @Sql AS varchar(max); " + @"DECLARE SubscriptionCursor CURSOR LOCAL FAST_FORWARD " + @" FOR " + @" SELECT id " + @" FROM sys.dm_qn_subscriptions " + @" WHERE database_id = DB_ID() " + @" AND timeout = @UniqueTimeout " + @"OPEN SubscriptionCursor; " + @"FETCH NEXT FROM SubscriptionCursor INTO @SubscriptionId; " + @"WHILE @@FETCH_STATUS = 0 " + @"BEGIN " + @" SET @Sql = 'KILL QUERY NOTIFICATION SUBSCRIPTION ' + CONVERT(varchar, @SubscriptionId); " + @" EXEC(@Sql); " + @" " + @" FETCH NEXT FROM SubscriptionCursor INTO @SubscriptionId; " + @"END"; command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = sql; command.Parameters.Add("@UniqueTimeout", SqlDbType.Int).Value = 432000; connection.Open(); command.ExecuteNonQuery(); } } }

private void ClearNotificationQueue() { using (var connection = new SqlConnection(connectionString)) { using (var command = new SqlCommand()) { string sql = @"DECLARE @Conversation AS uniqueidentifier; " + @"DECLARE ConversationCursor CURSOR LOCAL FAST_FORWARD " + @" FOR " + @" SELECT CEP.conversation_handle FROM sys.conversation_endpoints CEP " + @" WHERE CEP.state = 'DI' or CEP.state = 'CD' " + @" " + @"OPEN ConversationCursor; " + @"FETCH NEXT FROM ConversationCursor INTO @Conversation; " + @"WHILE @@FETCH_STATUS = 0 " + @"BEGIN " + @" END CONVERSATION @Conversation WITH CLEANUP; " + @" " + @" FETCH NEXT FROM ConversationCursor INTO @Conversation; " + @"END " + @""; command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = sql; connection.Open(); command.ExecuteNonQuery(); } } }

Did you use the above approach for clean up purpose ? if yes then first one script will not work when people has their database hosted in shared location. So tell me how do you library code manage to Clear Old Subscriptions from sql server when database is hosted in shared location. Our data base is hosted in shared location of ORCSWEB company and I talk to DB admin that I was getting exception when I try to clear old Subscriptions and they said to clear old Subscriptions one need super admin right which they cannot assign to me. So how your application clear old Subscriptions when db is hosted in shared location.

Please answer my question point wiese. Thanks Tridip

[tridip-bba]

HI,

One urgent question that your ServiceBrokerListener class has been developed using VS2013 ? I am working with VS2010. Can you tell me how could I use your ServiceBrokerListener class from my VS2010 IDE ?

Guide me in detail. Thanks Tridip

[dyatchenko]

Hi

1. Connection timeout may occur in case of the DB connection loss, but you will see NotificationProcessStopped instead.
2. I don't understand what are you talking about.
3. SqlDependencyEx doesn't create unreliable conversations. It means that there is no need to clean the old conversations up. Each conversation endpoint has its 1 minute lifetime. According to a Rusanu article it is a very bad practice to clean the old notification endpoints in the way you mentioned above.
4. I don't understand what are you talking about. I tried to find ServiceBrokerListener but I found nothing.

Thanks, Dmitriy

[tridip-bba]

HI,

I download your code from git and now tell me how could I incorporate your code in my project to implement sqldependencyex. My project is using VS2010 not VS2013.

Need guidance.

I also asked how did you develop a extended dependency classes. Did you extend Microsoft sql dependency classes or full develop your application from scratch ?

Thanks Tridip

[dyatchenko]

Hi

All the code is just SqlDependencyEx class. Just copy it to your project and you don't need to do something else. Actually this code was developed on VS2008 in the beginning. You're not supposed to see some compatibility problems. And yes, the component is not an extension of native SqlDependency. It was developed from the beginning to the end without any relies on SqlDependency.

Thanks, Dmitriy

[tridip-bba]

HI,

Now when I try to open your project called ServiceBrokerListener.sln from VS2010 then face problem but when I open your project with VS2013 then it opened.

I will go through the code of this project ServiceBrokerListener.WebUI after few days and will ask question like many area and how to implement this in MVC application.

Thanks Tridip

[tridip-bba]

HI,

When this script run then I am getting error as follows

Msg 102, Level 15, State 1, Procedure bba, Line 2 Incorrect syntax near '-'. Msg 102, Level 15, State 1, Procedure bba, Line 35 Incorrect syntax near '-'. Msg 102, Level 15, State 1, Procedure bba, Line 38 Incorrect syntax near '-'.

The below script run from your code

                USE [BBAreman]

                DECLARE @msg VARCHAR(MAX)
                DECLARE @crlf CHAR(1)
                SET @crlf = CHAR(10)
                SET @msg = 'Current user must have following permissions: '
                SET @msg = @msg + '[CREATE PROCEDURE, CREATE SERVICE, CREATE QUEUE, SUBSCRIBE QUERY NOTIFICATIONS, CONTROL, REFERENCES] '
                SET @msg = @msg + 'that are required to start query notifications. '
                SET @msg = @msg + 'Grant described permissions with following script: ' + @crlf
                SET @msg = @msg + 'GRANT CREATE PROCEDURE TO [<username>];' + @crlf
                SET @msg = @msg + 'GRANT CREATE SERVICE TO [<username>];' + @crlf
                SET @msg = @msg + 'GRANT CREATE QUEUE  TO [<username>];' + @crlf
                SET @msg = @msg + 'GRANT REFERENCES ON CONTRACT::[DEFAULT] TO [<username>];' + @crlf
                SET @msg = @msg + 'GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [<username>];' + @crlf
                SET @msg = @msg + 'GRANT CONTROL ON SCHEMA::[<schemaname>] TO [<username>];'
                --PRINT @msg

            IF OBJECT_ID ('bba-reman.sp_InstallListenerNotification_1', 'P') IS NULL
            BEGIN
                EXEC ('
                    CREATE PROCEDURE bba-reman.sp_InstallListenerNotification_1
                    AS
                    BEGIN
                        -- Service Broker configuration statement.
                        -- Setup Service Broker

                        IF EXISTS (SELECT * FROM sys.databases 
                                      WHERE name = ''BBAreman'' AND (is_broker_enabled = 0 OR is_trustworthy_on = 0)) 

            BEGIN
                 IF (NOT EXISTS(SELECT * FROM sys.fn_my_permissions(NULL, ''SERVER'')
                                         WHERE permission_name = ''CONTROL SERVER''))
                 BEGIN
                    DECLARE @msg VARCHAR(MAX)
                    SET @msg = ''Current user doesn''''t have CONTROL SERVER permission to enable service broker. ''
                    SET @msg = @msg + ''Grant sufficient permissions to current user or ''
                    SET @msg = @msg + ''execute ALTER DATABASE [<dbname>] SET ENABLE_BROKER with admin rights.''
                    RAISERROR (@msg, 16, 1)
                 END
                 ELSE 
                 BEGIN
                    ALTER DATABASE [BBAreman] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
                    ALTER DATABASE [BBAreman] SET ENABLE_BROKER; 
                    ALTER DATABASE [BBAreman] SET MULTI_USER WITH ROLLBACK IMMEDIATE

                    -- FOR SQL Express
                    ALTER AUTHORIZATION ON DATABASE::[BBAreman] TO [sa]
                    ALTER DATABASE [BBAreman] SET TRUSTWORTHY ON;               
                 END
            END

            -- Create a queue which will hold the tracked information 
            IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = ''ListenerQueue_1'')
                  CREATE QUEUE bba-reman.[ListenerQueue_1]
            -- Create a service on which tracked information will be sent 
            IF NOT EXISTS(SELECT * FROM sys.services WHERE name = ''ListenerService_1'')
                  CREATE SERVICE [ListenerService_1] ON QUEUE bba-reman.[ListenerQueue_1] ([DEFAULT]) 
                       -- Notification Trigger check statement.
            IF OBJECT_ID (''bba-reman.tr_Listener_1'', ''TR'') IS NOT NULL
                RETURN;

                        -- Notification Trigger configuration statement.
                        DECLARE @triggerStatement NVARCHAR(MAX)
                        DECLARE @select NVARCHAR(MAX)
                        DECLARE @sqlInserted NVARCHAR(MAX)
                        DECLARE @sqlDeleted NVARCHAR(MAX)
                        SET @triggerStatement = N''

            CREATE TRIGGER [tr_Listener_1]
            ON bba-reman.[ContentChangeLog]
            AFTER INSERT, UPDATE, DELETE 
            AS
            SET NOCOUNT ON;

            --Trigger ContentChangeLog is rising...
            IF EXISTS (SELECT * FROM sys.services WHERE name = ''''ListenerService_1'''')
            BEGIN
                DECLARE @message NVARCHAR(MAX)
                SET @message = N''''<root/>''''

                IF ( EXISTS(SELECT 1))
                BEGIN
                    DECLARE @retvalOUT NVARCHAR(MAX)

                    %inserted_select_statement%

                    IF (@retvalOUT IS NOT NULL)
                    BEGIN SET @message = N''''<root>'''' + @retvalOUT END                        

                    %deleted_select_statement%

                    IF (@retvalOUT IS NOT NULL)
                    BEGIN
                        IF (@message = N''''<root/>'''') BEGIN SET @message = N''''<root>'''' + @retvalOUT END
                        ELSE BEGIN SET @message = @message + @retvalOUT END
                    END 

                    IF (@message != N''''<root/>'''') BEGIN SET @message = @message + N''''</root>'''' END
                END

              --Beginning of dialog...
              DECLARE @ConvHandle UNIQUEIDENTIFIER
              --Determine the Initiator Service, Target Service and the Contract 
              BEGIN DIALOG @ConvHandle 
                    FROM SERVICE [ListenerService_1] TO SERVICE ''''ListenerService_1'''' ON CONTRACT [DEFAULT] WITH ENCRYPTION=OFF, LIFETIME = 60; 

                  --Send the Message
                  SEND ON CONVERSATION @ConvHandle MESSAGE TYPE [DEFAULT] (@message);
                  --End conversation
                  END CONVERSATION @ConvHandle;
            END
        ''
                        SET @select = STUFF((SELECT '','' + COLUMN_NAME
                                                         FROM INFORMATION_SCHEMA.COLUMNS
                                                         WHERE TABLE_NAME = ''ContentChangeLog'' AND TABLE_CATALOG = ''BBAreman''
                                                         FOR XML PATH ('''')
                                                         ), 1, 1, '''')
                        SET @sqlInserted = 
                            N''SET @retvalOUT = (SELECT '' + @select + N'' 
                                                 FROM INSERTED 
                                                 FOR XML PATH(''''row''''), ROOT (''''inserted''''))''
                        SET @sqlDeleted = 
                            N''SET @retvalOUT = (SELECT '' + @select + N'' 
                                                FROM DELETED 
                                                 FOR XML PATH(''''row''''), ROOT (''''deleted''''))''                            
                        SET @triggerStatement = REPLACE(@triggerStatement
                                                 , ''%inserted_select_statement%'', @sqlInserted)
                        SET @triggerStatement = REPLACE(@triggerStatement
                                                 , ''%deleted_select_statement%'', @sqlDeleted)
                        EXEC sp_executeSql @triggerStatement
                    END
                    ')
            END

Please help me how to fix this error.

Thanks, Tridip

[tridip-bba]

HI,

When I debug your sqldependencyex code then I saw your application was generating and executing sql . here I want to refer a small one snippet of your written sql below

CREATE TRIGGER [tr_Listener_1]

ON [bba-reman].[ContentChangeLog]

AFTER INSERT, UPDATE, DELETE 

AS

SET NOCOUNT ON;

--Trigger ContentChangeLog is rising...

IF EXISTS (SELECT * FROM sys.services WHERE name = ''''ListenerService_1'''')

BEGIN

    DECLARE @message NVARCHAR(MAX)

    SET @message = N''''<root/>''''

    IF ( EXISTS(SELECT 1))

    BEGIN

        DECLARE @retvalOUT NVARCHAR(MAX)

        %inserted_select_statement%

        IF (@retvalOUT IS NOT NULL)

        BEGIN SET @message = N''''<root>'''' + @retvalOUT END                        

        %deleted_select_statement%

        IF (@retvalOUT IS NOT NULL)

        BEGIN

            IF (@message = N''''<root/>'''') BEGIN SET @message = N''''<root>'''' + @retvalOUT END

            ELSE BEGIN SET @message = @message + @retvalOUT END

        END 

        IF (@message != N''''<root/>'''') BEGIN SET @message = @message + N''''</root>'''' END

    END

    --Beginning of dialog...

    DECLARE @ConvHandle UNIQUEIDENTIFIER

    --Determine the Initiator Service, Target Service and the Contract 

    BEGIN DIALOG @ConvHandle 

        FROM SERVICE [ListenerService_1] TO SERVICE ''''ListenerService_1'''' ON CONTRACT [DEFAULT] WITH ENCRYPTION=OFF, LIFETIME = 60; 

    --Send the Message

    SEND ON CONVERSATION @ConvHandle MESSAGE TYPE [DEFAULT] (@message);

    --End conversation

    END CONVERSATION @ConvHandle;

END

''

            SET @select = STUFF((SELECT '','' + COLUMN_NAME

                                 FROM INFORMATION_SCHEMA.COLUMNS

                                 WHERE TABLE_NAME = ''ContentChangeLog'' AND TABLE_CATALOG = ''bbareman''

                                 FOR XML PATH ('''')

                                 ), 1, 1, '''')

            SET @sqlInserted = 

                N''SET @retvalOUT = (SELECT '' + @select + N'' 

                                     FROM INSERTED 

                                     FOR XML PATH(''''row''''), ROOT (''''inserted''''))''

            SET @sqlDeleted = 

                N''SET @retvalOUT = (SELECT '' + @select + N'' 

                                     FROM DELETED 

                                     FOR XML PATH(''''row''''), ROOT (''''deleted''''))''                            

            SET @triggerStatement = REPLACE(@triggerStatement

                                     , ''%inserted_select_statement%'', @sqlInserted)

            SET @triggerStatement = REPLACE(@triggerStatement

                                     , ''%deleted_select_statement%'', @sqlDeleted)

            EXEC sp_executeSql @triggerStatement

        END

        '

Just tell me what is the meaning of the below line

%inserted_select_statement%

%deleted_select_statement%

what the above syntax mean ?

thanks

Tridip

From: Dmitriy Dyatchenko [mailto:notifications@github.com] Sent: Wednesday, April 29, 2015 2:11 PM To: dyatchenko/ServiceBrokerListener Cc: tridip-bba Subject: Re: [ServiceBrokerListener] How to use your library (#5)

Hi

All the code is just https://github.com/dyatchenko/ServiceBrokerListener/blob/master/ServiceBrokerListener/ServiceBrokerListener.Domain/SqlDependencyEx.cs SqlDependencyEx class. Just copy it to your project and you don't need to do something else. Actually this code was developed on VS2008 in the beginning. You're not supposed to see some compatibility problems. And yes, the component is not an extension of native SqlDependency. It was developed from the beginning to the end without any relies on SqlDependency.

Thanks, Dmitriy

— Reply to this email directly or view https://github.com/dyatchenko/ServiceBrokerListener/issues/5#issuecomment-97352916 it on GitHub. https://github.com/notifications/beacon/AE-LlBRBNQsIgkxT3-fuH2FnHiA3Nq4Pks5oEJCCgaJpZM4EFcsP.gif

[dyatchenko]

Hi,

There are two levels of script generation:

1. The script which generated in .Net
2. The script which generated in MsSQL

The first level script contains a stored procedures creation and a database preparation. The second level scripts are executed inside the first level to get access to the table structure, to make a select query for INSERTED and DELETED temporary tables. E.g. stored procedure from the first level asks all the needed column names from the database, makes a SQL query for INSERTED and DELETED, completes notification trigger script and executes it. So, according to your question what does %inserted_select_statement% mean - this statement is a key which the first level script uses to make the second level.

Regards, Dmitriy

[tridip-bba]

HI,

Sorry just do not understand what u r saying about this line %inserted_select_statement%

Can u please come with example for the usage of %inserted_select_statement% so I may try to understand the purpose of %inserted_select_statement%

Thanks Tridip

[dyatchenko]

Hi,

The answer is here:

-- Notification Trigger configuration statement.
DECLARE @triggerStatement NVARCHAR(MAX)
DECLARE @select NVARCHAR(MAX)
DECLARE @sqlInserted NVARCHAR(MAX)
DECLARE @sqlDeleted NVARCHAR(MAX)

SET @triggerStatement = N''{3}''

SET @select = STUFF((SELECT '','' + COLUMN_NAME
                                             FROM INFORMATION_SCHEMA.COLUMNS
                                             WHERE TABLE_NAME = ''{5}'' 
                                             AND TABLE_CATALOG = ''{0}''
                                             FOR XML PATH ('''')
                             ), 1, 1, '''')
SET @sqlInserted = N''SET @retvalOUT = (SELECT '' + @select + N'' 
                 FROM INSERTED 
                 FOR XML PATH(''''row''''), ROOT (''''inserted''''))''
SET @sqlDeleted = N''SET @retvalOUT = (SELECT '' + @select + N'' 
                 FROM DELETED 
                 FOR XML PATH(''''row''''), ROOT (''''deleted''''))''
SET @triggerStatement = REPLACE(@triggerStatement
                  , ''%inserted_select_statement%'', @sqlInserted)
SET @triggerStatement = REPLACE(@triggerStatement
                  , ''%deleted_select_statement%'', @sqlDeleted)
EXEC sp_executeSql @triggerStatement

In the following row:

SET @triggerStatement = REPLACE(@triggerStatement
                  , ''%inserted_select_statement%'', @sqlInserted)

The component takes all the column names from INFORMATION_SCHEMA.COLUMNS first, puts them into @triggerStatement and then executes the trigger statement EXEC sp_executeSql @triggerStatement.

For the current realization this behavior is ambiguous. We don't need column definitions, because we take all the columns from INSERTED and DELETED. We can use SELECT * FROM INSERTED FOR XML PATH("ROW"), ROOT ("inserted") instead of SELECT Column1, Column2, Column3 FROM INSERTED FOR XML PATH("ROW"), ROOT ("inserted"). But it was left for the future, e.g. if we monitor changes in a big table with hundreds of columns and we don't need to get information about all of them, but about the specific columns only. I will implement it in the near future.

Thanks, Dmitriy

[tridip-bba]

HI,

Thanks for your reply. still I do not understand the usage of the below lines of code

SET @triggerStatement = REPLACE(@triggerStatement
              , ''%inserted_select_statement%'', @sqlInserted)
SET @triggerStatement = REPLACE(@triggerStatement
              , ''%deleted_select_statement%'', @sqlDeleted)

I know what replace does but how this come %inserted_select_statement% from ??

Is it any variable or what ?? because you did not declare any variable by this name %inserted_select_statement% in code

What is it is?

I like to understand this %inserted_select_statement%

Please help me.

Thanks Tridip

[dyatchenko]

Hi,

%inserted_select_statement% is a part of a trigger template script. The trigger template statement has this line of code. %inserted_select_statement% is like a key to complete the trigger statement before execution. The trigger template statement is the following (The @triggerStatement variable from my example above):

CREATE TRIGGER [{1}]
ON {5}.[{0}]
AFTER {2} 
AS
     SET NOCOUNT ON;
     --Trigger {0} is rising...

     IF EXISTS (SELECT * FROM sys.services WHERE name = '{3}')
     BEGIN
              DECLARE @message NVARCHAR(MAX)
              SET @message = N'<root/>'

              IF ({4} EXISTS(SELECT 1))
              BEGIN
                 DECLARE @retvalOUT NVARCHAR(MAX)

                 %inserted_select_statement%

                 IF (@retvalOUT IS NOT NULL)
                        BEGIN SET @message = N'<root>' + @retvalOUT END                        

                 %deleted_select_statement%

                 IF (@retvalOUT IS NOT NULL)
                 BEGIN
                      IF (@message = N'<root/>') BEGIN SET @message = N'<root>' + @retvalOUT END
                      ELSE BEGIN SET @message = @message + @retvalOUT END
                 END 
                 IF (@message != N'<root/>') BEGIN SET @message = @message + N'</root>' END
              END

              --Beginning of dialog...
              DECLARE @ConvHandle UNIQUEIDENTIFIER
              --Determine the Initiator Service, Target Service and the Contract 
              BEGIN DIALOG @ConvHandle 
              FROM SERVICE [{3}] TO SERVICE '{3}' 
              ON CONTRACT [DEFAULT] WITH ENCRYPTION=OFF, LIFETIME = 60; 
              --Send the Message
              SEND ON CONVERSATION @ConvHandle 
              MESSAGE TYPE [DEFAULT] (@message);
              --End conversation
              END CONVERSATION @ConvHandle;
      END

As you can see %inserted_select_statement% in the center.

Thanks, Dmitriy

[tridip-bba]

HI,

If I try to compile your below trigger tsql code then sql server will throw error for this syntax

%inserted_select_statement%

There is nothing such exist in sql server which start with %variable_name% Very sorry do not understand the flow.

Thanks Tridip

[dyatchenko]

Hi,

What is your problem exactly? Why do you want to execute it? Of course it doesn't work, because it is a script template (it is not a code to execute, this code is raw). This code is executed in the runtime after auto-modification. If you haven't still solved the problem with dashes which you mentioned above, the solution is to put schema name between square brackets, like that [bba-reman], or try to use names without dashes (I missed it, sorry. I promise I will fix it).

Thanks, Dmitriy

[tridip-bba]

HI,

This issue [bba-reman] has been solved long back. The moment I add [] bracket then it started working.

You said it is a script template “%inserted_select_statement%” ok then where the template code is stored and assign to this “%inserted_select_statement%”. I checked your code but found no variable like this “%inserted_select_statement%”

Your coding flow was very ambiguous and hard to understand.

Sorry to request you that can you please construct a very small sample program where you would use this kind of “%inserted_select_statement%” Symantec then may I could understand the flow. So if possible write a small c# program with this kind of “%inserted_select_statement%” syntax which I can run in my pc to understand the flow.

Thanks Tridip

[dyatchenko]

Hi,

%inserted_select_statement% is not about C#. It is about SQL. It is needed on the database side. A stored procedure in the database uses this marker to perform some logic. Like the following stored procedure

CREATE PROCEDURE foo
@exec_param nvarchar(max)
AS
BEGIN
    DECLARE @out int
DECLARE @sql NVARCHAR(MAX)

SET @sql = N'select @out_param = %REPLACEMENT_MARKER%'
SET @sql = REPLACE(@sql, '%REPLACEMENT_MARKER%', @exec_param)

EXEC sp_executesql @sql, N'@out_param int OUTPUT', @out_param = @out OUTPUT

SELECT @out
END

As we can see the procedure uses a dynamic SQL to perform the logic. The procedure creates a SQL statement and executes it by itself as well as SqlDependencyEx procedures (SqlDependencyEx procedures are much more complicated). The procedure uses %REPLACEMENT_MARKER% to complete the dynamic query before execution. Thus, we can use the procedure as the following:

EXEC [dbo].[foo] @exec_param = N'10 + 1'

The result will be 11.

Thanks, Dmitriy

[tridip-bba]

HI,

Thanks for your answer but this line is not clear

EXEC sp_executesql @sql, N'@out_param int OUTPUT', @out_param = @out OUTPUT

Can you please explain the above tsql like easy way………if possible because I am not very advanced & experienced developed

Thanks Tridip

[dyatchenko]

Hi,

Here, here and here all the information you need.

Thanks, Dmitriy

[dyatchenko]

A brief instruction was added to the main page.