October 12, 2010 at 3:00 pm
Hi Folks
I am trying to manually import an Oracle table into SQL Server
In SQL Server:
I created the table
I used BCP to import the data
Now i am trying to put a UNIQUE index on the table using (parentid,ownerid,name) of the table
SQLServer will not allow it because it looks like it does not seem to distinguish between upper and lower case names:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DTREE' and the index name 'DTREE_PRIMARY'.
The duplicate key value is (-14997325, 14997325, 1 Vote for slide 44).
I dumped the data from Oracle:
DATAID PARENTID OWNERID NAME
---------- ---------- ---------- ------------------------
14996579 -14997325 14997325 1 Vote for slide 44
14995417 -14997325 14997325 1 vote for slide 44
In Oracle this was considered a UNIQUE index
In SQL Server it does not seem to be true. Is it ?????
How do i get SQLServer to distinguish that these two records are not the same when i try to index the table
Thanks
Jim
October 12, 2010 at 3:12 pm
The default collation in SQL Server is case insensitive. You need to change the collation of either the columns, the table, the database, or the server to a case sensitive collation if you want to mimic the Oracle behavior. The level at which you need to reset the collation will depend on your requirements for this data. If you have other tables you are importing and want this whole db to mimic Oracle, you might want to change it at the DB level. It can be quite confusing, in my opinion to have columns and tables with mixed collation. In any case you probably need to take a look at collation in BOL.
October 12, 2010 at 3:55 pm
Hi David
well..looking up these collation codes (which you need a PHD to review them all)
I have
SQL_Latin1_General_CP1_CI_AS
but i do not see what it should be chaged to in reviewing
http://msdn.microsoft.com/en-us/library/ms143508(v=SQL.100).aspx
any idea on what i can do ?
Thanks
Jim
October 12, 2010 at 4:49 pm
This should be valid:
SQL_Latin1_General_Cp1_CS_AS
as an alternative, per:
http://msdn.microsoft.com/en-us/library/ms180175(v=SQL.90).aspx
Are you running 2008?
October 12, 2010 at 5:03 pm
You can get the valid ones for 2008 by:
select * from fn_helpcollations()
October 12, 2010 at 7:39 pm
are you running an application from this DB?
i'd exercise caution before changing a collation from case insensitive... (if your Oracle was indeed case sensitive then there should not be an issue.)
If it is an issue to move to case sensitive perhaps consider using another value in your key, like the DATAID field you have in your Oracle output, which looks like a unique row id.
October 13, 2010 at 12:23 am
October 13, 2010 at 8:31 am
HI David
thanks
i will look into these documents
Jim
October 13, 2010 at 8:32 am
Hi luckus_g
it is not an issue to change it
i am in just testing creating tables and indexes and importing data right now
so i can start over after i make the change
Thanks
Jim
October 13, 2010 at 8:35 am
Thanks norie
will take a look
Jim
October 13, 2010 at 8:36 am
I am running the tests on windows server 2008 with sql server 2008
Jim
October 13, 2010 at 9:46 am
Hi Folks
I made the change to the database after dropping all tables as it would not allow me change the COLLATE value until I did so
a follow-up question and why
it seems that changing the database from case insensitive to case sensitive causes the database tables to become unknown
when trying to select or do an sp_help on them:
can someone tell me why the heck Microsoft would do something so stupid.
I just want to change the data, not every object along with the data in the database
Microsoft treats data and objects the same when it comes to case sensitivity???
Any incite is appreciated
Thanks
Jim
October 13, 2010 at 10:02 am
Yes, once a db is case sensitive, it's ALL case sensitive. I don't see a lot of case sensitve SQL Server installations in the wild.
You should be able to apply the case sensitivity at a more granular level (table or column). What happened when you tried that?
October 13, 2010 at 11:00 am
David Webb-200187 (10/12/2010)
This should be valid:SQL_Latin1_General_Cp1_CS_AS
JC-3113 (10/12/2010)
I haveSQL_Latin1_General_CP1_CI_AS
Just a quick note: the "_CI_" in the second one indicates case-insensitive.
The "_CS_" in the first one indicates case-sensitive.
JC-3113 (10/13/2010)
Hi FolksI made the change to the database after dropping all tables as it would not allow me change the COLLATE value until I did so
a follow-up question and why
it seems that changing the database from case insensitive to case sensitive causes the database tables to become unknown
when trying to select or do an sp_help on them:
can someone tell me why the heck Microsoft would do something so stupid.
I just want to change the data, not every object along with the data in the database
Microsoft treats data and objects the same when it comes to case sensitivity???
Any incite is appreciated
Thanks
Jim
Once the database is in a case-sensitive collation, then:
CREATE TABLE MyTest (col1);
SELECT * from mytest; -- this will fail - no such table
SELECT Col1 from MyTest; -- this will fail - no such column
And I haven't tested this, but it's quite possible (if your db is named MyOracleDBinSS), that
SELECT * from myoracledbinss.dbo.MyTest
will also fail.
Once a database is case sensitive, you have to be sensitive about what case you're using.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 11:14 am
Hi WayneS
I guess I found that out, but why ?
Why would you make the object(s) case sensitive at all ?
I just want the data to be case sensitive
are you telling me that a table name called "Table1" is not the same as one called "table1"
if that is the case
what an impact that is on typing in sql
Thanks
Jim
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply