Contents

1 語 蟲
2 DB覃 誤
3 覃朱慨願鍵
4 一危磯伎 覃 れ 讌郁鍵
5 Query襯 HTML襦 襷れ伎 覲企願鍵
6 谿瑚襭


1 語 蟲 #

use master
go
sp_configure 'show advanced options' , 1
go
reconfigure with override
go
sp_configure 'Database Mail XPs' , 1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go 

2 DB覃 誤 #

EXECUTE msdb . dbo . sysmail_add_account_sp
@ac count_name = 'MailServiceAccount'
, @description = 'Test Mail'
, @email_address = '覃殊殊'
, @display_name = 'Databaser.Net'
, @username = '覃殊覯AccountID'
, @password = 'れ '
, @mailserver_name = 'smtp覯 譯殊'

EXECUTE msdb . dbo . sysmail_add_profile_sp
@profile_name = 'MailServiceProfile'
, @description = 'Profile used for database mail' ;

EXECUTE msdb . dbo . sysmail_add_profileaccount_sp
@profile_name = 'MailServiceProfile'
, @account_name = 'MailServiceAccount'
, @sequence_number = 1

EXECUTE msdb . dbo . sysmail_add_principalprofile_sp
@profile_name = 'MailServiceProfile'
, @principal_name = 'public'
, @is_default = 1 ; 

3 覃朱慨願鍵 #

declare @body1 varchar ( 100 )
set @body1 = 'Server :' + @@servername + ' My First Database Email '
EXEC msdb . dbo . sp_send_dbmail
@recipients = '覃殊殊'
, @subject = 'My Mail Test'
, @body = @body1
, @body_format = 'HTML' ; 

4 一危磯伎 覃 れ 讌郁鍵 #

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'MailServiceProfile'
, @account_name = 'MailServiceAccount'

EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'MailServiceProfile' ;

EXECUTE msdb.dbo.sysmail_delete_acco unt_sp
@account_name = 'MailServiceAccount' ;

--exec msdb..sysmail_help_principalprofile_sp
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name = 'guest',
@profile_name = 'MailServiceProfile' ;

5 Query襯 HTML襦 襷れ伎 覲企願鍵 #

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
	N'<H1>蠍一蟆:SSIS</H1>' +
	N'<table border="1">' +
	N'<tr><th>覯覯</th>' +
	N'<th>蠍一蟆覈</th>' +
	N'<th>螳</th>' +
	N'<th>IP</th>' + 
	N'<th>覈</th>' + 
	N'<th>ろ覈</th>' +
	N'<th>觚ろ</th>' +
	N'<th>覯覈</th>' +
	N'<th>れ覈</th>' +
	N'</tr>' +
    CAST ( (
		select 
			td = ServerNo, ''
		,	td = 蠍一蟆覈, ''
		,	td = 螳, ''
		,	td = IP, ''
		,	td = job_nm, ''
		,	td = step_name, ''
		,	td = subsystem, ''
		,	td = ServerNM, ''
		,	td = PackageFileName 
		from work.dbo.ろ
		order by 1
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='aaa@databaser.net',
    @subject = '蠍一蟆:SSIS',
    @body = @tableHTML,
    @body_format = 'HTML' ;

6 谿瑚襭 #