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

EXCEPTION_ACCESS_VIOLATION

Hi,
using the SQL Server enterprise manager I try to creat a 'INSTEAD
OF'-trigger on a view. When I try to apply changes I get the error
EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what this
error is about? I've found almost no info using google.
//MagnusThese are most often bus in SQL Server (possibly data corruption, but not likely in this scenario).
If you didn't find anything on KB and you are current on service pack, I suggest you open a case
with MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Magnus Zotterman" <MagnusZotterman@.discussions.microsoft.com> wrote in message
news:6D3B4C3A-DC99-40EB-BB60-30A1979573D0@.microsoft.com...
> Hi,
> using the SQL Server enterprise manager I try to creat a 'INSTEAD
> OF'-trigger on a view. When I try to apply changes I get the error
> EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what this
> error is about? I've found almost no info using google.
> //Magnus|||Hi,Tibor
I am sure you meant bug instead of bus :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23p7KSHFsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> These are most often bus in SQL Server (possibly data corruption, but not
likely in this scenario).
> If you didn't find anything on KB and you are current on service pack, I
suggest you open a case
> with MS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Magnus Zotterman" <MagnusZotterman@.discussions.microsoft.com> wrote in
message
> news:6D3B4C3A-DC99-40EB-BB60-30A1979573D0@.microsoft.com...
> > Hi,
> > using the SQL Server enterprise manager I try to creat a 'INSTEAD
> > OF'-trigger on a view. When I try to apply changes I get the error
> > EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what
this
> > error is about? I've found almost no info using google.
> >
> > //Magnus
>|||:-)
Thanks for catching that, Uri.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwE$NLFsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi,Tibor
> I am sure you meant bug instead of bus :)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23p7KSHFsEHA.3324@.TK2MSFTNGP15.phx.gbl...
>> These are most often bus in SQL Server (possibly data corruption, but not
> likely in this scenario).
>> If you didn't find anything on KB and you are current on service pack, I
> suggest you open a case
>> with MS.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Magnus Zotterman" <MagnusZotterman@.discussions.microsoft.com> wrote in
> message
>> news:6D3B4C3A-DC99-40EB-BB60-30A1979573D0@.microsoft.com...
>> > Hi,
>> > using the SQL Server enterprise manager I try to creat a 'INSTEAD
>> > OF'-trigger on a view. When I try to apply changes I get the error
>> > EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what
> this
>> > error is about? I've found almost no info using google.
>> >
>> > //Magnus
>>
>|||Create it with QA.
Also see if you can create it from another EM client ..
"Magnus Zotterman" <MagnusZotterman@.discussions.microsoft.com> wrote in
message news:6D3B4C3A-DC99-40EB-BB60-30A1979573D0@.microsoft.com...
> Hi,
> using the SQL Server enterprise manager I try to creat a 'INSTEAD
> OF'-trigger on a view. When I try to apply changes I get the error
> EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what
this
> error is about? I've found almost no info using google.
> //Magnus

EXCEPTION_ACCESS_VIOLATION

Hi,
using the SQL Server enterprise manager I try to creat a 'INSTEAD
OF'-trigger on a view. When I try to apply changes I get the error
EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what thi
s
error is about? I've found almost no info using google.
//MagnusThese are most often bus in SQL Server (possibly data corruption, but not li
kely in this scenario).
If you didn't find anything on KB and you are current on service pack, I sug
gest you open a case
with MS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Magnus Zotterman" <MagnusZotterman@.discussions.microsoft.com> wrote in mess
age
news:6D3B4C3A-DC99-40EB-BB60-30A1979573D0@.microsoft.com...
> Hi,
> using the SQL Server enterprise manager I try to creat a 'INSTEAD
> OF'-trigger on a view. When I try to apply changes I get the error
> EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what t
his
> error is about? I've found almost no info using google.
> //Magnus|||Hi,Tibor
I am sure you meant bug instead of bus
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23p7KSHFsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> These are most often bus in SQL Server (possibly data corruption, but not
likely in this scenario).
> If you didn't find anything on KB and you are current on service pack, I
suggest you open a case
> with MS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Magnus Zotterman" <MagnusZotterman@.discussions.microsoft.com> wrote in
message
> news:6D3B4C3A-DC99-40EB-BB60-30A1979573D0@.microsoft.com...
this[vbcol=seagreen]
>|||:-)
Thanks for catching that, Uri.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwE$NLFsEHA.1816@.TK2MSFTNGP09.phx.gbl.
.
> Hi,Tibor
> I am sure you meant bug instead of bus
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23p7KSHFsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> likely in this scenario).
> suggest you open a case
> message
> this
>|||Create it with QA.
Also see if you can create it from another EM client ..
"Magnus Zotterman" <MagnusZotterman@.discussions.microsoft.com> wrote in
message news:6D3B4C3A-DC99-40EB-BB60-30A1979573D0@.microsoft.com...
> Hi,
> using the SQL Server enterprise manager I try to creat a 'INSTEAD
> OF'-trigger on a view. When I try to apply changes I get the error
> EXCEPTION_ACCESS_VIOLATION. I use SQL Server 2000. Does enyone know what
this
> error is about? I've found almost no info using google.
> //Magnus

exception that does nothing?

Hi,
I want to have an exception do nothing. or rather, I just want my trigger to ignore this problem, terminate the current block, and go on to the next one.

here is my code:

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (''); -- do nothing.

currently, im getting the error below. I think it has to do with the DBMS overflowing

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139

thanks.never mind, i believe i found out that you could just put 'null' instead of having dbms output, and the block terminates succesfully, without doing anything.

Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>
My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>

Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
>