SQL SERVER COLLATION issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL SERVER COLLATION issue

Hi Anyone, Please note that my solution provider, had addressed an issue for what I raised an issue, and my issue is, all of a sudden my application had got halted without working, returning an error, and the error is as follows /********************************************************************************************************************/
-2147217900Cannot resolve the collation conflict between "SQL_Latin_General_CPI_CI_AS" and "Latin1_General_CI_AS" in the equal to operaion
/********************************************************************************************************************/ so this issue was raised at the provider , and the sooner they gave me an answer saying please bring the working database as per to the master database working database – SQL_Latin1_General_CP1_CI_AS
master database – Latin1_General_CI_AS Please see attached file for the error Thanks, AB
why does no one reply to me thread..
For such cases, it is almost probable some query is joining some columns from user DB with other with either Master DB or Temp DB like a table variable or temp table for examples.. Therefore , you could work out 2 approaches : · To standardize your user DB according to server collation (master DB collation ) (Recommended if no mind from APP itself hitting this user DB)
· Or to use with collatecommands like collate Latin1_General_CI_AS Or collateSQL_Latin1_General_CP1_CI_ASfor string columns only not numerical columns

Hi Shehap, can I do the option one as follows ALTER DATABASE master
COLLATE LATIN1_General_CI_AS
GO and can you elaborate on the option 2 what you have mentioned about Thanks, AB
It should be for user DB as ALTERDATABASE DBName COLLATE LATIN1_General_CI_AS.

For the 2nd option , it could be something like below

SELECT [empid]
,[empname] collate SQL_Latin1_General_CP1_CI_AS
,[salary]–Invalid to use collatte command here since it is of numeric data type
,[deptid]–Invalid to use collatte command here since it is of numeric data type
FROM [Workshops].[dbo].[Employees]

Hi Shehap, the following error occured, while trying to collate it as per to the master database Msg 5030, Level 16, State 2, Line 1 The database could not be exclusively locked to perform the operation. Msg 5072, Level 16, State 1, Line 1 ALTER DATABASE failed. The default collation of database ‘iMIS’ cannot be set to Latin1_General_CI_AS.
Thanks, Antonio
You have to set that database to single user. After that should work.
Hi Luis, Thanks for your help, even the below after setting it to multi user it still does not work ALTER DATABASE someDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE someDatabase COLLATE SQL_Latin1_General_CP437_BIN
ALTER DATABASE someDatabase SET MULTI_USER Thanks, Antonio Benildus Muerling
Hi All, Please note that I altered it trying with ‘Latin1_General_CI_AS’, the above is a template that I have showed, sorry for the inconvenience. Thanks, Antonio Benildus Muerling
For such production DBs having intensive workload of end users , it is preferred to run the below query to alter any DB feature that are looking for single user

settransactionisolationlevelserializable

ALTERDATABASE DBName SETSINGLE_USERWITHROLLBACKIMMEDIATE

ALTERDATABASE DBName COLLATE Latin1_General_CI_AS

ALTERDATABASE DBName SETMULTI_USER

Please work out it and let me know your feedback

Hi Shehap, Thanks for the help, but I have tried this, and did not work out you know, see my reply to Luis, as I have pasted the code in that reply with what I tried, pleade advice me if this a different set of code, than what I have pasted, please help me in doing this, cause I can see right on top you have mentioned saying that settransactionisolationlevelserializable is that something that I have to set up with a value or something. Thanks, AB
Hi All, Thanks a million in reading this post Is there anyone please, to help me with the collation issue that am having, it indeed would be a great help, I am holding so many things and my backlog grows, please can you help me in sorting this..
issue is the system database which is the master database has a different collation that the working database, which prevents from working together, and am not in a position to work with the application to insert records, via the front end application, so as I am unable in updating records as well.. I have tried, doing altering the database setting up the single mode and still it does not work – this is for your information Thanks, AB

My suggestion is:
1) Detach users database with new collation. (also backup).
2) Use some script to get all sql users, jobs, etc.
3) Rebuild master to set new collation. (check book on line for that).
4) Generate old users.
5) Attach or restore.
It is not recommended to change the collation of the database that is in USE. You should have been prudent while creating the database.
Given the problem,
it is difficult to change the collation of the database to Latin1_General_CI_AS? when you do so you will confront the errors like…?
ALTER DATABASE Northwind COLLATE Latin1_General_CI_AS
Msg 5075, Level 16, State 1, Line 1701
The object ‘CK_Birthdate’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation. If you are still desparate to make this change then…? Create a new empty database with the desired collation
Script out the schema/index/view/proc definition from the old database
change the collation in the scripts to the desired collation
Create the objects in the new database
Export the data from old database to a file and Import the data to the new database. Believe me this is going to be a real challenge. Good Luck.
Hi All, this is now working very well, and the measures taken were. Cause of the problem
1) initially this was working fine with one sql server instance. (
2) this was copied to sql server defautl instance, and I do not the reason. (working database and the master are the same)
which created the issue, as a result of above 2) Resolution
1) this was copied back to the original place, after checking we have enough licences.
2) have tested several tests, working fine. all maintenace plans, of the automated backups, indexing have been implemented Thanks, AB
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |