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:
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
May 29, 2020 at 12:03 pm
Note to others about the jasonbrimhall link – MBAM blocked it for me:
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 29, 2020 at 3:45 pm
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
May 29, 2020 at 3:49 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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
May 30, 2020 at 5:46 pm
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.
June 5, 2020 at 3:36 am
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
December 10, 2020 at 3:09 am
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