XP_sendemail problem

I am trying to use xp_sendmail to send query results (Outlook 2000 and SQL 2000).  Executing the sp takes a long time - about 3 minutes before I finally receive the Sent Mail message but the recipient never receives the message.

Checking the Outllok client, I find the email in the INBOX. Opening the email has the Send Button available. Clicking on the Send button will go ahead and send the email.

I have tested the following:

•      I can send email to and from the mailbox I am using (using the client)
•      I have tested sending email from SQL mail – other than the delay it works
•      SQLServices /SQL Agent services are using the domain account – same as the Outlook client. All 3 are using the same account.

Thanks
banachekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:
Please post the test code that actually sends the email successfully

Please post the stored proc that does not send the email.

I bet there is a difference on the line between the 2.
0
ABaruhCommented:
What version of Outlook?
0
Duane LawrenceCommented:
I bet there is a difference on the line between the 2.

It should have read:
I bet there is a difference on the line that actually sends the email between the 2 stored procs.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MartinCMSCommented:
per half you might want to consider using XPSMTP.dll - SQL Server SMTP Mail XP.
It much more reliable then using xp_sendmail.  Here is the following link for info on XPSMTP

check out the Parameters in http://sqldev.net/xp/xpsmtp.htm


i.e.

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
    @FROM       = N'MyEmail@MyDomain.com',
    @FROM_NAME  = N'Joe Mailman',
    @TO         = N'MyFriend@HisDomain.com',
    @CC         = N'MyOtherFriend@HisDomain.com',
    @BCC        = N'MyEmail@MyDomain.com',
    @priority   = N'HIGH',
    @subject    = N'Hello SQL Server SMTP Mail',
    @message    = N'Goodbye MAPI, goodbye Outlook',
    @type       = N'text/plain',
    @attachments= N'c:\attachment1.txt;c:\attachment2.txt',
    @server     = N'mail.mydomain.com'
select RC = @rc

go
0
arbertCommented:
Did you select the Outlook profile you create to be the default profiles on the SQL Box?  If not, it won't be started when the SQLAgent logson and your mail will just sit there waiting to be sent.

"Checking the Outllok client, I find the email in the INBOX."  I take it you meant OUTBOX and not INBOX?
0
banachekAuthor Commented:
The version of Outlook is 2000.  

Here is the test code. I have backed everything off to be just a simple test.

EXEC xp_sendmail
      @recipients = 'dfulton@mydomain.com',
      @subject = 'Test Email'

If I understand the other post correctly, there are not two sps. The other test is simply using the test button on the SQL mail.   EM/Support Services/SQL Mail/Properties & Test
0
banachekAuthor Commented:
To arbert, yes, I have selected the default Profile and pushed the test buttons to make sure all is well
To arbert, no I meant Inbox - not Outbox. It goes into the Inbox for some reason and sits.

To  MartinCMS, would love to use the DLL - worked with it for a few hours but no @query option whicj is very important to me
0
Duane LawrenceCommented:
Ok, create a test stored proc that gets one row and sends the email using the same

EXEC xp_sendmail
     @recipients = 'dfulton@mydomain.com',
     @subject = 'Test Email'

See if it actually sends.  This will identify if it is a problem sending or if it is the volume of data from the other stored proc.
0
MartinCMSCommented:
You can still doing your @query using DLL, just in a different way.  If you can get xp_sendmail to work and want to look into the DLL option a little more, let me know and I will help you going with the @query.

Martin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MartinCMSCommented:
exec master.dbo.xp_sendmail @email_To,
@Query = @Query_Statement,
@Subject = @Subject_Line,
@Message = @Body,
@blind_copy_recipients = @bc,
@Attach_Results = 'TRUE',
@Width = 500   --- don't forget to add this line for the attachment to work accordingly
0
arbertCommented:
Um, your mail shouldn't  show up in the INBOX. Unless you sent it to the same profile you're sending from--is that the case?
0
banachekAuthor Commented:
arbert: not sure
My understaning is that sp_sendMail uses the SQLMail profile to send the mail. I have doublechecked to ensure that this is the profile used on SQLMail. So, if I understand all correctly, the Mapi client (user) will be my From. The recipient on the email itself is someone completely different. So I am not sending to the same person as I am sending it from if I understood your question properly.

Thanks
0
arbertCommented:
Ya, the mail should definately not show up in the inbox....Are you using an exchange server?
0
banachekAuthor Commented:
arbert:  yes Exchange 2000 with Outlook 2000
0
banachekAuthor Commented:
Martin:

I am persuing both paths at the moment as I need a solution.  The DLL approach is attractive since there is no need for the Mapi client, the configuration effort, and any ensuing challenges.  It may be the DLL has some issues as well but so far works extremely well - except for the @query limitation.

I have put some effort into including my query results in the messagefile which is easy enough. Don't like this approach as I have to have DTS write the file and because my results are several rows of "links",  I have not figured out how to maintain the links yet. For example, my query is setup to return the rows i need as links to an XML "view" for each row of data.

@query ='select ''http://www.mydomain.com/vieworder.aspx?orderid='' + CAST(orderno as varchar(12))
       from WHOrderHistory (NOLOCK)
       where auditnum = 32168
       order by OrderNo'

I am interested in any assistance in having the @query option added.

Thanks
0
arbertCommented:
You definately need to go with a "third party" approach.  XP_Sendmail won't format/return HTML/XML in your email bodies....
0
banachekAuthor Commented:
arbet: I did all of the needing formatting in the query. In my query, once I actually was able to force the email to be delivered, the links were there and worked well. If I could just get the sp_sendmail to send the mail, I would be done.

Thanks
0
arbertCommented:
Ya, that is weird, because xp_sendmail definately doesn't support HTML/XML--you should just end up with plain text in your emails...
0
banachekAuthor Commented:
Thanks all. I ended up resolving the problem using a combination of the xp_smtp_sendmail DLL, BCP, and xp_cmdshell. I would not defend its elegance but it works well. The end result is an email that has links embedded back to my XML server for a view of formatted data for each row of data.

It would be extremely beneficial if the xp_smtp_sendmail DLL can be expanded to include the @query option.

Thanks
David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.