CRM 4.0 Organization Import Failure - SQL Connection State Closed

• Question

• 

  

  0

  Sign in to vote

  Hi,

  I am trying to import an organization into a CRM 4.0 deployment. The organization is 30Gb+ with over 1,400 users.

  The import is from an existing org in the same deployment; basically creating a UAT environment from the existing Live org.

  This has been failing for the last few days; always with the same issue and always at the point when it starts to update users: System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

  I have trawled through the various forums and blogs and tried most/all of the various solutions posted:

  • Registry settings all checked and verified: oledbtimeout, extended timeout, optimise import, max ports, etc.
  • .Net 2 system.data.dll version - the version on site for .Net 2 X64 = 2.0.50727.4927.
  • Deleted entries in the reports tables.
  • Restarted the sql server.
  • Reduced the size of the database.
  • Checked the setupuser attribute in systemuser.
  • Checked the deployment users role and rights in the organisation.

  
  I have run sql profile traces during the import process and can verify that both the config and new organization databases are being accessed.
  I can see that it runs a select query against the systemuserorganisation table for each of the users.
  This table contains no data for the new organisation; so it's obviously unable to insert into this table.

  The profiler then shows that after running the user selects it then runs:

  1. exec sp_executesql N'UPDATE OrganizationFeatureMap SET IsDeleted = 1 , UniqueifierId = newid()  WHERE (((OrganizationId = @OrganizationId0)) ) AND (IsDeleted = 0)',N'@OrganizationId0 uniqueidentifier',@OrganizationId0='EC646BF4-77CD-E011-BDF8-02BFAC169E71'
  2. exec sp_executesql N'INSERT INTO Notification(Id, OrganizationId, CreatedOn, EventData, EventId) VALUES(newid(), @OrganizationId, getutcdate(), @EventData, @EventId)',N'@OrganizationId uniqueidentifier,@EventData nvarchar(39),@EventId int',@OrganizationId='00000000-0000-0000-0000-000000000000',@EventData=N'CrmConfigDb:Full:OrganizationFeatureMap',@EventId=24
  3. exec sp_executesql N'UPDATE OrganizationMaintenanceJobs SET IsDeleted = 1  WHERE (((OrganizationId = @OrganizationId0)) ) AND (IsDeleted = 0)',N'@OrganizationId0 uniqueidentifier',@OrganizationId0='EC646BF4-77CD-E011-BDF8-02BFAC169E71'
  4. exec sp_executesql N'INSERT INTO Notification(Id, OrganizationId, CreatedOn, EventData, EventId) VALUES(newid(), @OrganizationId, getutcdate(), @EventData, @EventId)',N'@OrganizationId uniqueidentifier,@EventData nvarchar(44),@EventId int',@OrganizationId='00000000-0000-0000-0000-000000000000',@EventData=N'CrmConfigDb:Full:OrganizationMaintenanceJobs',@EventId=24
  5. exec sp_executesql N'UPDATE SystemUserOrganizations SET IsDeleted = 1 , UniqueifierId = newid()  WHERE (((OrganizationId = @OrganizationId0)) ) AND (IsDeleted = 0)',N'@OrganizationId0 uniqueidentifier',@OrganizationId0='EC646BF4-77CD-E011-BDF8-02BFAC169E71'
  6. exec sp_executesql N'INSERT INTO Notification(Id, OrganizationId, CreatedOn, EventData, EventId) VALUES(newid(), @OrganizationId, getutcdate(), @EventData, @EventId)',N'@OrganizationId uniqueidentifier,@EventData nvarchar(40),@EventId int',@OrganizationId='00000000-0000-0000-0000-000000000000',@EventData=N'CrmConfigDb:Full:SystemUserOrganizations',@EventId=24

  It then marks the new orgnaization to be deleted:

  1. exec sp_executesql N'UPDATE Organization SET IsDeleted = 1 , UniqueifierId = newid()  WHERE (((Id = @Id0)) ) AND (IsDeleted = 0)',N'@Id0 uniqueidentifier',@Id0='EC646BF4-77CD-E011-BDF8-02BFAC169E71'

  This is the confusing thing, if the SQL connection is closed how can it still be reading and writing to both the config and organizational database?

  I think that the deployment may be the issue here; the original deployment was:

  • 3 load balanced web servers
  • 1 server for both database and reporting services

  The database was then moved to a different server; so the config is now:

  • 3 load balanced web server
  • 1 server for database
  • 1 server for reporting services

  In this deployment configuration all connection string details in both the registry and the mscrm_config database are correct.

  However, in the crm deployment manager and in the server table in mscrm_config there are still only the original 4 servers listed.
  It has no entry for the new db server. And the original dual role db + srs is still listed as being dual role.
  This is the only thing that I have left to blame for this issue; even though the connection string details are correct for each organization in organization table in mscrm_config.

  At this point any ideas or solutions are gratefully welcomed.

  Regards,
  Kieran

  Wednesday, August 24, 2011 10:40 AM