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

Question

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) Change default collation behavior after installation with this link

• 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 : enter image description here enter image description here enter image description here

All database (System and new) worked well, and collations is changed

enter image description here 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)

enter image description here

• 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.

• AlwaysLearning: Seems like you skipped the step to change the collation of the system databases using sqlservr -q from your linked article. New user databases are copied from the model database, so if it were still using the previous collation then new user databases will continue to use the previous collation.
• Amirhossein: @Alwayslearning thanks you for comment, I used -q and system data bases collation changed. Like article... Check by sys.databases all system database is changed. But when, check server property, collation is not changed
• Amirhossein: @AlwaysLearning Add picture of result on question
• Solomon Rutzky: @AlwaysLearning There actually isn't a separate step for user DBs. The sqlservr.exe -q option is a single execution that changes the system DBs, then the user DBs, then finally the instance itself. If any of the DBs encounter an error, then the process stops and the instance-level collation isn't changed. Please see my answer below for details.
• Solomon Rutzky: @Amirhossein Rather than posting multiple screen shots of command prompt output, just save the output to a text file and paste that in here. That way you can easily company-specific names with dummy names with a simple find/replace, and you can more easily fit all of the output. Or if there is still too much output, you can easily save that at PasteBin.com and post the link to the "paste" (after you do the find/replace, obviously).

Answer 1

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:

Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

• Amirhossein: Yes, I check log some index running with error like sys.columns. can I rollback this change? I guess ,can't change this multiple run and should reinstall SQL Server!
• Solomon Rutzky: @Amirhossein Hi there. "sys.columns" is not an error message. What is the error message? Also, while there is no real rollback with this option, you can certainly run it again specifying a different collation. It just won't run if you specify the collation that is currently the instance-level collation. To get back to the instance level collation you first need to run the command again with another collation that works, and then you can run it a 3rd time to get back to the original collation.