SQLDependency -- Invalid SQL

Question

Best I can tell from here, I am not breaking any rules with this query, but the change event fires constantly with an invalid status.

SELECT 
    COUNT_BIG([PK_Column]) AS RecordCount 
FROM 
    [dbo].[My_Table] 
GROUP BY 
    Varchar_50_Column

If I do this, everything works correctly.

SELECT 
    [PK_Column]
FROM 
    [dbo].[My_Table]

However, I don't want to return this much data, and SqlDependency seems to require executing the query once after each time the change event fires to set up (or reset) the subscription.

Any idea what the flaw is in my first query?

Why aren't you using COUNT_BIG(*)? It's going to give the same answer, and I've never seen that syntax before... — Aaron Bertrand
– Aaron Bertrand, Commented Sep 18, 2012 at 15:15
Trying Count_Big(*). Curious what syntax you've not seen before? — Phil Sandler
– Phil Sandler, Commented Sep 18, 2012 at 15:25
I've never seen anyone use COUNT_BIG(column_name). I'm not saying it's invalid, but perhaps SqlDependency doesn't understand what it is. — Aaron Bertrand
– Aaron Bertrand, Commented Sep 18, 2012 at 15:26
I've never used COUNT_BIG, but for a period of time (long ago) started using COUNT(PK_Column) after I was incorrectly told never to use "*", even with COUNT. I think the documentation that talks about valid aggregates is worded in a way that's open to interpretation. I only did it this way (including the GROUP BY, which I don't need) because of how I interpreted the documentation. — Phil Sandler
– Phil Sandler, Commented Sep 18, 2012 at 15:33

Pilgerstorfer Franz · Accepted Answer · 2012-09-18 15:17:40Z

3

Have a look at special considerations on query notifications If you make use of a group by clause you will have to use COUNT_BIG(*). On social.msdn I found another user having a similar / same problem - solving it with this statement.

edited Sep 18, 2012 at 15:17

answered Sep 18, 2012 at 15:12

Pilgerstorfer Franz

8,3473 gold badges46 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Sandler Over a year ago

COUNT_BIG(/*) works (without GROUP BY!). I believe the documentation on this is poorly worded, as even re-reading it now I still feel like it says GROUP BY is required and "/*" is not permitted. Anyway, thanks for your answer and I have it working.

Collectives™ on Stack Overflow

SQLDependency -- Invalid SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related