locked
Changing collations in SSRS RRS feed

  • Question

  • In our company, our databases use the US default collation, SQL_Latin1_General_CP1_CI_AS. The only exception being the databases that host our report servers, which use Latin1_General_CI_AS_KS_WS. I am told by our DBAs that SSRS was installed with the default settings, so I would conclude that this is the default for SSRS.

    While troubleshooting one report, I have noticed that the difference in collation is causing differences in sorting. Here is a sample of the results returned when a stored procedure is executed in SQL Server Management Studio:

    COMPLETE REQUESTED SCHEDULE
    COMPLETE REQUESTED SCHEDULE (2)
    COMPLETE REQUESTED SCHEDULE (3)
    COMPLETE REQUESTED SCHEDULE - OTHER
    COMPLETE REQUESTED SCHEDULE - THERAPY
    COMPLETE REQUESTED SCHEDULE FOR WEEK 1

    Now here is the same set when the stored procedure is ordered in SSRS:

    COMPLETE REQUESTED SCHEDULE
    COMPLETE REQUESTED SCHEDULE - OTHER
    COMPLETE REQUESTED SCHEDULE - THERAPY
    COMPLETE REQUESTED SCHEDULE (2)
    COMPLETE REQUESTED SCHEDULE (3)
    COMPLETE REQUESTED SCHEDULE FOR WEEK 1

    I have tried setting the kana-sensitivity and width sensitivity options to False, both in the dataset and the data field for the RDL file. I have also attempted changing the DataSet collation to Latin1_General and Latin1_General_100. Neither have changed the collation back to the way it is in SQL.

    Is there anything that can be done to fix this, other than changing the collation for the report server databases?
    Thursday, January 7, 2010 2:40 PM

Answers