Introduction

Versions

Installation (32 bit)

Please note that the case of the extended stored procedure name xp_SMTPSendMail80 must be input exactly as shown.

Installation (64 bit)

Please note that the case of the extended stored procedure name xp_SMTPSendMail80 must be input exactly as shown.

Recipient

Name of the recipient in the format 'anyone@anywhere.com'. Separate multiple addresses with commas or semi colons for instance 'anyone@anywhere.com,someone@somewhere.com'. Separate multiple addresses with commas or semi colons for instance 'anyone@anywhere.com,someone@somewhere.com'. To use aliasing format the email address with <>. e.g. 'Someone<someone@somewhere.com>,Anyone<anyone@anywhere.com'.

From

Name of the Sender in the format 'anyone@anywhere.com'. If a null value is passed then a default is used (sqlserver@hostname.com).

Address

Either IP address or name of the SMTP Server.

Subject (Optional/Null)

The subject line.

CC (Optional/Null)

Name of any recipients to receive a copy  in the format 'anyone@anywhere.com'. Separate multiple addresses with commas or semi colons for instance 'anyone@anywhere.com;someone@somewhere.com'.

BCC (Optional/Null)

Name of any recipients to receive a blind copy  in the format 'anyone@anywhere.com'. Separate multiple addresses with commas or semi colons for instance 'anyone@anywhere.com;someone@somewhere.com'.

Body (Optional/Null)

 The body of the message. If "<HTML" and "</HTML" are included then the content type of the message is set to HTML.

Filename (Optional/Null)

The location of the files to send. Standard NT wildcards, such as * and ?, can be used. xp_SMTPSendMail will automatically detect if each file is either text or binary and will send it in the correct MIME format (quoted printable or base64). If null then no file is sent.

Timeout (Seconds) (Optional/Null)

Used to terminate the call to xp_SMTPSendMail. After the extended stored procedure has been running for this length of time the call will terminate. Used to ensure that the call will terminate no matter what has happened.

SMTP Port (Optional/Null)

By default Port 25 is used. This option is for non-standard SMTP servers where the SMTP Port has been changed

Examples of calls are given below:

 exec master..xp_SMTPSendMail80 'admin@domain.com','sql@sqlserver.com', '123.23.45.34','Overnight error', NULL, NULL, 'Error on load','c:\temp\main*.log', 30, 1567
or
 exec master..xp_SMTPSendMail80 'admin@domain.com', 'sql@sqlserver.com', '123.23.45.34'

or

 exec master..xp_SMTPSendMail80 'admin@domain.com','sql@sqlserver.com', '123.23.45.34','Overnight error', NULL, NULL, 'Error on load','c:\temp\main*.log', 30, 1567
 

Please note that the extended stored procedure name is case sensitive. If the procedure is run with no parameters then it will give a list of the required parameters.

xp_SMTPSendMail80 @stringoption='optionvalue',@integeroption=1

@recipient

Name of the recipient in the format 'anyone@anywhere.com'. Separate multiple addresses with commas or semi colons for instance 'anyone@anywhere.com,someone@somewhere.com'

@from

address of the sender e.g. sql.server@bloggs.com

@address

SMTP gateway either DNS name or IP address

@subject

Subject line for email

@copy_recipients

Name of any recipients to receive a copy  in the format 'anyone@anywhere.com'. Separate multiple addresses with commas or semi colons for instance 'anyone@anywhere.com;someone@somewhere.com'.

@blind_copy_recipients

Name of any recipients to receive a blind copy  in the format 'anyone@anywhere.com'. Separate multiple addresses with commas or semi colons for instance 'anyone@anywhere.com;someone@somewhere.com'.

@body

The body of the message. If "<HTML" and "</HTML" are included then the content type of the message is set to HTML.

@attachments

