Post reply

SQL Server Agent 'raiserror' question

  • January 28, 2015 at 9:21 am

    #300652

    Let’s say I want to run a sql agent job that immediately notifies a user via email that a specific change occurred in the database application. In order to do this, I would run the job every 5 minutes where:

    Step 1: If query data does not exist, raiseerror – no records found.

    Otherwise:

    Step2: run query, send results in an email.

    In setting it up this way, I know that 99.99% of the time, I’m going to generate that raiseerror every 5 minutes. Is that taxing on the system? And/or will that create problems? Is there a better way to do it?

    I don’t have a specific example. I’m just trying to conceptualize possibilities for future projects.

  • January 28, 2015 at 10:02 am

    #1773748

    not finding data does not sound like an "error" to me in this case, so raiserror is probably no what you really want.

    i think you just want to send an email if the data exists, right?

    something like this is a rough framework, which only sends if data is detected.

    you also need logic to populate the tracking table, so if you run this every minute, it doesn't detect the same event over and over again.

    DECLARE @LastScanned DATETIME,

    @err VARCHAR(100) = '';

    SELECT @LastScanned = --SELECT

    MIN(DateExported)

    FROM [DBA_Utilities].[dbo].[ExportHistory]

    WHERE IsProcessed = 0

    IF @LastScanned IS NULL

    SELECT @LastScanned = --SELECT

    MAX(DateExported)

    FROM [DBA_Utilities].[dbo].[ExportHistory]

    IF @LastScanned IS NULL

    SELECT @LastScanned = dateadd(mm,-5,getdate()) --in the last five minutes if null everywhere else/first time gettign started

    IF EXISTS(SELECT * FROM DatabaseName.dbo.TableToWatch WHERe CreatedDate >=@LastScanned OR UpdatedDate >= @LastScanned)

    BEGIN

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='My DBMail Profile Name',

    @recipients='Lowell The Code Monkey<lowell@somedomain.com>;',

    @subject = 'TableToWatch Event: Changes Detected For Tracked Tables: Export Job Kicked Off',

    @body = 'Sanity check: This email notification for TableToWatch',

    @body_format = 'HTML';

    INSERT INTO [DBA_Utilities].[dbo].[ExportHistory] SELECT 'TableToWatch',getdate()

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply