Hello,

Could you help me on how to update a table in different servers at the same time ?

+-------+ | S1 | <------ TBLA | | | +-------+ | | +-------+ | | S2 | | | | TBLB <-+ +-------+

Updating the TBLA in the server S1 will also update the TBLB server S2.

I appreciate your help.

-- Anonymous, February 02, 1998

Answers

Re: Connecting to a different server

Alexandro,



It can be done rather easily if the update is small.  8 million rows on the
other hand will nuke your log.  If need be you could do it in small chunks.



What you will need to do is execute a stored procedure on the remote server in 
addition to executing it on the local server. For example, you have two servers 
S1 and S2. At server S1 you can execute a procedure on server S2 with the 
following syntax:



EXEC S2.dbname.owner.procname



You can also INSERT based on result from remote execution, like:
INSERT tblname
  EXEC S2.dbname.owner.procname



You might also be able to use a sp_sqlexec, which EXECutes the string sent
into it (max 255 characters).
INSERT tblname
  EXEC S2.dbname.dbo.sp_sqlexec "SELECT * FROM authors"



You need to configure the involved SQL Servers for "remote access",
check out chapter 10 in Administrators Companion. I have also explained 
the configuration for "remote access" below.



If you need multiserver transaction integrity, check out the MSDTC service
and the BEGIN DISTRIBUTED TRANSACTION command.



	Eric




Configuration for Remote Access



1. Make sure you have remote access on both servers.



sp_configure "remote access",1
go
reconfigure
go



(you will need to stop and start SQL Server for this to take)



2. On each server add the other one.



(on S1)
sp_addserver S2
go
(on S2)
sp_addserver S1
go



3. Map all of the logins that you need (proably just sa).



(on S1)
sp_addremotelogin S2,sa,sa
go
(on S2)
sp_addremotelogin S1,sa,sa
go

-- Anonymous, February 02, 1998