The SQL Server-to-SQL Server Query notification function allows you to update interface data in real time.

The launch of SQL Server 2005 has brought enterprise-level applications to a new level,
It also makes the foundation of the. NET platform more solid, because the combination of the two makes the database development simple
It is self-evident that multiple functions such as reporting services and Notification Services
All of them are surging. Today, we will introduce one of the features: the use of query notifications.
Query notifications are simply used in applications.ProgramSends a query request to the database and
After a request for a query notification, the database monitors any DML that performs the modification operation.
When monitoring data, it will send a message to the application to tell the program that the data has been modified. Sent message
Send the sp_dispatcherproc stored procedure to the application.
The onchang event of sqldependency receives messages, and then runs
.

To use the query notification, refer to the following aspects:
1. You must grant
Send permission because the sqldenpendency object uses
Servicebroker sends messages to querynotificationservice.
Use MSDB
Grant send on service ::
Http://schemas.microsoft.com/ SQL /Notifications/QueryNotificationS
Ervice]
To Guest
The statement in square brackets is like a URL address, but it is actually a querynotificationservice service.
The complete name. Note that the case cannot be incorrect.
2. Enable CLR on the database server because a stored procedure is used.
In sp_dispatcherproc Code Is written using. Net code.
Use master
Exec sp_configure 'clr enabled', 1
Reconfigure
3. Enable Service Broker
Alter database name set enable_broker
After execution, you can use select databasepropertyex ('database name ',
Isbrokerenabled) to verify whether the service broker is enabled. If 1 is returned, true is returned,
0 indicates false.
4. If the command executed by the database user contains a notification subscription request
Subscribe query configurations permission of the database where the command is executed.
Use dbname
Grant subscribe query configurications to database_principal
5. The notification owner must have the following permissions on the database to run the query.
Notification of successful sqldependency creation:
Create Service
Create queue
Create procedure
6. To receive notifications, the subscriber must
Querynotificationerrosqueue has the receive permission.
Grant receive on querynotificationerrosqueue to login
7. The send permission of the target device is also required:
Grant send on service: // theservice to login
8. the user running the query must have the select permission on the table to access the notification.
The code of the program can be viewed in msdn. The code is very detailed. The most important thing is the Select language.
The statement must write all column names. * is not allowed, and the table name must be set by two names.
For example, DBO. tablename.
If the configuration is correct, open SQL profiler and run the program, you can see that the program is sent to the database
Packet sending request. Note that SQL Server 2005 express does not support query.
Yes, and it seems that there is a problem to query the notification after porting the Express version database to the official version, because
I just transplanted the Express version of the database and did not make the query notification after two days.
After the database is rebuilt, everything is normal.