I’m using Data Services 4.0 version and would like the metadata (tables and stored procedure) to be case-sensitive with mixed case (as in the SQL server database). However, Data Services by default sets it to upper case when I do import by name or by browsing.
I’ve tried using double quotation marks when importing stored procedure and tables by name and it seems to be working for stored procedures only, is that a bug or by design?
Is there another way to change this behavior?
Are you importing from Oracle? If so then realize that Oracle stores metadata in its system tables in upper case. If you create a table this way:
CREATE TABLE MixedCase
Then in the system table it is stored as MIXEDCASE.
On the other hand, if you create a table this way:
CREATE TABLE "MixedCase"
(note the double quote marks)
Then in the system table it is stored as MixedCase.
Now, do you REALLY want to do this? There are some downsides to this in Oracle. If you create the table using the second method then EVERY call to the table must be the same way. If you try it as mixedcase or MIXEDCASE then it won’t work.
For this reason I recommend to all my clients that when you create objects in Oracle you DO NOT use double quotes.
I’m using MS SQL Server 2008, so this doesn’t apply.
The problem I’m having is more on the Data Services side, I’m trying to enforce the mixed case in the designer when importing a table instead of the default upper case.
Since you are using SQL Server case sensitivity doesn’t really matter (unless your DBA enable that). So you could go into the repository tables and change the table name to look like you want them to.
Case sensitive tables is a really nightmare in DS, especially if you aren’t aware of it.
In our case, the target warehouse is Sybase IQ, and for an “unknown” reason, DS treat this type of Store as Case Sensitive, The problem arised when we reimport a table (i.e. due to structure changes) and for any reason the case in the DB is different from the original metadata in DS. Since the DB is not case sensitive, that it’s not a problem at the DB level. DI reimport it without any warnings and then a lot (A LOT) of problem arises in CheckIn, CheckOut, import, export, DF, etc, etc. The main reason, in my opinion, is DS shouldn’t allow to do a reimport when the DS is treated as case sensitive and the case in the DB don’t match the case in the internal metadata.
So, if you could avoid the case embellishment, do it.
I wouldn’t like DS to handle the metadata as case sensitive since the SQL Server is not case sensitive. However, I would like it to retain the case when importing a table for readability purposes in the designer. In the DB we use camel case standards and when importing the tables to DS, it translates all the table names to upper case letters, which makes it really hard to read and maintain.
The DS repository database type is MS SQL Server 2008, I’m assuming the DS Repository database is not case-sensitive, is there a way to set it in DS or is it internally set based on the DB type?
I believe that the repository operates in such a way that it works the same way without regard to if it exists in a case-sensitive or case-insensitive database. The NORMNAME column is always uppercase if I recall correctly.
case-sensitive is database property not DS property and you can’t that from DS, you will have to change that in Database
if the Database that you are using in Datastore is case-sensitive (check the collation of the database in SQL Server) you will see the table name as it appears in the database
if the database is case insensitive then DS will normalize the identifiers and the table, column names will appear in upper case
so, for your case, if you have not done lot of development in DS, and the database is case-insensitive, then check if it’s possible to change the database collation to make it case-sensitive (before you do that check the implications of changing the collation of existing database) depending on the impact you will have to decide what is that you are acheiving just by displaying the names of objects same as it appears in Database
(BOB member since 2007-09-12)
Is this simply an aesthetic preference?
(BOB member since 2007-09-12)
(BOB member since 2006-02-02)