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



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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2