June 25, 2009 at 4:02 pm
We receive replication data from an external source and I want (you) to write a query that will
1. check to see that the data is current by comparing the "startedon" field to the current date, and
2. send an e-mail notification when the data is not current.
I'm able to get the logic in my head but not able to translate that into tsql just as yet. If the diff between the max value of startedon and the current date is greater than 2, then the e-mail should fire off.
Running SQL 2005 sp3...
June 25, 2009 at 4:21 pm
rodan (6/25/2009)
We receive replication data from an external source and I want (you) to write a query that will...
You are kidding, aren't you?
Please read this: The flip side
June 25, 2009 at 4:35 pm
Sorry, I was able to figure out the tough part for myself, too quick to ask for help.
select
DATEDIFF(day,max(startedon), GETDATE()) AS NumberOfDays
from dbo.table
June 25, 2009 at 4:44 pm
Now we get together 😉
Just don't see any question in your current post. It returns the count of days between the maximal statdon and today (well, NOW).
Do you have any problems with this result? Doesn't it fit the desired result?
June 25, 2009 at 5:24 pm
I've settled on the following, which I've set to run each morning. Right now, I just get the email and glance at it to make sure the datediff is one.
I would like to find a way to only generate the e-mail when the datediff is greater than one. I'll gladly take suggestions to make that happen.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail',
@recipients = 'rodan@mail.com',
@query = 'select DATEDIFF(day, MAX(dbname.dbo.FRQuoteRevision.StartedOn), GETDATE()) as NumberDays
from dbname.dbo.frquoterevision',
@subject = 'Replication',
@body = 'If the number of days is greater than 1, replication may be failing',
@attach_query_result_as_file = 0 ;
June 25, 2009 at 5:55 pm
Grasshopper,
Will something like this work? I know it runs the command SQL twice but maybe it's worth it.
DECLARE @ProblemFound INT
SELECT @ProblemFound =
CASE DATEDIFF(day, MAX(dbname.dbo.FRQuoteRevision.StartedOn), GETDATE())
WHEN 1 THEN 0
ELSE 1
END
FROM dbname.dbo.frquoterevision
IF @ProblemFound > 0 -- If problem found
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail',
@recipients = 'rodan@mail.com',
@query = 'select DATEDIFF(day, MAX(dbname.dbo.FRQuoteRevision.StartedOn), GETDATE()) as NumberDays from dbname.dbo.frquoterevision',
@subject = 'Replication',
@body = 'If the number of days is greater than 1, replication may be failing',
@attach_query_result_as_file = 0 ;
END
June 25, 2009 at 6:38 pm
I find this down right appalling:
rodan (6/25/2009)
We receive replication data from an external source and I want (you) to write a query that will1. check to see that the data is current by comparing the "startedon" field to the current date, and
2. send an e-mail notification when the data is not current.
Take the time to read the blog post that Flo told you to read. We are not slaves to be commanded, nor are we here to do your job for you.
If you have a problem and need help, then fine, we will help you, but You have to help us to do that. We will show you where your logic may be incorrect. We may help you correct a problem that you just can't see because of the Forest and Tree syndrome many of us encounter at times.
I also find this a little disturbing:
DBA with OCD (6/25/2009)
Grasshopper,Will something like this work? I know it runs the command SQL twice but maybe it's worth it.
DECLARE @ProblemFound INT
SELECT @ProblemFound =
CASE DATEDIFF(day, MAX(dbname.dbo.FRQuoteRevision.StartedOn), GETDATE())
WHEN 1 THEN 0
ELSE 1
END
FROM dbname.dbo.frquoterevision
IF @ProblemFound > 0 -- If problem found
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail',
@recipients = 'rodan@mail.com',
@query = 'select DATEDIFF(day, MAX(dbname.dbo.FRQuoteRevision.StartedOn), GETDATE()) as NumberDays from dbname.dbo.frquoterevision',
@subject = 'Replication',
@body = 'If the number of days is greater than 1, replication may be failing',
@attach_query_result_as_file = 0 ;
END
Some one is actually trying to help you still even though you personally haven't done a thing as of yet. Now, here I become a hypocrite because I'm going to provide you with a slight change to the above code:
DECLARE @ProblemFound INT;
SELECT
@ProblemFound = DATEDIFF(day, MAX(dbname.dbo.FRQuoteRevision.StartedOn), GETDATE())
FROM
dbname.dbo.frquoterevision;
IF @ProblemFound > 1 -- If problem found
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail',
@recipients = 'rodan@mail.com',
@subject = 'Replication',
@body = 'Number of days is greater than 1, replication may be failing'
END;
Next time you want help, be sure you have read and understood what it is we are here for, and it isn't to do your job, but to help you learn so that you can do a better job.
June 25, 2009 at 6:39 pm
rodan,
Let me take the working statement of "DBA with OCD" an add some slight improvements. To ensure that you don't get a mail if number of days is less than one and statement is executed just once.
DECLARE @CountOfDays INT
SELECT
@CountOfDays = DATEDIFF(DAY, MAX(dbname.dbo.FRQuoteRevision.StartedOn), GETDATE())
FROM dbname.dbo.frquoterevision
IF (@CountOfDays > 1) -- If problem found
BEGIN
DECLARE @statement VARCHAR(100)
SELECT @statement = 'SELECT ' + CONVERT(VARCHAR(10), @CountOfDays) + ' AS NumberOfDays'
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'Mail',
@recipients = 'rodan@mail.com',
@query = @statement,
@subject = 'Replication',
@body = 'If the number of days is greater than 1, replication may be failing',
@attach_query_result_as_file = 0 ;
END
Edit: Lynn was some minutes too fast for me.
June 26, 2009 at 9:35 am
Thanks for the help guys - sorry about the original message - I was really just trying to be funny but I have nothing but respect for you all. Forgot to put in the 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply