I have set up a SQL Server Agent job to call sp_send_dbmail with a very simple select on a certain table in a certain database. Within the properties > permissions of the target database, the Database Role 'public' has select permission. The job runs fine, the email arrives, all good. The problem is, if I turn off the worryingly generous 'public' select permission and try to add the SQLServerAgent user (which is the user the agent is running under and the user that owns the agent job) and give them select permissions, the job fails with this error message: > Failed to initialize sqlcmd library with error number -2147467259. > [SQLSTATE 42000] (error 22050) Can anyone shed any light on this for me? I suspect that maybe a different user is involved in some way. I cannot get SQL Server Profiler to run (it's not installed) and I don't have access to the server to install it.

I'm trying to send an email through SQL Server. The sp_send_dbmail has a parameter to specify the path of a file to send as an attachment. It works perfectly when the file is in the same server where SQL Server is hosted. However, I need to send a file that is stored on another server or computer in the same network. How can I specify the path for such file? Edit: I see no errors in the server agent logs nor dbmail. I ran this query: Select * FROM sys.dm_server_services and gave the account under the service_account column full control of the file in the remote server. This is how I'm calling the sp: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile', @recipients = 'email@email.com', @subject = 'Testing Attachments', @file_attachments = '\\servername\c$\Temp\OutputReport.pdf' Error: Msg 22051, Level 16, State 1, Line 0 Attachment file '\\\servername\c$\Temp\OutputReport.pdf' is invalid.

I want to test sending emails from multiple stored procedures but redirect them all to one email instead of spamming real users. ***Is it possible to configure sysmail to only send email to one address, whatever the specified recipients?***

I have a Production database that is able to send mail. And a Test that is not able to. They are both on the same SQL Server. The error is (SQL Server 2008): > Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'. I am an intern and have no previous experience with SQL server (just SQL DML, DDL). I tried to debug this by looking at the permissions of the 2 databases but they are the same. So I don't even know how people can send mail from the production. I looked online and in order for users to be able to send mail they have to be a user under msdb and have the DatabaseMailUserRole. In my case it's a Windows domain. What's funny is that this domain isn't there, so how is production sending mail? I also tried putting the domain there as a user and gave them the permission, but still no luck for the test database. How do I determine why/how the production database is able to send mail, so that I can debug? Thank you, I would appreciate any help. Let's say it's too complicated to debug and I want to copy the production database to the test database (as an exact duplicate), will all permissions carry over?

I have innumerous reports that I send from SQL Server to people. But for some reason there's this single one, with 10k+ rows, that I can't send via dbmmail. I tried top 7000 and it worked but top 8000 still creates the file, I can open it, but I can't send via dbmail. No errors or alerts about SQL attachment size limit. File is 1.5MB and I don't receive any error message. I changed only the attachment name to see if the code was wrong but it sent the new file (the smaller one). It's not an attachment size error because I configured it. Is there a row limit to send Excel files via dbmail? SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'failed' Only says "failed" on these items. Well code is here for people that wants to send excel files by email with header: EDIT: I did some tests. I created an .xls file with 7k rows and only 1 column. 15kb file and it was sent. Then I created a file with 26k rows. 1 column. File was sent. 170KB. And to my surprise, I ran the query, 9krows (bcp crates a 2mb file) and I just copied into an .xls file. The file was 160KB and I could send it via dbmail. I'm sure it's a size problem and not row limits. why is bcp creating such big file? SELECT * FROM msdb.dbo.sysmail_sentitems says the file was sent, but it wasn't. SELECT * FROM msdb.dbo.sysmail_unsentitems says "retrying". After some minutes there's 0 rows and still I didn't receive the email. SQL is configured to send 20MB files. ------------------------------------------- create TABLE ##tempsss ( ..fields VARCHAR(XX) --it needs to be varchar to be able to use header ) insert into ##tempsss SELECT ...fields... FROM some table or view go ------------------------------------------------------------- --passo 2 -- exec xp_cmdshell 'bcp "select ''field1'',''field2'',''field3'' UNION ALL select * from table or view" queryout "filepath\filename.xls" -U sa -P password -w -S servername' ------------------------------------------------------------- EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dba profile or something', @recipients = 'recipients email', @subject = 'email subject', @file_attachments='path\filename.xls' ------------------------------------------------------------- drop table ##tempsss -------------------------------------------------------------

Situation: we currently send emails from our sql 2012/asp app using the msdb.dbo.sp_send_dbmail process and implement multiple different domain accounts. It works (worked) well for us to send authorised emails from our application on behalf of our customers. Now that OAuth2 is being properly maintained, we now need to be able to connect to email accounts that are implementing OAuth2 for Authentication (or any of the other Authentication protocols eg encrypted password). Is this possible? Is the answer a different email agent? Who as solved this and how?

I'm getting an error when trying to send an e-mail through DbMail, apparently because the recipient has an accent on the e-mail. Is there anything inside SQL Server that can fix this (or that a DBA can do)? This is the error message I'm getting through 'sysmail_event_log' The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2024-11-01T18:41:43). Exception Message: 1) Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.MailFramework.Exceptions.BaseMailFrameworkException Message: Cannot send mails to mail server. (The client or server is only configured for E-mail addresses with ASCII local-parts: someMailNáme@gmail.com) I tried checking the database mail settings but there is nothing more than just the basic settings there, like the address, server and login info. Edit: Checked with the team in charge of e-mail in the company and there was nothing they could change from our side. Also found this StackOverflow post with a similar issue that basicallly answer my question, SendGrid has no support for non-ASCII characters in the local-part of the email. This issue was open in 2018 and to this day there is still no fix for this case. https://stackoverflow.com/questions/48270879/send-internationalized-email

