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

avatar image

Vinicio Aizpurua
2 2 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

avatar image

dvroman
1.1k 1 4 4

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

avatar image

Vinicio Aizpurua
2 2 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.

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:

x501
x34
x4

asked: May 03, 2010 at 12:56 PM

Seen: 3631 times

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

Copyright 2016 Redgate Software. Privacy Policy