none
SQL Server and Analysis Services Collation RRS feed

  • Question

  • Hi

    By default when I install SQL Server it uses a SQL collation (SQL_Latin1_General_CP1_CI_AS) however; when you install Analysis Services it uses a windows collation (Latin1_General_AS).

    Do you know of any issues if we accept the defaults for both when AS processes.

    Thanks

    Steve

    Wednesday, June 14, 2006 2:10 PM

Answers

  • You should be fine with accepting defaults for majority of the cases.

    Just to correct; The default Analysis Services collation is Latin1_General_CI_AS ( and not Latin1_General_AS)

    The two collations; SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS are little bit different in sorting strings in some corner cases.

    Yes, ideally you would like to have all the SQL Server columns to have the same collation as attirbutes in Analysis Services.
    The difference comes to play in some corner cases like;

    Select Top 10 * from customer order by lname

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights. 

    Thursday, June 15, 2006 6:57 AM

All replies

  • You should be fine with accepting defaults for majority of the cases.

    Just to correct; The default Analysis Services collation is Latin1_General_CI_AS ( and not Latin1_General_AS)

    The two collations; SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS are little bit different in sorting strings in some corner cases.

    Yes, ideally you would like to have all the SQL Server columns to have the same collation as attirbutes in Analysis Services.
    The difference comes to play in some corner cases like;

    Select Top 10 * from customer order by lname

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights. 

    Thursday, June 15, 2006 6:57 AM
  • argh! I fear "corner cases", especially when they're collation related. I don't want any suprises resulting from this. How do I set the Analysis Services collation to "SQL_Latin1_General_CP1_CI_AS"? I can't select it from the drop down menu, it only offerst "Latin1_General" and "Latin_general_100".
    Monday, April 12, 2010 11:49 AM
  • Hi AltControl,

    (Please ask every question in separate posts.)

    Analysis Services only supports Windows collations. SQL_Latin1_General_CP1_CI_AS (and others which start with "SQL_") is an SQL collation, used by the database engine.

    If you would like to have aligned collations, the only option you have is to use a Windows collation in the underlying database. But it's something which have to be decided at an early point of design.

    Regards,
    Zoli


    -- Zoltán Horváth
    -- MCITP SQL Server Business Intelligence Developer 2005, 2008
    -- MCITP Database Developer 2008, Database Administrator 2008
    -- Please mark posts as answered or helpful where appropriate.
    Monday, February 6, 2012 11:05 AM
  • Hi Zoltan,

    We plan have sql server and ssas on the same virtual server. We plan to have collation Latin1_General_CI_AS for both sql server and ssas. 

    please advice as if you are in my shoes.

    Thanks,

    Brahma


    ------------------------ Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql

    Friday, May 24, 2013 4:29 PM
  • Funny - today I started dealing with the same situation: DB Engine and SSAS on the same virtual box.

    (Yeah, I know, we don't virtualize SQL Server. But it's just a dev box :))

    Tuesday, December 8, 2015 10:07 AM