sql dependency query in c# throwing invalid sql, but works in sql server management studio

Question

SQL query to check for dependency is throwing invalid when it runs and give results in sql server management studio. Can someone help to find out what is wrong in it.

i need to have SUM to display end results

 public void GetAllTicket()
    {
        List<DashboardModel> resultlist = new List<DashboardModel>();

        using (SqlConnection connection = new SqlConnection(_connString))
        {
            string query = "SELECT SUM(CASE WHEN  [AssignedStaffID] = 8 and [IsAssignedStaffOverridden] = 0 THEN 1 ELSE 0 END) myticket,SUM(CASE WHEN [AgentGroupID] = 1 THEN 1 ELSE 0 END) agentgroup,SUM(CASE WHEN [AssignedStaffID] = 0 AND [AgentGroupID] = 1 THEN 1 ELSE 0 END) newticket,SUM(CASE WHEN [AssignedStaffID] = 8 AND [TicketStatusID] = 2 THEN 1 ELSE 0 END) resolvedticket FROM  [dbo].[Ticket]";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Notification = null;
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                connection.Open();
                DataTable dt = new DataTable();
                SqlDataReader reader = command.ExecuteReader();

                dt.Load(reader);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        resultlist.Add(new DashboardModel
                            {
                                MyTicketCount = int.Parse(dt.Rows[i]["myticket"].ToString()),
                                AgentGroupTicketCount = int.Parse(dt.Rows[i]["agentgroup"].ToString()),
                                NewTicketCount = int.Parse(dt.Rows[i]["newticket"].ToString()),
                                ResolvedTicketCount = int.Parse(dt.Rows[i]["resolvedticket"].ToString())
                            });
                    }
                }

            }
        }
        NotifyAllClients(resultlist);
    }

I get e.info= invalid below

    public void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Info == SqlNotificationInfo.Invalid)
        {
            Console.WriteLine("The above notification query is not valid.");
        }

        if (e.Type == SqlNotificationType.Change)
        {         
            GetAllTicket();
        }

    }

What is the complete invalid message? You know `SUM` is aggregate function, right? — Soner Gönül, Dec 03 '15 at 09:28
in if (e.Info == SqlNotificationInfo.Invalid) i get e.info= invalid — coder771, Dec 03 '15 at 09:29
please post the complete, verbatim error message; it would be best to put it into the question. — marmarta, Dec 03 '15 at 09:32
If you take that code and run it as a query using SQL Server Management Studio, either it will work or you'll get a more detailed response than "error message as invalid". How are you running this code? What does "dependency not firing" mean? — Matt Gibson, Dec 03 '15 at 09:48
Try to execute the query without SUM. You're not adding up different values, so it's of no use to put SUM in the select statement. — FreddieH, Dec 03 '15 at 09:49
@MattGibson it works with sql server management studio. but as i'm using sql dependency,there are certain rules with it, due to which it is not working with my c# project — coder771, Dec 03 '15 at 09:49
@FreddieH i need to sum as i want the end result too, to display — coder771, Dec 03 '15 at 09:52
What *is* "sql dependency"? Part of the reason you're getting downvotes is because you're posting code that you say is valid and works fine, and asking people what's wrong with it... This may be because people aren't clear on what you're asking, or what tool this error message about invalidity is coming from. — Matt Gibson, Dec 03 '15 at 09:52
Because you are not using proper grammar and neglect a lot of important information. How are you running it? What is the exact command you are using? What is the exact error message? — marmarta, Dec 03 '15 at 09:52
I have re-tagged this more appropriately now we know the technology we're dealing with. Can you post more of your code? I think we'll at least need to see the place where you're creating the SQL command with the SQL you've posted. — Matt Gibson, Dec 03 '15 at 10:01

score 1 · Accepted Answer · answered Dec 03 '15 at 11:07

1

This is not a valid query for SqlDependency because you use aggregates (SUM), but have no group by clause. The microsoft documentation about SqlDependency is this page here: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency%28v=vs.110%29.aspx

and it says

Query notifications are supported only for SELECT statements that meet a list of specific requirements.

There is a link to a very useful page which lists all these requirements in detail, and can be found here: https://msdn.microsoft.com/library/ms181122.aspx

There is a section on "Supported SELECT Statements" which has this little gem:

The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression.

It could be that there are other rules your query also breaks that are not immediately obvious from the code (eg, is dbo.tickets a view?).

answered Dec 03 '15 at 11:07

BeanFrog

2,297
12
26

thanks for the pointers. dbo.tickets is a table – coder771 Dec 03 '15 at 11:11
Did you try adding a group by statement - because that was the point of this answer? – BeanFrog Dec 03 '15 at 11:13
i have added group by but still it is invalid. I know there are issues with query. but dont know how to improve – coder771 Dec 03 '15 at 11:22
Have you read the rest of the posted article? Have you gone through every point in it and checked that your query fits? – BeanFrog Dec 03 '15 at 11:26
i changed my method. firstly a simple sql query to detect change,and then wrote the above code query to get results and display. Thanks for your help – coder771 Dec 03 '15 at 11:56

sql dependency query in c# throwing invalid sql, but works in sql server management studio

1 Answers1