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
1
vote
0 answers

sp_send_dbmail inserts line-breaks into the attached xml query result

When you use msdb.dbo.sp_send_dbmail to send a query result as an attachment, you'll have to provide the @query_result_width parameter which is an integer between 10 and 32767. Now, If you use For XML Path('Row'), Root('Data'), Type in your…
Achilles
  • 1,554
  • 1
  • 28
  • 36
1
vote
1 answer

SSISDB Trigger to email from DB Mail in SQL Server 2012

Problem: I'm unable to execute sp_send_mail from a trigger on SSISDB objects. Background: we use SSIS deployed to the SQL Server catalog and we're trying to place a trigger on the [internal].[projects] table of SSISDB in order to email management…
myz32300ztt
  • 61
  • 1
  • 5
1
vote
2 answers

Set dbo.sp_send_dbmail arguments to stored procedure return value?

I have stored procedure usp_emailRecipients that returns a semicolon-delimited list of email addresses. This is the list of email addresses that will receive an email sent using msdb.dbo.sp_send_dbmail. How can I set the value of @recipients to the…
fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110
1
vote
1 answer

Using sp_send_dbmail with recieptent and content from single sql row

I have a table filled with oldUserID, newUserID, name and email. I want to use sp_send_dbmail to the email on each row. For example: oldUserID | newUserID | name | email 21213125 | 2355233571 | Tom | tom@gmail.com 65465465 | 4564884664 | Mat |…
Glews
  • 77
  • 10
1
vote
1 answer

sp_send_dbmail is missing in sqlserver2008

I am unable to find the system stored procedure sp_send_dbmail, which should be under System Databases -> msdb. msdb.[dbo].sp_send_dbmail . But it's missing under the location where it should be. What should i do?
kaarthick raman
  • 793
  • 2
  • 13
  • 41
1
vote
1 answer

Clearing db mail queue in sql server

My DB server seems to be stuck in some kind of a trance. When I run a query, it runs fine. When I run the same query as part of @query tag in maildb, it gets stuck. When I remove the query, and send an empty mail, it gets stuck in mail queue. EXEC…
jitendragarg
  • 945
  • 1
  • 14
  • 54
1
vote
0 answers

SQL Server 2014 dbmail won't send attachment over 15MB, file size limit set to 60 MB

I have an automated e-mail that goes out and will sometimes have an attachment, this has been running for over 3-4 years now with no issue, until now. Up to now, attachments were maxing out at just over 14MB, but the other day we had two that failed…
BIrish
  • 29
  • 4
1
vote
1 answer

Sending dbmail with a query result with an okay format

Im trying create an automated mail with my query results, but the formatting is messed up and it's barely understandable. The query returns 10 columns with aproximately 10 lines. Is there any good way to easily format it better? It doesn't matter if…
Glews
  • 77
  • 10
1
vote
1 answer

Why can't I execute msdb.dbo.send_dbmail from a stored procedure being executed in a job?

I have a job scheduled to run on my server. That job executes a stored procedure. Inside that stored procedure I am executing msdb.dbo.sp_send_dbmail. When I execute the stored procedure logged in as an admin it runs fine. When the job runs though,…
Adam
  • 1,483
  • 4
  • 21
  • 50
1
vote
1 answer

Attachment file \\SERVER\file.csv is invalid. [SQLSTATE 42000] (Error 22051). The step failed

I've got 4 SQL servers: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. In SQL Server 2008 R2; I have created a SQL job that sends me email every Monday with reports from SQL Server 2005, SQL Server 2008 and SQL Server 2008…
DonPato888
  • 21
  • 1
  • 5
1
vote
1 answer

Send CSV file with db_sendmail - missing records

I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it…
olia
  • 11
  • 4
1
vote
1 answer

Embedding image in email with SQL [sp_send_dbmail]

