sp_send_dbmail (T-SQL)

Artikel
07/22/2022
9 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Managed Instance

Mengirim pesan email ke penerima yang ditentukan. Pesan dapat mencakup kumpulan hasil kueri, lampiran file, atau keduanya. Ketika email berhasil ditempatkan dalam antrean Email Database, sp_send_dbmail mengembalikan mailitem_id pesan. Prosedur tersimpan ini ada di database msdb .

tautan topik Ikon Konvensi Sintaks Transact-SQL

Sintaks

  
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]  
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]  
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]  
    [ , [ @from_address = ] 'from_address' ]  
    [ , [ @reply_to = ] 'reply_to' ]   
    [ , [ @subject = ] 'subject' ]   
    [ , [ @body = ] 'body' ]   
    [ , [ @body_format = ] 'body_format' ]  
    [ , [ @importance = ] 'importance' ]  
    [ , [ @sensitivity = ] 'sensitivity' ]  
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]  
    [ , [ @query = ] 'query' ]  
    [ , [ @execute_query_database = ] 'execute_query_database' ]  
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]  
    [ , [ @query_attachment_filename = ] query_attachment_filename ]  
    [ , [ @query_result_header = ] query_result_header ]  
    [ , [ @query_result_width = ] query_result_width ]  
    [ , [ @query_result_separator = ] 'query_result_separator' ]  
    [ , [ @exclude_query_output = ] exclude_query_output ]  
    [ , [ @append_query_error = ] append_query_error ]  
    [ , [ @query_no_truncate = ] query_no_truncate ]   
    [ , [ @query_result_no_padding = ] @query_result_no_padding ]   
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

Argumen

[ @profile_name = ] 'profile_name' Adalah nama profil untuk mengirim pesan. Profile_name berjenis sysname, dengan default NULL. profile_name harus berupa nama profil Email Database yang sudah ada. Ketika tidak ada profile_name yang ditentukan, sp_send_dbmail menggunakan profil privat default untuk pengguna saat ini. Jika pengguna tidak memiliki profil privat default, sp_send_dbmail menggunakan profil publik default untuk database msdb . Jika pengguna tidak memiliki profil privat default dan tidak ada profil publik default untuk database, @profile_name harus ditentukan.

[ @recipients = ] 'recipients' Adalah daftar alamat email yang dibatasi titik koma untuk mengirim pesan. Daftar penerima berjenis varchar(max). Meskipun parameter ini bersifat opsional, setidaknya salah satu @recipients, @copy_recipients, atau @blind_copy_recipients harus ditentukan, atau sp_send_dbmail mengembalikan kesalahan.

[ @copy_recipients = ] 'copy_recipients' Adalah daftar alamat email yang dibatasi titik koma untuk menyalin pesan ke karbon. Daftar penerima salinan berjenis varchar(max). Meskipun parameter ini bersifat opsional, setidaknya salah satu @recipients, @copy_recipients, atau @blind_copy_recipients harus ditentukan, atau sp_send_dbmail mengembalikan kesalahan.

[ @blind_copy_recipients = ] 'blind_copy_recipients' Adalah daftar alamat email yang dibatasi titik koma untuk menyalin pesan buta karbon. Daftar penerima salinan buta berjenis varchar(max). Meskipun parameter ini bersifat opsional, setidaknya salah satu @recipients, @copy_recipients, atau @blind_copy_recipients harus ditentukan, atau sp_send_dbmail mengembalikan kesalahan.

[ @from_address = ] 'from_address' Adalah nilai 'dari alamat' pesan email. Ini adalah parameter opsional yang digunakan untuk mengambil alih pengaturan di profil email. Parameter ini berjenis varchar(MAX). Pengaturan keamanan SMTP menentukan apakah penimpaan ini diterima. Jika tidak ada parameter yang ditentukan, defaultnya adalah NULL.

[ @reply_to = ] 'reply_to' Adalah nilai 'balas ke alamat' pesan email. Ini hanya menerima satu alamat email sebagai nilai yang valid. Ini adalah parameter opsional yang digunakan untuk mengambil alih pengaturan di profil email. Parameter ini berjenis varchar(MAX). Pengaturan keamanan SMTP menentukan apakah penimpaan ini diterima. Jika tidak ada parameter yang ditentukan, defaultnya adalah NULL.

[ @subject = ] 'subject' Adalah subjek pesan email. Subjeknya berjenis nvarchar(255). Jika tidak ada subjek yang ditentukan, defaultnya adalah 'SQL Server Message'.

