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

    Unanswered: UNION on two views (Collation issue)

    Hi folks,

    I'm having issues trying to create a union using two Views I created.

    View 1:
    Code:
    SELECT     dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, 
                          ExDescription.Surname + '. ' + ExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year, 
                          dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMember
    FROM         MONDAS.Reaseheath.dbo.ExDescription AS ExDescription INNER JOIN
                          dbo.Development INNER JOIN
                          dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id ON 
                          ExDescription.AccountCode = dbo.Development.Related_Staff_Id COLLATE Latin1_General_CI_AI
    View 2:
    Code:
    SELECT     dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, 
                          dbo.NonStaffExDescription.Surname + ', ' + dbo.NonStaffExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, 
                          dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMember
    FROM         dbo.Development INNER JOIN
                          dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id INNER JOIN
                          dbo.NonStaffExDescription ON dbo.Development.Related_Staff_Id = dbo.NonStaffExDescription.AccountCode
    In the first view I am linking to another server, which is why I have had to change the collation, but the second view all the tables are in the same database hence the collation is the same.
    But when I'm trying to do a UNION between the two I get another collation error, but can't figure out how to change the collation like I did in the first view.

    Heres the UNION query:
    Code:
    SELECT     Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMember
    FROM         dbo.DevView1
    UNION
    SELECT     Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMember
    FROM         dbo.DevView2
    Can anyone help?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Interesting problem. Try UNION ALL and see if that makes a difference.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    I have just got it working.

    Turns out I was putting the collation on the wrong field, it wasn't until I added collation on all the string fields, did I find the correct field.
    I was adding it to the Related_Staff_Id field, when I should have put it on the StaffMember field, which is concatenated from two fields in the other database.
    <- Hides behind a rock.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I hate it when servers get built with other than out of the box collation
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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