Configure a SQL Server Agent Operator
SQL Server Agent operators are used by SQL Server Agent to send notifications about events as they happen. Typically, this would include notifications that a SQL Server Agent Job failed during execution. You might also want to be notified if SQL Server Agent detects corruption on a database, or a log-full event, etc. The code in this post provides a simple method of creating an operator without using the point-and-click GUI, which might be a bit cumbersome, and can’t be easily scripted.
Pont au Change au Crepscule, by Edouard Cortès – theres an operator in there somewhere!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
USE msdb; DECLARE @DropExistingOperator bit = 0; DECLARE @OperatorName sysname = N'TestOperator'; DECLARE @EmailAddress sysname = N'person@company.domain'; DECLARE @ReturnCode int = 0; DECLARE @CategoryID int; BEGIN TRANSACTION; IF @DropExistingOperator = 1 BEGIN EXEC dbo.sp_delete_operator @OperatorName; END IF NOT EXISTS ( SELECT 1 FROM dbo.syscategories sc WHERE sc.name = N'DBA' AND sc.category_type = 3 --OPERATOR ) BEGIN EXEC @ReturnCode = dbo.sp_add_category @class = 'OPERATOR' , @name = N'DBA' , @type = 'NONE'; IF @ReturnCode = 0 BEGIN PRINT N'Created DBAs operator category'; END ELSE BEGIN GOTO failed END END SET @CategoryID = COALESCE( ( SELECT sc.category_id FROM dbo.syscategories sc WHERE sc.name = N'DBA' ), 0); IF NOT EXISTS ( SELECT 1 FROM dbo.sysoperators so WHERE so.name = @OperatorName AND so.category_id = @CategoryID ) BEGIN EXEC dbo.sp_add_operator @name = @OperatorName, @enabled = 1, @email_address = @EmailAddress, @category_name = N'DBA'; PRINT N'Added ' + @OperatorName + N' to the list of operators, with email address: ' + @EmailAddress + N'.'; END GOTO success failed: IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; PRINT N'Failed'; GOTO end_of_script success: PRINT N'Succeeded'; COMMIT TRANSACTION; SELECT OperatorName = so.name , so.email_address , CategoryName = sc.name FROM dbo.sysoperators so INNER JOIN dbo.syscategories sc ON so.category_id = sc.category_id ORDER BY sc.name , so.name; end_of_script: |
Specify the operator name and email address at the top of the script, via the @OperatorName and @EmailAddress parameters. If you want to replace an existing operator, set the @DropExistingOperator to 1.
The code above uses the built-in MSDB stored procedures, sp_add_category and sp_add_operator.
Check out the rest of our posts on SQL Server Agent. If you found this post helpful, please share it!