Problem trying to setup database mail in sql 2005 - please help

Question

Post reply

Problem trying to setup database mail in sql 2005 - please help

Jammyb2011

Valued Member

Points: 50
More actions
May 20, 2011 at 3:41 am

#236584

Hi Guys, first post on here, wonder if someone can point me in the right direction to troubleshoot this problem,
I know a very small amount with regards to sql but Im learning commands and syntax as I go.
Im trying to configure database mail on one of our sql servers, got it working fine on another sql 2005 server
On this one I have setup everything right as far as enabling database mail, creating a profile and an account with valid SMTP settings.
But on trying to send a test email i receive the following error:
been searching google all morning for various parts of this message but cant find what the problem is, everything i check look exactly the same as sql server that is working fine with database mail.
Any help greatly appreciated 🙂

Viewing 8 posts - 1 through 8 (of 8 total)

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

free_mascot One Orange Chip Points: 27164 More actions · Answer 1

Pl. refer following link.

http://msdn.microsoft.com/en-us/library/ms187540(v=sql.90).aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

M&M SSC-Insane Points: 21700 More actions · Answer 2

My suggestion is to delete and recreate the database mail profile from scratch. Just let us know if this works.

M&M

Jammyb2011 Valued Member Points: 50 More actions · Answer 3

free_mascot (5/20/2011)
Pl. refer following link.
http://msdn.microsoft.com/en-us/library/ms187540(v=sql.90).aspx
HTH

Thanks for the link, I had found that this morn and gone it but everything looks to be ok

Jammyb2011 Valued Member Points: 50 More actions · Answer 4

mohammed moinudheen (5/20/2011)
My suggestion is to delete and recreate the database mail profile from scratch. Just let us know if this works.

I have done that, had errors try to delete via the gui, but found the syntax to delete them without using the @force statement and that worked.

So i have now recreated the profile and account the same as the other working server i have.

Unfortunately still getting the same error though 🙁

Any other ideas guys ?

M&M SSC-Insane Points: 21700 More actions · Answer 5

Is there a way to validate the SMTP settings?

M&M

Jammyb2011 Valued Member Points: 50 More actions · Answer 6

mohammed moinudheen (5/20/2011)
Is there a way to validate the SMTP settings?

Yes, I have used the same SMTP settings on another sql server and its works ok.

If I remote desktop to the SQL Server in question I can telnet to the smtp server without problems.

I have to smtp settings i have tried as well, we can use our exchange smtp or the smtp address of the relay on the firewall.

but seem to get this error no matter which i try.

Anyone have any further ideas or tips to try?

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 7

Try running the text of the proc separately (creating the input variable as a "inline" variable and doing a SELECT on @retValue before the RETURN clause).

Here's the code from sp_helptext:

-- sp_SendMailQueues : Writes a send mail request to the queue.

--

CREATE PROCEDURE sp_SendMailQueues

@message_data varchar(max) -- The request in XML

AS

BEGIN

SET NOCOUNT ON

DECLARE @contract_name nvarchar(128)

DECLARE @message_type nvarchar(128)

DECLARE @retValue int

SET @message_type = '{//www.microsoft.com/databasemail/messages}SendMail'

SET @contract_name = '//www.microsoft.com/databasemail/contracts/SendMail/v1.0'

--Writes the message to the queue

EXEC @retValue = sp_SendMailMessage @contract_name, @message_type, @message_data

RETURN @retValue

END

Sounds like either one of those last three values (contact_name, message_type, or message_data) is returning NULL.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.