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_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER tr1_ProjectTask s_tab
ON dbo.ProjectTask s_tab
FOR UPDATE
AS
IF ( UPDATE(IsFinish ed) )
BEGIN
SET NOCOUNT ON
DECLARE @ProjectTasks_I D int
DECLARE @Employees_ID int
DECLARE @AllocationDela y 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_I D=ProjectTasks_ ID,
@Employees_ID=L astEditedBy,
@AllocationDela y=AllocationDel ay,
@AllocationTime =AllocationTime ,
@IsFinished=IsF inished
FROM ProjectTasks_ta b
WHERE ProjectTasks_ID IN ( SELECT ProjectTasks_ID FROM INSERTED )
--print '2_tr1_ProjectT asks_tab' -- UPDATE on IsFinished column
IF ( @IsFinished = 1 )
BEGIN
--print '3_tr1_ProjectT asks_tab' -- VALUE is 1
IF EXISTS ( SELECT * FROM
smdb.dbo.const_ ProjectStatusDe layTolerances_t ab
WHERE Duration = @AllocationTime AND @AllocationDela y >
MessageToleranc e )
BEGIN
SELECT @TOList=TOList , @CCList=CCList
FROM smdb.dbo.Create MailingList ('ToEmployeeCcH isBoss' ,
@Employees_ID ,default,defaul t,default )
SET @MailText='Text 1' + @CRLF
SET @MailText=@Mail Text + Text2' + @CRLF
+@CRLF
SET @MailText=@Mail Text +
'http://intra.etc.nec.d e/ProjectStatus/QuickStatus.asp ?TaskID=' +
CAST(@ProjectTa sks_ID as varchar(12)) + @CRLF + @CRLF
SET @MailText=@Mail Text + Text3'
EXEC @Status = master..xp_send mail @recipients=@TO List,
@copy_recipient s=@CCList,
@subject='Text3 ',
@message=@MailT ext,
@no_output=TRUE
END
END
END
GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO
############### ############### # end
############### ############### #########
"Greg D. Moore \(Strider\)" <mo************ ****@greenms.co m> wrote in message news:<7h******* ***********@twi ster.nyroc.rr.c om>...
"Rolf Kemper" <Ke*****@ee.nec .de> wrote in message
news:bb******** *************** ***@posting.goo gle.com... 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_send mail @recipients=@TO List,
@copy_recipient s=@CCList,@subj ect='the subject goes
here',@message= @MailText,@no_o utput=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
