Please help.
I am using 11.1.6.
When I try to add a SQL view to my data module via the data server connection I get the following error
"MSR-SMT-2190 Internal error trying to create smart metadata"
The SQL view is still available in the Datamodule window and when I add it to the module, I get the following Validation error
"XQE-DAT-0001 Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. at com.cognos.xqe.data.providers.relational.jdbc.JDBCTabularResult.analyzeSQLException(JDBCTabularResult.java:1612)"
Along with the usual War and Peace parts of the IBM error message!
To remove a lot of complication to fault find I created a very simple view in SQL to try to replicate, this is below,
-------
alter view DeleteME as
select
c.name,
j.SK as JobSK
from ss_dim_JobOrder j
left join ss_dim_ClientCorporation c on j.clientID= c.clientCorporationID and c.eDate = '2222-02-22'
-------
This still causes the error. The views work just fine if only selecting data from one table, as soon as the joins are added they fail.
I am at a loss, this is the first view I have tried to use in 11.1.6.
I have many views working just fine in an 11.1.3 install
----------------
After a bit more testing it seems that the error is being caused by the where clause. BUT only if the where clause is being used to filter by dates.
This is very very annoying that SQL code that works just fine on one version need re-writing.
Anoyone else having this problem, this loaded the data
alter view DeleteME as
select
c.name,
j.SK as JobSK
from ss_dim_JobOrder j
left join ss_dim_ClientCorporation c on j.clientID = c.clientCorporationID and c.eDate = CONVERT(DATE,'02/22/2222',101)
------------------------------
Jonathan berry
------------------------------