This worked out perfectly. Thanks a lot
I figured out one more way to accomplish this.
This is for notifying the SQL Server restart (not Agent) and can be implemented like this -
1. Set the “scan for startup procs” value to ‘true’
This can be done in the following way –
a. Right click on the server instance in management studio’s object explorer.
b. Go to Properties --> Advanced
c. Change value of ‘Scan for Startup Procs’ to ‘True’ and click Ok.
2. Stop and restart SQL Server for the change to take effect.
3. Create a procedure in the master database which sends the required mail. (This procedure should be in the master database only). Let the procedure name be P
4. Set procedure P for autoexecution (to be run when the server’s startup) using the procedure sp_procoption.
This will bind procedure P to startup, and P will be executed every time the server starts up.