case sensitive database datastore with ms sql 2012

[Former Member]

Hi all

i'm trying to create a case sensitive datastore with ms sql 2012 in DS 4.2 SP4.

i can only see the attribute <case_sensitive> in the ATL-File. it is possible to create the datastore, download it to ATL, change the attribute manualy in the ATL-File and upload it again. Then it works. But this seems like a workaround to me. is there an other solution?

i tried to fill in the "additional session parameter" with ;case sensitive = TRUE; but that didn't work.

any ideas?

thank you!

cheers

[former_member187605 · Accepted solution]

A DS data store is metadata only. It inherits the properties of its underlying database or application. If your database is case-sensitive, the data store will take that into account.

[Former Member]

Hi all

thank you very much!

it is like arun, manoj and dirk said: if the database is set to "case sensitive" (e.g. SQL_Latin1_General_CP1_CS_AS) , data services will set the case_sensitive attribute (to see in the atl-file) to yes.

what i still don't get: the database was already case sensitive, but with the collation SQL_Latin1_General_CP1_CI_AS. There were columns/tablenames that already had been with case sensitive letters. So the database seems to be case sensitive even without the collation.

if i'm right, the sql collation could be different on the database and on the table. if that makes sense - i don't think so. but in that case i guess that data services would take the collation from the database.

finally: set the collation to case sensitive (e.g.SQL_Latin1_General_CP1_CS_AS) in the database if possible or change the case_sensitive attribute in the atl file manually. (see first Question in this thread)

Thank you!

[former_member187605]

For the data store definition, DS will import the collation from the database, indeed.

[Former Member]

thanks Dirk!

[former_member198401]

Hi Damian,

As Manoj suggested can you please check the collation of the database

SELECT DATABASEPROPERTYEX('Database_Name', 'Collation')

Usually the Cases sensitive database collation is SQL_Latin1_General_CP1_CS_AS and Case Insensitive collation is SQL_Latin1_General_CP1_CI_AS

If the collation is case insensitive then change it to case sensitive by altering the database

ALTER DATABASE database_name COLLATE collation_name

You can choose he SQL Collation from the below link

Selecting a SQL Collation

Once your database is case sensistive then you can create the datastore and it will automatically contain tables and objects with case sensitive collation.

Hope this helps!!

Regards

Arun Sasi

[Former Member]

create database with case sensitive collation, when you create datastore in DS it will set the property based on that, what is the issue ?

[former_member198401]

Hi Damian,

Could you please create a configuration in the datastore under the Advanced option. Once you go into Configuration and Create an Alias under Aliases(e.g dbo or any db owner name)

Regards

Arun Sasi

[former_member451639]

Hi Damian,

I have no solution :;- (

We have two Environments and I found out, that we have two different settings.

I don't know why.

Regards,

Stefan

stefan.hess@mhp.com