Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. An error occurs when importing an organization in Microsoft Dynamics CRM 4.0 “ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.”

An error occurs when importing an organization in Microsoft Dynamics CRM 4.0 “ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.”

View products that this article applies to.

Symptoms

When importing a new organization using the CRM Deployment Manager, this may fail with exception: 

“ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.”

Open Windows Explorer, navigate to the folder %appdata% \Microsoft\MSCRM\Logs and open the file crm40dmsnapin.log.

At the end of the file the following entry is displayed:

Error| Import Organization (Name=Test, Id=e1d62b8d-443f-e011-9b34-005056bd363e) failed with Exception:
System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

↑ Back to the top

Cause

Importing organizations can be a long time consuming process. Each CRM user has to be remapped to an existing Active Directory user account and depending on configuration and number of users, the whole import process might fail due to a SQL timeout error.

↑ Back to the top

Resolution

Important: This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756 How to back up and restore the registry in Windows

First of all install the latest CRM Update Rollup. The latest Update Rollup contains several fixes regarding performance improvements during the import process of CRM organizations. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

949256Microsoft Dynamics CRM 4.0 updates and hotfixes

If installation of the latest Update Rollup does not help, follow the below step by step instructions:

01. Map users manually
Try to only map a few users during the Import process and then manually map each user after the Import has been completed. Make sure that at least the Setup user will be mapped.

02. Create OleDBTimeout Key on CRM Server
      - Click Start, click Run, type regedit, and then click OK
      - Go to the subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
      - Right-click MSCRM, point to New, click DWORD Value, and then type OleDBTimeout
      - Double click OleDBTimeout and enter 86400 decimal

The value represents 86400 seconds, which is equal to 24 hours. The default is 30 seconds. Values higher than 300 may long running queries cause excessive SQL blocking.

03. Create ExtendedTimeout Key on CRM Server
      - Click Start, click Run, type regedit, and then click OK
      - Go to the subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
      - Right-click MSCRM, point to New, click DWORD Value, and then type ExtendedTimeout
      - Double click ExtendedTimeout and enter 86400000 decimal

The value represents 86400000 milliseconds, which is equal to 24 hours. The default is 900000 milliseconds, which is equal to 900 seconds. Maximum value is 2147483647 decimal or 0x7FFFFFFF hexadecimal.

04. Create OptimizeOrgImport Key on CRM Server
      - Click Start, click Run, type regedit, and then click OK
      - Go to the subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
      - Right-click MSCRM, point to New, click DWORD Value, and then type OptimizeOrgImport
      - Double click OptimizeOrgImport and enter 1 decimal

Starting with Rollup 8, CRM does contain this hotfix, but it needs to be enabled manually. You cannot import the same organization database more than one time, when using this registry key. To disable the hotfix, set the value to 0.

This can also be done using the FixIt below:

977867The Import Organization Wizard takes a long time to import an organization in Microsoft Dynamics CRM 4.0

05. Create MaxUserPort Key on CRM Server and SQL Server
      - Click Start, click Run, type regedit, and then click OK
      - Go to the subkey: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters
      - Right-click Parameters, point to New, click DWORD Value, and then type MaxUserPort
      - Double click MaxUserPort and enter 65534 decimal

This value controls the available number of dynamic ports. The valid range for this value is from 5000 to 65534. By default, the number of available ports is 3977, because the first available port is port number 1024.

06. Create TcpTimedWaitDelay Key on CRM Server and SQL Server

      - Click Start, click Run, type regedit, and then click OK
      - Go to the subkey: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters
      - Right-click Parameters, point to New, click DWORD Value, and then type TCPTimedWaitDelay
      - Double click TCPTimedWaitDelay and enter 30 decimal

This value determines the length of time that a connection stays in the TIME_WAIT state when it is closed. During TIME_WAIT state, the socket pair cannot be re-used. Typically, this value is two times the maximum segment life time on the network (known as “2MSL” state). Valid range is 30 to 300.

07. Restart the CRM and SQL Servers

When the database could be imported successfully, delete the previous created registry keys afterwards.

↑ Back to the top

More Information

13:03:33| Error| Import Organization (Name=Test, Id=e1d62b8d-443f-e011-9b34-005056bd363e) failed with Exception:
System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
at Microsoft.Crm.CrmDbConnection.SetTransactionIsolationLevel(IsolationLevel il)
at Microsoft.Crm.CrmDbConnection.Dispose(Boolean disposing)
at Microsoft.Crm.SqlExecutionContext.Dispose(Boolean disposing)
at Microsoft.Crm.SqlExecutionContext.Dispose()
at Microsoft.Crm.Tools.Admin.ImportOrganizationInstaller.Import(Guid organizationId, String organizationUniqueName, String organizationFriendlyName, String sqlServerName, String databaseName, Uri reportServerUrl, String privilegedUserGroupName, String sqlAccessGroupName, String userGroupName, String reportingGroupName, String privilegedReportingGroupName, ICollection`1 users, MultipleTenancy multipleTenancy)

2003564 Microsoft Dynamics CRM 4.0 - Importing Organization fails with "The connection's state is closed"

977867 The Import Organization Wizard takes a long time to import an organization in Microsoft Dynamics CRM 4.0

↑ Back to the top

Keywords: kbmbspartner, kbmbsmigrate, kbsurveynew, kb

↑ Back to the top

Article Info
Article ID : 2528160
Revision : 1
Created on : 1/7/2017
Published on : 5/4/2012
Exists online : False
Views : 397