Multiple Database query using in clause.... 1

[Jhankins]

If anyone can help. When using Query Analyzer the Top query and the bottom query both produce valid results but when run together i get a error .

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

The bottom returns a list of names joe,jack etc.

Any Help would be great


select d.clerkname,d.slatype,d.amount,d.invoicenum
from dt..reportingdata d
inner join dt..transactions b
on d.cardnumber=b.customer and d.invoicenum = b.invoicenum
where
clerkname in
(
SELECT distinct Clerk_Name
FROM [T C]..clerk_table c
INNER JOIN [T C]..terminals t ON
c.Terminal_Id = t.Id
INNER JOIN [T C]..company_names n ON t.Customer = n.Id
WHERE (t.Number LIKE '96%')
)

 

[LogicalmanUS]

Jhankins,

Is the collation set the same for both databases? I take it this is a SQL2K instance.

To check the collation for a database, right click the database name in EM, select Properties. Once the dialogue box opens, click the General Tab, at the foot of the tab you will see 'Maintenance' and the collation is there.

Logicalman

 

[Jhankins]

CAn you change the collation name so they are the same???

 

[LogicalmanUS]

Jhankins,

Yep, you change the collation on both the database or the table itself using the ALTER DATABASE or LATER TABLE statements.

Be careful when changing either level of collation as other DBs or objects refereing them will be affected. In these cases you may use the CAST statement together with the COLLATE statement to ensure same-collation String comparisons.

Logicalman

 

[bjd4jc]

LogicalmanUS-

Your post was helpful! Have a star! For the sake of ease I will post the syntax for using the collate with cast statement.

Here is an excerpt from BOL:

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS