January 21, 2009 at 7:29 pm
Hi,
We have Sql server 2005 EE x64 on windows 2003 EE x64.In this SQL Server default instance, the sharepoint databases were stored. From the link http://support.microsoft.com/kb/843578
it says that SharePoint Portal Server 2003 and Windows SharePoint Services use the Latin1_General_CI_AS_KS_WS collation because the Latin1_General_CI_AS_KS_WS collation most closely matches the Microsoft Windows NTFS file system collation.
AND
SharePoint Portal Server 2003 and SharePoint Server 2007 will automatically create databases with the correct collation settings. Notice that the collation settings apply to the database level, but not to the SQL Server level
So, when I installed the SQL server, I went with the default collations settings SQL_Latin1_General_CP1_CI_AI.
Now the issue I first encountered so far is as below:
I opened the security->logins>select one login and go to properties->serverrole->usermapping->securables
as soon as I went to securables I got the below error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AI" in the UNION operation. (Microsoft SQL Server, Error: 468)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=468&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
So system databases have the collation settings as SQL_Latin1_General_CP1_CI_AI
user databases have the collation settings as Latin1_General_CI_AS_KS_WS
So my question is
1.do we need to choose the Latin1_General_CI_AS_KS_WS collation setting while installation when we are planning to store sharepoint databases in sql server?
2.In our case we already installed the sql server with SQL_Latin1_General_CP1_CI_AI and everything is in production.
system databases have the collation settings as SQL_Latin1_General_CP1_CI_AI
user databases have the collation settings as Latin1_General_CI_AS_KS_WS
So can Ignore this?how will effect in future?what are things I need to do?
Thanks
January 22, 2009 at 4:56 am
Everything will go well until sharepoint databases use tempdb. Now you need to choose either to change server collation or to change database collation. That is not easy action. Read Change Database of Server collation first. Try to change db collation (don't forget about its table fields collation) first, because changing server collation completely cleans up master database - logins, databases, server level triggers etc will be lost.
January 27, 2009 at 6:04 pm
Hi All,
do we need to choose the Latin1_General_CI_AS_KS_WS collation setting while installation of SQL Server 2005, when we are planning to store sharepoint databases in sql server?
We installed sql server with SQL_Latin1_General_CP1_CI_AI.
system databases have the collation settings as SQL_Latin1_General_CP1_CI_AI
user databases have the collation settings as Latin1_General_CI_AS_KS_WS(as sharepoint installation creates the databases with this collation)
In general, people who are going to store sharepoint databases in SQL Server 2005, what collation settings need to choose while installing SQL Server 2005?
So my question is should we install SQL Server with collation SQL_Latin1_General_CP1_CI_AI or not? when the share point databases are created with the collation Latin1_General_CI_AS_KS_WS
Advice me exactly which collation settings do I need to select while installing SQL Server 2005?(Considering sharepoint application)
Thank you
February 11, 2009 at 1:17 pm
Could you please advice me on this.....I found and one link http://www.jhsupport.se/download/SBS_2003_R2_Upgrading_the_Sharepoint_instance_to_SQL_2005.pdf (page # 24) in which default collation settings were chosen.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply