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
Latinwhen 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_UTF8and the database-level collation for your system databases is
SQL_Latin1_General_CP1_CI_AS(well, at least according to the error message; according to the screenshot of SSMS, your system databases are using
SQL_Latin1_General_CP1_CI_AI— only difference is the
_AI— which suggests that the initial collation was
SQL_Latin1_General_CP1_CI_ASor that multiple runs of
sqlservr.exe -qwere executed, or possibly that the database generating the error is both not in the screenshot list and was restored after the execution of
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 -qto change all collations but it fails while converting a database. You need to check the log messages that were generated when you ran
sqlservr.exe -qto see which one it was, or you might need to simply run
sqlservr.exe -qagain 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
sqlservr.exe, then it did not complete successfully.
Please see my blog post about the
sqlservr.exe -qapproach for more details on various things that can go wrong when using it: