Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    INDIA
    Posts
    2

    Unanswered: Alerts In Ms-sql

    Hello Techies!
    I am a newbie to MS-SQL. doing MCA final proj ,

    Here I want some info about MS-SQL user defined alerts...

    Actually I have to design an alert that display message like

    "the warranty period has been expired for ------- good" based on getdate() and warranty period comparision.

    In blank I have to display the ID of product retrieved from table.

    I have tried sp_addalert ,but an error is occuring

    "Stored procedure 'sp_addalert' not found." Is it reqired to be an administrator to use Stored procedures (i.e. sp_)

    how to create user alerts ......


    Any help appreciated....

    with regards,
    Vasavi.

    " A good Scientist is a person with original ideas. A good Engineer is a person who makes a design that works with as few original ideas as possible.there are no prima donnas in Engineering"

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    the sp_add_alert stored procedure is located in the msdb database, so you have to make sure that you are connected to the msdb db before executing the procedure.

    eg:
    use msdb
    go
    exec sp_add_alert ...
    Johan

  3. #3
    Join Date
    Jul 2003
    Location
    INDIA
    Posts
    2
    Originally posted by jora
    the sp_add_alert stored procedure is located in the msdb database, so you have to make sure that you are connected to the msdb db before executing the procedure.

    eg:
    use msdb
    go
    exec sp_add_alert ...

    VASAVI:

    Hello Jora,
    Thank you for ur help.
    So nice of You...
    I have used 'Use msdb'
    But an error occurred like this
    "Error 154 : A USE DATABASE statement is not allowed in a procedure or a tirgger"
    Then I tried like this :
    EXEC msdb.dbo.myprocedure

    where 'myprocedure' is a procedure written by me in msdb ,consisting stmt :
    ' EXEC sp_addalert @name='myalert',@message_id=50010,'@severity=null'

    I have added this 'myalert' from menu Server+Alerts/Operators+add new and I have selected my own database in database option there.

    But an error is occuring like this
    'Msg 14528, Level 16, State 1
    The @name or @new_name parameter you have supplied is not unique.'

    I have checked the alerts list ,but there it is unique.


    And one more thing I have a table like this TabProduct where there are fields startdate,enddate and difference gives warranty period. I have to design an alert to display "Warranty has been expired for ---- product",and i have display Productid in blank from tabProduct. Is it posibble to write user alert to check each and every row in tabProd(i.e for each product comparing warranty period with getdate function everyday and display alert when warranty period for that product expires.

    Awaiting Help....


    Many Regards,
    Vasavi.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •