Help needed

Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:

> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)