Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Collation error when trying to union two views

    Hi folks,

    Can someone help me with a stored procedure I'm creating?
    I'm trying to union two views together, but get the following error:

    Msg 457, Level 16, State 1, Procedure usp_Staff, Line 16
    Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
    I've tried adding a CAST to one of the field names and then collating, but I'm not sure which field in which view the error is referring to.

    View 1:
    Code:
    SELECT DISTINCT TOP (100) PERCENT AccountCode, Surname + N', ' + PreferredName AS StaffMember
    FROM         MONDAS.Reaseheath.dbo.ExDescription AS ExDescription
    ORDER BY StaffMember
    View 2:
    Code:
    SELECT     TOP (100) PERCENT AccountCode, Surname + N', ' + PreferredName AS StaffMember
    FROM         dbo.NonStaffExDescription
    ORDER BY StaffMember
    View 1 uses a linked server to retrieve data, with other views I have used COLLATE Latin1_General_CI_AI to retrieve the data from this linked server.
    So I used this in the stored procedure, but I still get the same error.

    Stored Procedure:
    Code:
    -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Procedure (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- This block of comments will not be included in
    -- the definition of the procedure.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    CREATE PROCEDURE usp_Staff
        -- Add the parameters for the stored procedure here
        @Name varchar(Max) = ''
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT DISTINCT AccountCode, CAST(StaffMember AS varchar(Max)) COLLATE Latin1_General_CI_AI
        FROM dbo.MondasStaff
        WHERE (StaffMember LIKE N'%' + @Name + N'%')
        UNION ALL
        SELECT DISTINCT AccountCode, StaffMember
        FROM dbo.NonMondasStaff
        WHERE (StaffMember LIKE N'%' + @Name + N'%')
    END
    GO
    The collation on View 2 is 'Latin1_General_CI_AS'.
    <- Hides behind a rock.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try explicitly collating StaffMember in the second part of your UNION query just like the collation was set in the first clause: CAST(StaffMember AS varchar(Max)) COLLATE Latin1_General_CI_AI
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Use DATABASE_DEFAULT for your COLLATE clause on both fields.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You need to use the COLLATE clause. Compare the collations of the two columns and decide which you want for the UNION.

    Also remove the "TOP (100) PERCENT" and "ORDER BY" from your views. The TOP and ORDER BY achieve nothing useful but they do cause a great deal of confusion.

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Thanks,

    I also had to do the CAST COLLATE on the AccountCode in the first view as well.
    <- Hides behind a rock.

Posting Permissions

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