Transaction between multiple servers

Transact-SQL
1

Hello
I have a server where 5 instances are installed
On server 1, I have 2 databases: DB1 and DB2
On server 2, 3 I have 1 database that has the same DB1 name

I created 3 stored procedures on DB2 database of server1

Procedure 1 reads DB1 from server 1 and DB2 from server 1, processes and returns informartions (insert into table in DB1 and update table in DB2)

Procedure 2 reads DB1 from server 2 and DB2 from server1, processes and returns informartions (insert into table in DB1 of server 2 and update table in DB2)

Procedure 3 reads DB1 from server 3 and DB2 from server1, processes and returns informartions (insert into table in DB1 of server 3 and update table in DB2)

I wrote the procedure correctly and I mention the name of the server I'm reading or writing

example:

select col from [server1]. [db1] .dbo.table1, [server1]. [db2] .dbo.table2 .....

insert into [server2]. [db1] .dbo.table1 ....

My problem

Some records are inserted on the server 3 for example while they must be on the server 2 or 3

I did not understand what happened

I hope my request is clear

thank you for helping me

NB: do linked servers affect what happened?

1 Like
2

Replication between 2 and 3?

3

no i d'ont use replication

2 DB different

4

Then I guess there's a problem in your sequence of stored procedure calls. hard to know with what you've posted.

1 Like
5

Interesting question. I agree with gbritton that there is something unclear about the layout of your server and intances in general and the sequence of the stpc calls in particular. Please explain in more details the setup of the server. Thank you