asked on

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

SOLUTION

Duane Lawrence

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ABaruh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Duane Lawrence

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

MartinCMS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

banachek

ASKER

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

banachek

ASKER

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

SOLUTION

Duane Lawrence

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

MartinCMS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MartinCMS

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

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

banachek

ASKER

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

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

banachek

ASKER

arbert: yes Exchange 2000 with Outlook 2000

banachek

ASKER

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

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

banachek

ASKER

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

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

banachek

ASKER

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