2

Can SQL Server push message to a program which listens to SQL Server?

For example:

There is a program A, listening for SQL Server. SQL Server will view a table named B, when B has some data, SQL Server will get the data and push it to A.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
allen
  • 250
  • 4
  • 13

3 Answers3

1

Yes, it's possible, see How to run a program from SQL?.

But, as that post states, there are a lot of reasons not to do so. SQL Server was written to be queried, so it will be a lot more efficient answering queries than pushing them.

Community
  • 1
  • 1
jlee-tessik
  • 1,510
  • 12
  • 16
  • Indeed,I want to sync data from database to program A with CDC.Since SqlDependecy can not do well, I must query all data in table when DML happened to it.I just want to update the change data. – allen Jan 20 '15 at 08:06
  • If you want CDC, why not just enable that for those tables you want to track? – Ben Thul Jan 20 '15 at 12:51
  • Because CDC is asynchronous,when the table changed,i do not know the message immediately – allen Jan 21 '15 at 00:52
  • Because CDC is asynchronous,I dont want to view the table by polling.Instead,when the table changed,SQL Server would send message to Program A, which table have changed and the change message. – allen Jan 21 '15 at 01:04
0

Are you looking for some kind of SqlServerNotifications | MSDN? For me that did not work because it requers some Configuration that we were not able to implement ( security reasons ) ... so i implemented my own notification layer with my own TCP network wrapper. When one client updates the database, it sends to all other clients a message with ID and table name, and the Client will update the entry by it self. Its not easy to implement and requers a lot of desgin.

Community
  • 1
  • 1
Venson
  • 1,772
  • 17
  • 37
  • You mean message would be send by the another client which execute the update operation,not SQL Server,right? – allen Jan 20 '15 at 07:56
  • Jes that was my Approach. I created my own TCP wrapper and implimented a logic that was notifying all other Clients. Of Course this solution is only good if we are talking about a LocalNetwork. Otherwise you would simply fail by trying to bypass the Target network Architektur (NAT PAT and so on). Other idere would by a server that maintains all connections and can spread the message to all clients. – Venson Jan 20 '15 at 08:03
0

You can use SqlDependecy (class Details) to detect changes in tables/views. This does require Enabling Query Notifications.

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.
    using (SqlCommand command=new SqlCommand(
        "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", 
        connection))
    {

        // Create a dependency and associate it with the SqlCommand.
        SqlDependency dependency=new SqlDependency(command);
        // Maintain the refence in a class member.

        // Subscribe to the SqlDependency event.
        dependency.OnChange+=new
           OnChangeEventHandler(OnDependencyChange);

        // Execute the command.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }
}

// Handler method
void OnDependencyChange(object sender, 
   SqlNotificationEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString, queueName);
}

To use SqlDependency, Service Broker must be enabled for the SQL Server database being used, and users must have permissions to receive notifications. Service Broker objects, such as the notification queue, are predefined.

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
  • Thank you.But It will register the command `SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers` to SQL Server in this way. Now,I have about 15 tables to be monitor,all them must be registered? – allen Jan 20 '15 at 07:48
  • I really want to reveive the message which table and which rows have been changed without `select` all table. – allen Jan 20 '15 at 08:10
  • Hello.If a system table have been changed , `SqlDependency` can useful? – allen Jan 20 '15 at 09:25
  • Yes each table you want to be monitored needs it's own query, just like selected data from different tables requires different queries. You have to use a `select` statement, but you can build the query any way you want (join, where, group by, having, etc). A table is a table, it shouldn't differentiate between user/system tables. – Erik Philips Jan 20 '15 at 17:29
  • If there are many tables to be monitored such as 20,SQL Server can work ok? – allen Jan 21 '15 at 01:01