xp_sendmail in trigger from asp page -error

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: R.D (RD_at_discussions.microsoft.com)
Date: 01/03/05 

Date: Mon, 3 Jan 2005 04:09:02 -0800

Hi all

I insert data into table A through an asp page which executes
encrypted sp for inserting data. I worked fine till i used a trigger on
the same table which uses xp_send mail for insert, upate.

the following scenareos occur
1.when i remove the trigger, data is getting inserted from asp form. on the
otherhand when trigger is added, data is NOT getting inserted from asp and
some syntax error in asp in shown.

2, however, I could insert data by executing stored procedure from access
front end which means error occurs only in asp

should i use set no count on in sp/trigger

my trigger code is :

CREATE TRIGGER MAILSENDING
ON dbo.OnlineGH
AFTER INSERT,UPDATE
as
DECLARE @mailname varchar(100),
@subject varchar (250),
@message varchar (250),
@STATUSNAME varchar (250),
@STATUS int,
@tempsubject varchar(250),
@tempmessage varchar (250)
SELECT @STATUS = [Status ID], @mailname = Email FROM INSERTED
SELECT @STATUSNAME = @StatusName from statustable WHERE StatusCode = @STATUS
SELECT @tempsubject = 'Your status of Request for accommodation at IICT'
SELECT @tempmessage = 'Your request for accommodation is ' ++ @STATUSNAME +
'for further information visit www.iictindia.org using your check status
idno:'
EXEC master.dbo.xp_startmail
EXEC master.dbo.xp_sendmail @recipients = @mailname
,@subject = @tempsubject
,@message = @tempmessage
EXEC master.dbo.xp_stopmail

Any Help will go along way in finishing my small application

R.D

Relevant Pages

  • Re: xp_sendmail in trigger from asp page -error
    ... What credentials are you using while connecting to database from ASP page? ... > 1.when i remove the trigger, data is getting inserted from asp form. ... > @subject varchar, ...
    (microsoft.public.sqlserver.programming)
  • Re: Help in writing a trigger
    ... --As per your inserting something and it throws an error ... --Check whether @SID already present. ... I have not written a trigger in past:(More over we are> talking ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)
  • Re: Determining trigger execution state
    ... an insert trigger will only populate the inserted ... but an update trigger will populate both inserted and deleted. ... > IF Inserting ...
    (microsoft.public.sqlserver.programming)
  • Re: BLOB (Text) flied in Trigger
    ... Your trigger looks fine for inserting the text column -- you're inserting ... RECID IN ... > This works fine for all fields except the NOTES field -- which is of type ...
    (microsoft.public.sqlserver.programming)
  • Re: Help in writing a trigger
    ... Instead of TEMP I made use of TABLE variable. ... ALTER TRIGGER sampleTrigger ON dbo.tSource ... --Check whether SID already present. ... --As per your inserting something and it throws an error ...
    (microsoft.public.sqlserver.programming)