The Heap™ – Consultancy ©®

9:04 AM

Also posted on Stack Overflow stackoverflow.com/q/64499056

Be patient. If the question contains sufficient detail and is of interest to our collation experts, it will get an answer soon.

Sure, Thank you :)

Q: Process to change collation on a database

@MuraliDharDarshan Meanwhile, take a look at the answers and reference links in:

6

I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment. So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly ...

Particularly sqlquantumleap.com/2018/06/11/…

People will likely be curious to see what is in your collation change scripts

And will want to know the SQL Server versions of all the instances involved

9:24 AM

yes, I am going through the post and the responses. I can share my collation change script. to summarize what it is doing is:
1. Drop FK Constaints
2. Drop indexes including PK
3. Drop check n default containts
4. Drop user statistics
6. Drop views, computed columns, SPs to resolve object bound bug
5. Tables are ready for collation change...so script will change the collations of the columns for all tables
6. Recreate above listed constaints again

I thought about to set up Transactional Replication only to make sure that production downtime could be minimized...i thought it will be smooth.. but this bug created the chaos which i mentioned in the post.

@MuraliDharDarshan Add it to your question, there's little point posting it here. This is a general chat room.

sure

Your question is quite involved, so it will be important to make it as precise as possible to encourage people to answer, and not close it for being too detailed for a Q & A-style treatment.

@MuraliDharDarshan You'll want to be specific about the commands used to implement step 5, and how you're accounting for code page issues (see links).

9:42 AM

I have not really thought about it. I just saw that special characters like 'åÅäÄöÖ' have been par of the Finnish language and so they should be migrated from SQL_Scandinavian_Pref_CP850_CI_AS to Finnish_Swedish_CI_AS seamlessly

I will go through that code page related details and its impacts on the data after collation change

It does seem like it ought to work in principle dbfiddle.uk/…

Replication issues aside

9:58 AM

Yes exactly we tested the same and it worked so we went ahead. However, in the case of replication, data gets changed. But these special characters work in the case of nvarchar. But it's not an agreeable/feasible solution.

I have added details about the collation change script to my post.

Yes I saw, thanks.

Thank you.

John aka hot2use

Morning

> nomination period began
> Oct 12 at 20:00
> election began
> Oct 19 at 20:00
> election ends
> **in 9 hours**
> moderator candidates
> 3
> moderator positions available
> 1

10:15 AM

@MuraliDharDarshan Ah actually there is a code page issue: dbfiddle.uk/… (click the ... to see the full result set)

sure, let me analyze its impact. Thank you for letting this know.

@MuraliDharDarshan It seems some of those CP850 characters either don't exist in the target collation, or the automatic "best fit" (similar-looking character) algorithm isn't doing a perfect job. For example char(128) in the source en.wikipedia.org/wiki/Code_page_850 is Ç, which gets best-fit translated to €, rather than NCHAR(199).

10:47 AM

Sure, will analyze it and see its impact. I am quite sure that such characters have not been used in the database, and that's why we took åÅäÄöÖ specifically to see that these chars should not have a problem after collation change.

Indeed. Still, it might help you figure out where the issue is occurring, and why.

Might

11:04 AM

yeah sure

12:36 PM

> Election closes in 6 hours.
9,168 voters were eligible, 1,817 visited the site during the election, 1,243 visited the election page, and 614 voted

bbaird

2:47 PM

good morning

Josh Darnell

5:17 PM

@bbaird Morning!

John aka hot2use

Morning again

Good Wednesday morning

mustaccio

5:44 PM

@MaxVernon did you move to NZ? I didn't realize it was a requirement for mod candidates to have NZ residency, though it might explain why Paul sticks around there for so long.

lol no I was going along with the "good morning" theme - and since it's legitimately morning in NZ right now, and most likely Paul is rolling out of bed, it seemed a good thing to say.

I'd love to live there though - it seems like a very interesting place.

Josh Darnell

> implying Paul goes to bed

@JoshDarnell good point. it certainly seems like he never sleeps.

6:03 PM

Do we have new Pope?

6:59 PM

lol not yet

bbaird

is good pope

hahahah so cute!

that dog is like "what did I do to deserve this?"

bbaird

(random internet picture, not my dog btw)

jcolebrand

7:50 PM

For those who are waiting till the last possible minute, that's basically now

Q: 2020 Community Moderator Election Results

8:02 PM

reddit.com/r/ProgrammerHumor/comments/jihp7p/…

John aka hot2use

2

Database Administrators's fifth moderator election has come to a close, the votes have been tallied and the new moderator is: They'll be joining the existing crew shortly—please thank them for volunteering, and share your assistance and advice with them as they learn the ropes! Also, please join...

discussion featured election

@MaxVernon congrats

thanks!

Josh Darnell

8:27 PM

Already diamonded!

Congrats, Max!

3

Q: 2020 Community Moderator Election Results

thanks Josh!

Master Database

5

Database Administrators's fifth moderator election has come to a close, the votes have been tallied and the new moderator is: They'll be joining the existing crew shortly—please thank them for volunteering, and share your assistance and advice with them as they learn the ropes! Also, please join...

discussion featured election