Compare feilds in seperate databases on seperate servers

16 Dec 2005 11:08 AM

Darren Savery

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?

16 Dec 2005 11:27 AM

ML

To do this right we'd have to see more DDL - particulary interested in keys.

ML

---
http://milambda.blogspot.com/

16 Dec 2005 11:59 AM

Rogas69

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

16 Dec 2005 12:27 PM

Darren Savery

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

Show quote

"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
>
>

16 Dec 2005 12:59 PM

Jens

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 =
localtable.somecolumn COLLATE SQL_Latin1_General_CP1_CI_AI

Normally you don´t 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.

16 Dec 2005 1:05 PM

ML

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/

16 Dec 2005 1:36 PM

Darren Savery

Thanks guys, i'll go check it out.

Show quote

"ML" <M*@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/