How to change data type in database layer?

[Invisi]

I have a date dimension in SQL Server where the first day of month column is of type date (without time). For some reason in the framework it says it's Nvarchar. I want to change it but it won't allow a change. How can I change it?

[Kiran P]

Hi,

Select the table in FM, go to Tools and choose Update Object.

Thanks
Kiran

[Invisi]

I find this article:
http://www-01.ibm.com/support/docview.wss?uid=swg21635710

My preference is to check and change in which mode my project is. I can't seem to find it though. I find this article about it:
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/t_dqm_fm_steps.html

It seems that somehow my current Cognos setup and data source connections don't support Dynamic Query. I have this article that describes to setting it:
https://www.ironsidegroup.com/2011/11/14/dynamic-query-mode-for-relational-databases/
But I fail to find the JDBC option.

Who knows what may be wrong with this current Cognos install? It's a 10.1 version if I'm correct. Does that already have Dynamic Query?

[Invisi]

Hi,

Select the table in FM, go to Tools and choose Update Object.

Thanks
Kiran

Thanks for the tip. It doesn't work sadly. It seems I have to change the data type to datetime2 in order for Cognos to recognise it as a date. So much for lean data types... :(

[Kiran P]

I find this article:
http://www-01.ibm.com/support/docview.wss?uid=swg21635710

My preference is to check and change in which mode my project is. I can't seem to find it though. I find this article about it:
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/t_dqm_fm_steps.html

It seems that somehow my current Cognos setup and data source connections don't support Dynamic Query. I have this article that describes to setting it:
https://www.ironsidegroup.com/2011/11/14/dynamic-query-mode-for-relational-databases/
But I fail to find the JDBC option.

Who knows what may be wrong with this current Cognos install? It's a 10.1 version if I'm correct. Does that already have Dynamic Query?

10.1 does support DQM. You need to have SQL JDBC drivers installed on the app tier so that JDBC tab appears in data source connection properties.

Check this link - http://bi-hints.blogspot.in/2013/02/jdbc-connection-to-ms-sql-server-in.html

[Invisi]

Thanks Kiran, I'll check it.

In the mean time Cognos also fails to recognise datetime2... I must go back to the Stone Age with datetime... The horror.

[prikala]

Is your data source using "Microsoft SQL Server (OLE DB)" type connection?
Install a sqlserver client and try using a native connection, that should help.

[Invisi]

I indeed have the OLE DB option. When I change it to 2008 Native, I get the error that the connection string is invalid. This would be because it's unable to access the "testDataSourceConnection" database. This seems obvious to me, as this database is missing on our server... Where does this error originate from? Something I failed to install, or something I forget to put in the settings? I put in the name of the database...  :-\

[Invisi]

Anybody?

[khabaleshri]

The datatype is read only in FM as this is set by the database.
You could try editing the query definition and casting the item to the
datatype you require.