0

I am currently working on a query that is registered for Query Notifications. In accordance w/ the rules of Notification Serivces, I can only use Deterministic functions in my queries set up for subscription. However, GetDate() (and almost any other means that I can think of) are non-deterministic. Whenever I pull my data, I would like to be able to limit the result set to only relevant records, which is determined by the current day.

Does anyone know of a work around that I could use that would allow me to use the current date to filter my results but not invalidate the query for query notifications?

Example Code:

SELECT fcDate as RecordDate, fcYear as FiscalYear, fcPeriod as FiscalPeriod, fcFiscalWeek as FiscalWeek, fcIsPeriodEndDate as IsPeriodEnd, fcPeriodWeek as WeekOfPeriod
FROM dbo.bFiscalCalendar 
WHERE fcDate >= GetDate() -- This line invalidates the query for notification...

Other thoughts:

We have an application controls table in our database that we use to store application level settings. I had thought to write a small script that keeps a record up to date w/ teh current smalldatetime. However, my join to this table is failing for notificaiton as well and I am not sure why. I surmise that it has something to do w/ me specifitying a text type (the column name), which is frustrating.

Example Code 2:

SELECT fcDate as RecordDate, fcYear as FiscalYear, fcPeriod as FiscalPeriod, fcFiscalWeek as FiscalWeek, fcIsPeriodEndDate as IsPeriodEnd, fcPeriodWeek as WeekOfPeriod
FROM dbo.bFiscalCalendar    
INNER JOIN dbo.xApplicationControls ON  fcDate >= acValue AND acName = N'Cache_CurrentDate'

Does anyone have any suggestions?

EDIT: Here is a link on MSDN that gives the rules for Notification Services

Nathan
  • 1,435
  • 5
  • 18
  • 28

2 Answers2

3

As it turns out, I figured out the solution. Basically, I was invalidating my query attempts because I was casting a value as a DateTime which marks it as Non-Deterministic. Even though you don't specifically call out a cast but do something akin to:

RecordDate = 'date_string_value'

You still end up w/ a Date Cast. Hopefully this will help out someone else who hits this issue.

This link helped me quite a bit.

http://msdn.microsoft.com/en-us/library/ms178091.aspx

Nathan
  • 1,435
  • 5
  • 18
  • 28
0

A good way to bypass this is simply to create a view that just says "SELECT GetDate() AS Now", then use the view in your query.

EDIT : I see nothing about not using user-defined functions (which is what I've used the 'view today' bit in). So can you use a UDF in the query that points at the view?

MartW
  • 12,348
  • 3
  • 44
  • 68
  • Unfortunately, views are not allowed to be accessed in queries for notification services either. – Nathan Oct 30 '09 at 12:58
  • How about UDFs? I see no reference to them being disallowed – MartW Oct 30 '09 at 13:10
  • UDF's are allowed BUT only if they also contain no non-deterministic functions (at least, I am 99% sure this is correct). I will double check this option though. Thanks. – Nathan Oct 30 '09 at 13:12
  • As it turns out, they have to be deterministic UDF's and SQL Server make that call based on the queries within. It ends up being the same issue. Good thought though. – Nathan Oct 30 '09 at 13:20