August 13, 2010 at 2:42 pm
I'm having the following predicament.
I recently installed SQL 2008 SP1 on 2 servers; in both installations I left the collation settings unchanged to their default values.
Server1 is on O/S: Windows Server 2008 R2 Enterprise
Server2 is on O/S: Windows Server 2008 Standard SP2
I just discovered that:
- on Server1 the Server collation for SQL is: SQL_Latin1_General_CP1_CI_AS
- on Server2 the Server collation for SQL is: Latin1_General_CI_AS
As a result, I'm getting server-collation conflict errors in multi-server queries between Server2 and other servers in my environment.
How could the default collation for SQL have changed?
I never had to worry about this while installing SQL before...
Anyone have any ideas?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 13, 2010 at 7:34 pm
I actually tried a brand new installation of SQL 2008 SP1 Enterprise Ed. on the server with O/S Windows Server 2008 Standard SP2, and while attempting to set the SQL_Latin1_General_CP1_CI_AS collation setting, I was told by the SQL setup that this collation setting is provided for backward compatibility only! The Latin1_General_CI_AS collation setting was offered as the default!
When I run a query spanning 2 servers, each on one of the two collation settings I get this error:
Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Are collation settings being changed on Windows Server 2008, to the point that older collation settings may never be applicable?!
All the machines in my env are on collation setting SQL_Latin1_General_CP1_CI_AS, which means on any instance which is on collation Latin1_General_CI_AS multi-server queries will fail!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 13, 2010 at 7:58 pm
I found the culprit!
The Windows locale is different in Server2 from that everywhere else in my environment.
On Server2 it is English/Canada.
On the rest of my env it is English/US.
This affects the default SQL collation setting according to this link: http://msdn.microsoft.com/en-us/library/ms143508.aspx.
For English/Canada the default collation setting is Latin1_General_CI_AS.
For English/US the default collation setting is SQL_Latin1_General_CP1_CI_AS
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 16, 2010 at 2:47 pm
I was able to switch the server collation on my SQL instance with help from this link:
In the directory where the SQL installation setup.exe was located, I opened a command prompt window and entered:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME="instanceName" /SQLSYSADMINACCOUNTS="domainName\acctName" /SAPWD="XXXXXXXX" /SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
I had to check this file for possible errors:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt
The switch worked, so I don't have to re-install the SQL...
BIG RELIEF... 😀
Just thought I would share this here, in case other people find it useful.
August 16, 2010 at 4:09 pm
Correct, SQL Server setup inspects the server locale during install and selects a collation based on the regional settings. It is important to set these correctly during OS deployment!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2010 at 7:01 pm
Perry Whittle (8/16/2010)
Correct, SQL Server setup inspects the server locale during install and selects a collation based on the regional settings. It is important to set these correctly during OS deployment!
Unfortunately I'm paying for the mistakes made by the systems admin... Life's not fair...:angry:
August 17, 2010 at 12:40 am
mphilippopoulos (8/16/2010)
Perry Whittle (8/16/2010)
Correct, SQL Server setup inspects the server locale during install and selects a collation based on the regional settings. It is important to set these correctly during OS deployment!Unfortunately I'm paying for the mistakes made by the systems admin... Life's not fair...:angry:
That's why I always insist that I carry out my own server builds. If I can't I check them rigorously them throw them back if they're wrong!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 12, 2020 at 12:16 am
I actually tried a brand new installation of SQL 2008 SP1 Enterprise Ed. on the server with O/S Windows Server 2008 Standard SP2, and while attempting to set the SQL_Latin1_General_CP1_CI_AS collation setting, I was told by the SQL setup that this collation setting is provided for backward compatibility only! The Latin1_General_CI_AS collation setting was offered as the default!
Yes, the SQL Server collations (those with names starting with SQL_ ) should be avoided if at all possible. Part of the reason is documented here: Impact on Indexes When Mixing VARCHAR and NVARCHAR Types . Unfortunately, SQL_Latin1_General_CP1_CI_AS is still the default for US English locales (until enough people vote for this item: Make Latin1_General_(100)_CI_AS the default collation for US English ). However, using the SQL_ collations doesn't exactly break anything.
When I run a query spanning 2 servers, each on one of the two collation settings I get this error:
Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
All the machines in my env are on collation setting SQL_Latin1_General_CP1_CI_AS, which means on any instance which is on collation Latin1_General_CI_AS multi-server queries will fail!
Well, queries in this scenario don't need to fail. First, this only affects comparing, concatenating, UNIONing string columns. But, there are two ways to get such queries to complete successfully:
COLLATE clause.
Also:
Setup.exe /QUIET /ACTION=REBUILDDATABASE approach that you used, there is an undocumented approach that does an in-place update of the collation_id meta-data of all applicable columns, so it doesn't require rebuilding [master] / [msdb] and side-steps most of the restrictions that might otherwise cause errors with ALTER DATABASE ... COLLATE .. , but also doesn't do code page conversions for VARCHAR data if changing the code page. It's the sqlservr.exe -q option which I have documented here: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
May 12, 2020 at 5:29 am
This thread is 10 years old
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 12, 2020 at 8:11 am
Perry Whittle wrote:Perry Whittle (8/16/2010)
Correct, SQL Server setup inspects the server locale during install and selects a collation based on the regional settings. It is important to set these correctly during OS deployment!Unfortunately I'm paying for the mistakes made by the systems admin...
Hello Perry and Marios.
Perry's first statement is correct: while installing SQL Server, the OS locale is used to determine which collation, by default, should be the instance-level collation. However, contrary to the rest of the quoted statements, it shouldn't matter what the OS-level locale is because that default collation can be overridden during the install. So, even if one server was configured to use English/Canada, you can still select SQL_Latin1_General_CP1_CI_AS. And, based on the 2nd post in this thread, #1206979 , it sounds like Marios was attempting to do just that, but got thrown off by the warning that the SQL_ collations are "provided for backward compatibility only". Don't get me wrong: it's definitely easier when the OS locale is set "correctly", but it's also not difficult to choose something other than the suggested / default collation.
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
May 12, 2020 at 8:14 am
As I said this thread is 10 years old
most people, whether ignorant or lazy, seem to rely on the installer choosing the correct collation.
it is important to set the regional settings, why would you want a server located in uk to have US regional settings
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 12, 2020 at 8:17 am
This thread is 10 years old
Exactly! And fortunately, these threads don't stop accepting new posts, hence misleading statements can be clarified / corrected so new readers finding this thread will come away with a better understanding of the issue(s).
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
May 12, 2020 at 8:28 am
As I said this thread is 10 years old
most people, whether ignorant or lazy, seem to rely on the installer choosing the correct collation.
it is important to set the regional settings, why would you want a server located in uk to have US regional settings
The age of the thread is irrelevant if readers are applying this information to current situations.
We can't help folks who don't know how this works, who don't click on the "collation" tab, or who don't care enough to look up how to take care of this, BUT, we can certainly help those who are trying and searching around and find this thread by providing the info that they are looking for.
As I said, it certainly is easier when the OS locale is set such that the SQL Server installer picks up the desired collation. BUT, stating that the installer picks up the collation from the OS locale without stating that it can be changed during install is inaccurate, and hence less helpful. I don't know why one might want a server in the UK to have US regional settings, but I don't need to know all of the possible use cases. Could be that a server is hosted in one place but primarily used in another, or could be that an organization has offices in several regions and SQL Server installed in more than one of those. Or it could be a mistake. In either case, people should have the full and accurate information. If they are dealing with an OS using a locale that doesn't default to the desired collation, it is not the end of the world, no need to yell at or even curse a sysadmin, just do an extra couple 0 clicks in the installer and move on. No need to stress over it or demand to install the OS yourself as not everyone will have that luxury. That's all.
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
May 12, 2020 at 8:32 am
The installer has changed drastically since 2008 so the post here, could be argued, is now irrelevant
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 13, 2020 at 6:52 am
The installer has changed drastically since 2008 so the post here, could be argued, is now irrelevant
True, there have been significant changes to the SQL Server installer over the past 6 versions (2008 R2, 2012, 2014, 2016, 2017, and now 2019). However, I would caution against making such a sweeping generalization that drastic changes means that everything has changed. This particular area, while changing slightly in terms of the UI, has not changed with regards to the default collation associated with the US English locale. In fact, this default is the same as it was all the way back in SQL Server 2000! So, I would argue that this post is highly relevant given that the behavior is the same across all 9 versions (2 older and 6 newer). And, that's why it's so important to have complete and accurate info: all of these versions are still in use (even if several are no longer supported) and people are stilling running into this problem and are still finding this post and are not automatically dismissing it just because it concerned SQL Server 2008.
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply