We had to rebuild our SQL server instance over this week and have restored every database including Master and other system settings, but our database mail does not seem to be working. All of the queued emails are sitting in Unsent and the mail queue is listed as Inactive. I've tried restarting the queue as well, although some other posts make it sound like Inactive is the default state for when it is not in use at that moment. We are using version 15.0.2116.2 I've been unable to find anything in the error log and the only indicator that it is not working besides the obvious lack of an email is the sysmail_event_log which used to have records in this order: Activation Successful; Activation Successful; DatabaseMail process is started; Mail Successfully Sent; Mail Successfully Sent; DatabaseMail Process is shutting down. Now all we get are the first two activation successful messages. After reviewing the Databasemail.exe.config it lists .net version 4.6, however we have 4.7 so I'm not sure if that could be a cause. I know previous versions needed .net v3.5. It's also worth noting that the email stored procedures worked perfectly before having to reinstall the sql server instance and restore the databases. The database mail profile is still the same as well, and the sql server service account that was previously used. Edit: After deleting all accounts and profiles linked to the database mail feature and starting that configuration over from scratch we did get the test mail working, in some of our stored jobs we now run into an error message "Msg 14607, Level 16, State 1, Procedure msdb.dbo.sysmail_verify_profile_sp, Line 42 [Batch Start Line 0] profile name is not valid" Edit2: The latest error was because the scheduled tasks called for a specific profile name in their steps.

**Database mail is not working:** Below Error while sending a test mail Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. (Microsoft SQL Server, Error: 14641) Checked all the settings of the service broker and Database Mail XPs all are enabled only. I ran the DBCC CHECKDB command on msdb also no error or inconsistency was found. while starting the database mail using this query sysmail_start_sp (dbo.sysmail_help_status_sp;) using this query we checked the status it is in the started state within 20 seconds automatically it returned to the stopped state. Please help me out and give your suggestions.

In SQL Server, I want to restrict all users (except a few specific users) to send an email with attachment. For example, someone import data into excel file and send it via mail using the sp_send_dbmail procedure. Can we restrict use of this procedure? Or is there any other way to do it? There are some users that have sysadmin permission, I want to restrict them too. If it is not possible, at least I want to get notification when someone sends an email with attachment. Trigger or procedure might work, but I could not get any result.

Below is the error message I see in windows event log. No errors while executing sp_send_dbmail 1) Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: There was an error on the connection. Reason: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.), connection parameters: Server Name: LAM90DB01, Database Name: msdb Data: System.Collections.ListDictionaryInternal TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo) HelpLink: NULL Source: DatabaseMailEngine HResult: -2146232832 StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo connectionInfo) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout) Exception Type: System.ComponentModel.Win32Exception NativeErrorCode: -2146893019 ErrorCode: -2147467259 Message: The certificate chain was issued by an authority that is not trusted Data: System.Collections.ListDictionaryInternal TargetSite: NULL HelpLink: NULL Source: NULL HResult: -2147467259

I work in a large retail business with a decent SQL Server environment. We have SQL Server replication, Availability Groups, Data warehouse, and some cloud services. In this beautiful environment, we use desktop applications with limited features. The business always needs some extra reports that the application support team delivers them late most of the time. As a senior DB Admin, they come back to me to try extracting the data form SQL Server to Excel files. As we grow larger and larger it has become almost like a daily task, I’ve managed to develop a C# tool to send most of those reports by mail to every one who needs them, but now it has become outdated and to be honest it takes a lot of time to be rewritten. Is there a tool that can run SQL query and add the results to Excel file and mail it to specific emails list ?

I have recently implemented an Agent Job which checks SQL Server every 10mins for any long running queries and if detected it will send out a mail to recipients with the information. However since putting this in, I notice alot of the below query and wonder if this is something I should be concerned about:

WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

Understand its from Database Mail and the wait info is (1x: 62093ms)BROKER_RECEIVE_WAITFOR but should I need to worry or simply exclude it from the alerting. Looking at it via sp_whoisactive can see that the open_transaction count is 1 and the status is suspended. Any help is appreciated.

On one of our production servers (SQL SERVER 2017 Enterprise) I get the following error whenever I right click and enable a Job in the SQL Server Agent: Alter failed for Job 'My Job'. (Microsoft.SqlServer.Smo) ... At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject". (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3162&EvtSrc=MSSQLServer&EvtID=14624&LinkId=20476 ------------------------------ Server Name: MyServer Error Number: 14624 Severity: 16 State: 1 Procedure: msdb.dbo.sp_send_dbmail Line Number: 242 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) The Agent user is a sysadmin. I'm able to send a mail using the procedure and the mail profile found in the agents Alert properties. I'm not sure what else to check. Has anyone else run into this?

I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, and SQL Server Agent will also send error emails to me, so my email profile seems to be set up correctly. The code:

BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = 'myaccount@emailcom',
@subject = 'Bad Data Report',
@query = N' USE Database;
   DECLARE @yesterday date = getdate() - 1;  select * from Timecards WHERE Date = @yesterday AND (DailyHours > 24.1 OR DailyHours < 0 ) ',
@exclude_query_output = 1,
@attach_query_result_as_file = 1,
@query_result_header = 1,
@query_attachment_filename = 'BadDataQuery.txt'
END

Following some advice from similar issues on this site, I tried looking through sysmail_allitems and sysmail_faileditems, but neither log any attempts to send. I also created a stored procedure that I had the job call as a roundabout way to send the email, but that also did not send an email nor leave an error. Any ideas what might be going on?

I have a simple script that should send an attachment using send_dbmail. It **works** when I exclude the attachment or send a very small file, e.g. 5KB. The prod file I want to send is 280KB. Exec msdb.dbo.sp_send_dbmail @profile_name = 'Activeprofile', @recipients = 'Dave@Dave.com.au', @subject = 'EmailStuff', @file_attachments = 'D:\SSIS\SSISData.csv' The Database mail configurations are default at 1MB file sizes and the only error I get in the database mail log is > Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2020-07-22T15:25:34). Exception Message: Cannot send mails to mail server. (Failure sending mail.). ) This is SQL 2016 SP1 CU5 (Yes I want to patch but management are 'scared') The process fails running from SQL Agent Job or Query window. The email targets an internal relay server. I am able to send the file using a test SQL server running SQL 2017, I was wondering if this was a bug with SQL 2016.

Is there a good way to determine how many emails have been sent by sql server (sql notifications + invocations of sp_send_dbmail by applications) for a given period of time? Need this info for billing discussions. SQL Server 2014

I need that all my system users can send email from the DB. not to assign everyone the role in the msdb database I created one login/user "send_mail", and grant permission on msdb db, specifically DatabaseMailUserRole. I need that all my users send email execute a store procedure that within execute the statement execute as user='send_mail" before of msdb..sp_send_db_mail. The user send_mail have db_owner, and the DatabaseMailUserRole, but can't send email. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. Anyone can help me please?