Cannot Resolve the Collation Conflict.

Question

Cannot Resolve the Collation Conflict.

After fresh installation of  SQL Server 2008  R2 default  instance , (full feature ), we received the following error message while using SQL Server Management Studio:


Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation. (Microsoft SQL Server, Error:468)

Answer 1

You probably have  SELECT statement that JOIN between tables on columns that have different collation, so to resolve it

SELECT <columns> FROM t1 JOIN t2 ON t1.col=t2.col COLLATE database default --or (Latin1_General_CI_AS_KS_WS)

---

Best Regards,Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://sqlblog.com/blogs/uri_dimant/

Answer 2

No

This is happening  when opening sql server management studio

Answer 3

Use COLLATE DATABASE_DEFAULT:

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Answer 4

Hmm, never seen it before, can you show a print screen here?

---

Best Regards,Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://sqlblog.com/blogs/uri_dimant/

Answer 5

Answer 6

You are using T-SQL statement taht contains UNION, see above, can you post that statement here?

---

Best Regards,Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://sqlblog.com/blogs/uri_dimant/

Answer 7

HI

Check this

select name, collation_name from sys.databases

-------------------

Alter DATABASE KLPD
COLLATE SQL_Latin1_General_CP1_CI_AS

Answer 8

I cannot reproduce the  error , i think when  i was checking the Database properties or login properties  the error pop ups .

Now i cant see the error

Answer 9

A similar issue and solution is discussed in the following link:

Cannot Resolve the Collation Conflict.

---

Krishnakumar S

Answer 10

Well you could change:

1. Server collation or
2. Database collation or
3. Use Collate in you query with union statement. This is the best way.

Answer 11

The options 1 and 2 are very complicated and not always done successful.  For option 2 is needed to create an empty database with correct collation and move all objects along with data over there.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and optimization

Answer 12

If it is while retriving data from tables, try following:

Create tables with "COLLATE DATABASE_DEFAULT" for fields.

The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.

http://msdn.microsoft.com/en-us/library/aa258237(v=sql.80).aspx

---

Trilok Negi

Answer 13

If this happens when you start SSMS, that I would suspect you can different collations between the system databases (which I believe isn't supported), so this is what I would check.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 14

Thank you for all , I can't find the error (Actually i cant recall when  and where it happened ),At least  the error logged somewhere ?

Answer 15

No, that type of error will not be logged by the database engine. And since the client is SSMS, you won't find it logged there either...

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 16

Hmm,try turning on SQL Profiler and see what is going on ....

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and optimization