Collation Error: 468 when trying to view login properties in SSMS

  • webrunner

    SSC-Dedicated

    Points: 30254

    Hello experts,

    I was recently confronted with a dev SQL Server that had been installed with the SQL_Latin1_General_CP1_CI_AS when it requires SQL_Latin1_General_CP1_CS_AS. I tried the steps at the following link, which looks like they rebuilt the system and user databases using SQL_Latin1_General_CP1_CI_AS.

    https://jasonbrimhall.info/2018/04/12/change-sql-server-collation/

    The recommended command was (using my collation):

    sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1_CS_AS"

    Almost everything seemed like it worked, except for two things:

    1. When I right-click the server properties in SSMS, the server collation still says SQL_Latin1_General_CP1_CI_AS - but master etc. say SQL_Latin1_General_CP1_CS_AS.
    2. When I try to open a login in SSMS (the usual one, under Security), I get this error:
    Cannot show requested dialog. (SqlMgmt)

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)

    Can anyone shed light on why those two things are happening and how I might fix them?

    This server has not been deployed so there is a better chance that I can detach databases, restart, etc., if needed.

    Thanks for any help!

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Phil Parkin

    SSC Guru

    Points: 244578

    Note to others about the jasonbrimhall link – MBAM blocked it for me:

    2020-05-29_7-02-12

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • webrunner

    SSC-Dedicated

    Points: 30254

    Thanks, Phil,

    Sorry about that site. I didn't get the warning but didn't realize it could be marked as suspicious by security software.

    Below are the relevant bits of the page's suggestions.

    This is for a default instance on SQL Server 2017. If you have a named instance or a different version of SQL Server, you will need to navigate the instance folder structure for your instance.

    Next is where the magic happens. We enter a command similar to this:

    1

    sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP850_CS_AS"

    Here is a quick summary of those flags in this command:

    [-m] single user admin mode

    [-T] trace flag turned on at startup

    [-q] new collation to be applied

    There are more such as -s available in books online for your perusal.

    If you are curious what is up with those Trace Flags, pretty simple. TF4022 is to bypass startup procs. TF3659 on the other hand is supposed to write errors to the error log (at least in theory).

    When the script starts, you will see something like the next two screens: [etc...]

    Here is a link to a similar startup command:

    https://www.winsysadminblog.com/2016/05/changing-the-sql-server-collation-after-installation-for-sql-server-2012-or-2014-without-re-installation/

    Finally, this link at http://www.mssqltips.com has a far more involved process for changing the server collation. I tried to avoid that but maybe it is the only way to do it properly?

    https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/

    Thanks again.

    -- webrunner

     

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Phil Parkin

    SSC Guru

    Points: 244578

    No need to apologise ... probably a false positive, but I put it there just in case.

    Wish I could help with your actual problem, but I'm just a lowly developer. Good luck.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Sue_H

    SSC Guru

    Points: 90673

    I've done a few of those changes, sometimes it's not too fun. The -q switch seems like it works most of the time but not all of the time. Undocumented commands are often easier but they have their risks and don't always work. You may need to do the work with the changing the system databases. One of the best articles I've read on changing collations is this one:

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

    Sue

     

  • stevec883

    Old Hand

    Points: 351

    If I inherit something that was built improperly, I use that as a justifiable reason to tear it down and rebuild it the correct way. I realize this is a 30,000 foot solution and you are looking for a way to remedy this down in the weeds, but I can't help but to think that even if you got to a point where it looks good and the collation issue appears to be resolved, what sort of problems down the road might resurface.

    • This reply was modified 1 month ago by  stevec883.
  • webrunner

    SSC-Dedicated

    Points: 30254

    Thanks, everyone!

    Sue_H, I followed the article and walked through the steps carefully. Thank you for posting 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/

    Turns out one of the databases had a limit on file growth which was causing an error that was forcing the sqlservr command to fail. It needed more space to rebuild indexes and ran out. When I first ran it, I just thought the process had completed in the Command Window but did not look carefully at the output (my fault).

    Following the steps in that link above, I was able to resolve that file-growth issue and re-run the command to wait until it had actually finished.

    When it did, I restarted the SQL services. This time the server collation was SQL_Latin1_General_CP850_CS_AS and I no longer saw that error with viewing logins at the server level.

    Thanks to you all, though. Your answers persuaded me to do the process more carefully to find the issue.

    -- webrunner

    • This reply was modified 4 weeks ago by  webrunner.
    • This reply was modified 4 weeks ago by  webrunner.
    • This reply was modified 4 weeks ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply