2000 Servers, 1 is case-sensitive and the other is case insensitive.
The ci server is doing the transfer and has the other set up as a
linked server.
I tried to run the query
insert into <targettablename>
select col1,col2,... from ciserver.dbname..tablename
where col1 not in (select col1 from <targettablename>
to which query analyzer responds
Cannot resolve collation conflict for equal to operation
I understand that this issue exists because of the cs/ci issue. I
have told the ci server to use remote collation in the linked server
properties but it still doesn't work. Is there an easy way to make
this work? I know I could use DTS to pull the data over and process
it locally but given that these machines sit next to each other
physically and share a LAN dedicated to server traffic it kind of made
sense to let them use each other's databases directly.
TIAThis is a multi-part message in MIME format.
--=_NextPart_000_005F_01C373B0.E8A40ED0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Not sure if this will help or not, but I've run into this problem as =well. Below is an insert/select statement I use and change the collation =on the statement for joins.
INSERT INTO TBLMEMBERCONTACT(FirstName, memberid, ISPRIMARY, Phone1, =Phone1Type, Fax, Title)
select ISNULL(NULLIF(contactperson, ''), 'N/A'), MEMBERID, 'Y', =PhoneNumber, 'Phone', FaxNumber, NULLIF(Responsibility, '')
FROM MONTHLYHIST..TBLMEMBERS
where contactperson COLLATE Latin1_General_CI_AI not in (select
firstname from tblmembercontact)
"Matthew Speed" <mspeed@.mspeed.net> wrote in message =news:knihlvgg8bvtrm290mg98m1096loma486n@.4ax.com...
> I have been asked to set up a routine to transfer data between two SQL
> 2000 Servers, 1 is case-sensitive and the other is case insensitive.
> The ci server is doing the transfer and has the other set up as a
> linked server.
> > I tried to run the query
> > insert into <targettablename>
> select col1,col2,... from ciserver.dbname..tablename
> where col1 not in (select col1 from <targettablename>
> > to which query analyzer responds > > Cannot resolve collation conflict for equal to operation
> > I understand that this issue exists because of the cs/ci issue. I
> have told the ci server to use remote collation in the linked server
> properties but it still doesn't work. Is there an easy way to make
> this work? I know I could use DTS to pull the data over and process
> it locally but given that these machines sit next to each other
> physically and share a LAN dedicated to server traffic it kind of made
> sense to let them use each other's databases directly.
> > TIA
--=_NextPart_000_005F_01C373B0.E8A40ED0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Not sure if this will help or not, but =I've run into this problem as well. Below is an insert/select statement I use and =change the collation on the statement for joins.
INSERT INTO TBLMEMBERCONTACT(FirstName, =memberid, ISPRIMARY, Phone1, Phone1Type, Fax, Title)select ISNULL(NULLIF(contactperson, ''), 'N/A'), MEMBERID, 'Y', PhoneNumber, ='Phone', FaxNumber, NULLIF(Responsibility, '')FROM =MONTHLYHIST..TBLMEMBERSwhere contactperson COLLATE Latin1_General_CI_AI not in (selectfirstname from tblmembercontact)
"Matthew Speed"
--=_NextPart_000_005F_01C373B0.E8A40ED0--|||You can use the COLLATE clause to specify the collation to be used e.g.
insert into <targettablename>
select col1,col2,... from ciserver.dbname..tablename
where col1 COLLATE csserver_collation not in (select col1 from
<targettablename>)
where csserver_collation is the collation of your Case Sensitive server (or
you can do it the other way round)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:knihlvgg8bvtrm290mg98m1096loma486n@.4ax.com...
I have been asked to set up a routine to transfer data between two SQL
2000 Servers, 1 is case-sensitive and the other is case insensitive.
The ci server is doing the transfer and has the other set up as a
linked server.
I tried to run the query
insert into <targettablename>
select col1,col2,... from ciserver.dbname..tablename
where col1 not in (select col1 from <targettablename>
to which query analyzer responds
Cannot resolve collation conflict for equal to operation
I understand that this issue exists because of the cs/ci issue. I
have told the ci server to use remote collation in the linked server
properties but it still doesn't work. Is there an easy way to make
this work? I know I could use DTS to pull the data over and process
it locally but given that these machines sit next to each other
physically and share a LAN dedicated to server traffic it kind of made
sense to let them use each other's databases directly.
TIA
