Solved

About collation

Posted on 2013-05-23
4
476 Views
Last Modified: 2013-05-24
Hi Experts,

1) I have installed SQL Server 2008 and noticed that the default collation installed is SQL_Latin1_General_CP1_CI_AS. This collation is "not case sensitive", unlike my existing Oracle database.

May I know which collation should I choose to match (as near or close as possible) the "default" collation in Oracle?

2) Is it possible to change the collation of my new SQL database without re-installing? If yes, how?
0
Comment
Question by:sepknow
4 Comments
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 125 total points
ID: 39193255
1) Try
SQL_Latin1_General_CP1_CS_AS

The CS stands for Case Sensitive. CI stands for Case Insensitive

2) Anytime I have got the collation wrong, I have recreated the database. I do not know if it can be done without recreating the database.
0
 
LVL 1

Assisted Solution

by:CalvinWin
CalvinWin earned 125 total points
ID: 39193408
Please refer to the below link to change the collation.

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

This command will rebuild all the system databases. You can attach back all the user databases later but all the SQL logins and agent jobs will be gone because of master, msdb and model database are rebuild too. Better generate the scripts for your login and agent jobs as well as other server objects if you have.
0
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 125 total points
ID: 39193414
A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages). Please read
http://msdn.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx

No need to reinstall as you can change it like
SELECT		a.User_ID,
FROM		dbo.Table_A AS a
WHERE		NOT EXISTS	(
					SELECT	*
					FROM	dbo.Table_B AS b
						WHERE	b.User_ID = a.User_ID COLLATE DATABASE_DEFAULT
						AND b.Attr_B = 'VALIDATED'
				)
ORDER BY	a.User_ID

Open in new window

0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 125 total points
ID: 39194914
Hi,
There is no "default" collation in Oracle, but you can check it with:
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
... just to be sure.
Regards,
Franck.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now