none
Sqlce 4.0 sp1 - Divide by zero exception when deleting

    Question

  • We have a c# service connecting to sqlce 4.0 sp1 database.

    When a process logs onto the service (via wcf), a record is created or updated in an 'Activity' table. This occurs every 5 seconds for each process enrolled.

    When a process closes, it logs off the service and its record is deleted.

    In another thread, expired records are deleted about every 5 seconds.

    Everything is fine for about 5 days. After that, divide by zero exceptions occur in the login (most of the processes run continually) and expiry procedures.

    Using VS 2010, we can query the table concerned without problems:

    SELECT        ActivityID, ApplicationName, MachineID, ProcessID, IPAddress, Active, LastRequestTime, LastRequest, NextPingTime
    FROM            Activity
    WHERE        (DATEDIFF(second, LastRequestTime, GETDATE()) > 2 * DATEDIFF(second, LastRequestTime, NextPingTime))

    However, attempting to delete causes a divide by zero exception.

    DELETE FROM Activity
    WHERE        (DATEDIFF(second, LastRequestTime, GETDATE()) > 2 * DATEDIFF(second, LastRequestTime, NextPingTime))

    Any ideas anyone?

    trelliot

    Monday, November 18, 2013 12:49 AM

Answers

  • Sorry, I monitor many SQL forums and forgot this was a SQL CE question.

    Baring a fix for this apparent bug, you could try deleting in multiple smaller batches instead of the entire table at once.  One way implement this workaround to do this is by adding a LastRequestTime datetime range to the WHERE clause and execute DELETE in a loop.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, November 20, 2013 12:38 PM

All replies

  • However, attempting to delete causes a divide by zero exception.

    DELETE FROM Activity
    WHERE        (DATEDIFF(second, LastRequestTime, GETDATE()) > 2 * DATEDIFF(second, LastRequestTime, NextPingTime))

    Any ideas anyone?

    I don't see an obvious reason how this could cause the divide by zero exception.  Are there perhaps any DELETE triggers on the table?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, November 18, 2013 1:10 AM
  • There are no triggers in the database - there was a similar thread on another forum where deletion of a large number of rows caused a div by 0 exception as well (sqlce 4.0).

    Remember, this error doesn't occur until days after starting the service and recreating the database after the error appears makes it go away.

    Monday, November 18, 2013 1:33 AM
  • Sorry, I monitor many SQL forums and forgot this was a SQL CE question.

    Baring a fix for this apparent bug, you could try deleting in multiple smaller batches instead of the entire table at once.  One way implement this workaround to do this is by adding a LastRequestTime datetime range to the WHERE clause and execute DELETE in a loop.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, November 20, 2013 12:38 PM