Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI"

Question

We recently started getting the following error while executing a stored procedure:

System.Data.SqlClient.SqlException (0x80131904): **Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation**.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal

When checking online and observing stored procedure we found that in the stored procedure developer was creating #Temp tables and that the issue could be resolved by creating the #Temp table with correct database default collation.

We would like to know how this collation got changed as the stored procedure was working properly earlier and we received this sort of error for the first time. We continuously keep getting windows/security patch updates for the servers; could these updates modify the collation property of the database?

CREATE table  #temp( Code varchar(5) COLLATE DATABASE_DEFAULT null, Amount decimal(11,2) null )
INSERT into #temp select PT.Code,(PT.Percentage/100*(@PAmount+@ICharge+@FCharges+(select CC from @temp where [IN] = @MyVar))) from PTax PT inner join tax T on PT.Code=T.Code where Code = @strCode and PId=@PId AND T.Flag =@CFlag
INSERT INTO #temp SELECT T.Code,0 from Tax T where Flag =@CFlag AND T.Code not in (select Code from #temp)  
drop table #tempTaxes

@Solomon Rutzky Output to 1st Query:

0   <Instance>      SQL_Latin1_General_CP1_CI_AS
1   master          SQL_Latin1_General_CP1_CI_AS
2   tempdb          SQL_Latin1_General_CP1_CI_AS
3   model           SQL_Latin1_General_CP1_CI_AS
4   msdb            SQL_Latin1_General_CP1_CI_AS
6   CustomerDB      Latin1_General_CI_AI

2nd Query output:

Latin1_General_CI_AI

3rd Query Output:

Tax 2017-09-15 01:46:39.217 2017-09-15 01:46:39.217

Any suggestions are welcome.

Answer 1

Let's see about the possibilities. The first one is that server's collation has changed. This is very unlikely, as it requires rebuilding system databases. Unless you have extremely dynamic environment with multiple admins doing stuff in chaotic manner, I wouldn't consider this further.

First and one half: is it possible that the database has been moved to another instance? If you are using client-side aliases, the change can be transparent.

The second one is that database's collation has changed. This is quite straight-forward, all one needs is an alter database command. So, check the database's current collation and expected collation. Do they match?

The third one is that the stored procedure itself has been modified or renamed. Maybe there used to be explicit collation specification, but that was dropped. Do you have sprocs in version control?

To see the last modify dates for stored procedures, query sys.procedures

SELECT create_date, modify_date, name
FROM sys.procedures;

As far as I know, without auditing it's hard to catch database changes after the fact. If you are using some kind of CI system, double-check if the database - or sprocs - were re-deployed recently.

It would be worth a shot to chat with other server admins and developers too.

Answer 2

It is highly unlikely that the Instance-level Collation was changed. That would require a full outage and would be something that someone would notice.

While it is also highly unlikely that the Database-level Collation has changed, it is also irrelevant since that Collation does not factor into this operation, assuming that the COLLATE DATABASE_DEFAULT found in your CREATE table #temp statement was just added as a fix for this error. I assume this from your statement:

When checking online ... we found that ... the issue could be resolved by creating the #Temp table with correct database default collation.

What does factor into this operation (prior to adding the COLLATE DATABASE_DEFAULT) is:

1. The Collation of tempdb, which should be the same as the Instance-level Collation, and
2. The Collation of the string column being compared to the temp tables string column: Tax.Code

This is because the error is happening here:

AND T.Code not in (select Code from #temp)

My guess is that if the stored procedure has not been changed recently (i.e. if COLLATE DATABASE_DEFAULT was there originally and was removed), then it is possible that the Code column in the Tax table was updated, either for the datatype or to set NULL / NOT NULL, and the COLLATE keyword was not specified, and the original Collation of that column did match the Collation of tempdb. I am guessing this because if you execute something like this:

ALTER TABLE [Tax] ALTER COLUMN [Code] VARCHAR(10) NOT NULL;

then the Collation of that column will be changed to the default Collation of the Database if it had been different.

So, to help narrow this down, please update the question with the results of the following three queries (you will need to be in the Database containing the Tax table when executing them):

SELECT sd.[database_id], sd.[name], sd.[collation_name]
FROM   sys.databases sd
WHERE  sd.[database_id] IN (1, 2, 3, 4, DB_ID())
UNION ALL
SELECT 0 AS [database_id], '<Instance>' AS [name], SERVERPROPERTY('collation')
ORDER BY [database_id];

and:

SELECT col.[collation_name]
FROM   sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'Tax')
AND    col.[name] = N'Code';

and:

SELECT tab.[name], tab.[create_date], tab.[modify_date]
FROM   sys.tables tab
WHERE  tab.[name] = N'Tax';