Vous êtes ici :
Configurer database mail en ligne de commande, plus pratique qu’avec la multiplicité de boîtes de dialogue…
Transact-SQL
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 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
/*------------------------------------------------------------------- [SCRIPT] Configure database mail [DATABASE] msdb [Date] 20210815 [DESCRIPTION] configure database mail and alerting configuration [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ -- Change variables values in this section ------------------------------------------ -- mail SMTP server configuration DECLARE @server_email_address NVARCHAR(128) = N'sqlalerting@mydomain.com' -- notifications : sender email address , @mailserver_name NVARCHAR(128) = N'smtpserver.mydomain.com' -- smtp server name or ip address , @smtpport INT = 25 -- smtp server port , @smtpenable_ssl BIT = 0 -- smtp server : enable ssl (0|1) , @smtpusername NVARCHAR(128) = NULL -- smtp server authentication username , @smtppassword NVARCHAR(128) = NULL; -- smtp server authentication password -- operator configuration DECLARE @operator_name NVARCHAR(128) = N'SQLAdmin' -- operator name , @operator_email NVARCHAR(128) = N'sqladmin@mydomain.com'; -- operator email ---------------------------------------------------------------------------------------- -- configure database mail EXEC sp_configure 'Show Advanced',1; RECONFIGURE EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE; IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysmail_profile WHERE name = N'AdminDBMail' ) EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'AdminDBMail' , @description = 'Notification mail profile'; ELSE PRINT 'Mail profile already existing, not modified'; DECLARE @display_name NVARCHAR(128) = N'SQL Alerting system - ' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)); IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysmail_account WHERE name = N'SQLAlerts' ) EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQLAlerts' , @description = 'SMTP account for SQL Agent Notifications' , @email_address = @server_email_address , @display_name = @display_name , @mailserver_name = @mailserver_name , @port = @smtpport , @enable_ssl = @smtpenable_ssl , @username = @smtpusername , @password = @smtppassword; ELSE PRINT 'Mail account already existing, not modified'; IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysmail_profileaccount PA JOIN msdb.dbo.sysmail_account A ON PA.account_id = A.account_id JOIN msdb.dbo.sysmail_profile P ON PA.profile_id = P.profile_id WHERE A.name = N'SQLAlerts' AND P.name = 'AdminDBMail' ) BEGIN EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'AdminDBMail' , @account_name = 'SQLAlerts' , @sequence_number = 1; EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1 , @databasemail_profile = N'AdminDBMail' , @use_databasemail = 1; END; ELSE PRINT 'Profile_account already existing, not modified'; IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysoperators WHERE name = @operator_name ) EXEC msdb.dbo.sp_add_operator @name = @operator_name , @enabled = 1 , @pager_days = 0 , @email_address = @operator_email; ELSE PRINT 'Operator already existing, not modified'; GO |
Table of Contents