email trigger on update/Access97 ODBC error

[nstouffer]

I am using Access97 front-end for an application.
SQL Server 2000 for the back-end of the application.
(ODBC remote linked tables)

i have the update (after) trigger on one sql table, and all
works fine when manually changing the row to the specified
criteria... trigger fires.. and email sends...

the problem is that when clicking the approve button
in the application under conditions that the trigger would fire i get a 'ODBC error - update on remote table failed'. and the update query that access97 sent seemed to abort.

any ideas how to fix ? thanks.

trigger code:

CREATE TRIGGER selfapproval1000 ON [dbo].[tblPOlog]
AFTER UPDATE
AS

--IF APPROVED_BY DIDNT CHANGE THEN EXIT
if not update(approved_by)
begin
return
end

--DETERMINE IF PO WAS SELF-APPROVED AND REACHES LIMIT OF $1000
IF EXISTS (SELECT * FROM INSERTED WHERE APPROVED_BY = INITIATED_BY AND TOTAL >= 1000 )
BEGIN

--EMAIL CODE
DECLARE @EMAILMESSAGE VARCHAR(255), @LOC VARCHAR(2), @PO varchar(10), @USER VARCHAR(40), @VENDOR VARCHAR(50), @LINK VARCHAR(255)
--SET VARIABLE VALUES
SELECT @LOC = LOCID, @PO = PONUMBER, @USER = APPROVED_BY, @VENDOR = VENDOR_NAME
FROM INSERTED
WHERE APPROVED_BY = INITIATED_BY AND TOTAL >= 1000
--BUILD LINK
SELECT @LINK = '

http://hidden/Crystal Reports/cpoPO-online-dynamic.asp?ponumber='

+ @PO

--BUILD MESSAGE
SELECT @EMAILMESSAGE = 'CPO Notification ! PO Number ' + @LOC + '-' + @PO +' has just been self-approved by ' + @USER + ' to ' + @VENDOR + '. To view this po click the following link: ' + @LINK

--SEND EMAIL
EXEC master..xp_sendmail @recipients = 'hidden@us.hidden.com', @message = @EMAILMESSAGE, @subject = 'CPO Controller - Self-Approval of PO over $1000'

END

 

[nstouffer]

well, i still don't understand the conflict totally, it seems to be between xp_sendmail and access97 updates sent
to remote linked tables... but i did do a workaround..

changed the trigger to insert email notice rows into another table, then scheduled a job to run every minute
to take the first row that hasn't been sent yet, and email it and then mark it as sent.. one check per minute is enough to keep up. kind of a mail queue if you will. is working fine now through access front-end.

nstouffer