Update a record in another SQL server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update a record in another SQL server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-13 : 09:01:18

Shantha writes "We are using SQL 6.5.

We have a SQL server and we want to seperate it into 2 servers. All the tables and SPs will be same. All the tables will be updated seperately except only one. This table should be updated at the same time in both servers. When this table is updated in one server updated record should be written to the other server at the same time. Is there a way to do it ?

Thanks

Shantha."

Nazim
A custom title

1408 Posts

Posted - 2002-03-13 : 09:07:56

You can use triggers and follow four part naming convention and access the table from other server .

Servername.databasename.owner.tablename

HTH

--------------------------------------------------------------

shantha
Starting Member

2 Posts

Posted - 2002-03-27 : 14:30:32

It is not accepting the 4 part naming convension as you described.
4 part naming convension can be used only for Stored procedures as
servername.Databasename.owner.SPname which we can run a SP in a remote server ? Please clarify.

Thanks

Shantha.

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-27 : 14:48:52

Upgrade to 7.0, or better yet 2000 and use merge replication.

You might be able to do it in 6.5 with Merge replication, but I would seriously consider upgrading.

-Chad

qchau_98
Starting Member

6 Posts

Posted - 2002-03-27 : 15:47:19

I agree with Chadmat doing Replication is your best bet. That's what I did to my DB Servers. It works very well.

Good Luck.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-27 : 17:17:44

I wouldn't use sql server replication for this as it will give you a lot of administrative overhead for something simple - and may not even fulfil the requirement.
Also what do you do if you restore one of the databases?

You say you want them updated at the same time - i.e. you don't want the process to complete until both databases are updated? This means that if the remote server goes down you cannot update the local one either.
If that is the case then you are letting yourself in for a very difficult future and replication won't support this anyway. Better to redesign the system so that you can allow for a little lag between the two systems - a minute or so will make things a lot easier.

If you need the two in step then create an insert,update, delete SPs on the remote database

create insSP
@fld1 ... all fields
as
insert tblk (fld1) select @fld1, ...
go

create updSP
@fldPK ... PK fields
@fldn ... all fields
as
update tblk
set fldn = @fldn ...
where fldPK = @fldPK
go
similar for delete

in the source table include triggers
create trigger ins_tbl
as

declare @fldPK ... @fldn...

set @fldPK = min(@fldPK) from inserted
while @fldPK <= (select max(fldPK) from inserted)
begin
select @fldPK = max(fldPK) from inserted where fldPK > @fldPK
select @fldn = fldn ... from tbl where fldPK = @fldPK
exec svr.db.owner.insSP @fldPK,...,@fldn...
end
go

do similar things for deleted/updated.
The example here is for single PK field - will work for compound though.
Can build script to auto-generate this if you need it for other tables.

If you can cope with a slight delay in synchronisation though it is better to transfer the data via staging tables. This will have a minimal impact on the source server, give an audit trail of what has been sent across and also be self recovering for the destination server - you just have to be careful about restoring the source server.
Create a table on the local and remote servers with the same structure as the table to transfer. The local one has and additional identity column (id) and action column and also a status field default ' '. The remote has the same but the id column is not identity.
The triggers just insert into this table and include the action I,U,D.
An SP on the local server gets records to transfer given an id.

create procedure trStagingTbl
@id int
as

set rowcount 100
select *
from StagingTblSnd
where id > @id
order by id
go

the remote database has a sceduled task which calls an SP

create procedure GetStagingTbl
as
declare @id int
select @id = max(id) from StagingTblRcv
insert StagingTblRcv
exec svr.db.owner.trStagingTbl @id

while exists (select * from StagingTblRcv where status = ' ')
begin
select @id = min(id) from StagingTblRcv where status = ' '

do inserts/updates/deletes here
Can do all of a single type in one block as long as the PK is not repeated.

end

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources