SQL Server Performance

how to change server collation in sqlserver 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by kumaravel v, Oct 8, 2008.

  1. kumaravel v New Member

    hi
    | want to change my server collation from "Latin1_General_CI_AS" to "Latin1_General_CI_AI"
    can you please advice me !
    thanks in advance!
  2. ghemant Moderator

    You can use alter database statement to change the collation, is this what you looking for!!
  3. kumaravel v New Member

    thanks for your response. I am expecting to change the collation in server level not database level.
    In my SQL Server 2005, the server collation is "Latin1_General_CI_AS" but i want to change into "Latin1_General_CI_AI"
  4. satya Moderator

    <P mce_keep="true">I guess you are looking to change server collation, which is a complex operation as you see following process:</P><UL><LI>Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.<BR></LI><LI>Export all your data using a tool such as bulk copy.<BR></LI><LI>Drop all the user databases.<BR></LI><LI>Rebuild the <STRONG>master</STRONG> database specifying the new collation in the SQLCOLLATION property of the <STRONG>setup</STRONG> command. For example:<BR><DIV class="" id=ctl00_rs1_mainContentContainer_ctl06_other><TABLE class=""><TBODY><TR><TD class="" style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: 0px; PADDING-TOP: 0px; BORDER-BOTTOM: 0px"><DIV class=CodeSnippetTitleBar><DIV class=CodeDisplayLanguage></DIV><DIV class=CopyCodeButton><A class=copyCode href="javascript:CopyCode('ctl00_rs1_mainContentContainer_ctl06other');"><IMG height=9 src="http://i.technet.microsoft.com/platform/Controls/CodeSnippet/resources/copy_off.gif" align=middle border=0> Copy Code</A></DIV></DIV></TD></TR><TR><TD class="" style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; BORDER-LEFT: 0px; PADDING-TOP: 0px; BORDER-BOTTOM: 0px"><PRE class=libCScode id=ctl00_rs1_mainContentContainer_ctl06other space="preserve">setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="sa-pwd" /SQLSYSADMINACCOUNTS="BUILTINADMINISTRATORS" /SqlCollation=SQL_Latin1_General_CP1_CI_AI</PRE></TD></TR></TBODY></TABLE></DIV></LI><LI>Create all the databases and all the objects in them.<BR></LI><LI>Import all your data. </LI></UL><P>Rather you can follow what Hemant referred above to set database alone. </P>
  5. kumaravel v New Member

    when i run this command, it shows "sqlrun_sql.msi" is not found. Then rollback everything and close the installation.
  6. MohammedU New Member

    Make you have the access to the media....
    I will advise you copy the installation files locally and use fully qualified name for setup.exe
  7. moh_hassan20 New Member

    you can work around by modifying installed path
    view: http://support.microsoft.com/kb/943635

Share This Page