To configure Database Mail in SQL Server 2005

First, the principles and components of SQL Server e-mail introduction:

Database Mail has 4 components: A configuration file, a mail processing component, an executable file, and a "logging and auditing component."

L Configuration components include:

1) The Database Mail account contains such as the SMTP server name, authentication type, e-mail address, and so on.

2) The Database Mail profile is a collection of Database Mail accounts.

L Mail Processing components

The Database Mail component you want is the Database Mail host database that you just mentioned, which is msdb by default.

L Database Mail executable file

Database Mail uses an external executable file to process messages, reducing the impact on SQL Server. When there is a message to be processed, Database Mail uses the Service Broker Activation External Program (DataMail90.exe) to send the message.

L Logging and auditing components

You can view related logging through the Database Mail log or query Sysmail_log system view.

As with our email, we need a username and password to connect to the mail server via SMTP (Simple message Transfer Protocol). We want SQL Server to send the message, first to tell it the user name, password, server address, Network Transfer Protocol, the port of the mail server ... and other information

Second, enable the SQL Server 2005 Mail feature

Method 1: Open the SQL Server Surface Area configuration:

Click "Functional Perimeter Configuration", select "Database Mail" in the open page, and select "Enable Database Mail stored Procedures"

Method 2: Execute the Enable statement

Use master
Go

exec sp_configure ' show advanced options ', 1
Go

Reconfigure
Go

exec sp_configure ' Database mail XPs ', 1
Go

Reconfigure
Go

Third, enable Service Broker on Database Mail host database

The default database host database is msdb, and Service Broker is enabled by default. You can see if msdb is enabled for service Broker by using the following script:

SELECT is_broker_enabled from sys.databases WHERE name = ' msdb '

Returns "0" if not enabled, or 1 if enabled. You can enable service Broker by using the following script:

ALTER DATABASE msdb SET enable_broker

Iv. Configuring Database Mail

1) Open ssms (SQL Server Management Studio), expand Servers in the Object Browser, manage nodes, right-click on Database Mail,

2) Select "Configure Database Mail" and the "Database Mail Configuration Wizard" dialog box appears.

3) Tap Next, if Database Mail has not been started, a dialog box pops up asking if you want to enable Database Mail and select Yes.

4) Enter "Worklogmial" in the "Profile Name" text box and click "Add" to join an SMTP account.

5) in the New Database Mail Account dialog box, enter the relevant information to determine.

6) Click Next and on the Manage Profile Security page, configure "Worklogmial" as a public profile and click Next.

7) The Configuration Parameters page appears, we leave the default, click Next, and then click Finish.

E. Send Test mail

1) Once again, right-click the Database Mail node in the Object Browser and select Send test e-mail, which appears as follows:

2) in the Data Profile drop-down box, select the "Worklogmail" we just created, fill in the recipient address (such as:[email protected]), click "Send ..." appears: (note: Send the number after the e-mail "16" Is the ID of the test message through which the status of the message can be queried in the log. ）

Or you can send a test message with the following script:

EXEC Msdb.dbo.sp_send_dbmail

@profile_name = ' Worklogmail ',

@recipients = ' [email protected] ',

@body = ' This is a test e-mail ',

@subject = ' Test '

Vi. FAQs

1) Externalmailqueue received an invalid XML message format. Conversation_handle ...

Workaround: Install SP1 at least for SQL Server

2) The message cannot be sent to the recipient due to a mail server failure. (Use account 1 (2007-12-06t10:08:32) to send mail.) Exception message: The message could not be sent to the mail server. (The mailbox name is not allowed.) The server responds as follows: Authorized to send mail, authentication is required).

Workaround: Your SMTP mail server requires authentication, and you do not provide the correct account password or have mistakenly chosen anonymous authentication.

3) Send mail using account 1 (2007-12-06t11:17:08). Exception message: The message could not be sent to the mail server. (not local user; try a different path.) The server response is: Auth error.).

FIX: The SMTP server for some free public mailboxes does not allow this service, please select another SMTP.

Vii. establishing a stored procedure for sending messages

--Send mail template stored procedure

Create PROCEDURE [dbo]. [MyMail]

@mailto varchar (max),

@mailsubject nvarchar (255),

@mailbody nvarchar (max)

As

BEGIN

Set @mailbody =replace (replace (@mailbody, ' &lt; ', ' < '), ' &gt; ', ' > ')

+n ' <br/> Thank you!<br/> wish you a happy work!<br/> sender: Research and development Management platform '

EXEC Msdb.dbo.sp_send_dbmail

@profile_name = ' Worklogmail ',

@recipients = @mailto,

@subject = @mailsubject,

@body = @mailbody,

@body_format = ' HTML '

END

--Log Reminder stored procedure

Create proc [dbo]. [Mailunwirte]

@days int=2

As

If EXISTS (SELECT * from Workweek where DATEDIFF (Day,workday,getdate ()) =0 and Iflag=1)

Begin

DECLARE @calcDate datetime

Select @calcDate =max (workday) from workweek where DateDiff (Day,workday,getdate ()) > @days and Iflag=1

DECLARE @username varchar (50)

DECLARE @mailto varchar (max)

DECLARE @depid varchar (20)

declare @mailbody nvarchar (max)

Declare mailto cursor for

Select S_user.name,s_user.email,s_user. Depid

From S_user

INNER JOIN S_usertransfer UT on ut.userid=s_user.id and UT. Startdate<[email protected]

Left joins Projectworklog L on l.effortstate=1 and S_user.id=l.worker and L.workday<[email protected]

where s_user.iflag= ' 1 ' and s_user.iseffort=1

GROUP by S_user.name,s_user.email,s_user. Depid

Having IsNull (sum (l.effort), 0) <8

• Open mailto

FETCH NEXT from mailto to @username, @mailto, @depid

While @ @fetch_status =0

Begin

Set @[email protected]+ ' Hello:<br/>&nbsp;&nbsp;&nbsp;&nbsp; you up to ' +convert (varchar (10), @ calcdate,20) + ', there is still a work log not completed, please fill out as soon as possible '

exec mymail @mailto, N ' log not filled in reminder ', @mailbody

Select @mailto =isnull (email, ') from S_user inner joins S_DEP on S_DEP. Manager=s_user.id where S_dep.id=depid

If @mailto! = "

Begin

Set @mailbody = ' Hello:<br/>&nbsp;&nbsp;&nbsp;&nbsp; ' [Email protected]+ ' Up to ' +convert (varchar), @calcDate, 20 + ', there is still a work log not completed, please urge it to fill in on time '

exec mymail @mailto, N ' log not filled in reminder ', @mailbody

End

FETCH NEXT from mailto to @username, @mailto, @depid

End

Close mailto

Deallocate mailto

End

Viii. Configuring Scheduled Tasks

1. New Job

2. New Job-General:

Step: General Setup Complete, click "Step" option, click "New" button to enter

Click "Confirm" to return, you can see the step has added a bar.

Plan: Step Setup Complete, click on "Schedule" option, click "New" button to enter

Click "Confirm" to return. You can see that one of the plans has been added.

Other options may not be set.

Click "Confirm" to finish adding the scheduled task.

To configure Database Mail in SQL Server 2005