Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004

    Unanswered: Cannot resolve collation conflict for equal to operation.

    Hi, I have this error when running a query:

    Cannot resolve collation conflict for equal to operation.

    but the situation is that I can run the query perfectly with one user (windows user) but using an standar user, I've got the error, as far as I know the collation feature applies to db's an objects not to users, what can I do to run this query with the standar user? both users have the same permission on the db. Below is the query attached.

    Thank you

    SELECT B.EmpSupervisorId, a.Info_ID as A_Info_ID,
    ISNULL(a.Owner_SSO_ID,'') as A_Owner_SSO_ID,
    ISNULL(a.Ref_SSO_ID,'') as A_Ref_SSO_ID,
    ISNULL(a.Prev_Owner_SSO_ID,0) as A_Prev_Owner_SSO_ID,
    ISNULL(a.MgmtTypeFlag,0) as A_MgmtTypeFlag,
    ISNULL(a.CurrentStatusID, 1) as A_CurrentStatusID,
    ISNULL(a.PrevStatusID,0) as A_PrevStatusID,
    ISNULL(C.ConTypeOption, '') as C_ConTypeOption,
    ISNULL(CAST(a.Last_Status_Update_Date AS VARCHAR),'') as A_Last_Status_Update_Date,
    ISNULL(B.sfBUID,'0') as A_Bus_Group_ID, 0 AS A_Bus_Group_Seg_ID,
    ISNULL(a.Organization,'') as A_Organization,
    ISNULL(a.ContactName,'') as A_ContactName,
    ISNULL(A.Title, '') as A_ContactTitle,
    ISNULL(a.ContactComName,'') as A_ContactComName, ISNULL(a.Phone,'') as A_Phone,
    ISNULL(a.NatureOfOppID,'') as A_NatureOfOppID, ISNULL(a.DealAmount,0) as A_DealAmount, ISNULL(CAST(a.Cust_Contacted_Date AS VARCHAR),'') A_Cust_Contacted_Date, ISNULL(CAST(a.Lead_Qualified_Date AS VARCHAR),'') A_Lead_Qualified_Date, ISNULL(CAST(a.Tran_Processed_Date AS VARCHAR),'') A_Tran_Processed_Date,
    ISNULL(CAST(a.Quote_Accepted_Date AS VARCHAR),'') as A_Quote_Accepted_Date,
    ISNULL(CAST(a.Approved_By_HFS_Date AS VARCHAR),'') as A_Approved_By_HFS_Date,
    ISNULL(CAST(a.Funded_By_HFS_Date AS VARCHAR),'') as A_Funded_By_HFS_Date,
    ISNULL(a.Lead_In_Amount,0) as A_Lead_In_Amount,
    ISNULL(a.Cust_Contacted_Amount,0) as A_Cust_Contacted_Amount,
    ISNULL(a.Lead_Qualified_Amount,0) as A_Lead_Qualified_Amount,
    ISNULL(a.Tran_Processed_Amount,0) as A_Tran_Processed_Amount,
    ISNULL(a.Quote_Accepted_Amount,0) as A_Quote_Accepted_Amount,
    ISNULL(a.Approved_By_HFS_Amount,0) as A_Approved_By_HFS_Amount,
    ISNULL(a.Funded_By_HFS_Amount,0) as A_Funded_By_HFS_Amount,
    ISNULL(CAST(CreationDate AS VARCHAR),'') as A_CreationDate,
    ISNULL(a.BusType,'') as A_BusType,
    ISNULL(a.NonHFS_XLink_ContactName,'') as A_NonHFS_XLink_ContactName,
    ISNULL(a.NonHFS_XLink_Bus_ID,'0') as A_NonHFS_XLink_Bus_ID,
    ISNULL(a.Comments,'') as A_Comments, ISNULL(a.ExistCustomerID,'') as A_ExistCustomerID,
    ISNULL(a.FinancialNeedID,'') as A_FinancialNeedID,
    ISNULL(a.CampaignID,'') as A_CampaignID,
    (GEC_HFS_CORE.dbo.GetBusinessDays(Last_Status_Upda te_Date, getdate())-1) as BusDateDiff, ISNULL(A.DealTypeID, '') as A_DealTypeID
    FROM tblInformation a
    LEFT OUTER JOIN tblContactType C ON a.Bustype = C.ConType_ID
    WHERE a.CurrentStatusID NOT IN (9, 10, 11) AND
    ISNULL(a.isNoChange,'NO') = 'NO' AND ISNULL(a.IsDeleted,'NO') = 'NO' AND
    DATEDIFF(DAY, Last_Status_Update_Date, GETDATE())<> 0 AND
    ISNULL(a.isNonHFS, 'NO') = 'NO'
    ORDER BY A_Info_ID

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Add COLLATE DATABASE_DEFAULT to character-based comparison operations and forget it.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2004
    It is woking now, tks. Any idea why it is necessary to specify this on one account and not with the other? I wuold like to have something to say to the users.


  4. #4
    Join Date
    Jul 2003
    San Antonio, TX
    Here's what I think (it may be too far fetched, but I've seen it):

    One of the tables has a twin brother under a different schema, which happens to be the standard login. That twin brother somehow was built using different collation. When that user logs on and runs the query (AND since you do not qualify the tables by schema) the twin brother is picked, thus, - collation errors.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts