Change SQL Server 2019 instance Collation After installation, and new problem
-
How to made this problem on SQL Server 2019
- Install SQL Server with wrong collation 2month ago
- Revived a report from SharePoint admin about has a problem with collation
- Resolve problem with MSSQLTIPs resolve solution (Use Option # 2) https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/
- CMD Run as admin
- Find SQL Server Binn dir
Run this :
sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"SQL_Latin1_General_CP1_CI_AI"
Result of running :
All database (System and new) worked well, and collations is changed
We use SQL Server enterprise edition on a company
But, when create new database, collation of database is correctly set
Latin
when tried to open table, from object explorer raised this error, and did not appear anything :ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Persian_100_CI_AI_SC_UTF8" in UNION ALL operator occurring in SELECT statement column 2. Could not use view or function sys.all_columns because of binding errors. (Microsoft SQL Server, Error: 451)
- Reinstall SQL Server is my treble solution, I need solution repair without reinstall.
I tried to resolve with change model database collation.
I have this problem for open users properties, in security>logins>sa right click properties and raise error 'Cannot show request dialog' I don't have this problem on database scope.
-
The problem is a collation mismatch between the instance-level collation (i.e. "server" collation) and the database-level collation of the database you are using. Your instance-level collation is
Persian_100_CI_AI_SC_UTF8
and the database-level collation for your system databases isSQL_Latin1_General_CP1_CI_AS
(well, at least according to the error message; according to the screenshot of SSMS, your system databases are usingSQL_Latin1_General_CP1_CI_AI
— only difference is the_AS
vs_AI
— which suggests that the initial collation wasSQL_Latin1_General_CP1_CI_AS
or that multiple runs ofsqlservr.exe -q
were executed, or possibly that the database generating the error is both not in the screenshot list and was restored after the execution ofsqlservr -q
).The cause of this problem can be one of the following:
- Restore a database onto a server that has a different instance-level collation than the database being restored, or
- Run undocumented
sqlservr.exe -q
to change all collations but it fails while converting a database. You need to check the log messages that were generated when you ransqlservr.exe -q
to see which one it was, or you might need to simply runsqlservr.exe -q
again to see. The final step of the process is to change the instance-level collation, so if the instance-level collation does not match the collation specified in the-q
option ofsqlservr.exe
, then it did not complete successfully.
Please see my blog post about the
sqlservr.exe -q
approach for more details on various things that can go wrong when using it:https://sqlquantumleap.com/2018/06/11/changing-the-collation-of-the-instance-and-all-columns-across-all-user-databases-what-could-possibly-go-wrong/