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

Failed to initialize sqlcmd library with error number -2147467259 with a XML variable

I'm trying to send an email using sp_send_dbmail having my select coming from an XML variable, but I'm getting the following error: Msg 22050, Level 16, State 1, Line 6 Failed to initialize sqlcmd library with error number -2147467259. This is my…
Evy
  • 11
  • 1
1
vote
2 answers

SQL Server job error on email notification

I have configured a database email, operators, and such on my SQL managed instance, to receive an email when a job fails. In the email, we get something like this "The yyy_job failed on step 3". But my question is... Is there a way to add the error…
Van Darth Vadden
  • 561
  • 1
  • 5
  • 11
1
vote
1 answer

How to change encoding of attachments from sp_send_dbmail?

Is there a way to change the encoding from default 'UniCode' to UTF-8 while generating data attachments from sp_send_dbmail on SQL Server? I found that there used to be a flag 'ANSI_Attachment' to accomplish just that but it got removed in SP2...…
android-developer
  • 1,574
  • 4
  • 20
  • 27
1
vote
0 answers

Send different query results to different recipients using DBMail

I have been asked to create a job to run daily which sends a list of todays shipments to each customer. I have a table with required detail of 'todays shipments' and the customers email addresses [Cust E-Mail],[Ship No],[Item Number], [Qty],[Sell-to…
user17135808
  • 11
  • 1
1
vote
0 answers

Calling sp_send_dbmail from a sproc within an execution context

Using SQL-Server 2019; I am trying to use sp_send_dbmail and have successfully sent test emails from SQL-Server so believe the configuration is fine. A sproc runs in the execution context of a SQL-Login, which updates some tables in a database and…
BraveSirRobin
  • 11
  • 1
1
vote
1 answer

Why do I get error -2147467259 when trying to send an email in SQL Server

I am trying to send an email containing the results of a stored procedure call inside SQL Server. declare @querytext nvarchar(100) set @querytext = 'sp_get_SPresults @item_name = ''Name of Group''''; EXEC msdb.dbo.sp_send_dbmail @profile_name…
Dan
  • 57
  • 6
1
vote
1 answer

How can I determine the error code from sp_send_dbmail in SQL Server 2005?

I'm trying to send an attachment using sp_send_dbmail in SQL Server 2005. I first wrote a CLR stored procedure that saves some content to a file on the server, then calls the stored procedure listed below, and then deletes the file it created. I…
Jim
  • 55
  • 2
  • 10
1
vote
1 answer

sp_send_dbmail - throws the following error for a 998 KB file. File attachment or query results size exceeds allowable value of 1000000 bytes

I know that in order to fix the error "File attachment or query results size exceeds allowable value of 1000000 bytes" we need to configure the Database Mail to increase the attachment size. But here my problem is, my file size is 998 KB (so 2 KB…
Sri
  • 11
  • 2
1
vote
0 answers

Sql Server 2019 (Fedora 32) - Msg 22022, Level 16, State 1, Line 0 SQLServerAgent is not currently running so it cannot be notified of this action

I am getting this error. Though Agent seems to be running I have checked running state by calling EXEC xp_servicecontrol 'querystate', 'SQLSERVERAGENT' And it shows "Stopped." Then I call EXEC xp_servicecontrol N'START',N'SQLServerAGENT'; And it…
Muhammad Usama
  • 17
  • 3
1
vote
0 answers

How do I resolve the error "Database Mail is not permitted to send files with the file extension"?

I would like to send an excel file as mail attachment with the following code. But I get an error. I would appreciate your help. EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AutoMail', @recipients = 'gokhanerdogdu@safikatiyakit.com.tr', @subject =…
Gökhan Erdoğdu
  • 11
  • 1
1
vote
0 answers

Microsoft SQL Server - SQL Server Agent with scheduled email using the - msdb.dbo.sp_send_dbmail - stored procedure truncates body of email

I have the below T-SQL script in SQL Server Agent, and it is scheduled to run at 7 AM and 7 PM everyday. DECLARE @Report_output nvarchar(4000); DECLARE @HourLapse_output real; DECLARE @ServerName_output nvarchar(50); DECLARE @Subject_output…
user3812887
  • 439
  • 12
  • 33
1
vote
1 answer

Color Code Based on Value in Sql Tabular Formatted Email

I have working code that sends an HTML tabular email, I just need help color coding specific results in the HTML code if they are under 0. set datefirst 1; set nocount on; if (object_id('tempdb..#temp') iS NOT NULL) drop table…
Mary Mahoney
  • 53
  • 2
  • 10
1
vote
0 answers

@query doesn't attach all the rows to @query_attachment_filename

When I attach my stored procedure to a file by the sp_send_dbmail the file doesn't have all the rows. For example the total rows are 243 but it only attaches 204 of them. Even if I change the stored procedure by a simple SELECT * FROM TABLE, the…
Jaime Fung
  • 11
  • 1
1
vote
2 answers

Format query output into an html table

I need the sql query result sent by e-mail to be in readable form. A change to html would allow to create results in the table. I need help implementing html in the code below. USE msdb go SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON IF EXISTS…
mlik
  • 63
  • 10
1
vote
1 answer

SQL : Loop through folder and Send email with attachment and archive file.Exec sp_send_dbmail

Hi Everyone – I am trying to create the below Stored Procedure where the patient should receive the attachment document via email. The documents are in the folder and each document filename (Example: LetterPatient_12345) has a patient ID (12345)…
user9273914
  • 99
  • 2
  • 11
1 2 3
…
15 16