Upgrade from CRM 2011 to 2013 fails with “Subquery returned more than 1 value. Not Permitted CRM 4.0″

We recently stumbled upon an issue when upgrading a customer from CRM 4.0 to CRM 2013. As is required, you must first upgrade the 4.0 database to 2011 and then from 2011 to 2013. The 4.0 to 2011 upgrade succeeded without error but threw an error when going from 2011 to 2013. 2013 was on update rollup 2. The error thrown was:

Error| Installer Complete: OrganizationUpgrader – Error encountered
14:56:47| Error| Exception occured during Microsoft.Crm.Tools.Admin.OrganizationUpgrader: Error.ActionFailed Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction
InnerException: System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

We upgraded the database from 4.0 to 2011 one afternoon and then tried going from 2011 to 2013 the following morning. As it turns out, CRM 2013 contains a query that checks to see if an organization is a new organization or an upgrade by checking the CreatedOn field of the system solution. It does this by seeing if the CreatedOn date/time is within one day (24 hours) of the current time. If the CreatedOn date of the System Solution is within 24 hours, it is considered a new organization. Here is the query:

IF EXISTS (SELECT * FROM SolutionBase WHERE SolutionId=’FD140AAD-4DF4-11DD-BD17-0019B9312238′
AND CreatedOn >= DATEADD(day, -1, GETUTCDATE()))

(This query goes on to Update prvAppendToOpportunity depth to global on sales person role and update prvAppendToQuote, prvAppendToOrder, prvAppendToInvoice to local on sales person role.)

When the 4.0 database is upgraded to 2011, it sets the CreatedOn date/time to whatever the UTC date/time is of that upgrade. So when going from 2011 to 2013 it sees the new date/time resulting from the upgrade to 2011 and considers it a new organization and errors out on the above query. Below is the query that can be run to check the CreatedOn date of your 2011 database.


The easy solution to this is to just wait 24 hours between the upgrade stages but if you are in a time crunch or minimizing down time for a go-live, you can update the CreatedOn date/time of the system solution directly in the 2011 database. Remember that the time is in UTC, changing the date back one day should be easier than trying to figure out the time difference in your time zone. However, keep in mind that updating fields directly through SQL is not a change supported by Microsoft so this is done at your own risk! Not sure if this issue is isolated to just Update Rollup 2 or if this occurs with 2013 RTM, Update Rollup 1, or Service Pack 1 yet.

Thanks to my young CRM Jedi Gage Pennisi for putting the details together!

Using CRM 4 with Required Client Side Certificates?

We could not find a document or knowledge base article as to where a CRM 4.0 Customer had installed and was using client side certificates with the REQUIRED flag checked.

It turns out that while you can setup IIS to accept client side certificates selecting the REQUIRED option will break CRM functionality:

  • Configuring the Outlook Client
  • Load Data function in the Email Router Configuration Wizard
  • Async Service Processing System Jobs
  • Registering Plugs.

This has been confirmed by Microsoft Support as is not specific to CRM but rather to applications dependent on calling webservices. The only option is to not set the REQUIRED flag, which defeats the purpose of required certificates.


Migrating CRM 4 Online Customizations to On Premise

Some customers have decided to go back to hosting their own CRM servers to allow for better backoffice integration and troubleshooting. When receiving the CRM Online 4.0 DB from Micrsoft make sure you identity which version of SQL server you have. Also, make sure your new CRM server is at CRM rollup 20.

You will want to update the systemuserbase and remove the “windowsliveid” data from all users. This will cause issues with mapping and turning on older disabled users.

Additionally, you might have to create a temporary domain user without CRM access, then add that user ID to the record in CRM first, save, then update again with the new domain\AD account associated with that user. In most cases, the users will have existing AD accounts that can be used to map over the records.