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()))
BEGIN

(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.

crmquery

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!