Sql-server – Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI”

collationsql serversql-server-2012stored-procedures

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.

Best Answer

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.

Related Question