Query Notification question

I am having issues getting a SqlDependency proof of concept app to work. I
took a step back and performed the MSDN lab titled SQL Server and ADO.NET
(LabB), located here:
http://msdn.microsoft.com/vstudio/t...l/default.aspx. It contain
s
an example using SqlDependency to monitor a simple SELECT statement on the
AdventureWorks Person.Contact table. This example works fine (after I add
the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
constructor and destructor, respectively. I have one issue: when I revise
the table involved in the SELECT statement to include a computed field (I
created a FullName column in the Person.Contact table), the SqlDependency's
OnChange event fires repeatedly. The following items show in the
SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source: Statement.
Subscription occurs repeatedly and I'm in an infinite loop.
I can't seem to find a rule that says I cannot use a computed field in a
table invloved in my SQL statement for use with SqlDependency. Can anyone
confirm that computed fields are NOT allowed when desinging a query for use
with query notification?SQL Server BOL has a list of what can't be used in a statement with Query
Notifications. I don't remember computed column, but do remember than all
aggregates other than SUM are forbidden. Don't forget that you must be two
part object names too. If you use an invalid query you should get a
notification with a reason of "invalid query". Have a look at the fields in
your notification when you receive it.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>I am having issues getting a SqlDependency proof of concept app to work. I
> took a step back and performed the MSDN lab titled SQL Server and ADO.NET
> (LabB), located here:
> http://msdn.microsoft.com/vstudio/t...l/default.aspx. It
> contains
> an example using SqlDependency to monitor a simple SELECT statement on the
> AdventureWorks Person.Contact table. This example works fine (after I add
> the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
> constructor and destructor, respectively. I have one issue: when I revise
> the table involved in the SELECT statement to include a computed field (I
> created a FullName column in the Person.Contact table), the
> SqlDependency's
> OnChange event fires repeatedly. The following items show in the
> SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source:
> Statement.
> Subscription occurs repeatedly and I'm in an infinite loop.
> I can't seem to find a rule that says I cannot use a computed field in a
> table invloved in my SQL statement for use with SqlDependency. Can anyone
> confirm that computed fields are NOT allowed when desinging a query for
> use
> with query notification?|||Thanks for the reply. I've looked at the BOL, and I can find no specifc
mention of computed fields being "against the rules." The value of the Info
member in the SqlNotificationEventArgs is Query, which means "A SELECT
statement that cannot be notified or was provided." according to MSDN2. The
value of the Type member is Subscribe, which means "There was a failure to
create a notification subscription. Use the SqlNotificationEventArgs object'
s
SqlNotificationInfo item to determine the cause of the failure." This leads
me to believe the same SELECT statement which works on a table with no
computed field suddenly fails the criteria check after I add the computed
field. I guess at this point I am just looking for confirmation that
computed fields are not allowed if you want to use Query Notifications.
Thanks again,
Chris
"Bob Beauchemin" wrote:

> SQL Server BOL has a list of what can't be used in a statement with Query
> Notifications. I don't remember computed column, but do remember than all
> aggregates other than SUM are forbidden. Don't forget that you must be two
> part object names too. If you use an invalid query you should get a
> notification with a reason of "invalid query". Have a look at the fields i
n
> your notification when you receive it.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>
>|||Hi Chris,
That would just about clinch it as an invalid query for me... I don't
remember computed field being against the rules, but you're being told that
it is. What's the computed field look like exactly?
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
> Thanks for the reply. I've looked at the BOL, and I can find no specifc
> mention of computed fields being "against the rules." The value of the
> Info
> member in the SqlNotificationEventArgs is Query, which means "A SELECT
> statement that cannot be notified or was provided." according to MSDN2.
> The
> value of the Type member is Subscribe, which means "There was a failure to
> create a notification subscription. Use the SqlNotificationEventArgs
> object's
> SqlNotificationInfo item to determine the cause of the failure." This
> leads
> me to believe the same SELECT statement which works on a table with no
> computed field suddenly fails the criteria check after I add the computed
> field. I guess at this point I am just looking for confirmation that
> computed fields are not allowed if you want to use Query Notifications.
> Thanks again,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Bob,
Here's the computed field formula for the FullName column I added to the
Person.Contact table:
(ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
')+isnull([LastName],'')))
I would like to add that the SELECT statement I'm using with the
SqlDependency object is NOT referencing the computed field. The statement i
s
(right out of the MSDN lab):
SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
What bothers me is that the computed field is deterministic. The only
variable changing in the scenario is the addition of this computed field,
which is NOT being referenced in my SELECT statement. If you remove the
computed field, the example again works flawlessly.
Thanks for the replies,
Chris
"Bob Beauchemin" wrote:

> Hi Chris,
> That would just about clinch it as an invalid query for me... I don't
> remember computed field being against the rules, but you're being told tha
t
> it is. What's the computed field look like exactly?
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
>
>|||Yep, at first glance I don't see anything wrong with this. Let me try it out
and experiment with some permutations. It may take a few minutes.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
> Bob,
> Here's the computed field formula for the FullName column I added to the
> Person.Contact table:
> (ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
> ')+isnull([LastName],'')))
> I would like to add that the SELECT statement I'm using with the
> SqlDependency object is NOT referencing the computed field. The statement
> is
> (right out of the MSDN lab):
> SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
> What bothers me is that the computed field is deterministic. The only
> variable changing in the scenario is the addition of this computed field,
> which is NOT being referenced in my SELECT statement. If you remove the
> computed field, the example again works flawlessly.
> Thanks for the replies,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Hi Chris,
I just tried this. Worked fine for me here (don't ya just hate that answer).
I tried with your expression, only thing I added was the alias "as Fullname"
but even without the alias, worked fine. Worked OK, I caught the
notification and processed it. Send me mail (by figuring out email from the
obfuscated version) and I'll send you the code.
What version are you using? I'm using the RTM version.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> Yep, at first glance I don't see anything wrong with this. Let me try it
> out and experiment with some permutations. It may take a few minutes.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
>|||Bob,
If I had a dollar for every time I told QA "it works on my machine" I'd be
wildy rich.
I've read that the connection used to send the SQL statement must adhere to
some standards as well, this might be an issue as I can't think of what else
is different between my setup and yours. I am using the latest and greatest
releases of Visual Studio 2005 and SQL server 2005, no Beta stuff here. I'l
l
let you know how it works.
Thanks for the help,
Chris
"Bob Beauchemin" wrote:

> Hi Chris,
> I just tried this. Worked fine for me here (don't ya just hate that answer
).
> I tried with your expression, only thing I added was the alias "as Fullnam
e"
> but even without the alias, worked fine. Worked OK, I caught the
> notification and processed it. Send me mail (by figuring out email from th
e
> obfuscated version) and I'll send you the code.
> What version are you using? I'm using the RTM version.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
> news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
>
>|||I need some help with a formula for the Formula Field(Computed Column)
In the Table Designer there is a column field called Formula. I have success
fully used this column on a couple of occasions, but I am stuck this time.
I have a column called Quantity (Char) and a column called NN (Char). When Q
uantity = 0 I want NN to = Complete. When Quantity is <> 0 I want NN to =
Incomplete.
My Formula for NN, in the Formula Field, is;
(CASE WHEN [Quantity] = 0 THEN Complete ELSE Incomplete END)
I have also tried ;
IIF([Quantity]="0", "Complete", "Incomplete")
Neither work.
Robert