exec xp_sendmail error on SQL Server 2000

I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded.
So I try to have it executed in a trigger but it failed.

Here is the trigger creation script and error message

use mlcb
go
if exists (select name
from sysobjects
where name = 'test' and
type = 'TR')
DROP TRIGGER TEST
GO

CREATE TRIGGER test on mlcb.dbo.trans_errlog
for insert
as
declare @.email_subject varchar(100),
@.email_content varchar(4000),
@.email_recipients varchar(50)

set @.email_subject='SQL Mail test mail'
set @.email_recipients='some@.world.com.tw'
set @.email_content='this is a test mail, don't reply this mail'

exec master.dbo.xp_sendmail @.recipients=@.email_recipients,@.subject=@.email_subj ect,@.message=@.email_content
GO

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Appreciate any prompt reply.

JDbefore we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.|||before we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.

Thanks very much for your reminders
I really didn't think about these.

Only one record is inserted at a time.
There is no much transaction, just something like error notification.

mmmmm, I didn't know unavailable exchange server will cause such issue.
So how can I avoid it?

How to fix the permission issue?

Thanks for your help!!

JD|||Let me echo the warning that was already posted. When you use SQL Mail in SQL 2000, you are opening a potential can of worms. Outlook is a single threaded application. If it hangs for any reason (say the Exchange server takes a vacation), you can end up with a heap of trouble. I tried running a subscription based service off of SQL 7.0/2000 back in '00/'01. We had to abandon that effort because SQL kept hanging whenever the mail server went off line (or network connectivity prevented a connection).

Go with something that is lightweight (ie, SMTP). Consider some other method for sending notifications; insert a record into a table, create an external app that runs on a schedule to watch that table, etc. Anything but this.

That said,

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Have you checked for the existence of this sp in master? the name looks wrong to me. It should by rights be 'master.dbo.xp_startmail'.

Have you configured a SQL Mail profile? Is the profile correct? Can you send mail outside of the trigger?

Regards,

hmscott|||hi hmscott,

Thanks for helping me away such dangerous condition.

It is really bad hear that. I thought I am almost there.
I will take your advice not using SQL Mail. There is no such warning heard before when searching around the web. Now I have to go from the beginning.

Can you provide any reference for SMTP usage?

But I am still want to know how to solve the issue I have right now.
I can run the script out of trigger.

I did exec master.dbo.xp_startmail.

master.xp_startmail was in the error message when firing the trigger.

Best Regards,
JD|||My first suggestion for looking into SMTP would be to investigate SQL 2005. SQL 2005 introduces Database Mail which is an SMTP based solution and works very well (you can even define multiple SMTP servers in case the primary is out to lunch somewhere). Besides, anything new you are designing now should be done in SQL 2005 since mainstream support for SQL 2000 won't be around too much longer...

As far as your error message...are you certain that you have configured SQL Mail correctly? Be sure you differentiate between SQL Mail and SQL Agent Mail. They work the same way (using Outlook and an Outlook Profile), but they must be configured separately.

Also, did you check for the existence of the master.dbo.sp_startmail proc?

Regards,

hmscott