none
Problem with deletion from database table when SqlDependency is active RRS feed

  • Question

  • Dear All,

    I have struggled with all the energy I had for the past week trying to troubleshoot an issue with SqlDependency, but to no avail.

    Here is the problem: My application uses SqlDependency. When I start it, I can SELECT and UPDATE data in a Microsoft SQL Server 2008 Express Edition database. Changes are immediately reflected in the application. Till this point I am satisfied with the SqlDependency. However, when I try to DELETE from the database table monitored in the SqlDependency, either from the application or from SQL Management Studio, I get the following two errors:

    Msg 0, Level 11, State 0, Line 0

    A severe error occurred on the current command.  The results, if any, should be discarded.

    Msg 0, Level 20, State 0, Line 0

    A severe error occurred on the current command.  The results, if any, should be discarded.

    When I stop my application the errors persist when I try to delete from SQL Management Studio. When I stop and restart SQL Server Service it now becomes possible to delete from the table. Therefore the above errors are experienced from both my application and SQL Management Studio when my application is started.

    I then opened the generated one of the corresponding SQLDump file and extracted the following lines:

    *   Exception Address = 0000000001314F5D Module(sqlservr+0000000000FE4F5D)                                      

    *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION                                                     

    *   Access Violation occurred writing address 0000000083CF2860                                                  

    * Input Buffer 314 bytes -                                                                                       

    *               DECLARE    @return_value int    EXEC  @return_value = [dbo].[De                                     

    *  leteCustomerVendor]            @AccountID = 150    SELECT 'Return Value' = @ret                                     

    *  urn_value 

     

    Here are the methods handling the SqlDependencies:

     

         private void OnDependencyChange(object sender, SqlNotificationEventArgs e)

            {

                SqlDependency dependency =

                    (SqlDependency)sender;

     

                dependency.OnChange -= OnDependencyChange;

     

                GetData();

            }

     

            SqlCommand command = null;

     

            private void GetData()

            {

                // Make sure the command object does not already have

                // a notification object associated with it.

                command.Notification = null;

     

                // Create and bind the SqlDependency object

                // to the command object.

                SqlDependency dependency = new SqlDependency(command);

     

                dependency.OnChange += new

                    OnChangeEventHandler(OnDependencyChange);

              

                command.Connection.Open();

                command.ExecuteReader();

                command.Connection.Close();

               

            }

     

    When I comment out the above three green lines in the GetData() method every thing is OK. However, these lines are necessary for the SqlDependency to work, and therefore should not be commented out.

    Please, I am impatient to learn from those genius who have the solution to my problem.

    Great regards.

    Friday, April 22, 2011 11:54 AM

Answers

  • Hi Biya-Bi,

    the query seems correct. Apparantly, the select has to comply with the conditions for a indexed view, and i've read this:

    "You must ensure that the new view meets all of the requirements of an indexed view. This may require you to change the ownership of the view and all base tables it references so they are all owned by the same user."

    Are they?

    Nico

     

    Monday, April 25, 2011 9:33 AM
  • Dear Nico,

    Here was the cause of the problem:

    I have three tables A, B and C. B has a foreign key to A, and C has a foreign key to B. When all the foreign key relationships had their Delete Rule set CASCADE, the SqlDependency worked perfectly with SELECT and UPDATE statements, but failed with the DELETE Statement. Deletion from table A was impossible from either my application or SQL Management studio. Please refer to earlier replies to see the errors that were thrown. But as soon as I set the Delete Rule between B and C to NONE, everything was ok. I tested it many times and realized that the CASCADE Rule was the cause of the issue.

    My aim now is to understand why the CASCADE Rule caused the errors. However, it has been very beneficial to me to exchange ideas with you. I was very encouraged because you reacted as soon as I posted my problem. I will be very glad to exchange with you again in future. Thanks very much.

    Best regards.

    • Marked as answer by Biya-Bi Wednesday, May 4, 2011 11:53 AM
    Wednesday, May 4, 2011 11:53 AM

