Compare feilds in seperate databases on seperate servers

Hi,
I want to run an update script where a field in a table in a database on a
server is equal to another field in a table in a database on a seperate
server. Here are the details:
Server 1:
Server name - Server1\Logi
Database - Ascent
Table - _customers
Field - email
Server 2:
Server name - Server2\Web
Database - ProductCart
Table - customers
Field - email
I want to update a field called 'flag' to equal 1 where the email addresses
are equal.
Can anyone help?To do this right we'd have to see more DDL - particulary interested in keys.
ML
http://milambda.blogspot.com/|||Hi
assuming you are on Server1\Logi, you can do something like
sp_addlinkedserver Web, @.srvproduct='', @.provider='SQLNCLI',
@.datasrc='Server2\WEB'
update _customers set flag = 1
from _customers a inner join Web.ProductCart.dbo.customers b on a.email =
b.email
exec sp_dropserver Web
Note that you should take care about proper credentials when connecting to
the linked server - read BOL about sp_addlinkedserver sproc. It is possible
also that it might be better to join tables on other column[s], but you did
not give any info on this.
HTH
Peter|||Hi Peter,
Thanks for the reply. I ran your script but got the following error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
Any ideas what this means?
Darren
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:OAH9afjAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> Hi
> assuming you are on Server1\Logi, you can do something like
> sp_addlinkedserver Web, @.srvproduct='', @.provider='SQLNCLI',
> @.datasrc='Server2\WEB'
> update _customers set flag = 1
> from _customers a inner join Web.ProductCart.dbo.customers b on a.email =
> b.email
> exec sp_dropserver Web
> Note that you should take care about proper credentials when connecting to
> the linked server - read BOL about sp_addlinkedserver sproc. It is
> possible also that it might be better to join tables on other column[s],
> but you did not give any info on this.
> HTH
> Peter
>|||You have to make sure that they are using the same collation to join
them, sample below:
Select * FROM
sometable localtable
Inner join
SomeotherServer.Database.Owner.SomeTable linkedtable
WHERE linkedserv.Somecolumn COLLATE SQL_Latin1_General_CP1_CI_AI =3D
localtable.somecolumn COLLATE SQL_Latin1_General_CP1_CI_AI
Normally you don=B4t need to specify that on both sides if you just
specify the collation on the side that is different to this on your
local server and vice cersa.
HTH, jens Suessmeyer.|||This means that the two databases use different collations. Look up
collations in Books Online, there you'll also find the COLLATE keyword, whic
h
you can use to solve the problem.
Something like that:
update _customers set flag = 1
from _customers a
inner join Web.ProductCart.dbo.customers b
on a.email = b.email collate <collation
name>
The collation name is displayed in database properties in Enterprise manager
.
ML
http://milambda.blogspot.com/|||Thanks guys, i'll go check it out.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:70933017-66F0-4438-B8EE-E5824B77E3C9@.microsoft.com...
> This means that the two databases use different collations. Look up
> collations in Books Online, there you'll also find the COLLATE keyword,
> which
> you can use to solve the problem.
> Something like that:
> update _customers set flag = 1
> from _customers a
> inner join Web.ProductCart.dbo.customers b
> on a.email = b.email collate <collation
> name>
> The collation name is displayed in database properties in Enterprise
> manager.
>
> ML
> --
> http://milambda.blogspot.com/