Answered by:
Hotfix for SE Bug 18083 : How do I fix this

Question
-
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.
if(exists(select InternalEMailAddress from SystemUserBase SU1 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 15 and ParentObjectId = SU1.SystemUserId) and InternalEMailAddress is not null)) if(exists(select SystemUserId from SystemUserBase SU2 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 15 and ParentObjectId = SU2.SystemUserId) and InternalEMailAddress is not null)) BEGIN Insert into EmailSearch (EmailSearchId, EmailAddress, EmailColumnNumber, ParentObjectId, ParentObjectTypeCode) values (NEWID(), (select InternalEMailAddress from SystemUserBase SU3 where not exists(Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 15 and ParentObjectId = SU3.SystemUserId) and InternalEMailAddress is not null), 15, (select SystemUserId from SystemUserBase SU4 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 15 and ParentObjectId = SU4.SystemUserId) and InternalEMailAddress is not null), 8) END -- End: Insert
- Edited by Bjorn Nornes Tuesday, October 25, 2011 1:14 PM
Tuesday, October 25, 2011 1:00 PM
Answers
-
Solved :
I manually added data for all users to table EmailSearchBase :Insert into EmailSearch (EmailSearchId, EmailAddress, EmailColumnNumber, ParentObjectId, ParentObjectTypeCode) values (NEWID(), SystemUser.InternalEMailAddress, 15, SystemUserId, 8)
The database upgrade process then went OK !
BON- Marked as answer by Jamie MileyModerator Wednesday, July 11, 2012 6:38 PM
Wednesday, October 26, 2011 11:00 AM -
I just want to note that manual Inserts are not supported.
Jamie Miley
Check out my about.me profile!
http://mileyja.blogspot.com
Linked-In Profile
Follow Me on Twitter!- Marked as answer by Jamie MileyModerator Wednesday, July 11, 2012 6:38 PM
Wednesday, July 11, 2012 6:38 PMModerator
All replies
-
I think I need more context here around why and where this occurred. What were you trying to do when this occurred? If you are manually running insert queries against the base tables as the queries above could suggest this would be a highly unsupported situation.
Jamie Miley
Check out my about.me profile!
http://mileyja.blogspot.com
Linked-In Profile
Follow Me on Twitter!Tuesday, October 25, 2011 6:26 PMModerator -
After import of CRM 4.0 organization to CRM 2011, the database needs to be updated. When trying to update the database/organization from Deployment manager this occurs.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I did a trace on SQL and the following errors:/* //--------------------------------------------------------------------------- // Hotfix history // CRM SE 18083: V5: CRM 2011: The user that signed up for CRM and created // the org cannot track emails if the primary email address is the same as that used for signing up. // 2/25/2011 abhishg //--------------------------------------------------------------------------- SE Bug 18083: CRM 2011: The user that signed up for CRM and created the org cannot track emails if the primary email address is the same as that used for signing up. This issue is because provisioning a new organization clones and updates guids for the initial user. It does not correct the rows in EmailSearch table. The problem occurs only for the initial user since he is special in the above aspect. This fix has three parts, first two are insertion query to insert "internalemailaddress" and "windowsliveid" fields respectively (becauase email serach table has seperate rows for different email fields). The third part is to remove incorrect rows from email search table. */ -- Insert query for InternalEmailAddress field if(exists(select InternalEMailAddress from SystemUserBase SU1 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 15 and ParentObjectId = SU1.SystemUserId) and InternalEMailAddress is not null)) if(exists(select SystemUserId from SystemUserBase SU2 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 15 and ParentObjectId = SU2.SystemUserId) and InternalEMailAddress is not null)) BEGIN Insert into EmailSearch (EmailSearchId, EmailAddress, EmailColumnNumber, ParentObjectId, ParentObjectTypeCode) values (NEWID(), (select InternalEMailAddress from SystemUserBase SU3 where not exists(Select 1 from EmailSearchBase where ParentObjectTypeCode = 5 and EmailColumnNumber = 15 and ParentObjectId = SU3.SystemUserId) and InternalEMailAddress is not null), 15, (select SystemUserId from SystemUserBase SU4 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 15 and ParentObjectId = SU4.SystemUserId) and InternalEMailAddress is not null), 8) END -- End: Insert -- Insert query for WindowsLiveId field if(exists(select WindowsLiveId from SystemUserBase SU5 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 109 and ParentObjectId = SU5.SystemUserId) and WindowsLiveId is not null)) if(exists(select SystemUserId from SystemUserBase SU6 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 109 and ParentObjectId = SU6.SystemUserId) and WindowsLiveId is not null)) BEGIN Insert into EmailSearch (EmailSearchId, EmailAddress, EmailColumnNumber, ParentObjectId, ParentObjectTypeCode) values (NEWID(), (select WindowsLiveId from SystemUserBase SU7 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 109 and ParentObjectId = SU7.SystemUserId) and WindowsLiveId is not null), 109, (select SystemUserId from SystemUserBase SU8 where not exists (Select 1 from EmailSearchBase where ParentObjectTypeCode = 8 and EmailColumnNumber = 109 and ParentObjectId = SU8.SystemUserId) and WindowsLiveId is not null), 8) END -- End: Insert -- Delete the incorrect rows BEGIN Delete EmailSearchBase where ParentObjectTypeCode = 8 and not exists(select 1 from SystemUser where SystemUserId = EmailSearchBase.ParentObjectId) END -- End: Delete
BONWednesday, October 26, 2011 6:22 AM -
Hi!
See my post: "Failed CRM 4.0 to 2011 migration. Hotfix for SE Bug 18083 interrupted with error". Maybe it will help you. But by the migration there is a lot of similar surprises....
Greeteings
Tomasz Stanaszek
Wednesday, October 26, 2011 6:25 AM -
Solved :
I manually added data for all users to table EmailSearchBase :Insert into EmailSearch (EmailSearchId, EmailAddress, EmailColumnNumber, ParentObjectId, ParentObjectTypeCode) values (NEWID(), SystemUser.InternalEMailAddress, 15, SystemUserId, 8)
The database upgrade process then went OK !
BON- Marked as answer by Jamie MileyModerator Wednesday, July 11, 2012 6:38 PM
Wednesday, October 26, 2011 11:00 AM -
I just want to note that manual Inserts are not supported.
Jamie Miley
Check out my about.me profile!
http://mileyja.blogspot.com
Linked-In Profile
Follow Me on Twitter!- Marked as answer by Jamie MileyModerator Wednesday, July 11, 2012 6:38 PM
Wednesday, July 11, 2012 6:38 PMModerator