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.

  • Can you post the SQL Code ? – Pathum Anjana Aug 2 at 5:32
  • Please don't add code in comments, those are hard to read. Edit the question and add data there. – vonPryz Aug 2 at 6:38
  • Added brief piece of code to question. It is requested to focus on what changed the collation property of our database. – Deepak Semwal Aug 2 at 6:46
  • you wouldnt have happened to have switched to contained databases, would you have? – swasheck Aug 2 at 21:03
  • @swasheck That is not likely. Switching the DB to be Contained would have the same affect as using COLLATE DATABASE_DEFAULT in the CREATE TABLE #temp statement, and so adding that to the column definition would not have made a difference. – Solomon Rutzky Aug 2 at 21:12

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.

  • SQL server is having single instance so first and second half also does not makes sense. We have not seen any update to sp's from long back, and have discussed with other server admins too neither they modified/updated (alter database commands)anything over the server. Few times back one SQL update (KB3072779) of SQL Server 2012 was applied on the server's. Is it possible that same update made some changes, same issue pointed here: link – Deepak Semwal Aug 2 at 8:13
  • >>>Few times back one SQL update (KB3072779) of SQL Server 2012 was applied<<< This could nod affect any collation. Please check modify_date of your proc using the code posted above, do not discuss it but just execute the code – sepupic Aug 2 at 11:27
  • Have already executed and checked that. Nothing been updated recently for the sp's. Shared link above states the same issue another user faced with his database, that is why just wanted to be sure. [link](dba.stackexchange.com/questions/155197/… – Deepak Semwal Aug 2 at 11:30

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';
  • Thanks for the reply. Added output of queries to question itself. – Deepak Semwal Aug 3 at 7:21
  • Well, the column hasn't been updated, and it's Collation matches the Database's Collation. Do you have clustering or availability groups or something along those lines set up?. If so, is it possible that the Instance failed over to another one that was set up with a different Collation? I really don't see a patch updating the Collation of the Instance and the 4 system DBs. And the easiest way to Change the Collation of everything would have also changed the Collation of the Customer DB and the Tax.Code Column. – Solomon Rutzky Aug 3 at 8:45
  • Hello Solomon, yes the DB is in cluster, and your suggestion are relating to situation. Post today's business hour we performed failover to another node but that too returned the same results. – Deepak Semwal Aug 3 at 14:15
  • Rather than guess, do you have a backup from just prior to the error first happening that you can restore onto a test instance to check? You would restore just master and CustomerDB. Then run those 3 queries again while in the restored CustomerDB. – Solomon Rutzky Aug 3 at 19:56
  • @DeepakSemwal Hi there. Are you (or were you) able to get a backup that you can restore to check the Collations of the DBs and column prior to the encountering the error? Since the table hasn't been updated, it is looking like the DB was moved to another instance or another node became active. How many nodes are in your cluster? So far you have tested 2. Are there more? – Solomon Rutzky Aug 7 at 18:08

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.