Solved

About collation

Posted on 2013-05-23
4
486 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run SQL Server Proc from Access 11 31
Query Syntax 17 34
Query Help - MSSQL - Averages 5 27
Connecting to multiple databases to create a Dashboard 5 25
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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