dbmail, smtp relay

Question

I am working on a transition from SQL 2000 to SQL 2008. One of the challenges that I am facing is the use of the dbmail. I configured it with no problem. I am able to send messages within my domain. For instance my sql email account "sql@domain.com" is able to send to "myself@domain.com". The problem is when I am trying to send an email out of the domain such "myself@gmail.com". The dbmail configuration it is based on smtp. SMTP relay on most installations is BLOCKED to avoid SPAM mails. My SQL account needs to send emails to email accounts out of my domain. In SQL 2000 my SQL account has a profile on Outlook 2003 and, configured with SQL 2000 mail and this way the email are sent with no problem. So the question is: Do you have a workaround solution?

Environment SQL 2000, SQL 2008 Exchange 2010, Office 2003, Office 2007.

Answer 1

I use CDOSYS for our in-house email. It is also based on SMTP, so it might have the same problem in your installation, but here goes.

CREATE      PROCEDURE [dbo].[usp_send_cdosysmail]
    @From_Addr  VARCHAR(500) ,
    @To_Addr    VARCHAR(500) ,
    @Subject    VARCHAR(500),
    @Body       VARCHAR(4000) ,
    @SMTPserver VARCHAR(25) = 'localhost',
    @BodyType   VARCHAR(10) = 'textbody',
    @Attachment NVARCHAR(200) = NULL
AS

DECLARE @imsg INT
DECLARE @hr INT
DECLARE @iRtn INT
DECLARE @source VARCHAR(255)
DECLARE @description VARCHAR(500)
DECLARE @output VARCHAR(1000)
DECLARE @errmsg nvarchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @SMTPserver
EXEC @hr = sp_oamethod @imsg, 'configuration.fields.UPDATE', NULL

EXEC @hr = sp_oasetproperty @imsg, 'to', @To_Addr
EXEC @hr = sp_oasetproperty @imsg, 'from', @From_Addr
EXEC @hr = sp_oasetproperty @imsg, 'subject', @Subject

IF @BodyType <> 'URL'
BEGIN
    -- If you are using html e-mail, use 'htmlbody' instead of 'textbody'.
    EXEC @hr = sp_oasetproperty @imsg, @BodyType, @Body
END
ELSE
BEGIN
    -- Generate The Body From The Input URL
    EXEC @hr = sp_OAMethod @imsg, 'CreateMHTMLBody', NULL, @body, 0, '',''
END
-- Add An Attachment?
IF @attachment IS NOT NULL
BEGIN
--     EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', @iRtn Out, @attachment
    EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, @attachment
--     EXEC sp_OAMethod @imsg, 'AddAttachment', NULL, @attachment
IF @hr <> 0
    PRINT CONVERT(VARCHAR, @hr) -- + ' ' + CONVERT(VARCHAR, ISNULL(@iRtn, 0))
    EXEC sp_OAGetErrorInfo @imsg
END

EXEC @hr = sp_oamethod @imsg, 'send', NULL

-- sample error handling.
IF @hr <> 0
SELECT @hr
BEGIN
    EXEC @hr = sp_oageterrorinfo NULL, @source out, @description out
    IF @hr = 0
    BEGIN
        SELECT @output = ' source: ' + ISNULL(@source, 'NULL Source')
        PRINT @output
        SELECT @output = ' description: ' + ISNULL(@description, 'NULL descr')
        PRINT @output
    END
    ELSE
    BEGIN
        PRINT ' sp_oageterrorinfo failed.'
        RETURN
    END
END
EXEC @hr = sp_oadestroy @imsg

Answer 2

0

Thanks for reading my post. Finally I found the solution. DBmail in SQL 2005/2008 is based on SMTP. In my case we have an Exchange account. The issued is solved by allowing the SQL Server 2008 (by IP in Exchange) in this case to be able to sent messages out of the domain. So this was not an SQL related problem but exchange. But it is good to know that we have a solution.

Thanks again

Vinny

answered May 05 '10 at 02:49 PM

Vinicio Aizpurua
2 ● 1 ● 1 ● 1

(comments are locked)

dbmail, smtp relay

Follow this question

Related Questions