Configure the mail server (SMTP simple step)

Steps:

 

1. Install SMTP

 

2. configure SMTP and configure the mail server used for the mail you want to transfer. For example, if your SMTP is used for Gmail mail, you need to set SMTP. of course, you can configure your own POP3 server, create your own domain name, and create your own mail.

 

More info, see here:

 

Http://fmuntean.wordpress.com/2008/10/26/how-to-configure-iis-smtp-server-to-forward-emails-using-a-gmail-account/

 

3. enable database mail function (sql20008 or 2005)/enable SQL Agent service via ruuning services. MSC in cmd

 

4. Configure database mail account and Profile

 

More Info:

Http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

Or

Http://www.db-staff.com/index.php/microsoft-sql-server/90-configure-database-mail

 

5. In Step 4, configure "from" email address, and "reply" email adress.

 

6. Execute the following script (or right-click Database mail to sent a test ):

 

Exec MSDB. DBO. sp_send_dbmail
@ Profile_name = 'johntest', -- Name of the profile you created in Step 4
@ Recipients = 'mmm @ hotmail.com ', -- who do you want to send an email?
@ Subject = 'aaa ',
@ Body = 'just a test ',
@ Body_format = 'html ';

 

Example:

Suppose there is a requirement that a user will receive an email prompt after registering the user information on the website.

 

Database layer:

Create Table user; user (userid, name, email, age, sex ,....)

Create an emailqueue table (emailqueueid, userid, to, subject, body .....)

Create a trigger on the talbe emailqueue. Trigger tr_sendnotificationmail to trigger the operation as inseration. Trigger will contain a similar script:

.....

.....

Declare @ to string

Declare @ s string

Declare @ B string

.....

.....

Exec MSDB. DBO. sp_send_dbmail
@ Profile_name = 'johntest', -- Name of the profile you created in Step 4
@ Recipients = @ to -- who do you want to send an email?
@ Subject = @ s,

@ Body = @ B,

@ Body_format = 'html ';

 

Create a stored procedure sp_insertuser. This stored procedure inserts data into Table usere and calls sp_insertemail.

Create a stored procedure sp_insertemail. This stored procedure inserts data into the emailqueue and is called by sp_insertuser.

 

Process:

User a registration --> program calls sp_insertuser --> User A is inserted into the user table; sp_insertemail is called --> email information is inserted into the emailqueue table

---> Trigger tr_sendnotificationmail is triggered ---> send an email