[ @body = ] 'body' Adalah isi pesan email. Isi pesan berjenis nvarchar(max), dengan default NULL.

[ @body_format = ] 'body_format' Adalah format isi pesan. Parameter berjenis varchar(20), dengan default NULL. Ketika ditentukan, header pesan keluar diatur untuk menunjukkan bahwa isi pesan memiliki format yang ditentukan. Parameter mungkin berisi salah satu nilai berikut:

TEXT
HTML

Default ke TEXT.

[ @importance = ] 'importance' Adalah pentingnya pesan. Parameternya berjenis varchar(6). Parameter mungkin berisi salah satu nilai berikut:

Rendah
Normal
Tinggi

Default ke Normal.

[ @sensitivity = ] 'sensitivity' Adalah sensitivitas pesan. Parameternya berjenis varchar(12). Parameter mungkin berisi salah satu nilai berikut:

Normal
Pribadi
Privat
Rahasia

Default ke Normal.

[ @file_attachments = ] 'file_attachments' Adalah daftar nama file yang dibatasi titik koma untuk dilampirkan ke pesan email. File dalam daftar harus ditentukan sebagai jalur absolut. Daftar lampiran berjenis nvarchar(max). Secara default, Database Mail membatasi lampiran file hingga 1 MB per file.

Penting

Parameter ini tidak tersedia di Azure SQL Managed Instance karena tidak dapat mengakses sistem file lokal.

[ @query = ] 'query' Adalah kueri yang akan dijalankan. Hasil kueri dapat dilampirkan sebagai file, atau disertakan dalam isi pesan email. Kueri berjenis nvarchar(max), dan dapat berisi pernyataan Transact-SQL yang valid. Perhatikan bahwa kueri dijalankan dalam sesi terpisah, sehingga variabel lokal dalam panggilan skrip sp_send_dbmail tidak tersedia untuk kueri.

Catatan

Saat menggunakan @query parameter, pengguna yang menjalankan sp_send_dbmail harus masuk SQL atau langsung dipetakan ke prinsipal (masuk) Azure AD atau AD. Jika pengguna adalah anggota grup Azure AD atau grup AD, pengguna tidak akan dapat menjalankan kueri. Hal ini disebabkan oleh Azure SQL Managed Instance peniruan dan batasan EXECUTE AS.

[ @execute_query_database = ] 'execute_query_database' Adalah konteks database tempat prosedur tersimpan menjalankan kueri. Parameter berjenis sysname, dengan default database saat ini. Parameter ini hanya berlaku jika @query ditentukan.

[ @attach_query_result_as_file = ] attach_query_result_as_file Menentukan apakah kumpulan hasil kueri dikembalikan sebagai file terlampir. attach_query_result_as_file berjenis bit, dengan default 0.

Saat nilainya adalah 0, hasil kueri disertakan dalam isi pesan email, setelah konten parameter @body . Saat nilainya adalah 1, hasilnya dikembalikan sebagai lampiran. Parameter ini hanya berlaku jika @query ditentukan.

[ @query_attachment_filename = ] query_attachment_filename Menentukan nama file yang akan digunakan untuk kumpulan hasil lampiran kueri. query_attachment_filename berjenis nvarchar(255), dengan default NULL. Parameter ini diabaikan ketika attach_query_result adalah 0. Ketika attach_query_result adalah 1 dan parameter ini ADALAH NULL, Database Mail membuat nama file arbitrer.

[ @query_result_header = ] query_result_header Menentukan apakah hasil kueri menyertakan header kolom. Nilai query_result_header berjenis bit. Saat nilainya adalah 1, hasil kueri berisi header kolom. Saat nilainya adalah 0, hasil kueri tidak menyertakan header kolom. Parameter ini default ke 1. Parameter ini hanya berlaku jika @query ditentukan.

Catatan

Kesalahan berikut dapat terjadi saat mengatur @query_result_header ke 0 dan mengatur @query_no_truncate ke 1:
Msg 22050, Level 16, State 1, Line 12: Failed to initialize sqlcmd library with error number -2147024809.

[ @query_result_width = ] query_result_width Adalah lebar garis, dalam karakter, yang digunakan untuk memformat hasil kueri. query_result_width berjenis int, dengan default 256. Nilai yang diberikan harus antara 10 dan 32767. Parameter ini hanya berlaku jika @query ditentukan.

