Event Notification question

6 Apr 2006 3:56 PM

Codesmith

I am working on a c# app that utilizes Event Notification. So far I have been
able to get it to work with pretty simplistic SQL statements but I have run
into a problem.

The query that I need the application to use relies on a view as part of an
inner join. When I add this SQL statement to the dependency the onChange
event fires continuously. Can I not use a view in a dependancy?

Here is the SQL that I am adding to the dependancy;

private string GetSQL()
        {
            return "SELECT dbo.Employees.FirstName, dbo.Employees.LastName,
dbo.Employees.Department, dbo.Employees.Email, " +
            "dbo.Employees.Extension, dbo.EventLog.Type,
dbo.EventLog.Location, dbo.EventLog.Comment, dbo.EventLog.ReturnDateTime, " +
            "dbo.EventLog.Time FROM dbo.Employees INNER JOIN dbo.LastEvent
ON dbo.Employees.ID = dbo.LastEvent.EmployeeID " +
            "INNER JOIN dbo.EventLog ON dbo.LastEvent.EmployeeID =
dbo.EventLog.EmployeeID AND dbo.LastEvent.LastEntry = dbo.EventLog.Time";
        }

Everything in this SQL statement is a table except for LastEvent.

LastEvent is a pretty simple view that takes the EventLog tables EmployeeID
and Time and selects the most recent entry.

Could this circular refrencing back unto itself be part of my problem?

6 Apr 2006 6:56 PM

Codesmith

I have narrowed my roblem down to the MAX aggregat function that I am using
in my view.

The view takes all fo a employees events and selects the most recent one
using a MAX(eventTime) aggregate.

Accoridng to the query generator I can not index the view as long as it
contains this MAX aggregate.

can anyone offer a suggested work around for this?

7 Apr 2006 1:32 AM

Remus Rusanu [MSFT]

The query restrictions are detailed here:
http://msdn2.microsoft.com/en-US/library/ms181122.aspx
The indexed view restrictions are detailed here:
http://msdn2.microsoft.com/en-US/library/ms191432(SQL.90).aspx

BTW, the feature is actually called Query Notifications. Event Notifications
is for delivering notifications when certain DDL events occur (like a table
is created or dropped).

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

Show quote

"Codesmith" <Codesm***@discussions.microsoft.com> wrote in message
news:B48F6877-D733-453B-9225-8338DED760E6@microsoft.com...
>I am working on a c# app that utilizes Event Notification. So far I have
>been
> able to get it to work with pretty simplistic SQL statements but I have
> run
> into a problem.
>
> The query that I need the application to use relies on a view as part of
> an
> inner join. When I add this SQL statement to the dependency the onChange
> event fires continuously. Can I not use a view in a dependancy?
>
> Here is the SQL that I am adding to the dependancy;
>
> private string GetSQL()
>        {
>            return "SELECT dbo.Employees.FirstName, dbo.Employees.LastName,
> dbo.Employees.Department, dbo.Employees.Email, " +
>            "dbo.Employees.Extension, dbo.EventLog.Type,
> dbo.EventLog.Location, dbo.EventLog.Comment, dbo.EventLog.ReturnDateTime,
> " +
>            "dbo.EventLog.Time FROM dbo.Employees INNER JOIN dbo.LastEvent
> ON dbo.Employees.ID = dbo.LastEvent.EmployeeID " +
>            "INNER JOIN dbo.EventLog ON dbo.LastEvent.EmployeeID =
> dbo.EventLog.EmployeeID AND dbo.LastEvent.LastEntry = dbo.EventLog.Time";
>        }
>
> Everything in this SQL statement is a table except for LastEvent.
>
> LastEvent is a pretty simple view that takes the EventLog tables
> EmployeeID
> and Time and selects the most recent entry.
>
> Could this circular refrencing back unto itself be part of my problem?

7 Apr 2006 12:27 PM

Codesmith

Thanks for clarifying the destinction between query notification and event
notification.

While having the list of DO NOTS for query notifications is insightful it
doesn't really answer my question.

I suppose all that I can do is watch the entire table for change by
removing the max aggregate. I could then call a seperate refreshData routine
that utilizes the view containing the MAX aggregate to populate my datagrid
control.

7 Apr 2006 5:02 PM

Remus Rusanu [MSFT]

I'm not an expert in QN, but I believe creating a notification for the whole
table should work. If the notification would fire too often for data not
related to the current grid content, you can probably restrict the scope to
a subset of the table, using a WHERE clause.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

Show quote

"Codesmith" <Codesm***@discussions.microsoft.com> wrote in message
news:FDE88837-BB28-47A2-928B-03EF80DCE36A@microsoft.com...
> Thanks for clarifying the destinction between query notification and event
> notification.
>
> While having the list of DO NOTS for query notifications is insightful it
> doesn't really answer my question.
>
> I suppose all that I can do is watch the entire table for change by
> removing the max aggregate. I could then call a seperate refreshData
> routine
> that utilizes the view containing the MAX aggregate to populate my
> datagrid
> control.