First timer here, so pardon any trespasses....
I have what would seem to be a pretty simple Union of 5 columns (Project ID, FY, Period, Name, Amount). One is from a table loaded with a CSV file, and the other is a view into an operational system. I can get this to work with casting of the data, but if I make any modifications (IF THEN ELSE to create a Jan/Feb/Mar text field for the label or something similar to set up a specific sort order) the report fails.
I think there are actually two issues here...
1. The data type in Framework Manager from the View table shows a data type of Character Length 16 while the data table is nVarChar (50). I can cast this, and it works until I augment the data (as I mentioned above).
2. I also wonder if I have some SQL Server setting a little funky in the background (wild random guess).
I typically get this series of errors upon validation (though some reports will still run even showing this same error).
UDA-QOS-006
UDA-SQL-0115 Inappropriate SQL Request
UDA-SQL-0564 [Microsoft OLD DB provider for SQL Server]Deferred prepare could not be completed
UDA-SQL-0564 [Microsoft OLD DB provider for SQL Server]Statement(s) could not be complete (SQL State =42000 SQLERRORCODE = 8180)
UDA-SQL-0564 [Microsoft OLD DB provider for SQL Server]Cannot resolve the collation conflict between "SQL_LATIN1_GENERAL_CP850_BIN" and "SQL_LATIN1_GENERAL_CP1_AS_IS" in the union (SQL STATE = 42000, SQLERRORCODE=468)
QE-DEF-0459 CLL Exception
RQP-DEF-0177
UDA-SQL-0115 Inappropriate SQL Request
UDA-SQL-0564 [Microsoft OLD DB provider for SQL Server]Deferred prepare could not be completed
UDA-SQL-0564 [Microsoft OLD DB provider for SQL Server]Statement(s) could not be complete (SQL State =42000 SQLERRORCODE = 8180)
UDA-SQL-0564 [Microsoft OLD DB provider for SQL Server]Cannot resolve the collation conflict between "SQL_LATIN1_GENERAL_CP850_BIN" and "SQL_LATIN1_GENERAL_CP1_AS_IS" in the union (SQL STATE = 42000, SQLERRORCODE=468)
RSV_VAL Unable to find query information for the item Fiscal Year
RSV_VAL Unable to find query information for the item Month
RSV_VAL Unable to find query information for the item Name
Cognos 10.2 / SQL Server 2008 R2
Any ideas would be wildly appreciated - I am tearing out large clumps of hair.
I believe the Latin1 issue is typically about Date conversions. Which I am not doing, but are probable in the underlying data model (which is unimagably complex). And the Character Length 16 issue looks to be more about a mixed field. In my case, I have a Project Field which has items like 002200, 002200.001, and 002200.002.
Is it poor form to respond to yourself?
anyway, after kicking this around a bit (I dragged in a DBA for a quick brainstorm....
1. I think the issue is with the Character Length 16 data type coming from the view in our data. It causes some issues with a mere join, but the cast function seems to get around this. This will cause a False Negative - the report will run - it just fails with the errors from my original post.
2. When you throw this data field into a Join is when the issue appears to cause the report to fail (at least at some point).
We were able to test a few things and the 3rd party vendor uses a non-standard set up when they set up SQL Server. We set up the BI DB with the same collation setting as the original vendor DB and it fixed a number of conflicts.
It is absolutely not "poor form" to respond to yourself!
In fact, it is great when people have resolved their own issue because it becomes a resource for the entire community to utilize.
When I resolve any situation, whether from someone else or by myself, I have found it good practice to modify the original post to say RESOLVED: <Issue Name>.
In that way it becomes really easy for everyone else to see.
Thanks,
Adam.