Correctly configure and use SQL mail

 

Introduction to the extended stored procedure for sending, receiving, and automatically processing emails using SQL Mail

SQL SERVER provides an extended storage process for sending and receiving emails through EXCHANGE or OUTLOOK. The following describes these processes.

1. Start SQL Mail

Xp_startmail @ user, @ password

Both @ user and @ password are optional.

You can also open Support Services in Enterprise Manager, right-click SQL Mail, right-click the menu, and then press Start to Start

Ii. Stop SQL Mail

Xp_stopmail

You can also use Stop in the menu in the above method to Stop

3. Send emails

Xp_sendmail {[@ recipients =] 'recipients [;... n] '}
[, [@ Message =] 'message>
[, [@ Query =] 'query>
[, [@ Attachments =] attachments]
[, [@ Copy_recipients =] 'Copy _ recipients [;... n]'
[, [@ Blind_copy_recipients =] 'blind _ copy_recipients [;... n]'
[, [@ Subject =] 'subject>
[, [@ Type =] 'Type>
[, [@ Attach_results =] 'Attach _ value>
[, [@ No_output =] 'output _ value>
[, [@ No_header =] 'header _ value>
[, [@ Width =] width]
[, [@ Separator =] 'separator>
[, [@ Echo_error =] 'echo _ value>
[, [@ Set_user =] 'user>
[, [@ Dbuse =] 'database>

@ Recipients is required.

Parameter description:

Parameter description
@ Recipients separated by commas
@ Message: the message to be sent.
@ Query: determines whether to execute a valid query that is attached to the email. In addition to the Insert table and delete table in the trigger, this query can reference any object.
@ Attachments attachment
@ Copy_recipients CC
@ Blind_copy_recipients BCC
@ Subject title
@ Attach_results: Specify the query result to be sent as an attachment.
@ No_header: name of the column that does not send the query result
@ Set_user: query the connected user name. The default value is Guset.
@ Dbuse: the database used for query. The default value is the default database.

4. read emails in the inbox

Xp_readmail [[@ msg_id =] 'message _ number> [, [@ type =] 'type' [OUTPUT]
[, [@ Peek =] 'peek>
[, [@ Suppress_attach =] 'reress _ attach>
[, [@ Originator =] 'sender' OUTPUT]
[, [@ Subject =] 'subobject' OUTPUT]
[, [@ Message =] 'message' OUTPUT]
[, [@ Recipients =] 'recipients [;... n] 'output]
[, [@ Cc_list =] 'Copy _ recipients [;... n] 'output]
[, [@ Bcc_list =] 'blind _ copy_recipients [;... n] 'output]
[, [@ Date_received =] 'date' OUTPUT]
[, [@ Unread =] 'unread _ value' OUTPUT]
[, [@ Attachments =] 'attachments [;... n] 'output])
[, [@ Skip_bytes =] bytes_to_skip OUTPUT]
[, [@ Msg_length =] length_in_bytes OUTPUT]
[, [@ Originator_address =] 'sender _ address' OUTPUT]

Parameter description:

Parameter description
@ Originator sender
@ Subject topic
@ Message
@ Recipients recipient
@ Skip_tytes: the number of bytes skipped when reading mail information. It is used to obtain Mail Information segments in sequence.
@ Msg_length: determines the length of all information. It is usually processed with @ skip_bytes.

5. process the next email in sequence

Xp_findnextmsg [[@ msg_id =] 'message _ number' [OUTPUT]
[, [@ Type =] type]
[, [@ Unread_only =] 'unread _ value>)

6. delete emails

Xp_deletemail {'message _ number '}

If no email number is specified, all emails in the inbox are deleted.

7. automatically process emails

Sp_processmail [[@ subject =] 'subject>
[, [@ Filetype =] 'filetype>
[, [@ Separator =] 'separator>
[, [@ Set_user =] 'user>
[, [@ Dbuse =] 'dbname>

 

 

> After a user registers Online, the system sends the randomly generated password to the user registration Email.
> When a user replies to a forum post, the content is sent to the user's Email.
Because the above processes are all completed in the storage process, it is convenient to avoid the parameter transmission and processing by the foreground program without using third-party components.

1. In order to use SQL mail, first you have an SMTP service on your server. Instead of installing the SMTP provided by win2000 server, I use imail6.04 SMTP, which is stable and powerful.
2. install an email system. I installed outLook 2000 and found that when configuring the mail profile, if
If outLook is not installed but other third-party programs are used, "email" cannot be found in the control panel of win2k Chinese server.
3. after outlook is installed, refresh the control panel and you will find the email item. Double-click the email configuration to name the configuration file (for example, myProfile ), in this configuration file, you can set attributes for SQL mail later.
4. Start outlook (set myProfile as the default configuration file) and test sending and receiving emails to confirm that outlook works properly.
5. start SQL server with the current domain account. In the Enterprise Manager support service, click SQL mail attributes. You can see that in the configuration file selection, the myProfile configuration file you just defined appears (you can also define multiple profiles). Select this configuration file for testing. The SQL statement returns the information about the successful start and end of an MAPI session, if an error occurs or the email configuration file is not found, it must be because the account you used to start SQL server has a problem.
6. Now you can use the XP_sendmail extended stored procedure in the query analyzer to send SQL mail in the following format:
Xp_sendmail {[@ recipients =] 'recipients [;... n] '}
[,] [@ Message =] 'message>
[,] [@ Query =] 'query>
[,] [@ Attachments =] attachments]
[,] [@ Copy_recipients =] 'Copy _ recipients [;... n]'
[,] [@ Blind_copy_recipients =] 'blind _ copy_recipients [;... n]'
[,] [@ Subject =] 'subject>
[, [@ Type =] 'Type>
[,] [@ Attach_results =] 'Attach _ value>
[,] [@ No_output =] 'output _ value>
[,] [@ No_header =] 'header _ value>
[,] [@ Width =] width]
[,] [@ Separator =] 'separator>
[,] [@ Echo_error =] 'echo _ value>
[,] [@ Set_user =] 'user>
[,] [@ Dbuse =] 'database>

@ Recipients is required.

Parameter description:

Parameter description
@ Recipients separated by commas
@ Message: the message to be sent.
@ Query: determines whether to execute a valid query that is attached to the email. In addition to the Insert table and delete table in the trigger, this query can reference any object.
@ Attachments attachment
@ Copy_recipients CC
@ Blind_copy_recipients BCC
@ Subject title
@ Attach_results: Specify the query result to be sent as an attachment.
@ No_header: name of the column that does not send the query result
@ Set_user: query the connected user name. The default value is Guset.
@ Dbuse: the database used for query. The default value is the default database.

7. however, if you use SQL mail in a web application, there are still some problems to solve: first, the account connecting to the database in the application, my database connection in the website program uses the UDL file and the account is DbGuest. This is a common account. Therefore, you must find XP_sendmail in the extended storage process of the master database, add the DbGuest user to its attributes and select the EXEC permission.
Well, now the settings are complete, the website program is run, and the user registration test has almost no delay. I received the Email from this SQL mail in the test mailbox:
"Thank you for your registration. We recommend that you change your password after the first logon"

 

 

SQL Mail brings great convenience to every database developer and DBA (Database Administrator, the SQL Server database proxy can automatically send an Email to the Administrator when an exception occurs in the system. The developer can use it to allow the database to automatically change the user password on a regular basis, and then send an Email to notify the user ...... And so on, we are freed from the complicated work to varying degrees. However, the configuration of SQL Mail is complicated. I believe that more than 90% of users have encountered various troubles when configuring SQL Mail, at least 70% gave up SQL Mail and chose other solutions to solve the problem. I am a Web developer who has personally experienced this and found a better alternative. Do not dare to exclusive, write it out to attract readers.
There are several ways to configure SQL Mail. The supported software can be divided into Exchange-based, Outlook2000 (or later) and third-party software configuration solutions. The three methods have their own advantages and disadvantages, mainly in the following aspects:

Use the Outlook client with SQL Server to implement SQL Mail
This solution has low software requirements. You only need to install the Outlook2000 or later client on the Server where SQL Server is located. It requires that the OutLook client must be opened during the use of SQL Mail; otherwise, the Mail can only be sent at the next time. In addition, if the server is a remote server, the configuration cannot be completed using Microsoft's official Remote Desktop. The alternative solution is that DBA can directly operate on the IDC or install PcAnywhere10 to replace the Remote Desktop.

High requirements for using Exchange
Microsoft recommends that you use Exchange as the best partner for SQL Mail. According to MSDN, "Microsoft recommends that you use Exchange Server for reliability due to the limitations and logon problems of POP3/SMTP protocols ". However, Exchange is not used for this purpose. It can be said that it is Qu Cai, and Exchange requires the server to configure the Domain manager. I believe this stuff is of little use to most database servers, it's just a waste of resources. If you want to configure SQL Mail on multiple servers, you need to configure the Domain manager on each server, or all servers must be configured in one domain, however, this is unrealistic for systems with scattered servers.

Use third-party system support
Microsoft MSDN said: "If you are using a third-party mail server, you must configure the mail server as a POP3 server. If the local mail services used by these mail servers may be installed by third-party mail clients, Microsoft will not support connection to these servers ". This means that you still need to use the mail service on the Windows platform. Anyone who writes websites using ASP must know that the Cdonts. dll component is actually .......
In the face of these problems, the author has changed to the 70% I just mentioned. Although countless web crawlers and tens of thousands of reads have configured SQL Mail, I still absolutely give up, because I don't want to build 2nd or 3rd servers ...... In the same way. The alternative solution is the Jmail component + OLE Automation Object, which solves the above problems.

Prerequisites
1. OLE automation functions
OLE Automation enables the application to operate on the objects implemented in another application, or expose the objects so that they can be operated. The automated client is an application that can operate on the public objects of another application. This article is worthy of being SQL Server. The application of the public object is called an Automation server and becomes an automation component. In this article, the Jmail component is used. The client operates these objects by accessing the properties and functions of the application objects.
The way to use the Ole component in SQL Server is to extend the storage process sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, sp_OAMethod, sp_OASetProperty, and sp_OAGetProperty by using several systems, for more information, see SQL Server books online.
How to use OLE automation objects:
(1) Call sp_OACreate to create an object.
Format: sp_OACreate clsid, objecttoken OUTPUT [, context]
Parameter: clsid -- the program id (ProgID) of the OLE object to be created ). This string describes the class of this OLE object, in the form of 'olecomponent. object ', OLEComponent is the component name of the OLE Automation server, and Object is the OLE Object name. message ";
Objecttoken -- indicates the returned object and must be a local variable of the int type. Used to identify the created OLE object and use it when calling other OLE automated stored procedures. This article uses it to call the attributes and methods of the JMail. Message component.
Context -- specify the execution Context in which the newly created OLE object runs. This parameter is not used in this article. See the SQL Server online documentation for other usage of all method attributes.
(2) Use this object.
(A) call sp_OAGetProperty to obtain the attribute value.
Format: _ OAGetProperty objecttoken, propertyname [, propertyvalue OUTPUT]
Parameters: (the preceding parameters are omitted .)
Propertyname -- property name of the object;
Propertyvalue -- attribute value of the returned object. This parameter has the OUTPUT attribute. After this operation, you can get the attribute value from propertyvalue.
(B) Call sp_OASetProperty to set the property to a new value.
Format: sp_OASetProperty objecttoken, propertyname, propertyvalue
(C) Call sp_OAMethod to call a method.
Format: sp_OAMethod objecttoken, methodname [, returnvalue OUTPUT] [, [parametername =] parametervalue [... n]
Parameter: Returnvalue -- Return Value of the call method. If no value is returned, this parameter is set to NULL;
Parametername -- the parameter name in the method definition, that is, the form parameter;
Parametervalue -- parameter value;
...... N -- indicates that many parameters can be included, and the number is limited by the method definition;
(D) Call sp_OAGetErrorInfo to obtain the latest error message.
Format: sp_OAGetErrorInfo [objecttoken] [, source OUTPUT] [, description OUTPUT]
Parameter: Source -- error Source;
Description -- error Description;
(3) Call sp_OADestroy to release the object.
Format: sp_OADestroy objecttoken

2. xp_mongoshell extended storage process
The extended stored procedure is in the master database, and its full path is master .. xp_cmdshell (note that there are two points in the middle). Its function is to execute the given command string in the command line interpreter of the operating system and return any output in text lines.
Format: xp_cmdshell {'COMMAND _ string'} [, no_output]
Parameter: 'COMMAND _ string' -- a command string executed on the command line interpreter of the operating system.
No_output -- is an optional parameter. It indicates that the specified command_string is executed, but no output is returned to the client. This parameter is not used in this article.

Procedure
(1) software preparation
Please go to renewal first. If there is only one JMail. dll file, follow the steps below to install it:
(A) Create a text file and run the following command:
Regsvr32 JMail. dll
Net start w3svc
Save it as Install. Bat (Note: Do not save it as Install. Bat. Txt)
(B) copy the file together with Jmail. dll to the System32 directory of the SQL Server database Server, and double-click Install. Bat.
(2) Are you ready? Come with me
(A) run the SQL Server Query analyzer and log on to the SQL Server database as a sa;
(B) If your stored procedure needs to be added to the yourdefacatcatalog database, enter the following command in the blank SQL window. Otherwise, modify the database name accordingly.
Use yourdefacatcatalog
Press F5 or run to run the command;
(C) Create a basic sending Stored Procedure
Copy the following code to the SQL Server command window and run it. The following code has corresponding comments, which are not explained in this article. If you have any questions, please refer to the previous "Preparation knowledge" or query the Help File of SQL Server. Of course, you can also contact the author.

Create Procedure dbo. sp_jmail_send
@ Sender varchar (100 ),
@ Sendername varchar (100) = '',
@ Serveraddress varchar (255) = 'smtp server address ',
@ MailServerUserName varchar (255) = null,
@ MailServerPassword varchar (255) = null,
@ Recipient varchar (255 ),
@ RecipientBCC varchar (200) = null,
@ RecipientBCCName varchar (200) = null,
@ Reciientcc varchar (200) = null,
@ RecipientCCName varchar (100) = null,
@ Attachment varchar (100) = null,
@ Subject varchar (255 ),
@ Mailbody text
As
/*
The stored procedure uses an office automation script to call the Dimac w3 JMail AxtiveX component to send emails instead of SQL Mail.
This method supports "server-side authentication"
*/
-- Declare common variables and error message variables used by w3 JMail
Declare @ object int, @ hr int, @ rc int, @ output varchar (400), @ description varchar (400), @ source varchar (400)

-- Create a JMail. Message object

Exec @ hr = sp_OACreate 'jmail. message', @ object OUTPUT

-- Set email Encoding
Exec @ hr = sp_OASetProperty @ object, 'charset', 'gb2312'

-- Authentication
If Not @ MailServerUserName is null
Exec @ hr = sp_OASetProperty @ object, 'mailserverusername', @ MailServerUserName
If Not @ MailServerPassword is null
Exec @ hr = sp_OASetProperty @ object, 'mailserverpassword', @ MailServerPassword

-- Set mail Basic parameters
Exec @ hr = sp_OASetProperty @ object, 'from', @ sender
Exec @ hr = sp_OAMethod @ object, 'cipcient', NULL, @ recipient
Exec @ hr = sp_OASetProperty @ object, 'subobject', @ Subject
Exec @ hr = sp_OASetProperty @ object, 'body', @ mailbody

-- Set other parameters
If not @ attachment is null
Exec @ hr = sp_OAMethod @ object, 'addattachment ', NULL, @ attachment, 'false'
Print @ attachment
If (Not @ recipientBCC is null) And (Not @ recipientBCCName is null)
Exec @ hr = sp_OAMethod @ object, 'cipiientbcc ', NULL, @ recipientBCC, @ recipientBCCName
Else If Not @ recipientBCC is null
Exec @ hr = sp_OAMethod @ object, 'cipiientbcc ', NULL, @ recipientBCC

If (Not @ recipientCC is null) And (Not @ recipientCCName is null)
Exec @ hr = sp_OAMethod @ object, 'cipiientcc', NULL, @ recipientCC, @ recipientCCName
Else If Not @ recipientCC is null
Exec @ hr = sp_OAMethod @ object, 'cipiientcc', NULL, @ recipientCC

If Not @ sendername is null
Exec @ hr = sp_OASetProperty @ object, 'fromname', @ sendername

-- Call the Send method to Send an email
Exec @ hr = sp_OAMethod @ object, 'send', null, @ serveraddress

-- Catch JMail. Message exception
Exec @ hr = sp_OAGetErrorInfo @ object, @ source OUTPUT, @ description OUTPUT

If (@ hr = 0)
Begin
Set @ output = 'error source: '+ @ source
Print @ output
Select @ output = 'error description: '+ @ description
Print @ output
End
Else
Begin
Print 'failed to get error message! '
Return
End

-- Release the JMail. Message object
Exec @ hr = sp_OADestroy @ object

(D) Simplify Stored Procedure operations to suit our daily usage habits
The above stored procedure can basically complete the mail sending operation, but it is very lengthy and does not meet our habits, for example, it does not support sending multiple SQL commands to the receiver or sending SQL command running results in the form of attachments (this is the function of SQL Mail, we can also do it, therefore, we need to write another stored procedure to call it to simplify operations and Expand functions.

Create Procedure SendMail
@ Sender varChar (50) = null,
@ StrRecipients varChar (200 ),
@ StrSubject varChar (200 ),
@ StrMessage varChar (2000 ),
@ SQL varChar (50) = null)
As

Declare @ SplitStr varchar (1) -- defines the email address delimiter variable
Declare @ strTemp varchar (200) -- defines temporary variables for multiple recipient strings
Declare @ email varchar (50) -- a single recipient string variable separated by a delimiter

Declare @ SenderAddress varChar (50)
Declare @ Attach varChar (200)

Declare @ defasensender varChar (50)
Declare @ MailServer varChar (50)
Declare @ User varChar (50)
Declare @ Pass varChar (50)
Declare @ SenderName varChar (50)
Declare @ AttachDir varChar (100)

-- Initialize the default variable
Set @ defasensender = 'default sending address'
Set @ MailServer = 'email server address'
Set @ User = 'smtp server authentication User address'
Set @ Pass = 'smtp server verification address'
Set @ SenderName = 'default sender name'
Set @ AttachDir = 'e: \ LOG \ WebData \ Jmail \ '+ Replace (Convert (varChar (19), GetDate (), 120 ),'-', ''),'', ''), ':', '{}'.txt'

-- Unify the Email address delimiter into semicolons
Set @ SplitStr = ';'
Set @ strTemp = @ strRecipients + @ SplitStr + 'end'
Set @ strTemp = Replace (@ strTemp ,',',';')

-- Determine whether an SQL statement exists
If (@ SQL is Null) Or (len (@ SQL) = 0)
Set @ AttachDir = Null
Else
Begin
Declare @ brief STR varChar (200)
Set @ resolve STR = 'bcp "'+ @ SQL +'" queryout '+ @ AttachDir +'-C'
EXEC master .. xp_mongoshell @ mongostr
End

While CharIndex (@ SplitStr, @ strTemp, 1) <> 0
Begin
Set @ email = left (@ strTemp, CharIndex (@ SplitStr, @ strTemp, 1)-1)
Set @ strTemp = right (@ strTemp, len (@ strTemp)-len (@ email)-1)
If (@ Sender Is Null) Or (Len (@ Sender) = 0)
Set @ SenderAddress = @ DefaultSender
Else
Set @ SenderAddress = @ Sender
Print @ email
-- Call sp_jmail_send to send an email
EXEC sp_jmail_send @ sender = @ SenderAddress, @ sendername = @ SenderName,
@ Serveraddress = @ MailServer, @ MailServerUserName = @ User, @ MailServerPassword = @ Pass,
@ Recipient = @ email, @ subject = @ strSubject, @ mailbody = @ strMessage, @ attachment = @ AttachDir
End
This stored procedure only supports sending attachments to SQL query results. If you want to send standard attachments, use the sp_jmail_send stored procedure directly or use its own extended functions.