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.
exec sp_add_alert ...
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 :
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.