SQL Server CE and Memory Issue

Hello,

Have a query that consistently gives back the following message.

Not enough storage is available to complete this operation

The query I'm using is a little more complex than what I'm usually using. It uses Case, has extra characters in the formatting like (i.e. '-------'), connects 2 databases. Free memory can be as much as 3 MB on the Pocket PC before sending the query and I can still receive the above message.

Was wondering if there are any ideas on how to prevent to much memory from being used. Have tried GC, disposing/reconnecting to the database before it calls query, but doesn't help. (have read somewhere SQL Server CE is unmanaged code and GC doesn't help clear up extra space).

Thanks,

Peter

Answer this question

SQL Server CE and Memory Issue

Robert3234
Yes. I am using SQL Mobile (SQL CE 3.0)

Using the query in a program. They query I'm using has numerous Case statements as well as functions LEN, CONVERT, CHARINDEX, SPACE.

I am disposing the previous connection before connecting again with a new query.

I am using the Advanced List from http://www.resco.net/ with the query I'm having trouble with. I've contacted them about this issue; however they indicated it may be an issue with SqlServer CE execution engine.

Regards,

Peter
Kim Carlsen
I too am seeing the problem.

In fact my Pocket PC shows 36Mb Free , even more in the object store....

We had a program suit running data collection applications for a warehouse withzero crashes. Speed was an issue so we swaped to SqlCeReturn data set........ now our programs carsh after 5 cycles of input.

Whats going on chaps
ShEi
Please confirm that you are using SQL Mobile (SQL CE 3.0)

If you are using SQL Mobile, please provide query details and how you are using query - in a program, in query analyzer etc.

If you are using SQL CE 2.0 check out following KB article http://support.microsoft.com/ kbid=827837

you should free ado object after use.
Kevjs
Hi All,

The issue here was that there were multiple connections getting created to the DB.

The strongly typed SqlCeResultSet method .Close/Dispose was only setting the stResultSet.connection =null, rather then calling stResultSet.Connection.Close.

The work around was to use a shared connection in a helper class and close it explicitly.

Thanks for your help on this one Dipesh,

pdns.
TimurKA
So anyways what is the solution for this issue, cause i'm facing the same and can't solve it.

Thanks
Tamim Sadikali
Hi All,

Happening to me too although i find it very difficult to reproduce, none the less an important issue to resolve.

We are using strongly typed SqlCeResultSets w/ partial classes to enable these result sets to filter the results with sql provided to a sqlcecommand object.

Free memory is at ~2.5MB when it occurs and we are using sql ce 3.0, wm5 and .net cf 2.0

Within the partial class we have similar to the following

----

Private Sub Open(ByVal UserID as String)

' Create default options

Me.resultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable

Me.resultSetOptions = (Me.resultSetOptions Or System.Data.SqlServerCe.ResultSetOptions.Sensitive)

Me.resultSetOptions = (Me.resultSetOptions Or System.Data.SqlServerCe.ResultSetOptions.Updatable)

'Get an open connection to the db.

Me.sqlCeConnection = DBAccess.GetConnection()

Dim sqlCeSelectCommand As System.Data.SqlServerCe.SqlCeCommand = SetupCommand(UserID)

sqlCeSelectCommand.ExecuteResultSet(Me.resultSetOptions, Me)

If (sqlCeSelectCommand IsNot Nothing) Then

     sqlCeSelectCommand.Dispose()

End If

End Sub

Public Sub CloseConn()

If (Not sqlCeConnection Is Nothing) Then

     sqlCeConnection.Close()

End If

End Sub

-------

----- Here is the DBAccess.GetConnection

Public Shared Function GetConnection() As SqlCeConnection

   Dim conn As SqlCeConnection = New SqlCeConnection(connectionString)

   conn.Open()

   Return (conn)

End Function

---- Finally when using the result sets we are using it similar to the following manner.

Dim rs As New demoResultSet(False)

rs.Open(userId)

While (rs.Read())

.....

End While

If (Not rs Is Nothing) Then

     rs.CloseConn()

      rs.Dispose()

End If

The only other way we use this RS is to used to bind to the Microsoft.WindowsCe.Forms.DataGrid. We close and dispose the current resultset that is bound to the grid before brining back and binding a new resultset with different filtered results.
stombiztalker
Hi Peter,

Excuse us for the late reply.

Can you please send us a sample program for the repro.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft India
SQL Server CE and Memory Issue

Answer this question