Terrible problem with xp_sendmail PLEASE HELP!

[jfogel]

Greetings,

I'm having a big problem with xp_sendmail on a particular machine. When I run exec master.dbo.xp_sendmail 'JFogel@atsrmis.com','hello' I get Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient.

This works fine on 7.0. On this particular SQK 2K server I can user SQLAgent mail with no problems. I get all my emails after a job completes. I suspected that there may be a bug in 2k so I updated to SP3 but no luck.

To confirm this was not a flat out 2k only problem I configured another server to be the same. They are both windows 2k(sp3) with outlook 2k(sr1) and SQL 2k sp3. Email from these works fine. On the new server I can use xp_sendmail all day long with no problems.

I know there must be something different and I don't suspect it is SQL Server that is having the problem. It has to be some little sys configuration but I'm just stuck.

I really need this to work because a procedure I'm working on requires this functionality. SQLAgent mail isn't an option here.

If anyone can help me I would be very greatful.

[skhanal]

Are you able to send email from Outlook client in the server to this address?

[jfogel]

Yes, everything is fine but xp_sendmail on that machine.

[MAK]

Please look this KB

http://support.microsoft.com/default...b;en-us;315666

Use SMTPSEND.exe instead.

[jfogel]

I read this I liked one of the work arounds

"Itemize the individual recipients of the personal distribution list in the @recipients parameter of the xp_sendmail system extended stored procedure."

Whatever that means. What is this smtpsend.exe?

[MAK]

it is an executable supplied with windows SDK I believe.

if you like I can give a complete instructions on how to use smtpsend.exe
along with the table and procedure u can use.

[jfogel]

That would be nice. I hope it supports attachments and something like the @query that xp_sendmail does. I really need those two options for the poject I'm working on.

I have a procedure that exports data to a text file from a temp table. I then attempt to call xp_sendmail to attach that export file and include the results from a "select * from ##........

[jfogel]

Also, it would really be nice to know why this does not work. As I stated in my original post, it does work on other SQL Server 2k machines, just not this particular one.

[JBane]

I'd be curious to see smtpsend as well.


Jeff

[MAK]

As far as I know, MAPI SQL mail will not work in cluster server cas' its not cluster aware.

Here is how I use smtpsend for email

--Usage of this email
--Example
--exec master.dbo.USP_SENDMAIL @@servername,"mak_999@yahoo.com","Test","c:\mytext ,txt"
--exec master.dbo.USP_SENDMAIL "Backup job","DBA Group","JOb failed!"


--Create a folder e:\smtp and copy smtpsend.exe to that folder
--create table in master database

use master
go
create table EmailGroups (Groupname varchar(100),EmailID varchar(100))
insert into EmailGroups (Groupname ,EmailId) values ("DBA Group","mak_999@yahoo.com")
insert into EmailGroups (Groupname ,EmailId) values ("mak_999@yahoo.com","mak_999@yahoo.com")
insert into EmailGroups (Groupname ,EmailId) values ("DBA Group","you@yahoo.com")
GO

--replace "171.21.81.51" with your SMTP host address

use master
go
CREATE PROCEDURE USP_SENDMAIL @SENDER VARCHAR(100),@RECEIVER VARCHAR(100),@SUBJECT VARCHAR(300), @filename varchar(100) = ''
AS
--CREATED BY :MAK
--CREATED DATE :JUNE 25,2002
--OBJECTIVE :CLUSTER SERVER SQL MAIL SETUP ISSUE. ALTERNATIVE NOTIFICATION PROCEDURE
--NOTES :CREATED A FOLDER E:\SMTP AND COPIED SMTPSEND.EXE UNDER.

SET QUOTED_IDENTIFIER OFF
DECLARE @QUERY VARCHAR(1000)
DECLARE @EMAILID VARCHAR(100)
DECLARE EMAIL CURSOR FORWARD_ONLY FOR SELECT EMAILID FROM EMAILGROUPS WHERE replace(GROUPNAME," ","") = replace(@RECEIVER," ","")
OPEN EMAIL
--print @receiver
--SELECT EMAILID FROM EMAILGROUPS WHERE replace(GROUPNAME," ","") = replace(@RECEIVER," ","")
FETCH NEXT FROM EMAIL INTO @EMAILID
WHILE (@@FETCH_STATUS =0)
BEGIN
--print @emailid
if @filename = ''
begin
SET @QUERY = 'EXEC MASTER..XP_CMDSHELL "E:\SMTP\SMTPSEND.EXE -t'+LTRIM(RTRIM(@EMAILID))+' -f'+@sender+' -h171.21.81.51'+' -s'+@SUBJECT++' AT '+CONVERT(VARCHAR,GETDATE())+''+'"'
PRINT @QUERY
EXEC (@QUERY)
end
else
begin
SET @QUERY = 'EXEC MASTER..XP_CMDSHELL "E:\SMTP\SMTPSEND.EXE -t'+LTRIM(RTRIM(@EMAILID))+' -f'+@sender+' -a' + @filename + ' -h171.21.81.51'+' -s'+@SUBJECT++' AT '+CONVERT(VARCHAR,GETDATE())+''+'"'
PRINT @QUERY
EXEC (@QUERY)
end

FETCH NEXT FROM EMAIL INTO @EMAILID
END
CLOSE EMAIL
DEALLOCATE EMAIL

[MAK]

u can add a group and individual email to the table and use the group to send email to all the members of the group.

any questions? send me email.

[jfogel]

Can you email me the executable? If it is over 1 md send it to jfogel@atsrmis.com

Thanks

[MAK]

yep. I did.

[jfogel]

I think I figured out the original problem. My servers are not part of a cluster also.

I changed the proxy account for the sql agent to user the admin account just like the service. I also granted "log on as batch job" to the sql agent user, restarted the services and BANG! it works.

I had a similar problem with Oracle's enterprise manager a while back and that log on as batch job was the cause of the problem too. Just like their programs are buggy so too is the documentation!

Thanks to all that helped here.