Trying to automate copying a record to a linked server

Transact-SQL
1

Afternoon all,

I'm trying to write a Trigger to copy data from a table on 'DB Server A' to an identical table on 'DB Server B'. The 2 servers are linked and i can manually run a query and it works fine. However the Trigger i wrote (adapted) doesn't show any errors, i'm looking to trigger it on 'Status' being changed to 'Live'.

Any ideas where i've gone wrong or should i be doing this another way?

Many thanks

Dave

USE [TestDB]
GO
/****** Object:  Trigger [dbo].[Copy_Emp_DB_A_To_DB_B]    Script Date: 08/23/2022 15:43:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Copy_Emp_Svr_A_To_Svr_B] 
ON [dbo].[Employees] 
AFTER UPDATE 
AS
BEGIN
    IF UPDATE(Emp_Status)
    BEGIN
			INSERT INTO DBServerB.TestDB.dbo.Employees
			(Emp_Title, Emp_FName, Emp_LName, Emp_Address1, Emp_Address2, Emp_Address3, Emp_Postcode)
			SELECT  
			i.Emp_Title, i.Emp_FName, i.Emp_LName, i.Emp_Address1, i.Emp_Address2, i.Emp_Address3, i.Emp_Postcode
            FROM inserted AS i
			LEFT JOIN Deleted AS d 
            ON i.Emp_TID = d.Emp_TID
            WHERE d.Emp_Status <> 'Live' 
				  AND 
				  i.Emp_Status = 'Live';		
	END
END
2

The only thing I see that could be wrong is if Emp_Status could be NULL (which presumably it would never be anyway).

You say "error", how do you know you are getting an error? You could always add error trapping, using BEGIN TRY, etc.:


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Copy_Emp_Svr_A_To_Svr_B] 
ON [dbo].[Employees] 
AFTER UPDATE 
AS
SET NOCOUNT ON;
IF UPDATE(Emp_Status)
BEGIN
	INSERT INTO DBServerB.TestDB.dbo.Employees
	(Emp_Title, Emp_FName, Emp_LName, Emp_Address1, Emp_Address2, Emp_Address3, Emp_Postcode)
	SELECT  
	i.Emp_Title, i.Emp_FName, i.Emp_LName, i.Emp_Address1, i.Emp_Address2, i.Emp_Address3, i.Emp_Postcode
    FROM inserted AS i
	LEFT JOIN Deleted AS d 
    ON i.Emp_TID = d.Emp_TID
    WHERE (d.Emp_Status <> 'Live' OR d.Emp_Status IS NULL)
			AND 
			i.Emp_Status = 'Live';		
END
/*end of trigger*/
3

what application is sitting on top of this database? what account connects to this database and does it have the right perms. it works for you maybe because you are dbadmi/dbowner.

4

@ScottPletcher
Thats a good shout ref 'IS NULL' technically i should never be null but i'll add it to cover all enentualities!

One big problem i'm seeing, it appears to trigger on any update not just the 'status' changing to 'live'...

@yosiasz
I haven't broken out to the application yet, i'm still working in Management Studio :frowning:

5

The UPDATE trigger will fire for any UPDATE, it just shouldn't do any INSERT unless the conditions in that SQL are met.

6

Morning,

Right i'm back on this now... OK after some head scratching and some reading i'm not convinced the problem lies with the Trigger. If I test the linked server's connection via SSMS then i get the sucseeded message, this is true testing both directions. It also works is i run the query manually, I can copy the requied information from Server A to Server B. However, i'm thinking the 'automated' trigger uses a different connection, DTC (Distributed Transaction Coordinator), which I don't yet understand...

My assumption was that if i cn run it manually then i can automate it....

Does that make sense, am i barking up the right tree...?

Many thanks

Dave

7

one idea

write a stored proc to
= copy into temp table #tables in destination
= then from temp table insert to destination table

schedule the stored proc in a job which runs every 5 minutes

8

DOH!!! How did i miss that one, Amazing thank you, its working :smiley: