inserting different case data

Currently am migrating my data from oracle to sqlserver. Here is a senario where am struck.

CREATE TABLE TEST (COL1 VARCHAR(90), CONSTRAINT TEST_PK PRIMARY KEY (COL1) );
INSERT INTO TEST VALUES ('test');
INSERT INTO TEST VALUES ('TEST'); -- UPPER CASE data

The above is acceptable in oracle but in sqlserver in case insesitive database the second value is not accepted as it voilates the PK constraint.

Answer this question

inserting different case data

Lizard Man
Before you can change COL1 (it's your primary key) you should drop primary key from this columnt, change the column and finally add your primary key again. I think Latin1_General is right for you, scince you use english
YU_MSVB
Have you tried to set the collation name of your Database to "[YourLanguage]_CS" CS is Case Sensitive
Shyam Vaidya
u got it right!! gr8 and thanx a ton
Beugen
thanx for the info. I tried the following but got some error:

ALTER TABLE TEST ALTER COLUMN COL1 VARCHAR(90) COLLATE SQL_Latin1_General_Cp1_CS_AS

I got the above from another forum, but resulted in below error

Server: Msg 5074, Level 16, State 8, Line 1
The object 'TEST_PK' is dependent on column 'COL1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN COL1 failed because one or more objects access this column.

Also I am not aware of "SQL_Latin1_General_Cp1_CS_AS" word. Mine is in Enlgish only hence lemmet know the syntax.
inserting different case data

Answer this question

inserting different case data

Hot Topic

SQL Server

inserting different case data

inserting different case data

Lizard Man

YU_MSVB

Shyam Vaidya

Beugen