I set up database mail on a SQL Server. When I send a test e-mail form SSMS it works. When I try to send an email using msdb.dbo.sp_send_dbmail via the query window I get: Mail (Id: xx) queued. but email not sent. log of email that sent via test e-mail form SSMS (sent successfully): Profile ID | Recipients| Copy Recipients | Blind Copy Recipients | Subject | From Address | Reply To | Body | Body Format | Importance | Sensitivity | File Attachments | Attachment Encoding | Query | Execute Query Database | Attach Query Result as File | Query Result Header | Query Result Width | Query Result Separator | Exclude Query Output | Append Query Error | Send Request Date | Send Request User | Sent Account ID | Sent Status | Sent Date | Last Mod Date | Last Mod User | |--------------|------------|------------------------|------------------|-----------------------|---------|--------------|----------|------|-------------|------------|-------------|------------------|---------------------|-------|------------------------|----------------------------|--------------------|-------------------|------------------------|----------------------|-------------------|-------------------------|-------------------|-----------------|-------------|------------------------|------------------------|----------------| 17 | myemail@gmail.com | NULL | NULL | Subject | NULL | NULL | Body | TEXT | NORMAL | NORMAL | NULL | MIME | NULL | 0 | 1 | 256 | 0 | 0 | 0 | 0 | 2024-10-10 12:10:02.467 | myuser | 11 | 1 | 2024-10-10 12:10:08.000 | 2024-10-10 12:10:08.900 | sa | log of email that sent via query (queued but not sent):

EXECUTE msdb.dbo.sp_send_dbmail
	@recipients = 'myemail@gmail.com',
	@subject = 'Subject',
	@body = 'Body',
	@profile_name = 'MyProfile'

| Profile ID | Recipients | Copy Recipients | Blind Copy Recipients | Subject | From Address | Reply To | Body | Body Format | Importance | Sensitivity | File Attachments | Attachment Encoding | Query | Execute Query Database | Attach Query Result as File | Query Result Header | Query Result Width | Query Result Separator | Exclude Query Output | Append Query Error | Send Request Date | Send Request User | Sent Account ID | Sent Status | Sent Date | Last Mod Date | Last Mod User | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 17 | myemail@gmail.com | NULL | NULL | Subject | NULL | NULL | Body | TEXT | NORMAL | NORMAL | NULL | MIME | NULL | 0 | 1 | 256 | 0 | 0 | 0 | 0 | 2024-10-10 12:01:15.897 | myuser | NULL | 0 | NULL | 2024-10-10 12:01:15.897 | sa | I checked the sysmail_faileditems, and there are no error logs related to what I sent... Why it is working via test e-mail form SSMS but not via query?

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.

I'm using sp_send_dbmail in a SQL agent job triggered nightly. The job queries our database and checks for product price updates, if there are some, it will send them in an email as an attachment to our e-commerce vendor. They have some automatic processes they will update our e-commerce platform, however their automated process cannot deal with the file provided by sp_send_dbmail. sp_send_dbmail seems to be putting null characters between all characters in the result set. See result of opening the csv in a hex editor: Viewed through a text editor I see as expected: sp_send_dbmail query here: SET @FileName = 'Update_' + CONVERT(VARCHAR(12),GETDATE),105) '.csv' SET @Query = 'Some Query' EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@domain.com' @query = @Query @attach_query_result_as_file = 1 @query_attachment_filename = @FileName @query_result_separator = ',' @query_result_no_padding = 1 END What's going on here? ****EDIT FOR ADDITIONAL CLARIFICATION**** Using Microsoft SQL Server 2008 R2 Collation is Latin1_General_CI_AS Leaving no_padding out leaves several trailing spaces in each returned field. each space (20) separated by a null (00)