The location of the files to send. Standard NT wildcards, such as * and ?, can be used. xp_SMTPSendMail will automatically detect if each file is either text or binary and will send it in the correct MIME format (quoted printable or base64). If null then no file is sent. Multiple filenames can be separated by commas. For example 'c:\temp\*.log,c:\temp\*.txt'

@timeout

Used to terminate the call to xp_SMTPSendMail. After the extended stored procedure has been running for this length of time the call will terminate. Used to ensure that the call will terminate no matter what has happened.

@html

Sets the content type of the mail to be HTML 0 (false)  ASCII 1 (true). Defaults to ASCII (0). If this set to true then any query results sent in the body of the message will be formatted as HTML tables.

@importance

Sets the email importance 1 High 2 Medium 3 Low (Defaults to Medium)

@priority

Sets the email priority 1 High 2 Medium 3 Low (Defaults to Medium)

@smtpport

By default Port 25 is used. This option is for non-standard SMTP servers where the SMTP Port has been changed

@query

Query text - multiple resultsets supported e.g. select * from sysusers exec sp_who

@qserver

Server name for query(defaults to current server).

@qdatabase

Database name for query(defaults to master).

@quser

Username for query(defaults to current user).

@qpassword

User password for query(defaults to current password).

@qrowsep

Row separator for query defaults to carriage return/linefeed.

@qfieldsep

Field separator for query (defaults to comma).

@qtableattribute

HTML Table attribute(defaults to nothing) e.g BORDER=1 BGCOLOR="#CCCCCC"

@qtablecaption

HTML Table Caption. (defaults to blank)

@qasattachments

Send query results as attachments (defaults to 0 - send in body of message)

@qbind

Bind to existing connection 1 (true) or use a new connection 0 (false) . Defaults to 1 (true). Care must be taken if set to 0 (false) as it is possible for the new connection to be blocked by the calling connection. This is a deadlock that SQL Server cannot detect.

@qfilename

Sets the name of the attachment. If omitted the attachment defaults to the query text.

@no_output

Is an optional parameter that sends the mail but does not return any output to the client session that sent the mail. The default is 0, which means that the client session of SQL Server receives output.

@qcaptionattribute

Attributes put into the <CAPTION> tag.

@qheaderattribute

Attributes put into the <TH> tag.

@qtrattribute

Attributes put into the <TR> tag.

@qtdattribute

Attributes put into the <TD> tag.

@stylesheet

Allows a header-defined style sheet to be created which applies to all the elements in the mail. For instance

@stylesheet =  'P { font-size: 16pt; 
 font-face: Verdana, sans-serif;
}

table {border-style: ridge;
border-width: thick;
background-color: blue} 

th { font-size: 12pt;
color: yellow;
}

td { font-size: 8pt;
font-face: Verdana, sans-serif;
color: red;
}' 

This formats the table header to be 12 pt and yellow and formats the other elements. This gives a great deal of control over the look of the email.

@codepage

Overrides the codepage that is automatically set. By default will take the codepage from SQL Server. Input as a number and sent as the ISO Code (iso-8859-X).

@debug

Write log to file defaults to c:\temp\sqlsmtpinterface.log

@debugfilename

Log Filename overrides default c:\temp\sqlsmtpinterface.log

@termination

Line Terminator.Some SMTP Servers require specific line terminators. The default works for the majority. 0 Carriage Return/Line Feed (default) 1 Carriage Return Only 2 Line Feed Only

@SMTPUsername

SMTP UserName

@SMTPPassword

SMTP Password

Result

If successful this field contains 0 and all other fields contain null. A non zero value indicates an error.

ErrorMessage

An indication of the error. Indicates the problem occurred at the TCP/IP level, such as server not found.

SMTPErrorNumber

Error number returned from the SMTP Server during a conversation. This is the errornumber returned from the SMTP Server.

SMTPErrorText

The text associated with that error number. This is the errortext returned from the SMTP Server.

QueryErrorMessage

Error returned running the query.

Then delete the dll smtpsendmail80.dll.

Then delete the dll smtpsendmail80_64.dll.