前一阵子用SqlDependency遇到了很多问题，跟大家的写法都一样，但是数据库日志文件不知道问什么会暴涨的厉害，最近看了一些文章，发现SqlDependency使用时到时有蛮多限制的，虽然我的问题还没有解决。
这是在微软官网上看到的，稍微注释了一下，又好些地方自己也不懂，就当是先记下来吧！
使用查询通知时的一些语法限制：
• The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
• 必须显式说明 SELECT 语句中提取的列名，且表名必须限定为两部分的名字（[dbo].[Users]）。注意这意味着在语句中引用的所有表都必须在同一数据库中。
• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
• 语句中不能使用通配符*、或者 表名.*的语法
• The statement may not use unnamed columns or duplicate column names.
• 语句中最好不要用未命名的列或者重复的列
• The statement must reference a base table.
• 语句只能对应一张表
• The statement must not reference tables with computed columns.
• 不能使用计算过的列
• The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
• 当没有使用GROUP BY语句时，SELECT查询的列中不能包含聚合表达式。只有使用了GROUP BY语句，才能使用COUNT_BIG() or SUM()这种聚合函数。需要注意的是，在可以为空的列上不能使用SUM()函数，同样也不能使用HAVING, CUBE, or ROLLUP.语句
• A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
• SELECT语句中查询的列应该是一个简单语句，同一个列不应该出现超过一次
• The statement must not include PIVOT or UNPIVOT operators.
• 语句中不能出现PIVOT or UNPIVOT操作
• The statement must not include the UNION, INTERSECT, or EXCEPT operators.
• 语句中不能出现UNION, INTERSECT, or EXCEPT操作
• The statement must not reference a view.
• 语句中不能使用视图
• The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
• 语句中不能包含DISTINCT, COMPUTE or COMPUTE BY, or INTO
• The statement must not reference server global variables (@@variable_name).
• 语句中不能使用全局变量
• The statement must not reference derived tables, temporary tables, or table variables.
• 语句中不能使用派生表、临时表和表级别变量
• The statement must not reference tables or views from other databases or servers.
• 不能使用来自其他数据库的表或视图
• The statement must not contain subqueries, outer joins, or self-joins.
• 不能包含子查询或是join操作
• The statement must not reference the large object types: text, ntext, and image.
• 不能使用大数据类型：如text, ntext, and image.
• The statement must not use the CONTAINS or FREETEXT full-text predicates.
• 不能使用CONTAINS or FREETEXT full-text predicates.（不懂）
• The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
• The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
• 不能使用以下聚合函数：AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
• The statement must not use any nondeterministic functions, including ranking and windowing functions.
• The statement must not contain user-defined aggregates.
• 不能包含用户自定义的集合
• The statement must not reference system tables or views, including catalog views and dynamic management views.
• 不能使用系统表或视图（包括目录视图和动态管理视图）
• The statement must not include FOR BROWSE information.
• 不能包含FOR BROWSE
• The statement must not reference a queue.
• 不能使用队列
• The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
• 不能包含无法返回值的条件语句（如where 1=0）
• The statement can not specify READPAST locking hint.
• 不能指定READPAST
• The statement must not reference any Service Broker QUEUE.
• 不能使用任意Service Broker QUEUE
• The statement must not reference synonyms.
• 不能给一个列指定两个名字
• The statement must not have comparison or expression based on double/real data types.
• 对于double/real类型，不能进行比较
• The statement must not use the TOP expression.
• 不能使用TOP表达式
贴上原文网址：http://msdn.microsoft.com/en-US/library/ms181122.aspx
还有在其他地方看到的有关内容，一并先记下来，粗略看了一遍，来自于《Pro SQL Server 2008 Service Broker》P374
A common problem with query notifications is in combination with the provided callback
(the OnChange event handler inside the SqlDependency class) handler in your application.
One of the most common error messages that can occur here is the following one inside the
event log:
The query notification dialog on conversation handle
'{5925E62A-A3BA-DC11-9E8E-000C293EC5A4}.'closed due to the following error:
'<?xml version="1.0"?>
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8470</Code>
<Description>Remote service has been dropped.</Description>
</Error>'
As you can see from the error description, the remote service has been dropped. But what
happened here? The immediate cause of the error message is clear, because the target service
of the query notification was dropped. But the real question is why this happens. Is it a pro-
gramming error or a configuration error? As you’ll see in a moment, it’s neither of these errors.
The first solution to this problem is that the SqlDependency.Stop method is called too
often in the application code. The general recommendation here is to call SqlDependency.
Start when the application starts up and SqlDependency.Stop when the application shuts down.
However, sometimes it turns out that this approach doesn’t solve the problem itself. Let’s
look how SqlDependency waits for notification messages. When you look at SQL Profiler when
you call SqlDependency.Start, you’ll see the T-SQL query submitted by the SqlDependency
background thread that waits for incoming notifications. See Listing 9-29.
exec sp_executesql
N'BEGIN CONVERSATION TIMER (''9c0b82d5-a3ba-dc11-9e8e-000c293ec5a4'')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle,
cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-6f91483f-089c-425e-afa6-0c1553ad1b52]),
TIMEOUT @p2;',N'@p2 int',@p2=60000
This query will start a new conversation timer with a time-out of two minutes (120
seconds) and then posts a WAITFOR T-SQL statement with a time-out of one minute (60,000
milliseconds). The idea here is that if the application exits abruptly the conversation timer will
fire and this will cause the activated procedure attached to the queue to run; this in turn will
clean up the SqlDependency infrastructure (the activated procedure itself, the Service
Broker service, and the queue). Normally the application will not disconnect abruptly, so the
WAITFOR T-SQL statement will time out after one minute, causing the SqlDependency to post
back the same query, which will reset the conversation timer again to two minutes. This
means the timer is actually never firing because it is continuously moved back two minutes.
If a notification is received, then the WAITFOR T-SQL statement will dequeue the notification
before the one-minute time-out occurs, and after the application callback is notified, the
SqlDependendy will again post the same query, resetting the timer again.
The problem here is that the same query is only posted again after the application callback
is notified. This means when the callback function is lasting longer than two minutes (or more
precisely, the time left from the original two minutes when the query was first launched), then
the conversation will fire and the SqlDependency infrastructure will be removed!
The application callback (the SqlDependency.OnChange event handler) is called synchro-
nously in the context of processing the WAITFOR T-SQL statement query result. If this callback
exceeds what’s left from the original timer of two minutes, then the conversation timer will
fire and the SqlDependency infrastructure will be removed. You can easily verify this behavior
by simply waiting in the debugger on a breakpoint set inside this callback. Shortly the SQL
Profiler will show that the activated stored procedure was launched and the procedure itself,
the Service Broker service, and the queue were dropped. Interestingly enough, after the appli-
cation is resumed, the SqlDependency class creates a new infrastructure by deploying a new
stored procedure, a new Service Broker service, and a new queue.
Of course, a two-minute time frame to process a callback seems long enough. But there is
one very common scenario that results in much more time: debugging. When you develop
applications, you often spend several minutes inside the debugger before you move on. So
please be careful when you debug an application that uses the SqlDependency class.
另一个：
I’ve seen a number of customers reporting problems about ERRORLOG growing out of control (tens of GBs) because of error like following:
2007-10-12 11:18:32.44 spid25s The query notification dialog on conversation handle ‘{EC54573A-9978-DC11-961C-00188B111155}.’ closed due to the following error: ‘<?xml version=”1.0??><Error xmlns=”http://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8490</Code><Description>Cannot find the remote service ‘SqlQueryNotificationService-4869b411-fa1c-4d8a-ab37-5bf5762eb98b’ because it does not exist.</Description></Error>’.
2007-10-12 11:37:20.69 spid51s The activated proc [dbo].[ SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d] running on queue tempdb.dbo. SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d output the following: ‘Could not find stored procedure ‘dbo. SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d‘
2007-10-12 10:59:39.32 spid51 Error: 28054, Severity: 11, State: 1.
2007-10-12 10:59:39.32 spid51 Service Broker needs to access the master key in the database ‘tempdb’. Error code:25. The master key has to exist and the service master key encryption is required.
All these messages are related one way or another to the ADO.Net component SqlDependency. I’ll present each one how it is caused and how to avoid id.
First, lets review in brief how the SqlDependency works. The application is supposed to invoke the static method SqlDependency.Start at startup to deploy the necessary infrastructure, then use instances of SqlDependency object associated with a SqlCommand to receive callbacks when the query executed is notified (data has changed), and finally call SqlDependency.Stop when the application shuts down to tear down the infrastructure deployed at startup. I have explained before how the server side Query Notifications feature works to detect the changes and to notify the subscriptions, see http://rusanu.com/2006/06/17/the-mysterious-notification.
The three errors above are all a result of the way how SqlDependency deploys and cleans up it’s infrastructure. The first error happens in the following scenario:
- SqlDependency.Start () is invoked by an application. At this moment a service, a queue and a procedure are created.
- SqlDependency is used to subscribe to query notifications. Perhaps some queries are notified and re-subscribed, in a normal operations mode.
- Application exists, SqlDependency.Stop is called and the service, queue and procedure are dropped. However, there are still subscribed notifications pending on the server.
- One or more of the pending subscriptions are notified in the server. This cause a notification message to be sent, but the destination service was dropped, so an error is returned to the sender. The QN receives this error and displays an error message in the ERRORLOG:
2007-10-12 11:18:32.44 spid25s The query notification dialog on conversation handle ‘{EC54573A-9978-DC11-961C-00188B111155}.’ closed due to the following error: ‘<?xml version=”1.0″?><Error xmlns=”http://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8490<Description>Cannot find the remote service ‘SqlQueryNotificationService-4869b411-fa1c-4d8a-ab37-5bf5762eb98b’ because it does not exist.</Description></Error>’
This is straightforward scenario and the application developer has done no mistake. However, this case should not result in huge ERRORLOG growth, because it will happen only for subscriptions notified after an application had exited. So if an application runs twice for 4 hours a day and subscribes to 10 queries, it should cause at most 20 entries like this a day. Multiplied by a decent size deployment base you will get something like 2000 entries for a 100 users deployment, annoying but not fatal. If you find your ERROLOG swamped by the message above (I’ve heard of thousands of entries added per hour), review your application behavior. Most likely you are calling SqlDepdendency.Stop way too often. Normally it should be called only on AppDomain unload. A possible workaround would probably have to be based on the kill query notification subscription verb, forcing the application to cleanup any pending subscription at shutdown. Good luck figuring out your own subscriptions from the other instances of the same application…
The second error message is a bit trickier. This one happens because the SqlDepndency cleanup attempted to drop the service, queue and procedure. Here is an example of how the cleanup procedure looks like:
CREATE PROCEDURE [SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d]
AS
BEGIN
BEGIN TRANSACTION;
RECEIVE TOP(0)
conversation_handle
FROM [SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d];
IF (SELECT COUNT(*) FROM [SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d]
WHERE message_type_name = ‘http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer’) > 0
BEGIN
DROP SERVICE [SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d];
DROP QUEUE [SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d];
DROP PROCEDURE [SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d];
END
COMMIT TRANSACTION;
END
It is possible that for whatever reason it fails to drop the service and the queue, but it dropped the procedure. Since there is no referential integrity rule to prevent the drop of a procedure that is attached as activation procedure to a queue, it is possible to drop the procedure and leave the queue declared with activation pointing to a missing procedure. In this case the activation mechanism will trigger the error message every five seconds:
2007-10-12 11:37:20.69 spid51s The activated proc [dbo].[ SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d] running on queue tempdb.dbo. SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d output the following: ‘Could not find stored procedure ‘dbo. SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d‘
Every five seconds for each queue left behind by SqlDependency, now that will grow the ERRORLOG to fill every last sector available on the disk in no time at all. The queues that have this problem must be manually fixed (ie. DROP QUEUE). One can identify them by looking up sys.service_queues for queues that have an activation procedure that no longer exists. Also one must identify the cause of the problem, why the service and queue could not be dropped, since the application that is using SqlDependency will continue to create new queues with orphaned activation.
Finally the last problem is the message about the lack of a master key in the database:
2007-10-12 10:59:39.32 spid51 Error: 28054, Severity: 11, State: 1.
2007-10-12 10:59:39.32 spid51 Service Broker needs to access the master key in the database ‘tempdb’. Error code:25. The master key has to exist and the service master key encryption is required.
This message is usually also caused by SqlDependency because the timer it creates to cleanup the service, queue and procedure needs a conversation (to use BEGIN CONVERSATION TIMER). This conversation is started without specifying the ENCRYPTION = OFF clause, so it will need a database master key to store the generated session keys. Although this conversation never sends any message and session keys are not actually needed, the message is periodically logged into the ERRORLOG. To avoid this message, there is a trivial workaround: simply create a database master key in the database
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
