SQL SERVER – How to troubleshoot collation conflict errors when executing queries

In MS SQL SERVER, the collation can be set at the column level. When you compare (or concatenate) 2 differents collation column in the query, this error occurs.

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

How to know the collation for a specific column name?

Type this query :

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SC.collation AS "Collation" 
FROM dbo.sysobjects SO 
INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U'  and SC.NAME = 'ColumnName'
ORDER BY SO.[name], SC.colid 

Solution 1: Fix the query

Sample 1 :

SELECT T1.*, T2.*
FROM Table1 T1, Table2 T2
WHERE T1.Col1 = T2.Col2

Sample 1 fixed :

SELECT T1.*, T2.*
FROM Table1 T1, Table2 T2
WHERE T1.Col1 COLLATE DATABASE_DEFAULT = T2.Col2 COLLATE DATABASE_DEFAULT

Sample 2 :

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 = T2.Col2

Sample 2 fixed :

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 COLLATE DATABASE_DEFAULT = T2.Col2 COLLATE DATABASE_DEFAULT

Sample 3 :

SELECT T1.Name + T2.Name 
FROM Table1 T1 
INNER JOIN Table2 T2 ON T1.Id = T2.Id

Sample 3 fixed :

SELECT T1.Name COLLATE DATABASE_DEFAULT + T2.Name COLLATE DATABASE_DEFAULT
FROM Table1 T1 
INNER JOIN Table2 T2 ON T1.Id = T2.Id 

As you can see, COLLATIONS can affect a query, in a concatenation in the SELECT clause, in a comparison in WHERE clause, and JOIN clause.

Solution 2: Fix the collation on the column

Query :

ALTER TABLE T1
ALTER COLUMN Col1 COLLATE SQL_Latin1_General_CP1_CI_AS

(OR DATABASE_DEFAULT for example)

The goal is to harmonize COLLATIONS in this solution.

How to read/write files on a network drive?

As an application developer, it is often seen reading / writing files locally or on a server for a web application.
However, in keeping the architecture already in place, sometimes, for safety reasons, that reading directories / write are on another machine, thus it is necessary to create network drives mapped on a particular machine (the machine that executes your program), this article will describe how.

Before accessing to your file in the network drive write this code :

 Process cred = new Process();

 cred.StartInfo.FileName = "net";
 cred.StartInfo.Arguments = @"use \\computername\sharename password /user:username \PERSISTENT:YES";
 cred.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
 cred.Start();
 cred.WaitForExit();

I recommand to use \PERSISTENT:YES , the network connection will be persistent, and won’t be closed after the next logon.

Now you should be able to create files, write (and read) on the network drive, like this :

using (System.IO.FileStream fs = System.IO.File.Create(@"\\computername\sharename\myfile.txt"))
{

}