March 28, 2006 at 11:50 pm
i have a subquery like this :
select top 100 percent clientid from table1 where
table1.userinfo='123546' and clientid not in (select clientid from #tmpTable)
order by clientid desc
in the #tmpTable i have : clientid and a column called "datatype"
with some rows!
the error that i recive is :
"cannot resolve collection conflict for equal to operation"
what is the problem?
thnaks i nadvance
peleg
March 30, 2006 at 6:24 am
the collation of the column userinfo is different from the default you need to cast it to the same collation. If the difference in collation isn't by design you can change the collation using a collation script/app in the scripts section: http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=471
Phil Nicholas
March 31, 2006 at 8:22 am
Just as an aside, the use of the 'In' function in your example is really a bad choice for what you are trying to achieve.
You would be far better off using a derived table (otherwise known as a sub-query), for example,
select top 100 percent clientid
from table1
Left outer join #tmpTable tmp on table1.clientid = tmp.clientid
where tmp.clientid Is Null
order by clientid desc
If #tmpTable contains duplicate clientid's then replace #tmpTable in the statement above with (Select Distinct clientid From #tmpTable)
This will be a far more efficient query because the 'In' function is called multiple times, which means it would have to re-evaluate the select statement for each record in your Table1!!
Phil's comments regarding your collation issue still stands this is really just icing...
Cheers
Rob
March 31, 2006 at 10:24 am
Another possible problem....#tablename creates a LOCAL temporary table. ##tablename creates a GLOBAL temporary table. You could be having a problem with your temporary table being out of 'scope'.
Try changing it to a global temporary table and see if that solves the issue.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply