Use the JMail component to send an Email instead of SQL Mail

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.