Post reply

A little help on Alerts

  • February 21, 2006 at 5:31 pm

    #167207

    Hello,

    Can anyone explain how to get consistent performance using Alerts.  The custom errors and alerts I have created do not always send an email notification, nor does the "history" (Date Last Occurred) always seem to update to record the latest instance.

    This occurs even when I try refreshing the alert, or specify "with log" when running Raiserror(50006, 16, 1) in Query Analyzer.

    It's a mystery to me...

    Thanks

    Elliott

  • February 23, 2006 at 2:28 am

    #622834

    there are some pittfalls with alerts

    first of all, an alert only responds when it is enabled

    If you check the procs for creating/modifying alerts, you'll see that you can specify for the alert only to respond when fired from a certain database

    That might explain why the alert is not always responding.

    I use this sql-script to define and use alerts, in this case to start a job.

    Check out help to tighten use of  cmdshell  or sp_start_job 

     

    btw wat version of sqlserver are you using ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 23, 2006 at 7:44 am

    #622929

    Thanks for those ideas; when I go in tomorrow, I'll check out the "enabled" property.  I hadn't known it was there.

    We are running SQL 2000 SP3 Std Ed.

    btw--I saw the explanation of using alerts to start jobs, but I didn't see the script.  Did I miss something?

    Thanks

  • February 23, 2006 at 7:54 am

    #622934

     

    The mentioned URL should contain this :

    -- The goal is to avoid the use of sp_start_job in an application. So we have the application use RAISERROR to activate the job.

    --  1) because you can only start jobs you own

    --  2) we don't want to open the cmdshell to everyone

    --  3) we want control regarding jobs that run on our server.

    --  4) we want to have an indication how much is "ad-hoc"

    --

    --     to be adjusted : messagenumber

    --   jobname

    --    database_name

    --

    -- IMPORTANT : The first time you activate an alert, SQLServerAgent needs to be restarted.

    --            (needs also to be done when you have disabled all alerts and want to enable them again !)

    --

    -- IMPORTANT : When you disable a job, you have to disable the alert-related schedule

    --               or SQLAgent will write messages in it's log saying it cannot start the job.

    --   It will keep on doing so every _second_ until the job is reenabled. Eventualy you disk will get full !!!

    -- exec msdb.dbo.sp_help_alert

    go

    print @@servername

    USE Master

    go

    Declare @MessageNummer int

    Declare @jobname varchar(128)

    Declare @DbName varchar(128)

    set @MessageNummer = 60010   -- <-- ADJUST!!! (> 50000)

    set @jobname = 'NameOfJobToBeLaunched'  -- <-- ADJUST!!! (has to be existing)

    set @DbName = 'mydb'    -- <-- ADJUST!!! I only want the alert to fire when raised in the richt db.

    --

    Declare @wrkstr1 varchar(128)

    Declare @wrkstr2 varchar(128)

    set @wrkstr1 = 'UserAlert Requesting Job ' + @jobname + ' to be launched.'

    -- message toevoegen in Master

    exec sp_addmessage  @msgnum = @MessageNummer

        , @severity =  10 

        , @msgtext = @wrkstr1

        -- , @lang =  'language'

        , @with_log = 'true' 

        -- , @replace =  'replace' 

    set @wrkstr1 = 'UserAlert_Requesting_Job_' + @jobname

    set @wrkstr2 = 'Requesting Job ' + @jobname + ' to be launched.'

    -- Alert definiëren in MSDB

    exec msdb.dbo.sp_add_alert @name = @wrkstr1

        , @message_id = @MessageNummer

        , @severity = 0    -- must be 0 because we provide message_id

        , @enabled = 1     -- 0 = disabled / 1 = enabled

        , @delay_between_responses = 5  -- Seconden

        , @notification_message = @wrkstr2

        --, @include_event_description_in = 0

        , @database_name = @DbName

        --, @event_description_keyword ='Requesting Job ' + @jobname + ' to be launched.'

        , @job_name = @jobname

       -- , @raise_snmp_trap = raise_snmp_trap] not with SQL 7.0

       -- , @performance_condition = 'performance_condition'

        --, @category_name = 'Application Events'

    exec msdb.dbo.sp_help_alert

    go

    -- Cleanup alert

    -- check sp_delete_alert BOL

    --

    --

    -- usage

     use mydb

     go

      RAISERROR ( 60010  , 10 , 1 )

     

      go

    --

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 23, 2006 at 8:05 am

    #622947

    That looks good.  thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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