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

excuting xp_sendmail

When excuting the xp_sendmail in the Query analyzer, my mail gets send. When doing this in my code it doesn't work eventhough I explicitly use startmailn then xp_sendmail and then sp_stopmail? I think this is a problem of user rights? I gave my user as parameter but even then it didn't work. Any suggestions? I'm pretty sure, the used code is correct.

greetings,
Geoffhumm.

Make sure you configure both the server and the SQL Server Agent to send mail. You do this by specifying the mail profile and running a quick test.

Exchange server

a little problem
I dont know why, but it is impossible to send mail bigger than 4MB
outside my company. We are using MS Exchange 2000. I have set up the
limit for sending and receving for 20MB. It is strange because inside
the company it is OK, mails bigger then 5MB am able to send. But
outside'?Sure you are in the right group ?
But a limit can be declareated for in and outside the company, check the
default setitings of
your organsation settings.
HTH
"Jan West" <jan.west@.cz.liteonpi.com> schrieb im Newsbeitrag
news:#YLicqCtDHA.2492@.TK2MSFTNGP12.phx.gbl...
> a little problem
> I dont know why, but it is impossible to send mail bigger than 4MB
> outside my company. We are using MS Exchange 2000. I have set up the
> limit for sending and receving for 20MB. It is strange because inside
> the company it is OK, mails bigger then 5MB am able to send. But
> outside'?
>|||Thanks and sorry I have just subscribed the right group
Jens napsal(a):
> Sure you are in the right group ?
> But a limit can be declareated for in and outside the company, check the
> default setitings of
> your organsation settings.
> HTH
>
> "Jan West" <jan.west@.cz.liteonpi.com> schrieb im Newsbeitrag
> news:#YLicqCtDHA.2492@.TK2MSFTNGP12.phx.gbl...
>>a little problem
>>I dont know why, but it is impossible to send mail bigger than 4MB
>>outside my company. We are using MS Exchange 2000. I have set up the
>>limit for sending and receving for 20MB. It is strange because inside
>>the company it is OK, mails bigger then 5MB am able to send. But
>>outside'?
>
>

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
Biva
I had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>
|||Hi Jim,
You are awesome. That was it. I did xp_stopmail and xp_startmail and
now its working fine. Thanks a bunch for your help. Saved me a lot of
time.
biva
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
BivaI had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
BivaI had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>|||Hi Jim,
You are awesome. That was it. I did xp_stopmail and xp_startmail and
now its working fine. Thanks a bunch for your help. Saved me a lot of
time.
biva
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!