How to send HTML-format mails in SQL Server, SQL Server

How to send HTML-format mails in SQL Server, SQL Server

SQL server sends an html-format email. The reference code is as follows:

DECLARE @ tableHTML NVARCHAR (MAX); -- get the current system time and data statistics time set @ d_nowdate = convert (datetime, convert (varchar (10), dateadd (day,-1, getdate (), 120), 120); -- if data exists (select top 1 * from t_table1 (nolock) where d_rq = @ d_nowdate) begin set @ str_subject = 'xx '+ convert (varchar (10), @ d_nowdate, 120) + 'Net worth. '; SET @ tableHTML = n' <H1> XX </H1> <br> currently in test <br> <table border = "1"> '+ N' <tr> <th> date </th> <th> fund code </th> <th> fund name </th> <th> net worth </th> <th> cumulative net worth </th> </tr> '+ CAST (select convert (varchar (10 ), @ d_nowdate, 120) as 'td ', '', VC_JJDM as 'td','', Vc_jjmc as 'td ', '', en_jdwjz as 'td ','', EN_LJJZ as 'td 'from t_table1 t left join t_table2 tt on t. VC_JJDM = tt. c_FUNDCODE where d_rq = @ d_nowdate order by Vc_jjmc, VC_JJDM for xml path ('tr '), ELEMENTS -- TYPE) as nvarchar (MAX) + N' </table> '; -- send the email exec @ I _result = msdb. dbo. sp_send_dbmail @ profile_name = 'profile-mail', @ recipients = 'email address 1; email address 2; email address 3', @ subject = @ str_subject, @ body = @ tableHTML, @ body_format = 'html'; end

The mail effect is as follows:

XX net worth

Currently in testing

Date	Fund code	Fund name	Net worth	Cumulative net worth
2013-12-20	111111	AAAAA	0.98300000	0.98300000
2013-12-20	222222	BBBBB	1.04900000	1.04900000
2013-12-20	333333	CCCCC	0.64000000	0.64000000
2013-12-20	444444	DDDDD	0.99400000	0.99400000
2013-12-20	555555	EEEEE	1.05700000	1.05700000
2013-12-20	666666	FFFFF	0.73400000	0.73400000

How to create SQL Server 2005 database emails

Database Mail replaces SQL Mail. It uses a simple SMTP server instead of the MAPI account required by SQL Mail to send emails. This allows your organization to send emails with attachments and query results, append query results, and format HTML emails. You can also use it to set many other configurations without having to own an Exchange server or configure any type of MAPI work zone. In addition to SMTP, Database Mail has many advantages:
It runs outside the database engine, so it has the least pressure on the database engine.
It supports clusters and fully supports the cluster environment.
Its user Profile allows redundant SMTP servers. (I will discuss this in detail later in this article .)
It allows you to send query text to stored procedures in the form of parameters. Stored Procedures will execute queries and send results in emails.
Messages are sent asynchronously through a Service Broker queue, so you do not have to wait for a response when sending an email. It provides multiple security protection for email sending, such as a filter that controls the attachment extension and an attachment size manager. Before creating and using Database Mail to create a Database Mail solution, you need to make some planning work. First, you must have a valid SMTP server to send emails. If you do not have an SMTP server, refer to Microsoft Knowledge Base Article 308161 to learn about how to create an SMTP server. If you cannot determine whether the organization has an SMTP server, ask your network administrator to obtain the machine name or the IP address of the server. Your network administrator may need to configure the Server so that SQL Server can send emails. In Database Mail, the Account stores the information that the Database engine uses to send email messages. An account only saves information for an email server, such as the account name, email address, reply email address, server name or IP address, and optional security settings. To send a Database Mail email, you must use a user Profile ). User information is set up for one or more accounts. This kind of user information-account settings are very useful. It allows you to associate several accounts with one user profile, which means you can associate several email servers with one user profile. Therefore, when you try to send an email, the system will try every account in the user profile until the message is successfully sent. This setting is useful if one or more SMTP servers fail. It also allows you to develop application code for sending emails without worrying about modifying the Profile name for different environments. You can use the same Profile name in the development and production environments. The only difference is that the accounts in user data are different. It is time to learn how to create a Database Mail account. In our example, I assume that you are sitting in front of a development machine with system administrator access permissions. If you do not have the system administrator permission, you need to become a member of the database DatabaseMailUserRole. The following script creates some variables that I will use throughout the instance.

SQL server 2005

You can create a trigger, create an identical temporary table, and add two columns. One is to save the trigger time and the other is to save the operation. Such as insert and update.

This trigger is triggered each time data is modified, and the updated data is inserted into the temporary table.

When querying a temporary table in the future, you can see when the column position changes through comparison.