Stack Overflow
Stack Overflow

Questions tagged [sp-send-dbmail]

`sp-send-dbmail` is a built-in stored procedure available in SQL Server since 2005, which allows one to send e-mails using the SQL service, for alerting a DBA when a problem is detected or limited reporting.

This procedure allows one to send an e-mail directly from SQL, including an optional query. It is well-suited for simple alerts and debugging, and in a pinch can be used to distribute reports, though it supports only a limited amount of formatting.

To use this sproc, one must first configure a Database Mail account and profile. This is available from within SQL Studio under [Server Name] > Management > Database Mail > right-click > Configure Database Mail.

Here's a code sample for a very simple message:

EXEC msdb..sp_send_dbmail
    @profile_name = 'My Profile',
    @recipients = 'dba@widgetcorp.com',
    @from_address = 'database_alerts@widgetcorp.com',
    @subject = 'Error Detected',
    @body = 'A problem has been found, best get on it.'

sp-send-dbmail has a large number of parameters, well-documented on MSDN:

234 questions
78
votes
7 answers

How to send email from SQL Server?

How can I send an email using T-SQL but email address is stored in a table? I want to loop through the table and be able to send email. I cannot find a good example of doing this so far.
moe
  • 5,149
  • 38
  • 130
  • 197
47
votes
11 answers

Convert a SQL query result table to an HTML table for email

I am running a SQL query that returns a table of results. I want to send the table in an email using dbo.sp_send_dbMail. Is there a straightforward way within SQL to turn a table into an HTML table? Currently, I'm manually constructing it using…
Matt Victory
  • 739
  • 2
  • 8
  • 13
47
votes
6 answers

'profile name is not valid' error when executing the sp_send_dbmail command

I have a windows account with users group and trying to exec sp_send_dbmail but getting an error: profile name is not valid. However, when I logged in as administrator and execute the sp_send_dbmail, it managed to send the email so obviously the…
user384080
  • 4,576
  • 15
  • 64
  • 96
19
votes
8 answers

sp_send_dbmail executed from job fails with query result attached as file

I have faced with the following issue: when trying to send email with results of query attached as file, using sp_send_dbmail via executing ordinary query everything seems to be working OK. But if add the same code into JobStep and run the job, it…
Paul Kyrejto
  • 1,285
  • 3
  • 12
  • 19
17
votes
4 answers

How can I send plain text email (with line breaks) using sp_send_dbmail?

I have a SQL Server 2008 procedure that sends email via sp_send_dbmail. I'm using the following code: set @bodyText = ( select N'Here is one line of text ' + N'It would be nice to have this on a 2nd…
chris
  • 36,094
  • 53
  • 157
  • 237
12
votes
2 answers

sp_send_dbmail embed mhtml file in body

I have an SSRS report that I need to embed in the body of an email using the sp_dbmail stored proc in SQL Server. I'm able to do this using the front end of Outlook by attaching the .mhtml export of the SSRS report using the "Insert as Text" option…
Pops
  • 468
  • 2
  • 15
12
votes
2 answers

meaning of the values of sent_status on msdb.dbo.sysmail_mailitems

I am sending emails from SQL Server, and need to map the values of the sent_status column on the msdb.dbo.sysmail_mailitems table to something more descriptive. So far I have identified two values: 1 = 'Sent' 2 = 'Failed' Are there any more…
patovega
  • 343
  • 2
  • 5
  • 16
11
votes
8 answers

Stored procedure using SP_SEND_DBMAIL sending duplicate emails to all recipients

I have a stored procedure that is run every night which is supposed to send the results of a query to several recipients. However on most days it ends up sending a duplicate email a minute later. The code I am using is as follows (all emails and…
andewM
  • 181
  • 1
  • 1
  • 7
7
votes
3 answers

sp_send_dbmail will not send query results

I've tried every avenue on every damn forum suggested, but to no avail! Need to send results of SQLPERF(logspace), that have been stored in a table, via sp_send_dbmail to recipient. Step 2 of job is where failure occurs. Please help! EXEC…
Jean
5
votes
1 answer

How can I use sp_send_dbmail to send multiple queries?

I'm trying to send an email using sp_send_dbmail. I need it to send one query as an attachment and another as part of the body of the email. The problem is that sp_send_dbmail only has one @query parameter, and I can't see any way to add another…
chama
  • 5,973
  • 14
  • 61
  • 77
5
votes
2 answers

SQL Server SP_SEND_DBMAIL Image file attachment

I am using a trigger on a table to send an email using sp_send_dbmail. I want to include a file attachment in the email of an image type. The raw data for the jpeg is stored in the ndl_Image column which is of type binary. I have the following…
general exception
  • 4,202
  • 9
  • 54
  • 82
5
votes
1 answer

Why am I getting the following error when using database mail -> Procedure sysmail_verify_profile_sp, profile name is not valid?

I wrote a trigger to grab a certain row of records after a specific column change and store the records into another table called Feedback. Then I am trying to using the following code to email the changes to our users using sp_send_dbmail. However,…
Chidi Okeh
  • 1,537
  • 8
  • 28
  • 50
5
votes
1 answer

SQL Server sp_send_dbmail - How to send mail TO Gmail?

I am using sp_send_dbmail which is working perfectly except when I send to recipients using Gmail accounts. The database mail log give the following error: Error,80,The mail could not be sent to the recipients because of the mail server failure.…
Marc
  • 9,217
  • 21
  • 67
  • 90
5
votes
2 answers

sp_send_dbmail attach files stored as varbinary in database

I have a two part question relating to sending query results as attachments using sp_send_dbmail. Problem 1: Only basic .txt files will open. Any other format like .pdf or .jpg are corrupted. Problem 2: When attempting to send multiple attachments,…
Mindstorm Interactive
  • 529
  • 1
  • 6
  • 8
4
votes
3 answers

sp_send_dbmail attachment encoding

I am using sp_send_dbmail in SQL2005 to send an email with the results in an attachment. When the attachment is sent it is UCS-2 Encoded, I want it to be ANSI or UTF-8. Here is the SQL EXEC msdb.dbo.sp_send_dbmail @recipients =…
chris
1
2 3
…
15 16