56

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


27

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


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


16

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


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


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


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

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


10

You are running into the dreaded 'sandboxed' mode of the EXECUTE AS context, as described in Extending Database Impersonation by Using EXECUTE AS. In short code running under EXECUTE AS USER ... is trusted only inside the database context, not at the instance context. There are three ways out: the easy way: mark the current database as TRUSTWORTHY ALTER ...


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

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


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


9

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


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


8

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


7

Odds are the the account with which the Database Mail profile is logging into the SMTP server doesn't have rights within Exchange to send to the distribution list. You need to work with your exchange admin to see what's going on within the Exchange server. There are tracing tools within Exchange they can use to see exactly what's going on.


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

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

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


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 use convert (date, GETDATE()) to get just the 'date' part: select GETDATE(), convert (date, GETDATE()) ----------------------- ---------- 2012-11-16 08:58:20.750 2012-11-16 And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it. ...


5

You are far better off simply inserting into a mail queue table in the trigger and having a separate email process handling the mail - either in SQL Server Agent or an external program. This gives you the benefit of using the trigger as you desire, relatively low latency as the other job can see the mail once it's committed, scalability of managing the mail ...


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

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

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