[ @query_result_separator = ] 'query_result_separator' Adalah karakter yang digunakan untuk memisahkan kolom dalam output kueri. Pemisah berjenis char(1). Default ke ' ' (spasi).

[ @exclude_query_output = ] exclude_query_output Menentukan apakah akan mengembalikan output eksekusi kueri dalam pesan email. exclude_query_output sedikit, dengan default 0. Ketika parameter ini adalah 0, eksekusi prosedur tersimpan sp_send_dbmail mencetak pesan yang dikembalikan sebagai hasil dari eksekusi kueri di konsol. Ketika parameter ini adalah 1, eksekusi prosedur tersimpan sp_send_dbmail tidak mencetak pesan eksekusi kueri apa pun di konsol.

[ @append_query_error = ] append_query_error Menentukan apakah akan mengirim email ketika kesalahan kembali dari kueri yang ditentukan dalam argumen @query . append_query_errorsedikit, dengan default 0. Ketika parameter ini adalah 1, Email Database mengirim pesan email dan menyertakan pesan kesalahan kueri dalam isi pesan email. Ketika parameter ini adalah 0, Email Database tidak mengirim pesan email, dan sp_send_dbmail berakhir dengan kode pengembalian 1, menunjukkan kegagalan.

[ @query_no_truncate = ] query_no_truncate Menentukan apakah akan menjalankan kueri dengan opsi yang menghindari pemotongan jenis data panjang variabel besar (varchar(max), nvarchar(max), varbinary(max), xml, teks, ntext, gambar, dan jenis data yang ditentukan pengguna). Saat diatur, hasil kueri tidak menyertakan header kolom. Nilai query_no_truncate berjenis bit. Saat nilainya 0 atau tidak ditentukan, kolom dalam kueri terpotok menjadi 256 karakter. Saat nilainya 1, kolom dalam kueri tidak terpotok. Parameter ini default ke 0.

Catatan

Saat digunakan dengan data dalam jumlah besar, opsi @query_no_truncate menggunakan sumber daya tambahan dan dapat memperlambat performa server.

[ @query_result_no_padding ] @query_result_no_padding Jenisnya adalah bit. Defaultnya adalah 0. Saat Anda mengatur ke 1, hasil kueri tidak dilapisi, mungkin mengurangi ukuran file. Jika Anda mengatur @query_result_no_padding ke 1 dan Anda mengatur parameter @query_result_width, parameter @query_result_no_padding akan menimpa parameter @query_result_width.

Dalam hal ini tidak ada kesalahan yang terjadi.

Catatan

Kesalahan berikut dapat terjadi saat mengatur @query_result_no_padding ke 1 dan menyediakan parameter untuk @query_no_truncate:
Msg 22050, Level 16, State 1, Line 0: Gagal menjalankan kueri karena opsi @query_result_no_append dan @query_no_truncate saling eksklusif.

Jika Anda mengatur @query_result_no_padding ke 1 dan Anda mengatur parameter @query_no_truncate, kesalahan akan muncul.

[ @mailitem_id = ] mailitem_id [ OUTPUT ] Parameter output opsional mengembalikan mailitem_id pesan. Mailitem_id berjenis int.

Mengembalikan Nilai Kode

Kode pengembalian 0 berarti berhasil. Nilai lainnya berarti kegagalan. Kode kesalahan untuk pernyataan yang gagal disimpan dalam variabel @@ERROR.

Tataan Hasil

Setelah berhasil, mengembalikan pesan "Mail queued."

Keterangan

Sebelum digunakan, Email Database harus diaktifkan menggunakan Panduan Konfigurasi Email Database, atau sp_configure.

sysmail_stop_sp menghentikan Email Database dengan menghentikan objek Service Broker yang digunakan program eksternal. sp_send_dbmail masih menerima email ketika Email Database dihentikan menggunakan sysmail_stop_sp. Untuk memulai Email Database, gunakan sysmail_start_sp.

Saat @profile tidak ditentukan, sp_send_dbmail menggunakan profil default. Jika pengguna yang mengirim pesan email memiliki profil privat default, Database Mail menggunakan profil tersebut. Jika pengguna tidak memiliki profil privat default, sp_send_dbmail menggunakan profil publik default. Jika tidak ada profil privat default untuk pengguna dan tidak ada profil publik default, sp_send_dbmail mengembalikan kesalahan.

sp_send_dbmail tidak mendukung pesan email tanpa isi. Untuk mengirim pesan email, Anda harus menentukan setidaknya salah satu @body, @query, @file_attachments, atau @subject. Jika tidak, sp_send_dbmail mengembalikan kesalahan.

