I am working on a c# app that utilizes Event Notification. So far I have bee
n
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?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?|||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
"Codesmith" <Codesmith@.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?
