Collation Error 2

[Will192]

Here is my SQL:

CREATE TABLE #I (S CHAR(6))
INSERT INTO #I SELECT DISTINCT S FROM B
SELECT * FROM A WHERE S IN (SELECT S FROM #I)

Here is the error that I am getting:
Server: Msg 446, Level 16, State 9
Cannot resolve collation conflict for equal to operation.

I know that I could simplify these into one statement, but I have about 12 other SELECT statement that I will need to use the #I table in one way or another. I have simplified my code to post here. Preprocessing the subquery will greatly improve my performance on the other 12 statements. I have similar subquery setups in other procedures, but for some reason this one won't work.

Thanks in advance for any responses to this post.

Will Summers

 

[donutman]

Looks like there may be a difference in the column datatypes between A and #I.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Will192]

Both columns are CHAR(6). The only difference is that table A allows nulls and table B does not.

 

[donutman]

Filter those out with a derived table and see if that effects the result of the In clause.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[nikhilparchure]

The problem is that the collation setting of the tempdb and the DB you are using or different.


Possible Solution
1) Drop and recreate database if small in size
2) use of database default..

Hope this helps
Cheers

 

[donutman]

Wow, how where you able to conclude that nikhilparchure? Maybe that has happened to you? If that can happen then changing the collation order is something that should not be done lightly. I would think that linked servers would have a difficult time working between other brands of servers.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]