MS SQL Problems

[Gernwax]

Hey,
Don't know if this is the best place to post this, but would appreciate any help. I am getting the following error when i try and execute some SQL on MS-SQL 2000:

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

Here is my SQL:
INSERT INTO [DB1].[dbo].[Applications] (Application_Name)

SELECT DISTINCT([DisplayName]) as Application_Name FROM [DB2].[dbo].[Products]

WHERE [DisplayName] NOT IN (SELECT Application_Name FROM [DB1].[dbo].[Applications])

[CaseyRyan]

Looks like there are different collations on the two databases you're using (DB1 and DB2). Sql server can't resolve the conflict.

One possible solution is to select everything from DB2 into a DB1 temp table and then do the comparison inside of DB1.

USE DB1 
SELECT DisplayName INTO #tmpDB2Products FROM [DB2].[dbo].[Products] 
-- 
-- Now Insert with Select using the temp table  
INSERT INTO [DB1].[dbo].[Applications] (Application_Name)  
SELECT DISTINCT([DisplayName]) FROM #tmpDB2Products 
WHERE [DisplayName] NOT IN (SELECT Application_Name FROM [DB1].[dbo].[Applications]) 
--  
-- Drop the Temp Table  
DROP TABLE #tmpDB2Products 

I don't have 2 databases with different collations to try this with :) so the code may need some tweaking.

Another way to do it would be to specify the explicit collation when you are doing your select.
You can do this using the COLLATE command. This may be the easiest way, but I'm the least familiar with it so I'm inclined to try the method above first. For the COLLATE command, you would have to determine which are the different collations (they can be at the database, table or column level). Then take the column in DB2 that you're querying and use hte COLLATE command to convert it into the same collation as DB1.

-=casey=-

[Gernwax]

Hi,
thanks, i fixed it in the end by adding COLLATE Latin1_General_CI_AS to the where statement.

I didn't understand why the error was given originally though as all tables and database on the server have that collation as the default.