**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.

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and execute. I entered a rule for the firewall outbound port 587. I tried another mail account and profile with the same unsent issues. The only entries in the logs (db mail logs) are starting and ending of the service. There are no errors anywhere that I can find. The emails appear to simply enter the send queue and never leave it. The accounts can send and receive email on their own and from a SQL Server 2014 instance on another machine. I've got a queue of items with sent status "unsent" and checked all the normal places with expected results in all of them, aside from a long queue of unsent mail: SELECT * FROM msdb..sysmail_event_log order by log_id DESC SELECT * FROM dbo.sysmail_mailitems SELECT * FROM dbo.sysmail_sentitems USE msdb SELECT sent_status, * FROM sysmail_allitems SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'; EXECUTE msdb.dbo.sysmail_help_status_sp I have tried turning it off and on again... so did I miss a DMV etc. that could shed light on this situation? Is this a known issue with SQL Server 2016 that I haven't in my searches? Any other possible steps to get this mail sent?

The SQL Server alert system configuration page (see below image) allows me to choose only a single mail profile. Does that mean I can only use a single email address to send notifications in my SQL Server agent jobs? Basically I want to send out email using different email address, so that in the email client, like Outlook, I can filter and manage emails based on different sender email address. Is this possible?

Here's the problem we are having: I have Job Notifications turned on for several SQL Server nightly Jobs. Sometimes those email notifications come to me when I'm out of office, and my Outlook is set up to automatically send "out of office" emails. **The problem is that the "out of office" emails are going to my coworker.** Similarly, when my stored procedures sends users a warning email, if the users have "out of office" emails turned on, my co-worker gets all those "out of office" emails. My coworker is annoyed. My co-worker set up our SQL Server database mail. He set up an account called 'Internal DBAs' and then put his own email as the email for the 'Internal DBAs' account. The "Internal DBAs" account is the only database mail account currently set up on the SQL Server. I'm not very familiar with SQL Server database mail, but I'm guessing that this is why my coworker is getting all the "out of office" emails. I would think that the solution would be to substitute my coworker's email address with an un-monitored email address to use as the database mail account. That way no one gets the "out of office" emails. We didn't have this problem with our old SQL Server. I don't know why he set things up this way, but I think my coworker thinks that he needs to have his email address as the email to use for the database mail account. Is there a good reason to use a DBA's personal email address as the address for the database mail account? Does anyone have a sense of what is standard practice? Or am I mistaking the cause of the problem? Any illuminating thoughts would be most appreciated!