All replies

  • Hi Biya-Bi,

    i may not be a genius, but it does not not take a genius to wonder how and when your command instance is created and initialized.

    I suspect that the command refers (or should refer) to the table being monitored, no?

    regards,

    nico

    Friday, April 22, 2011 2:10 PM
  • Hi Nico,

    Thanks very much for your spontaneous response. You are right. The command containing the DELETE statement refers to the table been monitored. Please, continue Nico. Do you mean that it is not possible to delete from a table being monitored? If yes, how can I handle this case? In practice, a user may want to delete data from a table been monitored by a SqlDependecy.

    Regards.

     


    Friday, April 22, 2011 2:37 PM
  • Biya-Bi,

    what i meant was that i can't see the code for the command. what is in it, and where  and how is it initialized?

    SqlCommand command = null;

    Nico

    Friday, April 22, 2011 2:46 PM
  • Dear Nico,

     

    Here are the details you asked. The DependencyString property is a protected propterty so that sub classes can initialize the command used in the SqlDependency. By initializing this property the implicitely call GetData() for the first time. Please review the content of the GetData() method above.

        

    Kind regards

     

     protected string DependencyString

            {

                get { return _dependencyString; }

                set

                {

                    _dependencyString = value;

     

                    command = new SqlCommand(DependencyString, new SqlConnection(GetConnectionString()));

     

                    GetData();

                }

            }

     

            private static string GetConnectionString()

            {

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                builder.ConnectionString = SqlDependencyProject.Properties.Settings.Default.OptimumConnectionString;

                builder.Pooling = false;

                return builder.ToString();

            }

    Friday, April 22, 2011 2:57 PM
  • What is in the SQL? do you reference a base table?
    Friday, April 22, 2011 3:18 PM
  • Nico, here is the SQL. AccountTable is a base table.

    DELETE FROM AccountTable WHERE AccountID=@AccountID

    Friday, April 22, 2011 3:25 PM
  • Did you comply with the requirements described in

    http://msdn.microsoft.com/en-us/library/aewzkxxh(VS.80).aspx ?

    I see that you re-use the same command; that's a bad practice; create a new one every time you need one

    The same goes for your connection; both implement  IDisposable, so you should dispose them.

    Use the 'using' statement, it guearantees that they are disposed.

     

    Friday, April 22, 2011 3:27 PM
  • you mean that you use this query in the SQLDependency?

    AFAIK you can only use Select statements in an SQLDepencency.

    Use a normal command and connection to perform your deletes.

    regards,

    Nico

    Friday, April 22, 2011 3:30 PM
  • Ok Nico,

    Thanks for your advise. Let me bring the adjustments concerning the command and connection objects. I will give you the results in some minutes. Concerning requirements, I think to have complied. I have read them over and over. But I think the UPDATE statements wouldn’t have been reflected in the application if I did not comply to the requirements. Please, let me remind you that only the DELETE statement brings troubles.

    Thanks.

     

    Friday, April 22, 2011 3:38 PM
  • Nico,

    I am only using the DELETE statement to check how the dependency behaves after I delete a row from the base table. An example of the CommandText of the command used in the SqlDependency is SELECT CustomerID, CustomerName FROM dbo.AccountTable,dbo.CustomerAccountTable WHERE AccountID=CustomerID

     Thanks.

    Friday, April 22, 2011 3:43 PM
  • Nico,

     

    DELETE  statement doesnot still work with the following code. I have disposed the SqlConnection and SqlCommand objects (in the GetData()) method as you asked.

     

    Regards

     

    protected string DependencyString

            {

                get { return _dependencyString; }

                set

                {

                    _dependencyString = value;

     

                    GetData();

                }

            }

     

    private void GetData()

            {

                using (SqlConnection connection = new SqlConnection(GetConnectionString()))

                {

                    using (SqlCommand command = new SqlCommand(_dependencyString, connection))

                    {

                        // Make sure the command object does not already have

                        // a notification object associated with it.

                        command.Notification = null;

     

                        // Create and bind the SqlDependency object

                        // to the command object.

                        SqlDependency dependency = new SqlDependency(command);

     

                        dependency.OnChange += new

                            OnChangeEventHandler(OnDependencyChange);

     

                        command.Connection.Open();

                        command.ExecuteReader();

                        command.Connection.Close();

     

     

                    }

                }

            }


    Friday, April 22, 2011 4:01 PM
  • this is indeed a difficult one; i've read that something similar occurred in SQL Server 2005 sp2 when using non-ansi joins, so one never knnows:

    try this as query:

    CustomerID, CustomerName FROM dbo.AccountTable join dbo.CustomerAccountTable on  AccountID=CustomerID

    I propose this thread should be moved to the SQL SERVER forums

    regards

    Nico

    Friday, April 22, 2011 5:19 PM
  • Dear Nico,

    I tried the query

    SELECT CustomerID, Name FROM dbo.AccountTable join dbo.CustomerAccountTable on  AccountID=CustomerID

    first with JOIN and second with INNER JOIN but the problem persisted. I started a thread on this on a SQL Server forum http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ce5c11c9-4a67-481f-b1dc-a98aecdd5557 since Friday 15th April 2011 but I had no solution. That is why I tried this forum.

    Yesterday night, I discovered that there were no errors when I used either of the tables in the query. It even worked with the query

    SELECT CustomerID, CustomerSince FROM dbo.CustomerAccountTable;SELECT AccountID, Name FROM dbo.AccountTable

    Notice that the above query consists of two sub queries separated with a semi colon.

    Till this point, I understood that the problems I experienced were due to the JOIN statement. I followed my investigations by implementing the Watcher Application described at http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.90).aspx . Surprisingly enough, the query they used in the dependency uses INNER JOIN statements. Under the same conditions as my application, I experienced no problem with the Watcher Application, despite the INNER JOINS in the SqlDependency statement. Here is the statement:

    SELECT Production.Product.ProductID, Production.Product.Name, Production.Location.Name AS Location, Production.ProductInventory.Quantity FROM Production.Product INNER JOIN Production.ProductInventory ON Production.Product.ProductID Production.ProductInventory.ProductID INNER JOIN Production.Location ON Production.ProductInventory.LocationID Production.Location.LocationID WHERE ( Production.ProductInventory.Quantity <= @Quantity ) ORDER BY Production.ProductInventory.Quantity, Production.Product.Name

    The only difference with my application is that the Watcher Application uses the Adventures database Compatibility level: SQL Server 2005 (90) whereas my application uses a database Compatibility level: SQL Server 2008 (100).  I am using SQL Express 2008.

    I then changed the compatibility level of my database to SQL Server 2005 (90) , but still experienced the same problem. This is where I am currently.

    Kind Regards.

    Saturday, April 23, 2011 10:36 AM
  • Biya-Bi

    did you try the select with full qualified column names? that also seems to be a difference, and i've seen it as a requirement in some other cases.

    Regards,

    Nico

    Saturday, April 23, 2011 11:13 AM
  • Nico,

    I tried the full qualified column names but the problems persist.

    Thanks

    Saturday, April 23, 2011 3:23 PM
  • Biya-Bi,

    can you show what you used as query?

    nico

    Saturday, April 23, 2011 4:25 PM
  • Nico,

    Here is the query:

    SELECT dbo.AccountTable.AccountID, dbo.AccountTable.Name,dbo.CustomerAccountTable.CustomerSince FROM dbo.AccountTable INNER JOIN dbo.CustomerAccountTable ON dbo.AccountTable.AccountID=dbo.CustomerAccountTable.CustomerID

    Monday, April 25, 2011 7:40 AM
  • Hi Biya-Bi,

    the query seems correct. Apparantly, the select has to comply with the conditions for a indexed view, and i've read this:

    "You must ensure that the new view meets all of the requirements of an indexed view. This may require you to change the ownership of the view and all base tables it references so they are all owned by the same user."

    Are they?

    Nico

     

    Monday, April 25, 2011 9:33 AM
  • Hi Nico,

    I am not using views. I execute the above query directly in either SQL management studio or by assigning the CommandText property of SqlCommand object. If I misunderstood you, please clarify me.

    Regards

     


     
    Monday, April 25, 2011 9:58 AM
  • Biya-Bi,;

    yes you misunderstood me, i said the select has to comply with the conditions for an indexed view. Are all tables owned by the same user?

    Monday, April 25, 2011 5:01 PM
  • Hi Nico,

    Sorry for the delay in my response. We experienced no Internet availability in my region for one and a half day.

    By executing  exec sp_help I verified that all the tables are owned by the same user (dbo).

     

    Thanks

    Wednesday, April 27, 2011 1:22 PM
  • hi biya-bi,

    i was wondering if any of those tables has triggers. If so, could you try it with the triggers disabled?


    Regards, Nico
    Wednesday, April 27, 2011 6:22 PM
  • Hi Nico,

    When I started experiencing this issue I had no triggers on my tables. Today, I disabled the triggers I added later, but still does not work

    Thanks


    Thursday, April 28, 2011 8:39 AM
  • Are you sure there are no relations with one of the tables that would prohibit deleting records, e.g. a table that acts as child table to one of these two?
    Regards, Nico
    Thursday, April 28, 2011 8:52 AM
  • There are only referential integrities of foreign keys among my tables. These behave perfectly when my application is not started. In  SQL Management  Studio I can delete or experience integrity exceptions when my application is not started . This is the normal behavior.  I expect  at least errors on foreign k constraints on trying to delete rows while my application is running. This is not the case. Instead, as soon as I start the application and the try to delete rows from SQL Management studio, I get the following in SQL management  Studio:

    Msg 0, Level 11, State 0, Line 0

    A severe error occurred on the current command.  The results, if any, should be discarded.

    Msg 0, Level 20, State 0, Line 0

    A severe error occurred on the current command.  The results, if any, should be discarded.

    Thanks

    Thursday, April 28, 2011 11:13 AM
  • do you have create rule or sp_bindrule in your tables' script?

    see this: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/08e9e4e6-8c3a-4234-a516-e2e3368f1ea3/


    Regards, Nico
    Thursday, April 28, 2011 12:17 PM
  • another possibility: do you have an  index defined on dbo.AccountTable.AccountID and dbo.CustomerAccountTable.CustomerID? if not, create one and try again.
    Regards, Nico
    Thursday, April 28, 2011 12:36 PM
  • Ok, Nico give me some time to create an index and try again
    Thursday, April 28, 2011 12:52 PM
  • Dear Nico,

    Even with indexes the problem persists

    Regards

    Thursday, April 28, 2011 1:16 PM
  • I propose you make a script of you db with only the offending elements, and try to reproduce the errors in new db. If you can reproduce the error, post the script to the SQL SERVER forum.

     


    Regards, Nico
    Thursday, April 28, 2011 1:26 PM
  • Dear Nico,

    Here was the cause of the problem:

    I have three tables A, B and C. B has a foreign key to A, and C has a foreign key to B. When all the foreign key relationships had their Delete Rule set CASCADE, the SqlDependency worked perfectly with SELECT and UPDATE statements, but failed with the DELETE Statement. Deletion from table A was impossible from either my application or SQL Management studio. Please refer to earlier replies to see the errors that were thrown. But as soon as I set the Delete Rule between B and C to NONE, everything was ok. I tested it many times and realized that the CASCADE Rule was the cause of the issue.

    My aim now is to understand why the CASCADE Rule caused the errors. However, it has been very beneficial to me to exchange ideas with you. I was very encouraged because you reacted as soon as I posted my problem. I will be very glad to exchange with you again in future. Thanks very much.

    Best regards.

    • Marked as answer by Biya-Bi Wednesday, May 4, 2011 11:53 AM
    Wednesday, May 4, 2011 11:53 AM