There are SO many caveats to using query notification wtih SQL Server. Things like the fact that it is not based on transactions (eg you will get a notifcation even if something is rolled back). You have to do a LOT of trolling around in the books online to find all of this stuff and frankly, I still do NOT have my notifications firing yet.

So here is a list from the Books Online for the October CTP of SQL Server 2005 of rules to follow when you are building a query with which you want to susbcribe to the service.

Query notifications are supported for SELECT statements that meet the following requirements:

• 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.
• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
• The statement may use unnamed columns or duplicate column names.
• The statement must reference a base table.
• 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.
• A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
• The statement must not include PIVOT or UNPIVOT operators.
• The statement must not include the INTERSECT or EXCEPT operators.
• The statement must not reference a view.
• The statement must not contain any of the following: 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.
• The statement must not reference the large object types: text, ntext, and image.
• The statement must not use the 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.
• 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.
• 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).

That’s just the query rules. There are also rules for the connection and a lot of setup to do on the database. I will eventually succeed at this! I swear I will. Of course, by then, I will probably have been fired from all of the other contract work I am supposed to be doing. (Not really…)

Posted from BLInk!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!