locked
Microsoft Sync Framework - there is insufficient system memory in resource pool 'internal' to run this query RRS feed

  • Question

  • I have tried to synchronize data between SQL server and Sql Compact database.

    When I tried to synchronize data with large size I got error: there is insufficient system memory in resource pool 'internal' to run this query.

    My source code is given here:

     // create a connection to the Sql Server database
                SqlConnection serverConn = new SqlConnection(AppConfig.ConnectionStringSe);
    
             
                // create a connection to the SyncCompactDB database
                SqlCeConnection clientSqlConn = new SqlCeConnection(AppConfig.ConnectionString);
             
                // string scope = "filetered_tempalte";
    
                //create scope for server tables   
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filter");
                //DbSyncTableDescription templateDescription =
                //   SqlSyncDescriptionBuilder.GetDescriptionForTable("tblBusinessLetter", serverConn);
                //scopeDesc.Tables.Add(templateDescription);
    
                DbSyncTableDescription usertemplateDescription =
                  SqlSyncDescriptionBuilder.GetDescriptionForTable("tblBusinessUserLetter", serverConn);
                scopeDesc.Tables.Add(usertemplateDescription);
                
                //DbSyncTableDescription categoryDescription =
                //   SqlSyncDescriptionBuilder.GetDescriptionForTable("tblCategory", serverConn);
                //scopeDesc.Tables.Add(categoryDescription);
    
                //DbSyncTableDescription templateCategoryDescription =
                //   SqlSyncDescriptionBuilder.GetDescriptionForTable("tblTemplateCategory", serverConn);
                //scopeDesc.Tables.Add(templateCategoryDescription);
    
                DbSyncTableDescription UsertemplateCategoryDescription =
                   SqlSyncDescriptionBuilder.GetDescriptionForTable("tblUserTemplateCategory", serverConn);
                scopeDesc.Tables.Add(UsertemplateCategoryDescription);
    
                //DbSyncTableDescription variableDescription =
                //   SqlSyncDescriptionBuilder.GetDescriptionForTable("tblVariableDetails", serverConn);
                //scopeDesc.Tables.Add(variableDescription);
    
                SqlSyncScopeDeprovisioning serverSqlDepro = new SqlSyncScopeDeprovisioning(serverConn);
                SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
                serverTemplate.ObjectSchema = "dbo";
    
                SqlSyncScopeProvisioning serverProvRetail = new SqlSyncScopeProvisioning(serverConn);
    
                SqlCeSyncScopeDeprovisioning clientSqlCeDepro = new SqlCeSyncScopeDeprovisioning(clientSqlConn);
             
    
                try
                {
    
                    //serverTemplate.Tables["tblBusinessLetter"].AddFilterColumn("bit");
                    //serverTemplate.Tables["tblBusinessLetter"].FilterClause = "[side].[bit] = @customertype";
                    //SqlParameter param = new SqlParameter("@customertype", SqlDbType.Bit, 1);
                    //serverTemplate.Tables["tblBusinessLetter"].FilterParameters.Add(param);
    
                    if (!serverTemplate.TemplateExists("filter"))
                    {
                        serverTemplate.Apply();
                       // serverSqlDepro.DeprovisionTemplate("filter");
                    }
    
                   
    
    
    
                    serverProvRetail.ObjectSchema = "dbo";
                    serverProvRetail.PopulateFromTemplate("BusinessLetters", "filter");
                    //  serverProvRetail.Tables["tblBusinessLetter"].FilterParameters["@customertype"].Value = false;
    
    
                    if (!serverProvRetail.ScopeExists("BusinessLetters"))
                    {
                        serverProvRetail.Apply();
                     
                    //    serverSqlDepro.DeprovisionScope("filter");
                    }
                   // serverProvRetail.PopulateFromScopeDescription(scopeDesc);
    
                    
                     serverProvRetail.PopulateFromScopeDescription(scopeDesc);
    
    
                    var clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("BusinessLetters", null, "dbo", serverConn);
                    var clientSqlConfig = new SqlCeSyncScopeProvisioning(clientSqlConn, clientSqlDesc);
    
                    if (!clientSqlConfig.ScopeExists("BusinessLetters"))
                    {
                        clientSqlConfig.Apply();
                        //clientSqlCeDepro.DeprovisionScope("BusinessLetters");
                    }
    
                    
    
                    SampleSyncOrchestrator syncOrchestrator;
                    SyncOperationStatistics syncStats;
    
                    // Data is downloaded from the server to the SQL Server client.
                    syncOrchestrator = new SampleSyncOrchestrator(
                        new SqlCeSyncProvider("BusinessLetters", clientSqlConn, null),
                        new SqlSyncProvider("BusinessLetters", serverConn, null, "dbo")
                        );
                    syncStats = syncOrchestrator.Synchronize();
                   
                    
    
    
                    //if (serverTemplate.TemplateExists("filter"))
                    //{
                    //    serverSqlDepro.DeprovisionTemplate("filter");
                    //}
    
                    //if (clientSqlConfig.ScopeExists("BusinessLetters"))
                    //{
                    //    clientSqlCeDepro.DeprovisionScope("BusinessLetters");
                    //}
    
                    serverConn.Close();
                    serverConn.Dispose();
    
                    clientSqlConn.Close();
                    clientSqlConn.Dispose();
    
                    return "Synchronization Completed Successfully";
                }
    
                catch(Exception ex)
                {
                    if (serverTemplate.TemplateExists("filter"))
                    {
                        serverSqlDepro.DeprovisionTemplate("filter");
                    }
    
                    if (serverProvRetail.ScopeExists("filter"))
                    {
                        serverSqlDepro.DeprovisionScope("filter");
                    }                               
    
                    serverConn.Close();
                    serverConn.Dispose();
    
                    clientSqlConn.Close();
                    clientSqlConn.Dispose();
    
                    return ex.Message;
                }
               
            }
    
    
     public class SampleSyncOrchestrator : SyncOrchestrator
        {
            public SampleSyncOrchestrator(SqlCeSyncProvider localProvider, SqlSyncProvider remoteProvider)
            {
              //  remoteProvider.MemoryDataCacheSize = 150000;
               // remoteProvider.CleanupBatchingDirectory = true;
               // remoteProvider.ApplicationTransactionSize = 20000;
                this.LocalProvider = localProvider;
                this.RemoteProvider = remoteProvider;
             
                this.Direction = SyncDirectionOrder.UploadAndDownload;
               
            }
        }

    Please help me to solve it.




    Monday, May 19, 2014 5:11 AM

All replies

  • this is more of a SQL Server rather than Sync Fx. out of curiosity, how big is the dataset that you're synching?
    Wednesday, May 21, 2014 11:36 AM