Query Error: Collation Conflict (on tables on 2 different servers)

Hi all,
I have a query that I need to run where I join two tables that bothreside on different servers. I use an INNER JOIN statement to attemptto join these tables, but for some reason I am getting the followingerror message...
"Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Thanks
Tryst

SQL JOINs are from Rene Descartes math so you can only create INNER JOIN if the two tables are equal, if they are not equal you use OUTER JOIN. The other problem the error did not show is you need LINKED Server to run your query so your tables maybe equal to qualify for INNER JOIN but SQL Server is not seeing that because the servers are not linked. Try the links below to get started, the Microsoft link have the System stored procs you need to create LINKED Server. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3085211

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp

|||Hi Caddre, thanks for the reply.
I did manage to run the sp_addlinkedserver stored procedure to create a linked server instance.
The problem may be the column types between the two tables.
I have now tried using a LEFT OUTER JOIN, but still get the same error message...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
LEFT OUTER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Tryst
|||

It is Column collation related try these links to resolve it. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7ory.asp

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8818馠

query contains concatenated inner results

I have a table item(id, item_name, item_added_date, item_price).
The second table is item_viewedby(id, item_id, userid).
The second table holds the users who are viewing a certain item.
So data in first table item is like:
id item_name item_added_date item_price
1 toothbrush 01/01/2003 100
2 toothpaste 02/02/2003 125
And data in second table item_viewedby is like:
id item_id userid
1 1 jane
2 1 john
3 1 kim
4 2 jim
5 2 tim
I want to execute a query so that for each item I have a single row of
item_id item_price item_viewed_by
Where item_viewed_by is a semi_colon separated list of users who are
viewing a certain item.
So my result table is like
item_id item_price item_viewed_by
1 100 jane;john;kim
2 125 jim;tim
I am wanting to put this query in a stored procedure and call it from
my ASP.NET code.
If this is to be done using cursors then please help me with that, too
Thanks,
FredSee the following example which will give you a fair idea how can you
convert rows to columns.
Ex:
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'jane')
insert into tab values(1 ,'john')
insert into tab values(1 ,'kim')
insert into tab values(2 ,'nick')
insert into tab values(3 ,'jim')
insert into tab values(3 ,'tim')
go
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @.id int, @.old_id int
declare @.seq_num int
declare @.cats varchar(50), @.f_cats varchar(50)
select @.id=0, @.old_id=0,@.seq_num=0, @.cats='', @.f_cats=''
declare c1 cursor for
select id, cats from tab order by id
open c1
fetch c1 into @.id,@.cats
while @.@.fetch_status = 0
begin
If @.old_id <> @.id and @.old_id <> 0
begin
insert into #tmp values(@.old_id, @.f_cats)
select @.seq_num=0, @.f_cats=''
end
select @.f_cats = @.f_cats + case @.f_cats when '' then '' else ',' end + @.cats
select @.old_id = @.id
fetch c1 into @.id,@.cats
end
close c1
deallocate c1
insert into #tmp values(@.old_id, @.f_cats)
select * from #tmp
--
-Vishal
"Fred" <fredg1232003@.yahoo.com> wrote in message
news:2022fcea.0307281714.3529f2f@.posting.google.com...
> I have a table item(id, item_name, item_added_date, item_price).
> The second table is item_viewedby(id, item_id, userid).
> The second table holds the users who are viewing a certain item.
> So data in first table item is like:
> id item_name item_added_date item_price
> 1 toothbrush 01/01/2003 100
> 2 toothpaste 02/02/2003 125
> And data in second table item_viewedby is like:
> id item_id userid
> 1 1 jane
> 2 1 john
> 3 1 kim
> 4 2 jim
> 5 2 tim
> I want to execute a query so that for each item I have a single row of
> item_id item_price item_viewed_by
> Where item_viewed_by is a semi_colon separated list of users who are
> viewing a certain item.
> So my result table is like
> item_id item_price item_viewed_by
> 1 100 jane;john;kim
> 2 125 jim;tim
> I am wanting to put this query in a stored procedure and call it from
> my ASP.NET code.
> If this is to be done using cursors then please help me with that, too
> Thanks,
> Fred|||yes you can review it..........
if you get any issues just inform.
regards
Hari Sharma, India
Wangkhar@.yahoo.com (WangKhar) wrote in message news:<bb269444.0308040106.63f76743@.posting.google.com>...
> hkvats - I took the liberty of reviewing your code - hope you dont mind.|||This type of concatenation over rows is
very easy with the RAC utility for S2k.
No coding required.
Check out:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite released.
www.rac4sql.net

query around databases

I need to query tables on different databases, which all reside on the same database server. How to do this?

is it db1.tablex inner join db2.tabley ?

Hi dear,

try this

db1.dbo.tablex inner join db2.dbo.tabley

Thanks

Best Regards,

Muhammad Akhtar Shiekh