?
Solved

SQL Collation and migration

Posted on 2008-06-23
5
Medium Priority
?
409 Views
Last Modified: 2013-11-30
Dear experts,

I have a MS-SQL server that uses SQL_Latin1_General_CP1_Cl_AS as its default collation. Now, I want to export the whole database to another brand new SQL 2005 server, to which I can apply whatever collation I need.

So now I have two simple questions:

1. AFAIK, that collation uses Latin characters; character insensitive; accent sensitive. However, for my purpose an accent insensitive collation might be more appropriated. The Latin characters are also a must. What type of collation do you suggest that I assign to the new server?

2. How can I export that database (I was requested a dump file) so that ir "converts" the data to the new database collation method I decide to use (after your suggestion)?

Thanks in advance and best regards,

Helder
0
Comment
Question by:HelderConde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 400 total points
ID: 21850588
0
 
LVL 8

Accepted Solution

by:
sbagireddi earned 1600 total points
ID: 21850649
http://msdn.microsoft.com/en-us/library/ms175835.aspx

and look up the "Collate" syntax.
0
 

Author Comment

by:HelderConde
ID: 21850783
Hi,

Thanks for your replies. Those answers you provided will allow me to alter the collation type of my database. Cool.

However, my main question is: which Collation should I use?

Best regards and thanks for your help.

Helder
0
 

Author Comment

by:HelderConde
ID: 21850808
Oh, and by the way, please keep in mind that I'm searching for an accent insensitive collation, ok?

Thanks!

Helder
0
 
LVL 8

Assisted Solution

by:sbagireddi
sbagireddi earned 1600 total points
ID: 21850843
I think this should suit your purpose: SQL_Latin1_General_Cp1_CI_AI

Although you may want to check this out:

http://msdn.microsoft.com/en-us/library/ms180175.aspx

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question