By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,812 Members | 1,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,812 IT Pros & Developers. It's quick & easy.

xp_sendmail blocks trigger ?

P: n/a
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.
How can this be solved better ?
Any hint is highly welcome
Regards
Rolf
Jul 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"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

Jul 20 '05 #2

P: n/a
you can take the status of the update/insert trigger and put a IF
condition to send a email notification.

Thank you
Raju
Jul 20 '05 #3

P: n/a
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

Jul 20 '05 #4

P: n/a
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

Jul 20 '05 #5

P: n/a
Rolf Kemper (Ke*****@ee.nec.de) writes:
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.


One way is to write the mailing task to a table, and then have an SQL
Agent job to poll that table and send the mail.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

P: n/a

"Rolf Kemper" <Ke*****@ee.nec.de> wrote in message
news:bb**************************@posting.google.c om...
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.
Again, need to know what exactly your ultimate goal is.

As Erland I think it was said, perhaps having a scheduled task firing off
every minute or so is the best way to go.


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

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.