Sunday, May 25, 2014

Import Organization Failed: Subquery returned more than 1 value

While trying to import a CRM 2011 organization to CRM 2013 I encountered the following error:

Viewing the log detail unfortunately file did not shed much more light on this.

The only thing I had to go on was the SQL error itself. This indicated that somewhere the system was trying to execute a nested SQL statement that looked something like the following:

select field1 from table1 where field2 = (select field3 from table2)

And that the results from table2 returned more than a single row which is not allowed as the nested relationship is using the "=" rather than "in" operator.

Armed with this information, I decided to run a SQL trace while recreating the import error. And then performed a bit of a needle-in-haystack search for something that might fit the above - using correlating times from the SQL Trace and CRM trace file (although in retrospect I think I could have searched the SQL trace for a statement like 'update organization set set state=...' as this is what happens directly after the failed error).

Lo and behold, I came across the query below which matched the kind of query pattern I was looking for. The suspect had been identified...

Running the essence of the above query in SQL it came back with the following - looks like a smoking gun to me!

In the interest of moving forward as quickly as possible, I decided to run a query that eliminate this particular error by ensuring a singleton output.

update Role set RoleTemplateId = null
where RoleId != 'BDD45E0B-7482-DC11-AB36-005056BC02DA'
and RoleTemplateId='A4BE89FF-7C35-4D69-9900-999C3F603E6F'

Re-running the import again subsequently succeeded... and yet another successful CRM 2013 upgrade under the belt.


  1. I had the excact same error. Seems to be a Problem with RU2 (maybe in Combination with SQL2008). I was able to Import the same DB to a CRM2013 UR1 deployment with RU1 without any problems. As soon as I was migrating to CRM2013 UR2 with SQL2008 I had the same issue. Many thanks, the workaround worked fine for me

  2. Brilliant - just saved my bacon - crm4 > crm2011 > crm2013 (crash as above - now good) now to crm2015 - javascript all rewritten - needed the data