February 13, 2017 at 11:02 am
Hello Team,
I need to update a column of a table if it's null and do nothing if it's not null, not sure how to script that logic. Need some help
Table: [dbo].[ReplicationLatencyCheck]
Columns: [PublisherTime], [SubscriberTime], [ReplTime (Secs)]
1. I will have an agent job executing every minute to insert a getdate() in to [PublisherTime]--- at Server A
2. Need Second agent job executing continuously to check if [SubscriberTime] IS NULL--- at Server B
3. IF NULL it will insert a getdate() into [SubscriberTime] and calculate the difference and insert that into [ReplTime (Secs)]
This is what i already have:
USE DBATools
GO
INSERT INTO [dbo].[ReplicationLatencyCheck] (PublisherTime) VALUES (GETDATE())--- at Server A
UPDATE [dbo].[ReplicationLatencyCheck] SET SubscriberTime = (GETDATE()) WHERE SubscriberTime IS NULL--- at Server B
--WAITFOR DELAY '00:00:05';
UPDATE [dbo].[ReplicationLatencyCheck]
SET
[ReplTime (Secs)] = (SELECT DATEDIFF(second, PublisherTime, SubscriberTime)
FROM [DBATools].[dbo].[ReplicationLatencyCheck] WHERE [ReplTime (Secs)] IS NULL)
WHERE [ReplTime (Secs)] IS NULL
I need to be able to wrap up the last 2 update statement in a way that the updates only happen if SubscriberTime is NULL, I believe I'm very close. But i want to be able to avoid alerts/agent job failures since the job at subscriber will execute continuously
THANKS IN ADVANCE!!!
February 13, 2017 at 2:05 pm
Most everything I see in your post appears okay, except perhaps the UPDATE. Seems more appropriate to do it this way:
UPDATE RLC
SET RLC.[ReplTime (Secs)] = DATEDIFF(second, PublisherTime, SubscriberTime)
FROM [DBATools].[dbo].[ReplicationLatencyCheck] AS RLC
WHERE RLC.[ReplTime (Secs)] IS NULL;
There are some recent posts on this site that talk about UPDATE performing badly when an IMPLICIT JOIN is happening. Much better to use a table alias as the target for the UPDATE keyword. Jeff Moden was the post author, btw.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2017 at 5:36 pm
sgmunson - Monday, February 13, 2017 2:05 PMMost everything I see in your post appears okay, except perhaps the UPDATE. Seems more appropriate to do it this way:
UPDATE RLC
SET RLC.[ReplTime (Secs)] = DATEDIFF(second, PublisherTime, SubscriberTime)
FROM [DBATools].[dbo].[ReplicationLatencyCheck] AS RLC
WHERE RLC.[ReplTime (Secs)] IS NULL;There are some recent posts on this site that talk about UPDATE performing badly when an IMPLICIT JOIN is happening. Much better to use a table alias as the target for the UPDATE keyword. Jeff Moden was the post author, btw.
Thank you!!!
February 14, 2017 at 7:23 am
MVP_enthusiast - Monday, February 13, 2017 5:36 PMsgmunson - Monday, February 13, 2017 2:05 PMMost everything I see in your post appears okay, except perhaps the UPDATE. Seems more appropriate to do it this way:
UPDATE RLC
SET RLC.[ReplTime (Secs)] = DATEDIFF(second, PublisherTime, SubscriberTime)
FROM [DBATools].[dbo].[ReplicationLatencyCheck] AS RLC
WHERE RLC.[ReplTime (Secs)] IS NULL;There are some recent posts on this site that talk about UPDATE performing badly when an IMPLICIT JOIN is happening. Much better to use a table alias as the target for the UPDATE keyword. Jeff Moden was the post author, btw.
Thank you!!!
Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply