63

+1 to @jsauni for the wizard You can also use the following system functions. EXEC msdb.dbo.sysmail_help_configure_sp; EXEC msdb.dbo.sysmail_help_account_sp; EXEC msdb.dbo.sysmail_help_profile_sp; EXEC msdb.dbo.sysmail_help_profileaccount_sp; EXEC msdb.dbo.sysmail_help_principalprofile_sp; The wizard is probably easier to follow but these will give you ...


30

If you open the Database Mail Configuration Wizard again, on the Select Configuration Task, the second option should be to Manage Database Mail accounts and profiles, select this option and click next. You should now be on the Manage Profile and Accounts step, select View, change, or delete an existing account and click next. You should be able to view the ...


21

You can also query this data with a select statement: select * from msdb.dbo.sysmail_profile p join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id join msdb.dbo.sysmail_account a on pa.account_id = a.account_id join msdb.dbo.sysmail_server s on a.account_id = s.account_id If it is a migration that would require you to look this info ...


17

On a whim, when double checking permissions, I double clicked on the actual DB mail executable. The result on the SQL Server 2014 machine was an empty command window. On SQL Server 2016 clicking the DB Mail executable presented this message: I cannot find this requirement anywhere in the SQL Server 2016 documentation, yet it is clearly a requirement. DB ...


15

Here how can you create HTML body part of your mail. Step 1: DECLARE @Body NVARCHAR(MAX), @TableHead VARCHAR(1000), @TableTail VARCHAR(1000) SET @TableTail = '</table></body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;...


14

TLS1.2 is the only version of TLS considered secure now (March 2019). It took considerable time and effort to discover that there are 2 essential, additional settings which are required to get this working which are not well known nor well documented, by Microsoft or on the web generally. The following could save you a great deal of time and effort. These ...


13

Make the alert run a job that runs the EXEC msdb.dbo.sp_send_dbmail with the @query parameter: EXEC dbo.sp_send_dbmail @profile_name = 'mail_profile' , @recipients = 'some@mail.com' , @subject = 'some subject' , @body = 'the body text goes here' , @query = 'EXEC sp_AskBrent;' , @attach_query_result_as_file = 1; The @...


13

Database mail uses a Service Broker queue and an asynchronous background process to actually send emails, so performance shouldn't be a big deal. But logon triggers can easily cause downtime, so they require a great deal of care in writing and testing. Also you could end up with thousands of emails if a developer runs a load test or somesuch. So it's ...


12

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server. Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is: bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T You would then attach the file ...


12

SQL Server core licenses are expensive: using a SQL Server machine's CPU to send thousands of emails has no justification, especially when there are countless better options in terms of features, manageability, setup, troubleshooting. Another point is that tight coupling between the email and database features makes the application difficult to scale, ...


11

Your code is backwards. You are sending the email, then generating the query results. The sp_send_dbmail stored procedure will run the query for you, and append the results either in the body of the email or as an attachment. In case you aren't aware, Microsoft has extensive, comprehensive, and free documentation on their website for SQL Server. For ...


11

Error code 101 is because your query result attachment is larger than the "MaxFileSize" Database Mail configuration option. Investigation My first attempt at figuring this out was to run sp_helpttext on the sp_send_dbmail procedure to look at the implementation: EXEC sp_helptext 'sp_send_dbmail'; The main body of that procedure returns a bunch of ...


10

According to Microsoft Support, there's a bug in SQL server 2016 Setup that causes the database mail not to work without .net 3.5 There's a work around by creating a DatabaseMail.exe.config file in the same folder where the DatabaseMail.exe exists (Binn folder) write the following to the file and save with utf-8 encoding <?xml version="1.0" encoding="...


9

This is a limitation of what can be displayed in the "Messages" tab via either PRINT or RAISERROR. They can either show 4000 characters of NVARCHAR data or 8000 character of VARCHAR data. There are a few ways around this, such as breaking the string into chunks that will work within those limits via a CURSOR. But, if: you have less than 8001 characters (...


8

Yes you can send the report via HTML format, for example as listed in MS: Scenario: This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail ...


8

The problem with your first query is the use of unescaped brackets with the LIKE operator. When using the LIKE operator, there are a few special characters such as brackets that require escaping if you want to look for them. Characters enclosed in brackets will tell the SQL engine to look for a match between any character listed inside (same as regular ...


7

No, it is not required for SQL Server 2017. The issue with Database Mail not working was fixed in 2016 SP1 CU2, and 2016 RTM CU2: FIX: SQL Server 2016 Database Mail does not work on a computer that does not have the .NET Framework 3.5 installed or stops working after applying SQL Server update I know of several SQL Server 2016 and 2017 systems that are ...


6

Yes SMTP server would be good to configure the database mail. Limitations and Restrictions Enabling SQL Server Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so Service Broker can obtain the necessary lock. Also, To configure Database Mail ...


6

You need to first configure DB mail in SQL Server in order to utilise this functionality. You can use the below link as a reference for DB mail configuration purposes. https://www.brentozar.com/blitz/database-mail-configuration/ Once DB mail is set up, you can enable database mail on SQL Server Agent. The same link describes how to do that. Once it is ...


6

Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @...


6

You can use SQL Mail with SQL Server Express edition. Borrowing liberally from Sending Mail using SQL Server Express Edition To configure SQL mail we need to follow below steps. --Create Sysmail Account --Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail account. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = '...


5

You can specify the profile_name in sp_send_dbmail as below : EXEC msdb.dbo.sp_send_dbmail @profile_name = 'here goes your profile name' Refer to BOL for more details. You can Lists the accounts associated with one or more Database Mail profiles using sysmail_help_profileaccount_sp Below is the t-sql that will give you all the details : SELECT [sa]...


5

The file encoding for the attachments is standard UTF-16 Little Endian (LE) -- one of the various Unicode encodings. The first two bytes, shown in the first image, are FF FE. These are the Byte Order Mark (BOM), which is an explicit indication of the files encoding. And you can see in the chart on that linked Wikipedia page that FF FE indicates an encoding ...


5

You can SQL Inject it without much issue, sadly. Here's a simple test I just ran with an elevated account with the results included: USE [master] GO DECLARE @msgbody NVARCHAR(4000) SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile' ...


5

This is actually super easy, and you don't even need to go through the trouble of doing INSERT... EXEC with the stored proc. USE msdb EXEC sp_helptext 'sysmail_help_account_sp' This will return the script info: CREATE PROCEDURE dbo.sysmail_help_account_sp @account_id int = NULL, @account_name sysname = NULL AS SET NOCOUNT ON ...


5

Please read: Enhanced Azure Security for sending Emails – November 2017 Update which outlines the recommendations, options and restrictions for sending emails in Azure. In particular: For Pay-As-You-Go or Microsoft Partner Network subscriptions created after November 15, 2017, there will be technical restrictions blocking e-mail sent directly from ...


5

I don't think SQL Server has any built-in functionality to achieve what you're after. One possible solution is to use a local SMTP server as a relay, so that the email is not encrypted on the initial hop (between SQL Server and the local SMTP server), but it gets encrypted leveraging the SMTP server's functionalities on the second hop (to the outbound SMTP ...


5

Is Database Mail not supported in this SQL Server version? Which SQL Server version (or edition) required to use Database Mail? Database mail is not included when using SQL Server Express edition: Source To be able to use database mail, you could opt for Web, Standard or Enterprise edition. In non production environments you could also go for developer ...


Only top voted, non community-wiki answers of a minimum length are eligible