I want to embed an image into an HTML email with SQL server. The emails are getting sent but the images do not show. My code is as follows: DECLARE @body_custom VARCHAR(MAX) SET @body_custom = ' Embedded…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/sql" class="post-tag grid--cell" title="show questions tagged 'sql'" rel="tag">sql</a> <a href="../../questions/tagged/sql-server-2008-r2" class="post-tag grid--cell" title="show questions tagged 'sql-server-2008-r2'" rel="tag">sql-server-2008-r2</a> <a href="../../questions/tagged/html-email" class="post-tag grid--cell" title="show questions tagged 'html-email'" rel="tag">html-email</a> <a href="../../questions/tagged/email-attachments" class="post-tag grid--cell" title="show questions tagged 'email-attachments'" rel="tag">email-attachments</a> <a href="../../questions/tagged/sp-send-dbmail" class="post-tag grid--cell" title="show questions tagged 'sp-send-dbmail'" rel="tag">sp-send-dbmail</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked May 04 '15 at 18:16">asked May 04 '15 at 18:16</time> <a href="../../users/3772469/astora" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/3772469.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Astora" /> </a> <div class="s-user-card--info"> <a href="../../users/3772469/astora" class="s-user-card--link">Astora</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">55</li> <li class="s-award-bling s-award-bling__silver" title="2 silver badges">2</li> <li class="s-award-bling s-award-bling__bronze" title="5 bronze badges">5</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-2909099"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status "> <strong>1</strong> answer </div> </div> </div> <div class="summary"> <h3><a href="../../questions/2909099/sql-server-2005-sp-send-dbmail" class="question-hyperlink">SQL Server 2005 sp_send_dbmail</a></h3> <div class="excerpt">When we use sp_send_dbmail to send email with attachment, the attachment gets copied into a folder inside C:\Windows\Temp. As we have many emails to be sent every day, the temp folder grows rapidly. This is the case with SQL Server 2005. We noticed…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/sql-server" class="post-tag grid--cell" title="show questions tagged 'sql-server'" rel="tag">sql-server</a> <a href="../../questions/tagged/sql-server-2005" class="post-tag grid--cell" title="show questions tagged 'sql-server-2005'" rel="tag">sql-server-2005</a> <a href="../../questions/tagged/sp-send-dbmail" class="post-tag grid--cell" title="show questions tagged 'sp-send-dbmail'" rel="tag">sp-send-dbmail</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked May 25 '10 at 22:55">asked May 25 '10 at 22:55</time> <a href="../../users/344535/jit" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/344535.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Jit" /> </a> <div class="s-user-card--info"> <a href="../../users/344535/jit" class="s-user-card--link">Jit</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">125</li> <li class="s-award-bling s-award-bling__silver" title="3 silver badges">3</li> <li class="s-award-bling s-award-bling__bronze" title="10 bronze badges">10</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-28984579"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status answered-accepted"> <strong>1</strong> answer </div> </div> </div> <div class="summary"> <h3><a href="../../questions/28984579/sp-send-dbmail-incorrect-syntax-near" class="question-hyperlink">sp_send_dbmail Incorrect syntax near '<'</a></h3> <div class="excerpt">I'm having a problem sending a HTML formatted email from SQL server. With the following section of code I get a "Line 1, incorrect syntax near '<'" error. SET @tableHTML = '<H1>Progress Report</H1>' + '<table border="1">' + '<tr>' + …</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/html" class="post-tag grid--cell" title="show questions tagged 'html'" rel="tag">html</a> <a href="../../questions/tagged/sql" class="post-tag grid--cell" title="show questions tagged 'sql'" rel="tag">sql</a> <a href="../../questions/tagged/sp-send-dbmail" class="post-tag grid--cell" title="show questions tagged 'sp-send-dbmail'" rel="tag">sp-send-dbmail</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Mar 11 '15 at 10:49">asked Mar 11 '15 at 10:49</time> <a href="../../users/2295457/user2295457" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/2295457.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="user2295457" /> </a> <div class="s-user-card--info"> <a href="../../users/2295457/user2295457" class="s-user-card--link">user2295457</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">101</li> <li class="s-award-bling s-award-bling__bronze" title="11 bronze badges">11</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-28966769"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status "> <strong>1</strong> answer </div> </div> </div> <div class="summary"> <h3><a href="../../questions/28966769/remove-lines-from-csv-created-through-send-dbmail" class="question-hyperlink">Remove lines from CSV created through send_dbmail</a></h3> <div class="excerpt">I am running a send_dbmail in a procedure and creating a csv-file. But I would like to remove 2 things from the file: I would like to remove line 2 and the last line(3 rows affected). Is there a way? </div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/sql" class="post-tag grid--cell" title="show questions tagged 'sql'" rel="tag">sql</a> <a href="../../questions/tagged/sql-server" class="post-tag grid--cell" title="show questions tagged 'sql-server'" rel="tag">sql-server</a> <a href="../../questions/tagged/sql-server-2008" class="post-tag grid--cell" title="show questions tagged 'sql-server-2008'" rel="tag">sql-server-2008</a> <a href="../../questions/tagged/sp-send-dbmail" class="post-tag grid--cell" title="show questions tagged 'sp-send-dbmail'" rel="tag">sp-send-dbmail</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Mar 10 '15 at 14:46">asked Mar 10 '15 at 14:46</time> <a href="../../users/4117633/zeb" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/4117633.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Zeb" /> </a> <div class="s-user-card--info"> <a href="../../users/4117633/zeb" class="s-user-card--link">Zeb</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">21</li> <li class="s-award-bling s-award-bling__bronze" title="8 bronze badges">8</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="s-pagination pager fr"> <a class="s-pagination--item" href="../../questions/tagged/sp-send-dbmail_page=5" rel="prev" title="Go to page 5">Prev </a> <a class="s-pagination--item" href="../../questions/tagged/sp-send-dbmail_page=1" rel="" title="Go to page 1">1</a> <a class="s-pagination--item" href="../../questions/tagged/sp-send-dbmail_page=2" rel="" title="Go to page 2">2</a> <a class="s-pagination--item" href="../../questions/tagged/sp-send-dbmail_page=3" rel="" title="Go to page 3">3</a> <div class="s-pagination--item s-pagination--item__clear">…</div> <a class="s-pagination--item" href="../../questions/tagged/sp-send-dbmail_page=15" rel="" title="Go to page 15">15</a> <a class="s-pagination--item" href="../../questions/tagged/sp-send-dbmail_page=16" rel="" title="Go to page 16">16</a> <a class="s-pagination--item" href="../../questions/tagged/sp-send-dbmail_page=7" rel="next" title="Go to page 7"> Next</a> </div> </div> </div> </div> </div> <script src="../../static/js/stack-icons.js"></script> <script src="../../static/js/fromnow.js"></script> </body> </html>