Collation Error: 468 when trying to view login properties in SSMS
-
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:
- 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.
- 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 -
Note to others about the jasonbrimhall link – MBAM blocked it for me:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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:
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 -
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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:
Sue
-
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.
-
Thanks, everyone!
Sue_H, I followed the article and walked through the steps carefully. Thank you for posting it!
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
-------------------
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 - webrunner wrote:
Thanks, everyone!
Sue_H, I followed the article and walked through the steps carefully. Thank you for posting it!
...
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.
@webrunner: That's awesome. I'm glad my article was able to help you solve that problem 😺 .
@SueH: Thanks for those kind words regarding my article on changing collations! I'm glad to hear that it's been helpful.
Just FYI: I have more info to add to the article regarding some edge-cases, etc, but it's just been one of those years 🙃 . Hopefully I have time to work in those updates early next year.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply