xp_sendmail: Could not resolve recipient

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xp_sendmail: Could not resolve recipient

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2006-03-14 : 17:07:24

I used this example/syntax from an article here. However, I am not having much success. The mail server is Outlook Exchange.

xp_sendmail can be used to send simple notification messages, such as:
xp_sendmail
@recipients='graz@sqlteam.com',
@subject='The Yaks are loose again',
@Message='Summon the Yak Herders!'

Here is what I tried first:
xp_sendmail
@recipients='firstname.lastname@company.com',
@subject='Did you get that thing I sent you?',
@Message='Just testing out xp_sendmail. Howd I do?'
My results:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80070005

Then I tried using my operator name:
xp_sendmail
@recipients='pledell',
@subject='Did you get that thing I sent you?',
@Message='Just testing out xp_sendmail. Howd I do?'
My results:
Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient

Any assistance you can provide would be appreciated.

*************************
Got some code from Rob. Can anyone help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-14 : 18:42:34

Did you setup SQL Mail?

http://support.microsoft.com/default.aspx?scid=kb;en-us;263556

Also check this out:
http://support.microsoft.com/default.aspx/kb/820220

Tara Kizer
aka tduggan

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2006-03-15 : 12:32:55

Yes, I've set up SQL Mail. I can successfuly send test msgs and alerts from jobs in Enterprise Manager.

I don't have any special characters in my alias name on the exchange server. I have tried using the SMTP email address in the query.

MSSQLSERVER and SQLSERVERAGENT are using the same account. Which is my windows log in account and the one associated with my work email.

I have tried ...
declare @notify_email_operator_name nvarchar(500)
set @notify_email_operator_name = N'pledell'
EXEC xp_sendmail
@recipients=@notify_email_operator_name,
@subject='Did you get that thing I sent you?',
@Message='Just testing out xp_sendmail. Howd I do?'

and

EXEC master..xp_sendmail 'pledell', 'this is a test message from SQLServer'

Both return ...
Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient

Do I need a particular "type" of exchange email?

Any other ideas?

*************************
Got some code from Rob. Can anyone help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-15 : 14:16:33

@recipients is an e-mail addy and not an operator.

Here's what I do when I have SQL Mail issues...log into the database server using the MSSQLSERVER service account. Open up Outlook. Send an e-mail to someone. Does it work?

Tara Kizer
aka tduggan

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2006-03-17 : 10:03:42

Yes. I can email people from Enterprise Manager, but not from query analyzer.

*************************
Got some code from Rob. Can anyone help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-17 : 11:20:49

Okay, but did you try what I asked in my last post?

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 12:57:10

Ledell, This may not be the answer to whatever u faced with, but u can send email as follows :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=7824
http://www.sqlteam.com/item.asp?ItemID=5003

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2006-03-17 : 14:57:28

quote:
Originally posted by tkizer

Okay, but did you try what I asked in my last post?

Tara Kizer
aka tduggan

Yes ... I had mentioned that in a previous posting. If you mean something different, I have no idea what it is and would love further guidance.

quote:
Originally posted by Ledell

MSSQLSERVER and SQLSERVERAGENT are using the same account. Which is my windows log in account and the one associated with my work email.

*************************
Got some code from Rob. Can anyone help?

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2006-03-17 : 15:29:05

Srinika -

Thank you for the suggestion. I don't know if it will work either, but I am reading thru the article and will see what I can do. I keep thinking that the problem is a simple one that is right in front of me, that I just don't see. Esp since I've had a few people say, "you shouldn't be having any troubles with that".

*************************
Got some code from Rob. Can anyone help?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 16:39:09

Ledell, its very easy way
only thing is that u should have CDONTS in ur SQL Server machine

Worth giving a try.
U don't need to have any other (as Outlook) in the Server

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-17 : 17:13:37

I was referring to this post:

quote:

Here's what I do when I have SQL Mail issues...log into the database server using the MSSQLSERVER service account. Open up Outlook. Send an e-mail to someone. Does it work?

E-mail must work from Outlook on the database server using the MSSQLSERVER service account before it can ever work inside SQL Mail.

Tara Kizer
aka tduggan

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2006-03-17 : 20:22:26

Tara,

I have SQL Server installed on my PC. I log into my PC with a windows login/password. The MSSQLSERVER / SQLSERVERAGENT account uses my windows login. When I log into my PC, MSQLSERVER / SQLSERVERAGENT are started automatically with my window's log in account. Outlook works fine. I am able to send / receive emails from it fine. I am able to send emails using Enterprise Manager.

However, I am not able to send an email using a SQL Statement in Query Analyzer.

Is there anything in the code I have posted above that doesn't look right? Is there a special Outlook account I need for this to work? What am I missing?