CODEWITHSUNDEEP
sqlsql-serverreporting-servicessql-server-2014
PJD
PJD

Reputation: 775

Collation Conflict in SSRS

Currently using SQL Server 2014 and having an issue with the Collation on a stored procedure. I currently have a report that runs a stored procedure, the code works fine when run in SSMS but I have noticed that while it is being run as part of a SSRS report there is a collation issue between Latin1_General_CI_AS_KS_WS and Latin1_General_CI_AS.

My database is set at Latin1_General_CI_AS as well as all my table columns so I am at a loss as to where it is pulling this Latin1_General_CI_AS_KS_WS inconsistency, can anyone offer any suggestions?

Thanks

Upvotes: 2

Views: 2890

Answers (1)

EzLo
EzLo

Reputation: 14189

Latin1_General_CI_AS_KS_WS is the default collation for SSRS.

Either you write your SQL queries with the COLLATE property on all your string comparisons or reinstall SSRS with the correct default collation.

Keep in mind that if you use ALTER DATABASE to change the SSRS database collation, it won't affect already created objects, just new ones from that point onwards.

If this is a problem with a particular column (and not the whole database), you can run this query to check all column's current collation.

SELECT 
    ColumnName = QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '.' + QUOTENAME(c.name),
    Collation = c.collation_name
FROM 
    sys.schemas s
    INNER JOIN sys.tables t ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE 
    C.collation_name is not null
ORDER BY 
    1

Then use ALTER TABLE <tablename> ALTER COLUMN <columnname> <type> COLLATE <newCollate> to change it.

Upvotes: 2

Related Questions