Dear Greg,
thank you for offering some help. You confirmed my fear that my
approch is bad in general. So, if you can offer a strategie please let
me know. In particular,
where can I get more about the things to consider when using external
DLL.
Here is the code. I just cut the version comments in header and text
in the mail to make it shorter.
Thanks a lot
Rolf
#################### code of trigger #############################
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER tr1_ProjectTasks_tab
ON dbo.ProjectTasks_tab
FOR UPDATE
AS
IF ( UPDATE(IsFinished) )
BEGIN
SET NOCOUNT ON
DECLARE @ProjectTasks_ID int
DECLARE @Employees_ID int
DECLARE @AllocationDelay int
DECLARE @AllocationTime int
DECLARE @LastEditedBy int
DECLARE @IsFinished bit
DECLARE @TOList nvarchar(1000)
DECLARE @CCList nvarchar(2000)
DECLARE @MailText nvarchar(1000)
DECLARE @Status int
DECLARE @CRLF char(2)
SET @CRLF = CHAR(10) + CHAR(13)
SELECT @ProjectTasks_ID=ProjectTasks_ID,
@Employees_ID=LastEditedBy,
@AllocationDelay=AllocationDelay,
@AllocationTime=AllocationTime,
@IsFinished=IsFinished
FROM ProjectTasks_tab
WHERE ProjectTasks_ID IN ( SELECT ProjectTasks_ID FROM INSERTED )
--print '2_tr1_ProjectTasks_tab' -- UPDATE on IsFinished column
IF ( @IsFinished = 1 )
BEGIN
--print '3_tr1_ProjectTasks_tab' -- VALUE is 1
IF EXISTS ( SELECT * FROM
smdb.dbo.const_ProjectStatusDelayTolerances_tab
WHERE Duration = @AllocationTime AND @AllocationDelay >
MessageTolerance )
BEGIN
SELECT @TOList=TOList , @CCList=CCList
FROM smdb.dbo.CreateMailingList ('ToEmployeeCcHisBoss' ,
@Employees_ID ,default,default,default )
SET @MailText='Text1' + @CRLF
SET @MailText=@MailText + Text2' + @CRLF
+@CRLF
SET @MailText=@MailText +
'http://intra.etc.nec.de/ProjectStatus/QuickStatus.asp?TaskID=' +
CAST(@ProjectTasks_ID as varchar(12)) + @CRLF + @CRLF
SET @MailText=@MailText + Text3'
EXEC @Status = master..xp_sendmail @recipients=@TOList,
@copy_recipients=@CCList,
@subject='Text3',
@message=@MailText,
@no_output=TRUE
END
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
############################### end
#######################################
"Greg D. Moore \(Strider\)" <mo****************@greenms.com> wrote in message news:<7h******************@twister.nyroc.rr.com>.. .
"Rolf Kemper" <Ke*****@ee.nec.de> wrote in message
news:bb**************************@posting.google.c om... Dear All,
we are running SQL2000 Sever and make use of the xp_sendmail.
For any reason the mail service can run into problems and it looks
like that the statemnt below gets not finished.
EXEC @Status = master..xp_sendmail @recipients=@TOList,
@copy_recipients=@CCList,@subject='the subject goes
here',@message=@MailText,@no_output=TRUE
Unfortunately the statement is in an update trigger and hence it
blocks the table for any further updates.
My questions are:
Can I achieve a kind of timeout check in my trigger in order to bypass
the
xp_sendmail call ?
In general, sending mail in a trigger may not be a good idea.
May is an understatement.
Triggers need to execute as quickly as possible.
Anything calling an external DLL is going to be an issue in more ways than
one as you've discovered.
How can this be solved better ?
What exactly are you trying to do? (i.e. why send the email in the trigger?)
Any hint is highly welcome
Regards
Rolf
