Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.
Let us see following example.
USE AdventureWorks
GO
/* Create Test Table */
CREATEÂ TABLE TestTable (FirstCol VARCHAR(10))
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTERÂ TABLE TestTable
ALTERÂ COLUMN FirstCol VARCHAR(10)
COLLATEÂ SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Database Cleanup */
DROPÂ TABLE TestTable
GO
When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.
Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.
Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.
Reference : Pinal Dave (http://www.SQLAuthority.com)
50 Comments. Leave new
Brian/Pinal – I’m really looking forward to that post, it will be most helpful. Thank you very much!
Definitely something worthy, in the mean time Brian if possible can you email me a copy of the script to, urgently needed
Hello again.
Apologies for this taking as much time as it did to prepare, but I now have all of the documentation ready. Note, this is not a click-bang-done task. It’s a series of scripts that generate DDL which must be executed against the target machines. It uses DMO via OLE, so make sure your instances are not disabling the sp_oaCreate, sp_oaMethod, etc SP’s.
Unfortunately, the documentation is only from my POV, and has NOT been beta tested against the masses.
I am emailing the article to Pinal Dave along with the scripts, but I have no control over when he has the time to review and publish.
For those of you that have urgent needs… you’ve got to realize, your email address is NOT posted, nor is mine. The back-end of Pinal’s blog filters out email address in posts. No way for me to know where to send. So, if time is of the essence, be a little diligent and refer to my posts on March 2nd and September 12th. My email address is obfuscated in the CONVERT() statement. Just copy and paste it in a Q/A window.
Hi,
Is it possible to set a collation sequence on a schema so all tables within that schema are case-sensitive, whereas rest of database is case-insensitive?
Cheers
Gordon
Hi
please i got a problem, i got 2 apps MS Dynamics CRM and MS Dynamics GP
but CRM is in Modern_Spanish_CI_AI
and GP in SQL_Latin1_General_CP1_CI_AS
i want to do a query like this
SELECT * FROM TABLE_IN_GP WHERE ID NOT IN ( SELECT ID FROM LINKEDSERVER.CRM_DB.dbo.TABLE_IN_CRM )
but i got this error
Cannot resolve the collation conflict between “Modern_Spanish_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
i even put at the end COLLATE SQL_Latin1_General_CP1_CI_AS
but i still got the same error
it’s not posible to change the collations of the DBs
could you help me please
thanks a lot
FAC,
Is the COLLATE statement inside or outside the parentheses? It should be inside.
If that doesn’t fix it, try troubleshooting by minimizing your select criteria. Try just a single column from the GP table.
Hi Brian
thanks for your response
i modified the query like this
SELECT ITEMNMBR
FROM IV00101
WHERE
ITEMNMBR NOT IN ( select productnumber from CRMLINKED.CRMDB.dbo.ProductBase where productnumber COLLATE SQL_Latin1_General_CP1_CI_AS )
but still got the error
Cannot resolve the collation conflict between “Modern_Spanish_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
any other ideas please
thanks a lot
FAC:
In the above query, are the fields ITEMNMBR and productnumber of a numeric type? The COLLATE clause will only work with character data types: char, nchar, varchar, nvarchar, text, ntext
If they are string based types, try the following:
SELECT ITEMNMBR
FROM IV00101 i
LEFT JOIN CRMLINKED.CRMDB.dbo.ProductBase p
ON p.productnumber COLLATE SQL_Latin1_General_CP1_CI_AS = i.ITEMNMBR
WHERE p.productnumber IS NULL
Great! that did it
both columns are character date type
Thanks a lot Brian!
Thanks, very very useful and It Works !
Thanks Pinal
If i change collation for entire database,it changed but table level collation not effected.Please suggest me to get the collation change in all tables in that database.
I tried collation change using
‘alter database testCollationChange
collate SQL_Latin1_General_CP1_CI_AS ‘
Is anyother script?
great, thanks and thanks google for showing this on top
Hi Brain ,
Data stored in Sql Server 2005
in a table the Column Consists Data in iscii Format how
Can i Show that data in specifeid Language
The Data was inserted using CDAC iplugin the Language Used is Telugu
Waiting..;
For Reply
Hi Brain ,
Data stored in Sql Server 2005
in a table the Column Consists Data in iscii Format how
Can i Show that data in specifeid Language
The Data was inserted using CDAC iplugin the Language Used is Telugu
Waiting..;
For Reply
Data is visible in this format
AnilKumar:
Collations do not translate data from one language to another. They specify sort-order definitions. If the plugin you mention is capable of language translations, then you will need to consult its documentation.
Sorry I cannot help you further.
Nice Articles.
Thank! Good articles
I want to have all languages in my tables in a database. Currently when i insert the arabic characters, it is getting inserted as “?”. Let me know what collation to be used for all languages , for all tables and its syntax.
hi sir this is gopi
while intalling sql server 2005 i did not install sql server profiler now i want that profiler it is possible sir reply me
how to restore the database with out mdf,and log files
Hi Pinal, nice post as always. I have personally made a little script to help me understanding which columns i have to change in order to maintain the correct collation on a Database.
Here’s the script if anyone needs something like this.
NOTE: it doesn’t do nothing, but just prints the T-Sql instructions that allow to change the collation, this because based on my experience, i prefer to double check a couple of times before messing up with DB Settings :)
DECLARE @collationType VARCHAR(100)
SELECT @collationType = ‘Latin1_General_CI_AS’
DECLARE collationToChangeColumns CURSOR
FOR
SELECT colonne.NAME ,
‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + tabelle.NAME + ‘]’ ,
colonne.SYSTEM_type_id ,
colonne.max_length
FROM sys.columns colonne
JOIN sys.tables tabelle
ON colonne.OBJECT_ID = tabelle.OBJECT_ID
WHERE collation_name @collationType
AND tabelle.TYPE = ‘u’
DECLARE @tableName AS VARCHAR(100)
DECLARE @columnName AS VARCHAR(100)
DECLARE @typeID AS INT
DECLARE @maxLength AS INT
DECLARE @sqlString AS NVARCHAR(4000)
OPEN collationToChangeColumns
FETCH collationToChangeColumns INTO @columnName, @tableName, @typeID, @maxLength
WHILE @@FETCH_STATUS -1
BEGIN
IF @typeID NOT IN ( 35, 99 )
BEGIN
IF @maxLength > 0
BEGIN
SET @sqlString = ‘ALTER TABLE ‘ + @tableName
+ ‘ alter column [‘ + @columnName + ‘] ‘
+ CAST(TYPE_NAME(@typeID) AS VARCHAR(20)) + ‘(‘
+ CAST(@maxLength AS VARCHAR(10)) + ‘) collate ‘
+ @collationType + ‘;’
PRINT @sqlString
END
END
FETCH collationToChangeColumns INTO @columnName, @tableName, @typeID,
@maxLength
END
CLOSE collationToChangeColumns
DEALLOCATE collationToChangeColumns
eabidi syntax without CURSOR
DECLARE @OldCollation VARCHAR(100), @NewCollation VARCHAR(100)
SELECT @OldCollation = ‘SQL_Latin1_General_CP1_CI_AS’,
@NewCollation=’Estonian_ci_as’;
WITH XChange (XColumn,XTable,XType,XLen)
as(
SELECT c.NAME ,
‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + t.NAME + ‘]’ ,
c.SYSTEM_type_id ,
c.max_length
FROM sys.columns c
JOIN sys.tables t ON c.OBJECT_ID = t.OBJECT_ID
WHERE
collation_name=@OldCollation
AND t.TYPE = ‘u’)
SELECT ‘ALTER TABLE ‘ + XTable
+ ‘ alter column [‘ + XColumn + ‘] ‘
+ CAST(TYPE_NAME(XType) AS VARCHAR(20)) + ‘(‘
+ CAST(XLen AS VARCHAR(10)) + ‘) collate ‘
+ @NewCollation + ‘;’
FROM XChange
where XType not in (35,99)
and XLen>0;