InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 21:56:18 GMT20http://www.sqlservercentral.com/Forums/Topic969271-1550-1.aspx[quote][b]mphilippopoulos (8/16/2010)[/b][hr][quote][b]Perry Whittle (8/16/2010)[/b][hr]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![/quote]Unfortunately I'm paying for the mistakes made by the systems admin... Life's not fair...:angry:[/quote]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!Tue, 17 Aug 2010 00:40:24 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic969271-1550-1.aspx[quote][b]Perry Whittle (8/16/2010)[/b][hr]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![/quote]Unfortunately I'm paying for the mistakes made by the systems admin... Life's not fair...:angry:Mon, 16 Aug 2010 19:01:53 GMTmphilippopouloshttp://www.sqlservercentral.com/Forums/Topic969271-1550-1.aspxCorrect, 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!Mon, 16 Aug 2010 16:09:14 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic969271-1550-1.aspxI was able to switch the server collation on my SQL instance with help from this link:[url]http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/ab266beb-5618-4f3a-bd4b-b9fdc7f02344/[/url]In the directory where the SQL installation setup.exe was located, I opened a command prompt window and entered:[quote]Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME="instanceName" /SQLSYSADMINACCOUNTS="domainName\acctName" /SAPWD="XXXXXXXX" /SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"[/quote]I had to check this file for possible errors:[b]C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt[/b]The switch worked, so I don't have to re-install the SQL... BIG RELIEF... :-DJust thought I would share this here, in case other people find it useful.Mon, 16 Aug 2010 14:47:53 GMTmphilippopouloshttp://www.sqlservercentral.com/Forums/Topic969271-1550-1.aspxI found the culprit!The [b]Windows locale[/b] is different in [b]Server2 [/b]from that everywhere else in my environment.On [b]Server2 [/b]it is [i]English/Canada[/i].On the rest of my env it is [i]English/US[/i].This affects the default SQL collation setting according to this link: [url]http://msdn.microsoft.com/en-us/library/ms143508.aspx[/url].For English/Canada the default collation setting is [b]Latin1_General_CI_AS[/b].For English/US the default collation setting is [b]SQL_Latin1_General_CP1_CI_AS[/b]Fri, 13 Aug 2010 19:58:53 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic969271-1550-1.aspxI actually tried a brand new installation of [b]SQL 2008 SP1 Enterprise Ed[/b]. on the server with O/S [b]Windows Server 2008 Standard SP2[/b], and while attempting to set the [b]SQL_Latin1_General_CP1_CI_AS[/b] collation setting, I was told by the SQL setup that this collation setting is provided for backward compatibility only! The [b]Latin1_General_CI_AS[/b] 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:[quote]Msg 468, Level 16, State 9, Line 11Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.[/quote]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 [b]SQL_Latin1_General_CP1_CI_AS[/b], which means on any instance which is on collation [b]Latin1_General_CI_AS[/b] multi-server queries will fail!Fri, 13 Aug 2010 19:34:41 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic969271-1550-1.aspxI'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.[b]Server1[/b] is on O/S: Windows Server 2008 R2 Enterprise[b]Server2[/b] is on O/S: Windows Server 2008 Standard SP2I just discovered that:- on [b]Server1[/b] the Server collation for SQL is: [i]SQL_Latin1_General_CP1_CI_AS[/i]- on [b]Server2[/b] the Server collation for SQL is: [i]Latin1_General_CI_AS[/i]As a result, I'm getting server-collation conflict errors in multi-server queries between [b]Server2 [/b] 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?Fri, 13 Aug 2010 14:42:49 GMTMarios Philippopoulos