[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1090
  • Last Modified:

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
0
banachek
Asked:
banachek
  • 7
  • 5
  • 3
  • +2
11 Solutions
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now