Send an email using the JMail component instead of SQL Mail

Jmail|jmail components

Say two words: In one's most difficult time, write down this article, send to So-and-so to cheat point fee, the result is ignored, a few years later, churning hard drive to turn out. Oh, may be helpful to users who want to send mail in SQL Server.

54powerman ^_^

SQL Mail Technology is a great convenience for every database developer and DBA (database administrator), which enables SQL Server database agents to automatically send an email notification to administrators when the system is abnormal, Developers can use it to make the database automatically modify the user's password periodically, and then send email to notify the user ... These applications, in varying degrees, liberate us from the intricacies of our work. However, SQL Mail configuration is more complex, I believe that more than 90% people in the configuration of SQL Mail have encountered a variety of problems, at least 70% of people have given up SQL Mail and choose Other solutions to solve this problem. The author is a web developer who has experienced all of this and found a better alternative. Dare not to enjoy, write to readers.
There are several ways in which SQL mail configuration can be divided into configurations based on Exchange, Outlook2000 (above) and Third-party software, which are available in three different ways, mainly in the following ways:

Implementing SQL Mail with the Outlook client with SQL Server
The software requirements for this scenario are low and you need to install Outlook2000 above client on the server where SQL Server resides. It requires that the Outlook client must be turned on during SQL Mail use, otherwise the message can be sent only the next time it is opened. In addition, if the server is a remote server, with Microsoft's official Remote Desktop can not complete the configuration, the alternative is the DBA to go to the computer room directly, or install PCANYWHERE10 to replace the remote Desktop to operate.

Using Exchange requires a higher
Microsoft recommends using Exchange as the best partner for SQL Mail, and the MSDN information suggests that you use Exchange Server for reliability because of the limitations of the POP3/SMTP protocol and the logon issue. But exchange is not specifically doing this to use, it is wasted, and exchange requires the server to configure the domain manager, I believe this dongdong for most of the database server is not very useful, but only a waste of resources. If we were to configure SQL Mail on multiple servers then we would need to configure the domain manager on each server, or all the servers would be configured in one domain, but it would not be realistic for servers with a more decentralized system.

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 service used by these mail servers may have been installed by a Third-party mail client, Microsoft will not support connecting to these servers. This means that you will also use the Windows Platform Mail services, the use of ASP to write a website friends must know that the Cdonts.dll component is really ...
In the face of these problems, the author has become I just said that 70%, although crawling countless, read the text tens of thousands of SQL Mail configuration well, but I still absolutely give up, because I do not want to do 2nd, 3rd Taiwan ... Time to repeat the same. The alternative is the JMail component +ole Automation object, the above problem is solved.

Preliminary knowledge
1. OLE Automation functions
OLE Automation enables an application to manipulate objects implemented in another application, or to expose objects so that they can be manipulated. An Automation client is an application that can operate on a exposed object that belongs to another application, and this article is worth SQL Server. An application that exposes objects is called an Automation server and an Automation component, as in this article, the JMail component. The client operates on these objects by accessing the properties and functions of the Application object.
The way to use OLE components in SQL Server is to have several system extended stored procedures sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, sp_OAMethod, sp_OASetProperty, and sp_ Oagetproperty, a brief introduction to the use of the method, detailed reference to SQL Server Books Online.
How OLE Automation objects are used:
(1) Invoke sp_OACreate to create the object.
Format: sp_OACreate clsid,objecttoken OUTPUT [, Context]
Parameter: clsid--is the program identifier (ProgID) of the OLE object to be created. This string describes the class of the OLE object, in the form of ' OLEComponent.Object ', OLEComponent is the component name of the OLE Automation server, object is the OLE object name, and the "jmail.message" used in this article;
The objecttoken--is the returned object flag and must be a local variable with a data type of int. Used to identify the created OLE object and will be used when other OLE Automation stored procedures are invoked. In this article, it is used to invoke the properties and methods of the Jmail.message component.
context--specifies the execution context in which the newly created OLE object will run. This article does not use this parameter, so do not repeat. In line with this, see the SQL Server online documentation for other uses of all method properties.
(2) Use the object.
(a) Call sp_OAGetProperty to get the property value.
Format: _oagetproperty objecttoken,propertyname [, PropertyValue OUTPUT]
Parameters: (Parameters that have been previously seen, omitted below.) ）
The property name of the propertyname--object;
propertyvalue--the property value of the object returned, with the output attribute, you can get the value of the property from the PropertyValue when you perform the operation.
(b) Call sp_OASetProperty to set the property to a new value.
Format: sp_OASetProperty objecttoken, PropertyName, PropertyValue
(c) Call sp_OAMethod to invoke a method.
Format: sp_OAMethod objecttoken, MethodName [, ReturnValue OUTPUT] [, [parametername =] ParameterValue [... n]]
Parameter: returnvalue--calls the return value of the method, or null if there is no return value;
The name of the parameter in the parametername--method definition, which is the formal parameter;
parametervalue--parameter value;
... n--said, can take a lot of parameters, the number is defined by the method constraints;
(d) Call sp_OAGetErrorInfo to get the most recent error message.
Format: sp_OAGetErrorInfo [Objecttoken] [, source output] [, description output]
Parameter: source--error source;
description--error description;
(3) Invoke sp_OADestroy to release the object.
Format: sp_OADestroy objecttoken

    2. xp_cmdshell extended Stored Procedure
    The extended stored procedure in the master database, its full path is master. xp_cmdshell (note that the middle is 2 dots), its function is to execute the given command string as the operating system command-line interpreter and return any output as a text line.
    Format: xp_cmdshell {' command_string '} [, No_output]
    parameters: ' command_string '-- is a command string that executes on the operating system command line interpreter. The
    no_output--is an optional parameter that executes the given command_string, but does not return any output to the client. This parameter is not used in this application.

    operation Methods
    (1) Software preparation
    please go to http:/ /www.dimac.net/ or a Web site that downloads the latest version of the JMail component in the country, if you get an installation version, perform WeJMailx.exe, and the system's configuration installer will automatically complete. If you have only one JMail.dll file, follow these steps to install:
    (a) Create a new text file and enter the following command:
    regsvr32 JMail.dll
    net start w3svc
    Save As Install.bat (note, don't save it as Install.Bat.Txt)
     (b) This file, together with Jmail.dll, is copied to the System32 directory of the SQL Server database server and performs a double click Install.bat.
    (2) Ready? Come with me.
    (a) Run SQL Server Query Analyzer and log on to the SQL Server database as an SA;
    (b) If your stored procedure is to be added to the Yourdefaultcatalog database, enter the following instructions in the Blank SQL window, otherwise modify the database name accordingly.
    Use Yourdefaultcatalog
    Press F5 or run the button to run the directive;
    (c) Create a basic send stored procedure
    Copy the following code into the SQL Server Command window and run it. The following code has a corresponding comment, the article does not explain more, if you have any questions, please check out the previous "preliminary knowledge" or query the SQL Server Help file, of course, 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 (=null),
@recipientBCCName varchar (=null),
@recipientCC varchar (=null),
@recipientCCName varchar (=null),
@attachment varchar (=null),
@subject varchar (255),
@mailbody text
As
/*
This stored procedure uses office automation scripts to invoke the Dimac W3 jmail Axtivex component to send mail instead of SQL Mail
This method supports server-side authentication
*/
--Declaring common variables and error information variables used by W3 JMail
Declare @object int, @hr int, @rc int, @output varchar (), @description varchar (), @source varchar (400)

--Create a Jmail.message object

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

--Set Message 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 the message basic parameters
Exec @hr = sp_OASetProperty @object, ' from ', @sender
Exec @hr = sp_OAMethod @object, ' addrecipient ', NULL, @recipient
Exec @hr = sp_OASetProperty @object, ' Subject ', @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 are null) and (not @recipientBCCName is null)
Exec @hr = sp_OAMethod @object, ' addrecipientbcc ', NULL, @recipientBCC, @recipientBCCName
Else If not @recipientBCC is null
Exec @hr = sp_OAMethod @object, ' addrecipientbcc ', NULL, @recipientBCC

