Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    76

    Unanswered: 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. I searched the forums and found below link

    http://www.dbforums.com/t905653.html

    But on doing it 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.

  2. #2
    Join Date
    Oct 2005
    Posts
    37
    I think it depends on whether the SQL Server (or instance in 2000/7) was built as case sensitive or not

  3. #3
    Join Date
    Oct 2005
    Posts
    37
    sorry that should be 2000/2005

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by cheeku
    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. I searched the forums and found below link

    http://www.dbforums.com/t905653.html

    But on doing it 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.
    Here you go
    Code:
     CREATE
    Code:
    TABLE Test (COL1 VARCHAR(90),CONSTRAINT TEST_PK PRIMARYKEY(COL1));
    
    INSERTINTO Test VALUES('test');
    
    INSERTINTO Test VALUES('TEST');-- UPPER CASE data 
     
    ----This will fail
    altertable Test
    
    dropconstraint TEST_PK 
    
    altertable Test
    
    altercolumn Col1 varchar(90)collate SQL_Latin1_General_Cp1_CS_AS notnull
    
    altertable Test
    
    addconstraint TEST_PK primarykey(col1)
    
    ---this will not fail
    INSERTINTO Test VALUES('TEST');-- UPPER CASE data
    select*from Test

    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Aug 2004
    Posts
    76
    u got it right!! gr8 and thanx a ton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •