none
SqlDependency, Entity Framework and C# RRS feed

  • Question

  • Hi All,

    I am trying to run a sample created using C# for SQL dependency. Whenever certain data is inserted in given table, I want a notification in my C# application. Right now, I am having both (my C# application as well as my SWl Server 2008 R2) the setups on the same machine which is not connected to any network.

    My problem is, although my broker service is enabled  SQL server is not throwing me notification back in my C# application, although I modified data in my database. In simpler words, my sqlDependency_Onchange event is not firing.

    Please help me out.


    Regards, http://www.shwetalodha.blogspot.in/


    Monday, September 15, 2014 1:53 PM

Answers

  • When a Service Broker message fails to arrive as expected, check the transmission_queue. 

    select cast(message_body as nvarchar(max)) message, transmission_status
    from sys.transmission_queue

    And generally see Service Broker Troubleshooting.


    One common cause is having the database owned by some invalid user (databases should generally be owned by sa).  If you see:

    An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 11. Could not obtain information about Windows NT group/user 'xxxxxx', error code 0x534.

    then run

    alter authorization on database::adventureworks2012 to sa


    To fix the database ownership. 

    David


    David http://blogs.msdn.com/b/dbrowne/



    Wednesday, September 17, 2014 1:41 PM
  • Hello All,

    Thanks for providing me pointers. I was able to resolve my issue. It was due to authorization as David also pointed out.


    Regards, http://www.shwetalodha.blogspot.in/

    Wednesday, September 17, 2014 1:44 PM

All replies

  • can you check, if you have enabled the grant access in SQL server side towards the c# application side?

    http://technet.microsoft.com/en-us/library/ms166038(v=sql.105).aspx

    regards

    joon

    Monday, September 15, 2014 2:04 PM
  • My application is console application. I can't get service and contract details here.

    Regards, http://www.shwetalodha.blogspot.in/

    Monday, September 15, 2014 2:28 PM
  • Could you share your SELECT statment?


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Tuesday, September 16, 2014 9:03 AM
  • SqlConnection connection = new SqlConnection("Data Source=7979XXXXxxxx;Initial Catalog=MyDB;User ID=USER;Password=PWD;");
    
                command = new SqlCommand("SELECT * FROM dbo.Product", connection);
    
                SqlDependency.Stop(con);
                SqlDependency.Start(con);
    
                command.Notification = null;
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
    
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    MessageBox.Show("query executed");
                }


    Regards, http://www.shwetalodha.blogspot.in/


    Tuesday, September 16, 2014 3:39 PM
  • This line

     using (SqlDataAdapter adapter = new SqlDataAdapter(command))

    does not run the query.  Using the DataAdapter to Fill() a DataSet will run the query. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 16, 2014 3:55 PM
  • Hello David,

    I tried both adapter.Fill(dataset) and command.ExecuteReader(). But no luck.


    Regards, http://www.shwetalodha.blogspot.in/

    Tuesday, September 16, 2014 4:00 PM
  • You cannot use SELECT *, must specify column list.

    LIke; SELECT Col1, Col2 FROM dbo.MyTable


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Tuesday, September 16, 2014 4:43 PM
  • Here's your snippet wrapped in a short, complete working example:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace SqlDependencyTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                var constr = @"Data Source=.\sql2014;Initial Catalog=AdventureWorks2012;Integrated Security=true";
                using (SqlConnection connection = new SqlConnection(constr))
                {
                    connection.Open();
                    var command = new SqlCommand("SELECT ProductID, Name, Size FROM Production.Product", connection);
    
                    SqlDependency.Stop(constr);
                    SqlDependency.Start(constr);
    
                    command.Notification = null;
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += (s, a) =>
                        {
                            Console.WriteLine("OnChange Event Fired.  Info:{0} Type: {1} Source: {2}", a.Info, a.Type, a.Source);
                        };
    
                    using (var rdr = command.ExecuteReader())
                    {
                        var dt = new DataTable();
                        dt.Load(rdr);
                    }
    
                    var cmd2 = new SqlCommand("update Production.Product set Name = Name  where ProductID = 1;", connection);
                    cmd2.ExecuteNonQuery();
                }
                Console.WriteLine("Hit any key to exit");
                Console.ReadKey();
    
            }
    
        }
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by ErikEJMVP Tuesday, September 16, 2014 7:26 PM
    Tuesday, September 16, 2014 6:39 PM
  • Hello ErikEJ,

    I tried your solution by giving column names in my query, but again no luck. Am I missing any setting from SQL server side except enabling service broker?


    Regards, http://www.shwetalodha.blogspot.in/

    Wednesday, September 17, 2014 6:51 AM
  • Yes - http://msdn.microsoft.com/en-us/library/ms172133(v=vs.110).aspx

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Wednesday, September 17, 2014 6:56 AM
  • Hello David,

    I tried your sample code also, but didn't work. Perhaps I am missing some settings in SQL server. I am using Windows Authentication with everything(code and DB) on the same machine. can you please provide mesome other pointer in this direction.

    The only change I did in SQL server is enabling Servicebroker


    Regards, http://www.shwetalodha.blogspot.in/

    Wednesday, September 17, 2014 7:07 AM
  • When a Service Broker message fails to arrive as expected, check the transmission_queue. 

    select cast(message_body as nvarchar(max)) message, transmission_status
    from sys.transmission_queue

    And generally see Service Broker Troubleshooting.


    One common cause is having the database owned by some invalid user (databases should generally be owned by sa).  If you see:

    An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 11. Could not obtain information about Windows NT group/user 'xxxxxx', error code 0x534.

    then run

    alter authorization on database::adventureworks2012 to sa


    To fix the database ownership. 

    David


    David http://blogs.msdn.com/b/dbrowne/



    Wednesday, September 17, 2014 1:41 PM
  • Hello All,

    Thanks for providing me pointers. I was able to resolve my issue. It was due to authorization as David also pointed out.


    Regards, http://www.shwetalodha.blogspot.in/

    Wednesday, September 17, 2014 1:44 PM