April 2, 2002 at 6:59 am
Hello All!
I'm playing with the thought of using the xp_sendmail procedure for one of my T-SQL scripts.
First off my network consists of 13 or so servers, most of which are Windows 2000, along with 2 Lotus Notes Domino Servers and A Solaris Box. Most are dedicated servers for Terminal Services, SQL Server, ISA , etc...
I'm mostly running Notes for internal mail, then run POP3 through the local ISP using MS Outlook for external mail. I currently do not have Exchange installed on any of the servers.
I have SQLSERVER 2000 running on about 5 machines, 2 dedicated servers for in-house programs and intranet(web) data access. The other three are running on development desktops, like my own.
So here's my question... Since I have SQL server installed locally and can connect to all of the other SQL servers on my domain, can I just use MY personal POP3 account for SENDING email from my SQLSERVER? I guess what is confusing me is the difference between SQL MAIL and SQLAgentMail? According to what I read SQL Mail must use an MAPI connection, but you use it for processing mail (i.e. sending and recieving mail), but you can send by just using SQLAgentMail and it can use POP3??
I've been threatening to install EXCHANGE server on one of my boxes. Will this be the turning stone? I guess my main question is, is it possible to set up the ability to send mail from SQL SERVER 2000 with my current setup?.. if not through xp_sendmail..then what else?
Thanks for any replies
April 2, 2002 at 7:51 am
SQLMail does require a MAPI connection. However, I am playing with notes to see if I can build a stored procedure to do the same. We have you direct SMTP send thru code as well with CDONTs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 2, 2002 at 8:52 am
I'd be interested in what you find out with NOTES. It appears as if I have it working on my local installation of SQL SERVER.
I did have to create the sql service login as my domain account instead of the local login. Also the main problem was that I needed to install the latest Service Pack for SQL SERVER. (Thank god for Microsoft Tech Net subscription! I'd hate to have to download those files)
It appears as if I must have Outlook open in order for the sendmail to work. How do you get around not having an email client open on a server in order to send mail?
Also I have a quick T-SQL question. I'm going to run a scheduled task from my main SQL SERVER dedicated server, (which I don't have an email client installed) so I wanted to play around with connecting to my local server from the script running on my main server.
Does anyone know the T-sql syntax to connect to another SQL SERVER (so I can execute the sendmail) then disconnect.
CONNECT TO does not seem to be a T-SQL syntax, but an embedded sql for C
Thanks again
April 2, 2002 at 9:51 am
I have a resource page about SQL mail at:
http://www.sqlservercentral.com/columnists/sjones/sqlmailresources.asp
There is a procedure for Notes. I have not tested it, but you can email the author and see if he will help.
Steve Jones
April 9, 2002 at 3:36 pm
CDONTs is a bit more stable than xp_sendmail.
It is very easy to set up, however you will need to set up three stored procedures on each server (as opposed to the one for xp_sendmail).
Here are the three sp's that we have used, create them in the following order.
CREATE PROCEDURE sp_hexadecimal (@binvalue varbinary(255),
@hexvalue varchar(255) OUTPUT)
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789abcdef'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
CREATE PROCEDURE sp_displayoaerrorinfo (@object int,
@hresult int)
AS
DECLARE @output varchar(255)
DECLARE @hrhex char(10)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
SET CONCAT_NULL_YIELDS_NULL OFF
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @hresult, @hrhex OUT
SELECT @output = ' HRESULT: ' + @hrhex
PRINT @output
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
GO
CREATE PROCEDURE sp_sendSMTPmail (@recipient varchar(8000),
@Subject varchar(255),
@message text = null,
@From varchar(255) = null,
@copy_recipients varchar(8000) = null,
@blind_copy_recipients varchar(8000) = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0
)
/* Name: sp_sendSMTPmail
Purpose: Send an SMTP mail using CDONTS object.
Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.
Returns: 0 if successful, 1 if any errors
Sample Usage:
sp_sendSMTPmail 'your_email@here.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',
@copy_recipients='from_me@here.com',
@Importance=1,
@Attachments='c:\boot.ini;c:\autoexec.bat'
History:
*/
AS
SET NOCOUNT ON
DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)
SELECT @From = isnull(@From, @@SERVERNAME) + '@here.com'--change to your domain
-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified
IF @message IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @message
IF @hr <> 0 GOTO ObjectError
END
IF @copy_recipients IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @copy_recipients
IF @hr <> 0 GOTO ObjectError
END
IF @blind_copy_recipients IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @blind_copy_recipients
IF @hr <> 0 GOTO ObjectError
END
IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END
-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END
EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError
SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END
-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @recipient, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError
-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError
PRINT 'Message sent.'
RETURN 0
ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply