RE: Problem with different collation
From: Baisong Wei[MSFT] (v-baiwei_at_online.microsoft.com)
Date: 04/08/04
- Next message: Martin Kraus: "List of active SQL statements"
- Previous message: Lasse Edsvik: "V<>W collation?"
- In reply to: David N: "Problem with different collation"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 08 Apr 2004 08:48:12 GMT
Hi David,
As my understanding of your question, there is a query that will run on
linked servers with different collations and you encounter a error of
'Cannot resolve collation conflict' error, right.
I have to say it is an known issue of the SQL Server 2000. But there is
some workaround to deal with this problem.
1.One workaround to this problem is you could add the collation for the
linked server in your query. Here is a test I take.
In my local computer, my database 'PUBS' is with Collation
'SQL_Latin1_General_CP437_BIN'. I add ther SQL Server on my notebook
'archer-ibm'.I create one alias 'IBM' by Client Network Utility for the
default SQL Server instance:
EXEC sp_addlinkedserver 'IBM', N'SQL Server'
GO
Then, on SQL Server 'IBM', I create a table with collation of
Danish_Norwegian_CI_AS:
create table tst2
(col varchar(100) collate Danish_Norwegian_CI_AS)
insert into tst2 values('NESAAS')
insert into tst2 values('White')
Then, I use the Query Analyzer to run the following query:
select * from pubs..authors a inner join IBM.test.dbo.tst2 b on
a.au_lname=b.col collate
I got the following error:
----------------------------------------------------------------------------
----------------------
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
Then I add the colation of the linked server table.
select * from pubs..authors a inner join IBM.test.dbo.tst2 b on
a.au_lname=b.col collate Danish_Norwegian_CI_AS
it will return the right result:
----------------------------------------------------------------------------
-----------------------
172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA 94025
1 White
2) Another workaround to it is to add the linked server through ODBC but
NOT OLEDB: I use the Client Network Utility to create another alias for the
default instance of SQL Server on 'archer-ibm ', which is 'IBMODBC'. Then
I add another linked server by:
EXEC sp_addlinkedserver
@server = 'IBMODBC',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=archer-ibm;UID=sa;PWD=Mypassword;'
GO
Then I could also get the result of the record I need:
select * from pubs..authors a inner join IBMODBC.test.dbo.tst2 b on
a.au_lname=b.col
So, I wonder if in your query of UNION ALL, you could add the collation at
the end of the query of the linked server. Or if you could avoid using the
OLEDB to add the linked server. Hope these coud be helpful in resolving
your problem.. If you still have questions, please feel free to post
message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
- Next message: Martin Kraus: "List of active SQL statements"
- Previous message: Lasse Edsvik: "V<>W collation?"
- In reply to: David N: "Problem with different collation"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
