sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
 
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
	    @display_name NVARCHAR(128),
		@username nvarchar(128),
		@password nvarchar(128),
		@enable_ssl int,
		@port INT;

        SET @profile_name = 'sql-alert';
		SET @account_name = 'sql-alert';
		SET @SMTP_servername = 'smtp.gmail.com';
		SET @port = 587 --default 25
		SET @enable_ssl = 1
		SET @email_address = 'dataplatform.gr@gmail.com';
        SET @display_name = 'info@dataplatform.gr';
		SET @username = 'dataplatform.gr@gmail.com';
		SET @password= 'password';

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile (sql-alert) already exists.', 16, 1);
  GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 RAISERROR('The specified Database Mail account (sql-alert) already exists.', 16, 1) ;
 GOTO done;
END;

BEGIN TRANSACTION ;

DECLARE @rv INT;

EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername,
	@username=@username,
	@password=@password,
	@enable_ssl=@enable_ssl,
	@port=@port;
	

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account (sql-alert).', 16, 1) ;
    GOTO done;
END

EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile (sql-alert).', 16, 1);
	ROLLBACK TRANSACTION;
    GOTO done;
END;

EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to associate the specified profile with the specified account (sql-alert).', 16, 1) ;
	ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

sp_configure "show advanced options",1
go
sp_configure "Database Mail XPs",1
go
reconfigure
go
sp_configure "show advanced options",0
go

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1, 
		@databasemail_profile=N'sql-alert', 
		@use_databasemail=1
GO

USE [msdb]

GO

DECLARE

        @rec_email_address NVARCHAR(128);

        SET @rec_email_address= N'alert_receiver_dataplatform.gr@gmail.com';

EXEC msdb.dbo.sp_add_operator @name=N'sqlserver_alerting', 
        @enabled=1, 
        @weekday_pager_start_time=80000, 
        @weekday_pager_end_time=180000, 
        @saturday_pager_start_time=90000, 
        @saturday_pager_end_time=180000, 
        @sunday_pager_start_time=80000, 
        @sunday_pager_end_time=180000, 
        @pager_days=0, 
        @email_address=@rec_email_address, 
        @category_name=N'[Uncategorized]'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 17', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 17', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 18', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 18', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 19', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 19', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 20', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 20', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 21', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 21', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 22', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 22', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 23', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 23', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 24', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 24', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'severity 25', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'severity 25', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'extra error 823', 
		@message_id=823, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 823', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'extra error 824', 
		@message_id=824, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 824', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'extra error 825', 
		@message_id=825, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'extra error 825', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Role Change', 
                                @message_id=1480, 
                                @severity=0, 
                                @enabled=1, 
                                @delay_between_responses=300, 
                                @include_event_description_in=1, 
                                @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Role Change', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Data Movement Suspended', 
                                @message_id=35264, 
                                @severity=0, 
                                @enabled=1, 
                                @delay_between_responses=300, 
                                @include_event_description_in=1, 
                                @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Data Movement Suspended', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Availability Group - Data Movement Resumed', 
                                @message_id=35265, 
                                @severity=0, 
                                @enabled=1, 
                                @delay_between_responses=300, 
                                @include_event_description_in=1, 
                                @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Availability Group - Data Movement Resumed', @operator_name=N'sqlserver_alerting', @notification_method = 1
GO

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Database Mail XPs', 1;  
GO  
RECONFIGURE  
GO

Use [master]
GO

RAISERROR (N'An error occurred Severity 17: test test test !', 17, 1)
WITH LOG
 
GO