I am trying to set up email from my SQL Server 2014 installation. As I have the Express Edition, I do not have the Database mail wizard, but I seem to have all the stored procedures in the msdb database. The error message is: *The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account X (date). Exception Message: Cannot send mails to mail server. (Failure sending mail.).* It seems to me that the error indicates a problem with the email connection, rather than SQL Server, so I tried to send from a different email address – but with the same error. Both of these outgoing email addresses work reliably for my normal work (using Thunderbird). The set-up data was copied from Thunderbird. I have used the set-up SQL from https://www.codeproject.com/articles/485124/configuring-database-mail-in-sql-server -- Enable service broker in the MSDB database. USE [master] GO ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT GO --Enabling Database Mail sp_configure 'show advanced options',1 reconfigure; GO -- Enable the db mail feature at server level sp_configure 'Database Mail XPs',1 reconfigure; --Creating a Profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'SQLProfile', @description = 'Mail Service for SQL Server' ; -- Create a Mail account EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQL_Email_Account', @email_address = 'somebody@gmail.com', @mailserver_name = 'smtp.googlemail.com', @port=465, @enable_ssl=1, @username='somebody@gmail.com', @password='Emailid password' -- TODO ENSURE VALID PASSWORD FOR THE ACCOUNT IS ENTERED ABOVE -- Adding the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'SQLProfile', @account_name = 'SQL_Email_Account', @sequence_number =1 ; -- Granting access to the profile to the DatabaseMailUserRole of MSDB EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'SQLProfile', @principal_id = 0, @is_default = 1; The above is not intended to be run as a single script; each step was performed separately and each step worked. Email was tested with: --Sending Test Mail -- See https://msdn.microsoft.com/en-us/library/ms190307.aspx for all arguments EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLProfile', @recipients = 'somebodyelse@yahoo.co.uk', @body = 'Database Mail Testing', @subject = 'Database Mail from SQL Server'; select * from msdb.dbo.sysmail_event_log Then try a different email address: USE [master] GO -- Create a Database Mail account 2 EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'SQL_Email_Account 2', @description = 'Alternative Mail account', @email_address = 'somebody3@gmx.co.uk', @display_name = 'Alternate GMX', @port=465, @enable_ssl=1, @username='somebody3@gmx.co.uk', @password='ValidPassword', @mailserver_name = 'mail.gmx.com'; GO -- Add the account 2 to the profile EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'SQLProfile', @account_name = 'SQL_Email_Account 2', @sequence_number = 2 ; -- Make one not the default EXECUTE msdb.dbo.sysmail_update_principalprofile_sp @profile_name = 'SQLProfile', @principal_name = 'public', @is_default = 0 ; -- Show the new default profile EXEC msdb.dbo.sysmail_help_principalprofile_sp But this also failed with the same error. There are no messages in the SQL Server logs nor my firewall log. I have tried some diagnostics that web searches have suggested: Use msdb Go select * from sysmail_profile select * from sysmail_account select * from sysmail_profileaccount where profile_id=1 select * from sysmail_server EXEC msdb.dbo.sysmail_help_account_sp; EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'SQLProfile'; EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'; EXEC msdb.dbo.sysmail_help_principalprofile_sp; EXEC msdb.dbo.sysmail_help_status_sp; exec [dbo].[sysmail_configure_sp] 'LoggingLevel', 3 All the return values are normal, so I’ve not posted a duplicate of the set-up data. My only problem is that setting LoggingLevel to verbose does not seem to have any effect – is that data stored elsewhere that I have not looked? Any hints would be appreciated, either to fix the problem or for more diagnostics. Thank you.

I have deleted the job and I don't see it in the object explorer nor in sysjobs, and yet it is sending out notifications when it 'fails'. I've done the following solution: 1) Restart MSSQLSERVER & Agent + restart machine 2) Clean up database mail & past logs Side note: 1) There's only one SQL server instance 2) This email triggers when job fails under SQL Server Agent

I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution. So here is my problem: As per our company policy, we want database mail to be able to send emails over port 25 with TLS 1.2 enabled and with TLS 1.0 & TLS 1.1 disabled. Our mail server is Exchange Server 2010, our SQL Server 2016 (Developer and Enterprise editions) boxes have OS of Windows Server 2016 Standard editions. Our SQL Server version is: select @@version ---------------------------------------- Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) Dec 22 2017 11:25:00 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor) We have the DB mail configuration as shown here. The issue is whenever we turn on SSL use msdb exec dbo.sysmail_update_account_sp @account_id=2, @enable_ssl = 1; We CANNOT send db mail (no matter whether our SMTP authentication is Windows Authentication, Basic authentication or Anonymous Authentication). The error message in db mail log is as follows: > Message > > The mail could not be sent to the recipients because of the > mail server failure. (Sending Mail using Account 2 > (2018-07-30T10:52:41). Exception Message: Cannot send mails to mail > server. (Failure sending mail.). ) But if we turn off this SSL, there is no problem for db mail sent out. So how can we enable SSL and uses TLS 1.2 for db mail? I have enabled TLS 1.2 by adding registry as shown below The details is from this link (see the FAQ section)

I have the following query: select COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections' , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered' , COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low' , COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High' , COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad' , COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator' , COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad' , COUNT(*) as 'Total Live' from AccountExtensionBase as a INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId where New_AccountStage = 7 and AddressTypeCode is null I want to set this up to automatically email the values on a daily basis. How can I convert this into a stored procedure and use. I am using SQL server 2008

I'm having some problens with SQL Database mail, basicaly anything that I try to do gives me an error like this one below: >Transaction aborted when accessing versioned row in table 'dbo.sysmail_profile' in database 'msdb'. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning. The error is when I try to delete a profile from database mail, but if I run a query related to change/create a profile, it gives me an error similar to this one. EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notification'; My tempdb is not full and there is lot of space in the tempdb and in the disk, I also tried to restart the database, but it did not work. Any ideas about what this error can be? I'm kinda stuck with this problem and could not find a solution.