Setting up SQL Mail??

  • 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

  • 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)

  • 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

  • I have a resource page about SQL mail at:

    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

  • 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)


    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)


    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


    SELECT @hexvalue = @charvalue


    CREATE PROCEDURE sp_displayoaerrorinfo (@object int,

    @hresult int)


    DECLARE @output varchar(255)

    DECLARE @hrhex char(10)

    DECLARE @hr int

    DECLARE @source varchar(255)

    DECLARE @description varchar(255)


    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


    SELECT @output = ' Source: ' + @source

    PRINT @output

    SELECT @output = ' Description: ' + @description

    PRINT @output




    PRINT ' sp_OAGetErrorInfo failed.'




    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 '', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',








    DECLARE @object int,

    @hr int,

    @StrEnd int,

    @Attachment varchar(255),

    @return int,

    @Msg varchar(255)

    SELECT @From = isnull(@From, @@SERVERNAME) + ''--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


    EXEC @hr = sp_OASetProperty @object, 'Body', @message

    IF @hr <> 0 GOTO ObjectError


    IF @copy_recipients IS NOT NULL


    EXEC @hr = sp_OASetProperty @object, 'Cc', @copy_recipients

    IF @hr <> 0 GOTO ObjectError


    IF @blind_copy_recipients IS NOT NULL


    EXEC @hr = sp_OASetProperty @object, 'Bcc', @blind_copy_recipients

    IF @hr <> 0 GOTO ObjectError


    IF @HTMLFormat <> 0


    EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0

    IF @hr <> 0 GOTO ObjectError


    -- Loop through the ; delimited files to attach

    CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

    WHILE isnull(len(@Attachments),0) > 0


    SELECT @StrEnd = CASE charindex(';', @Attachments)

    WHEN 0 THEN len(@Attachments)

    ELSE charindex(';', @Attachments) - 1


    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)


    RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)

    RETURN 1


    EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment

    IF @hr <> 0 GOTO ObjectError

    SELECT @Msg = 'File ' + @Attachment + ' attached.'

    PRINT @Msg


    -- 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



    EXEC sp_displayoaerrorinfo @object, @hr

    RETURN 1



Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply