Problem with SQLServerCE

Discussion in 'microsoft.public.sqlserver.ce' started by Zowie, Jul 3, 2009.

  1. Zowie

    Zowie Guest

    Hi.

    I'm facing a problem and I ask if anybody can help me solving it.
    I developed an application in C# (compact framework 2), running on a
    Windows CE 5 industrial pc, that uses SqlServer CE to connect to a
    local DBF file. The Database is acessed from several processes and
    each process uses a different connection. The method Open() is called
    everytime needed, and after use the Close() method is called. These
    connections objects are maintained in a Static Class.
    I have the application installed in several PCs and in some of them,
    after a long time, although I get no error or exception, the dabase
    "freezes", that is, the inserts/updates/deletes don't change the DBF
    file. The strange thing is that, even after a reboot to the PC, it
    keeps like that, the commands are executed correctely (the number of
    rows affected returned is correct, etc) but nothing changes on the
    Database. The only solution that I could find, that worked, was to
    copy the application folder to a new folder on the same location in
    the disk, changed that new folder name to match the old one and, after
    restart, the Database file starts to get updated again.

    I guess it could be related to some Operating system problem? Any
    Ideas?

    Thanks in advance,
     
    Zowie, Jul 3, 2009
    #1
    1. Advertisements

  2. How do you know the data in the database didn't change? In other words,
    you've told us a bit about how you are communicating with the database when
    everything works, but little about the actual failure mode. What code is it
    that's making a change that you say isn't getting to the database?

    Also, we'll want to know what version of SQL Server Compact is on the
    devices.

    Paul T.
     
    Paul G. Tobey [eMVP], Jul 6, 2009
    #2
    1. Advertisements

  3. Zowie

    Zowie Guest

    Hi Paul.
    Thanks for your reply.
    I had put this matter a little apart, as it happens rarely, and only getting
    back to it now.

    I'm using SQL Mobile 3 on the devices.
    Whe in failure mode, the inserts and updates don't work but no error is
    given. For example, the following code executes completely, the transaction
    commits, the "id" value I get is the value on the DB, no exception is thrown,
    but the only record in table "TransactionID" (has only 1 record) remains
    unchanged (the column "id" isn't incremented as it is normally).

    SqlCeConnection connection = null;
    SqlCeTransaction transaction = null;

    try
    {
    connection = GetConnection(process);
    connection.Open();
    transaction = connection.BeginTransaction();
    SqlCeCommand command = new SqlCeCommand("SELECT id from
    TransactionID;", connection);
    command.CommandType = System.Data.CommandType.Text;
    command.Transaction = transaction;
    SqlCeDataReader dataReader =
    command.ExecuteReader(System.Data.CommandBehavior.Default);
    dataReader.Read();
    long id = dataReader.GetInt64(0);
    dataReader.Close();
    id++;
    SqlCeCommand command2 = new
    SqlCeCommand(String.Format("Update TransactionID SET id = {0};", id),
    connection);
    command2.CommandType = System.Data.CommandType.Text;
    command2.Transaction = transaction;
    int rowAffected = command2.ExecuteNonQuery();
    if (rowAffected <= 0)
    throw new ApplicationException("Erro ao actualizar ID da
    transacção na BD!");

    transaction.Commit();
    transaction.Dispose();
    transaction = null;

    command.Dispose();
    command = null;
    command2.Dispose();
    command2 = null;
    dataReader.Dispose();
    dataReader = null;
    return id;
    }
    catch (Exception ex)
    {
    transaction.Rollback();
    }
    finally
    {
    if (connection != null)
    connection.Close();
    }

    Even if it isn't within a transaction, the result is the same, for instance
    the following code executes with no problem but, when in failure mode, the
    record with id=0 remains unchanged:

    SqlCeConnection conn = GetConnection(process);
    try
    {
    conn.Open();
    //Prepare command
    SqlCeCommand command = new
    SqlCeCommand(String.Format("Update ActivityRecords set lastCommunication =
    '{0}' where ID = 0 ",

    lastCommunication.ToString("yyyy-MM-dd HH:mm"), conn);
    command.CommandType = System.Data.CommandType.Text;

    res = command.ExecuteNonQuery();

    command.Dispose();
    command = null;
    }
    catch (Exception ex)
    {

    throw;
    }
    finally
    {
    conn.Close();
    }
     
    Zowie, Aug 19, 2009
    #3
  4. If you are using VS for development, and there is a automatic deployment
    happening; then the database attached to the project might be getting
    deployed and overwriting the new and updated database file on the device.
    So, you might perceive that as the changes are getting lost. You might want
    to turn off 'Copy' of database file.

    Thanks,
    Laxmi
     
    Laxmi Narsimha Rao Oruganti [MSFT], Sep 1, 2009
    #4
  5. Zowie

    Zowie Guest

    Thank you Laxmi, for you attention.
    That isn't the case.
    No automatic deployment is happening, for the application is already
    installed and running in production.

    But thanks anyway.

    Best regards,
     
    Zowie, Oct 30, 2009
    #5
  6. Hey Zowie,

    There are thousands of applications running on SQL CE, and I believe we
    are missing something here than a bug. Can you give us more details here to
    get to the problem?

    Thanks,
    Laxmi
     
    Laxmi Narsimha Rao Oruganti [MSFT], Oct 30, 2009
    #6
  7. Zowie

    Zowie Guest

    Hy Laxmi.

    Unfortunately i can't provide more information other than that in the
    previous messages...
    It's a strange problem, indeed, and i believe it has to do (somehow...) with
    the filesystem, but it happened only a few times and I can't reproduce it.

    The fact is, all the operations done against the database work fine for some
    time and, for no apparent reason, in some cases it stops working. For
    instance: the "Update TransactionID SET id = 12345" command returns 1 row
    affected but the value in the DB keeps as it was before... Every single
    update/insert/delete commands are executed (no errors or exceptions raised)
    but the changes aren't reflected in the Database -- It's like the file was
    "frozen".

    Thanks,

    Zowie
     
    Zowie, Oct 30, 2009
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.