Email Database menggunakan konteks keamanan Microsoft Windows dari pengguna saat ini untuk mengontrol akses ke file. Oleh karena itu, pengguna yang diautentikasi dengan Autentikasi SQL Server tidak dapat melampirkan file menggunakan @file_attachments. Windows tak memperbolehkan SQL Server menyediakan mandat dari komputer jarak jauh ke komputer jarak jauh lainnya. Oleh karena itu, Email Database mungkin tidak dapat melampirkan file dari berbagi jaringan jika perintah dijalankan dari komputer selain komputer yang SQL Server jalankan.

Jika @query dan @file_attachments ditentukan dan file tidak dapat ditemukan, kueri masih dijalankan tetapi email tidak dikirim.

Saat kueri ditentukan, kumpulan hasil diformat sebagai teks sebaris. Data biner dalam hasil dikirim dalam format heksadesimal.

Parameter @recipients, @copy_recipients, dan @blind_copy_recipients adalah daftar alamat email yang dibatasi titik koma. Setidaknya salah satu parameter ini harus disediakan, atau sp_send_dbmail mengembalikan kesalahan.

Saat menjalankan sp_send_dbmail tanpa konteks transaksi, Database Mail memulai dan melakukan transaksi implisit. Saat menjalankan sp_send_dbmail dari dalam transaksi yang ada, Database Mail mengandalkan pengguna untuk menerapkan atau mengembalikan perubahan apa pun. Ini tidak memulai transaksi dalam.

Izin

Jalankan izin untuk sp_send_dbmail default ke semua anggota peran database DatabaseMailUser dalam database msdb . Namun, ketika pengguna mengirim pesan tidak memiliki izin untuk menggunakan profil untuk permintaan, sp_send_dbmail mengembalikan kesalahan dan tidak mengirim pesan.

Contoh

A. Mengirim pesan email

Contoh ini mengirim pesan email ke teman Anda menggunakan alamat myfriend@Adventure-Works.comemail . Pesan memiliki subjek Automated Success Message. Isi pesan berisi kalimat 'The stored procedure finished successfully'.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = 'yourfriend@Adventure-Works.com',  
    @body = 'The stored procedure finished successfully.',  
    @subject = 'Automated Success Message' ;

B. Mengirim pesan email dengan hasil kueri

Contoh ini mengirim pesan email ke teman Anda menggunakan alamat yourfriend@Adventure-Works.comemail . Pesan memiliki subjek Work Order Count, dan menjalankan kueri yang memperlihatkan jumlah perintah kerja dengan DueDate kurang dari dua hari setelah 30 April 2004. Email Database melampirkan hasilnya sebagai file teks.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = 'yourfriend@Adventure-Works.com',  
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder  
                  WHERE DueDate > ''2004-04-30''  
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,  
    @subject = 'Work Order Count',  
    @attach_query_result_as_file = 1 ;

C. Mengirim pesan email HTML

Contoh ini mengirim pesan email ke teman Anda menggunakan alamat yourfriend@Adventure-Works.comemail . Pesan memiliki subjek Work Order List, dan berisi dokumen HTML yang menunjukkan perintah kerja dengan DueDate kurang dari dua hari setelah 30 April 2004. Email Database mengirim pesan dalam format HTML.

DECLARE @tableHTML  NVARCHAR(MAX) ;  
  
SET @tableHTML =  
    N'<H1>Work Order Report</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +  
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +  
    N'<th>Expected Revenue</th></tr>' +  
    CAST ( ( SELECT td = wo.WorkOrderID,       '',  
                    td = p.ProductID, '',  
                    td = p.Name, '',  
                    td = wo.OrderQty, '',  
                    td = wo.DueDate, '',  
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty  
              FROM AdventureWorks.Production.WorkOrder as wo  
              JOIN AdventureWorks.Production.Product AS p  
              ON wo.ProductID = p.ProductID  
              WHERE DueDate > '2004-04-30'  
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2   
              ORDER BY DueDate ASC,  
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC  
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) +  
    N'</table>' ;  
  
EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',  
    @subject = 'Work Order List',  
    @body = @tableHTML,  
    @body_format = 'HTML' ;

Lihat juga

Database Mail
Objek Konfigurasi Email Database
Prosedur Tersimpan Email Database (Transact-SQL)
sp_addrolemember (Transact-SQL)