Julie and I were having issues with getting Sql Query Notifications to work for ASP.NET cache dependencies (for our DevConnections talks). The trick was this:
sp_dbcmptlevel yourdatabasename,90
Julie did most of the research on this one, with help from Sushil Chordia and Leonid Tsybert from Microsoft. She has more on her blog, but I’m noting here so I might remember this next time.