x

dbmail, smtp relay

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.

more ▼

asked May 03, 2010 at 12:56 PM in Default

Vinicio Aizpurua gravatar image

Vinicio Aizpurua
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

answered May 03, 2010 at 05:51 PM

dvroman gravatar image

dvroman
1.1k 2 2

dvroman

Thanks for you answer. I already tested this solution thru vbs script. It works within the domain perfectly. Problem is still there when you trying to send en email out of your domain.

Thanks Vinny
May 04, 2010 at 10:35 AM Vinicio Aizpurua
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered May 05, 2010 at 02:49 PM

Vinicio Aizpurua gravatar image

Vinicio Aizpurua
2 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x29
x4

asked: May 03, 2010 at 12:56 PM

Seen: 2799 times

Last Updated: May 03, 2010 at 03:59 PM