Query Notification SELECT QUERY rules

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!  

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.