locked
Hotfix for SE Bug 18083 : How do I fix this RRS feed

  • 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  

    Tuesday, October 25, 2011 1:00 PM

Answers

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 PM
    Moderator
  • 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
    
    

    BON
    Wednesday, 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
    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!

    Wednesday, July 11, 2012 6:38 PM
    Moderator