Collation Conflicts

[JonMusto]

Hi All,

I wonder if anyone can help me?

I'm trying to create a view, which is a union of two other views:

SELECT *
FROM dbo.V_USERS_INTERNAL
UNION ALL
SELECT * FROM dbo.V_USERS_EXTERNAL

When i try to create the view a get a collation error.

To be more precice, when i run the SQL in Query Analyzer i get the following error.

Server: Msg 457, Level 16, State 1, Line 1
Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.

Now, the two views i'm joing up look at two T_USER tables in different databases. I checked the collation names of both databases and they match 'SQL_Latin1_General_CP1_CI_AS'.
So i checked the collation names of the induvidual char types in both databases... And they all match too...
Is there something i'm missing???

Any help would be great, Cheers,
Jonathan.

 

[vongrunt]

First check view definitions. UNION requires that columns must be specified in exact order. If first view returns columns ABC and second ACB, UNION will attempt to match A-A, B-C and C-B respectively. Just one of arguments not to use SELECT *.

If column has no specified collation, it takes database default. If col and DB collation aren't coercible, implicit conversion can fail. Are DB and column collations different?

 

[JonMusto]

I've just checked that and they are in the correct order. And the collation names match up, everything is 'SQL_Latin1_General_CP1_CI_AS'.

Just one thing.

One of the tables doesn't have a column that the other table does so i'm substituting that value with a string, could this be affecting it in anyway?? See Below ('INTERNAL' AS F_Organisation):

SELECT LiveHydra.dbo.T_USERS.F_REC_ID, LiveHydra.dbo.T_USERS.F_LOGON_NAME AS F_LoginName,
LiveHydra.dbo.T_USERS.F_FULL_NAME AS F_FullName, LiveHydra.dbo.T_USERS.F_EMAIL, LiveHydra.dbo.T_SITE.F_NAME AS F_Site,
'INTERNAL' AS F_Organisation, LiveHydra.dbo.T_USERS.F_REC_DEL
FROM LiveHydra.dbo.T_USERS LEFT OUTER JOIN
LiveHydra.dbo.T_SITE ON LiveHydra.dbo.T_USERS.F_REF_SITE = LiveHydra.dbo.T_SITE.F_REC_ID

 

[vongrunt]

This will give you collations for all columns in views:

select A.name, B.name, B.collation
from sysobjects A
inner join syscolumns B on A.id=B.id
where A.name in ('V_USERS_INTERNAL', 'V_USERS_EXTERNAL')
order by A.name, B.colid