Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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