Colation problem

Hi,
I am using SQLServer 2000 with SP4 and I am getting a strange collation problem.
I have three tables, TEmployee, TMechanic and TManager, each with the two columns Firstname, Lastname which are both varchars.

I run the following query:
<PRE>
SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM ((
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TEmployee query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TCoManager query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM TMechanic query1 WHERE FIRSTNAME = 'John')
UNION
(SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM TEmployee query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM
TCoManager query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
)) OUTERUNION

</PRE>

I get the following error:
Cannot resolve collation conflict for column 2 in SELECT statement.

If I change my select statement to only have one column (doesn't matter which column) it doesn't happen.

Any ideas, greatly appreciated?I am betting the collations on the columns comprising your union query do not match. when someone created one of these tables they may have specified some column level collations. I think you are going to have to export the data from the problematic table recreate your table with the write collations and then reimport your data. However this may have been done for a reason. Got documentation?|||Hi Thrasymachus,
Thanks for getting back to me.
I have looked at the scripts and they look ok, they are just simply create table statements with n oreference to collation.
So I need to know what does SQLServer set them to, or what does it think they are?
Ok this sounds like a stupid question so forgive, how do I check the collation levels for the columns?
What's interesting is a similar query works fine:

SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM (
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TEmployee query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM
TCoManager query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
FROM TMechanic query1 WHERE FIRSTNAME = 'John'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM TPerson query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
FROM
TCoManager query2 WHERE FIRSTNAME = 'Michael'
UNION ALL
SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
) OUTERUNION

Note the differences are:
I have changed the "Union" keyword in the middle to a "Union All" and removed inner brackets.

Any help greatly appreciated.|||execute sp_help sp in both table and check the difference.

exec sp_help tablename|||I have checked the collations, of all the tables, they are all Latin1_General_CI_AS. I think the problem is because SQLServer decides to change the collation in one of the sub selects. As the problem doesn't happen if I limit the number of nested sub selects to one.
Does anybody know how I get the default collation for a database, i.e. not just the collation for a column / table?
Thanks|||SELECT DatabasePropertyEx (http://msdn2.microsoft.com/en-us/library/ms186823.aspx)('master', 'Collation')
-PatP|||Hi,

I wonder if the problem might be with tempdb... is the default collation for tempdb different from your user database ? SQL might be storing intermediate data in tempdb, particularly if you are using UNION vs. UNION ALL.

Bill