Hello all. Couldn't find anything specific to this situation so apologies in advance if it has been covered. My situation is this:
I have a 2012 SQL environment that was created using the default collation of "SQL_Latin1_General_CP1_CI_AS" and, of course, all the system db's would have that collation as well. After creation, some user databases were restored to it as part of a migration from another 2012 SQL instance. Those user (application) databases had a collation of "Latin1_General_BIN". There were some issues as one would expect where some procedures were experiencing "collation mismatch" errors around things created in tempdb.
I therefore used the steps outlined in an article at mssqltips to change the collation of the system dbs (and instance db according to the article) to match the collation of the other user dbs (i.e. set them to be Latin1_General_BIN). This worked fine with the exception of the instance collation which did not get changed. It is still at the default.
So, my question: what is the impact of having every database on the instance including the system dbs be the same except for the sql instance itself. Appreciate the input.
We have that situation on several of our instances now, mostly due to package software. For example, Atlassian uses SQL_Latin1_General_CP437_CI_AI, whereas our dbs use the default CI_AS.
It's not really an issue except when you try to combine things for those dbs. In certain cases, if the collations aren't compatible, you'll have to add COLLATE clause(s).
Well, looks like it's caused an issue where if I create any new SQL account and try to get properties on it (or even existing ones) I get an error saying "cannot resolve the collation....in the equal to operation" I guess it's not happy having the system db's have a different collation from the instance. Any way I can change the instance collation?
You'd have to provide more specifics. I have no idea how you actually "tr[ied] to get properties on it".
I thought the instance collation was the same as the system dbs.
I have a 2012 SQL environment that was created using the default collation of "SQL_Latin1_General_CP1_CI_AS" and, of course, all the system db's would have that collation as well.
Only a user db(s) has different collation, right?
Sorry, properties on an existing login by right clicking in SSMS and choosing "Properties"
Later on in my post I indicate that I had attempted to change the collation using steps outlined in an article. It worked for all system db's but not for the sql instance. I just tried to change all the system db's back to the default instance but it fails to do so.
Right now, the user db's match with the system db's but not with the sql instance.