:::: MENU ::::

Posts Categorized / SQL Server Collations

  • Aug 26 / 2014
  • 0
Data Modeling and Database Design, dbDigger, SQL Server Collations

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Today one of our development team member reported following error on newly restored database in staging environment.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Error was generated in a stored procedure. First of all i found that collation of subject database is different than SQL_Latin1_General_CP1_CI_AS. Using this method i changed the collation of subject database to required SQL_Latin1_General_CP1_CI_AS.
I was hopeful that changing collation of database will solve the issue but it was not the case. I opened the stored procedure code and got the tables being used in it.
Suppose we have table1, table2 and table3 in USP. Now we have to get all those columns where collation is different than our required collation. Following script did task and returned the columns with different collation.

SELECT object_name(object_id) as TableName,   
 name as columnName, collation_name  
 FROM sys.columns  
 WHERE OBJECT_ID IN (SELECT OBJECT_ID  
 FROM sys.objects where name in ('table1','table2','table3'))  
 and collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
GO

Once we have the list of columns then we can change their collation to match with required collation. This can be done with following script.

ALTER TABLE table1 ALTER COLUMN ColumnNameHere nvarchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
GO
ALTER TABLE table2 ALTER COLUMN ColumnNameHere varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
GO
ALTER TABLE table3 ALTER COLUMN ColumnNameHere varchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
GO

This time i found that issue was resolved and there was no error.

  • Jul 20 / 2009
  • 0
Data Modeling and Database Design, dbDigger, SQL Server Collations, T-SQL Interview Questions

Change Collation of a databse through T-SQL

In a previous post i discussed that how to get list of SQL Server collations. Now we have to see that how to change collation o a database.
Before completion of this task make sure that you have no column value that depends upon collation settings.
After that get name of required collation that you are going to implement. List of collations can be retrieved from our previous article. In this example we will change collation of database AdventureWorks to ‘SQL_Latin1_General_CP1_CI_AS’

 
Alter Database AdventureWorks  
 Collate SQL_Latin1_General_CP1_CI_AS  
 GO  

After that a success message will appear and collation is now changed.

  • Mar 21 / 2009
  • 4
Data Modeling and Database Design, dbDigger, SQL Server Collations, System Functions

Get list of all SQL Server collations

Various collations are available in all versions of SQL Server. According to BOL A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages). SQL Server collation may effect sort order for unicode, non-unicode data types and code page used for storing non-unicode character data types. In order to get list of all available collations of SQL Server use following script

 
SELECT *  
 FROM ::fn_helpcollations()  
 GO  

In case of SQL Server 2005 and onwards function prefix :: may be removed. Hence script for collation list in SQL Server 2005 and onwards will be

SELECT *  
 FROM fn_helpcollations()  
 GO  
  • Mar 21 / 2009
  • 0
dbDigger, SQL Server Collations

Invalid collation ‘%.*ls.

The error message

Msg 448, Level 16, State 3, Line 14
Invalid collation ‘%.*ls’.

is a run time error message and may be generated at all versions of SQL Server. The reason for this error is use of unknown collation in a command. To remove the error specified collation name may be corrected first. Collations control the physical storage of character strings in SQL Server. Separate collations can be specified down to the column level in a table, and each column can be assigned different collations. To get list of available collations in SQL Server use following script

 
USE DBNamehere  
 GO  
 SELECT *  
 FROM ::fn_helpcollations()  
 GO  

In case of SQL Server 2005 and onwards full colons may be removed from prefix of functions. Syntax for list of all collations in SQL Server 2005 and onwards will be

 
USE DBNamehere  
 GO  
 SELECT *  
 FROM fn_helpcollations()  
 GO  
Consult us to explore the Databases. Contact us