Change SQL Server 2019 instance Collation After installation, and new problem

[emerson]

How to made this problem on SQL Server 2019

1. Install SQL Server with wrong collation 2month ago
2. Revived a report from SharePoint admin about has a problem with collation
3. 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.

[Trenton]

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 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 _AS vs _AI — which suggests that the initial collation was SQL_Latin1_General_CP1_CI_AS or that multiple runs of sqlservr.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 of sqlservr -q).

The cause of this problem can be one of the following:

1. Restore a database onto a server that has a different instance-level collation than the database being restored, or
2. 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 ran sqlservr.exe -q to see which one it was, or you might need to simply run sqlservr.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 of sqlservr.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/