If (not @recipientCC are null) and (not @recipientCCName is null)
Exec @hr = sp_OAMethod @object, ' ADDRECIPIENTCC ', NULL, @recipientCC, @recipientCCName
Else If not @recipientCC is null
Exec @hr = sp_OAMethod @object, ' ADDRECIPIENTCC ', NULL, @recipientCC

If not @sendername is null
Exec @hr = sp_OASetProperty @object, ' FromName ', @sendername

--Call the Send method to send a message
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 = ' Source of error: ' + @source
Print @output
Select @output = ' ERROR description: ' + @description
Print @output
End
Else
Begin
Print ' failed to get error message! '
Return
End

--Releasing the Jmail.message object
Exec @hr = sp_OADestroy @object

(d) Simplify the operation of stored procedures to suit our usual habits of use
The above stored procedure can basically complete the mail to send the operation, but is very verbose, and does not conform to our custom, for instance it does not support many sends to the receiver, does not support sends the SQL instruction the result to Send as the attachment form (this is SQL Mail the function, we can also do), So we're going to write a stored procedure to invoke it to simplify the operation and extend the functionality.

Create Procedure SendMail
@Sender VarChar (m) =null,
@strRecipients VarChar (200),
@strSubject VarChar (200),
@strMessage VarChar (2000),
@sql VarChar (m) =null)
As

Declare @SplitStr varchar (1)--Define message address separator variable
Declare @strTemp varchar (200)--Define multiple recipient string temporary variables
Declare @email varchar (50)--single recipient string variable separated by a delimiter

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

Declare @DefaultSender 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 @DefaultSender = ' Default Send Address '
Set @MailServer = ' mail server address '
Set @User = ' SMTP server authenticate user address '
Set @Pass = ' SMTP Server Authentication address '
Set @SenderName = ' Default sender name '
Set @AttachDir = ' E:\LOG\WebData\Jmail\ ' +replace (replace (VarChar (), GetDate (), 120), '-', ', ', ', ', ' ), ': ', ', ' + ' txt '

--Unified Email address separator as a semicolon
Set @SplitStr = '; '
Set @strTemp = @strRecipients + @SplitStr + ' end '
Set @strTemp =replace (@strTemp, ', ', '; ')

--Determine if there are SQL statements
If (@Sql is Null) Or len (@Sql) =0)
Set @AttachDir =null
Else
Begin
Declare @CmdStr VarChar (200)
Set @CmdStr = ' bcp '+ @Sql + '"queryout ' + @AttachDir + ' -C '
EXEC Master.. xp_cmdshell @CmdStr
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 mail
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 extends only the SQL query results attachment sent, if you want to send the standard attachment, please use the sp_jmail_send stored procedure directly or expand the function.