Union Issue with Different Data Types

[ens]

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.

[ens]

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). 

[ens]

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.

[adam_mc]

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.