xp_sendmail blocks trigger ?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Rolf Kemper
    #1

    xp_sendmail blocks trigger ?

    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.
    How can this be solved better ?


    Any hint is highly welcome
    Regards
    Rolf
    Tags: None
    • Greg D. Moore \(Strider\)
      #2
      Re: xp_sendmail blocks trigger ?


      "Rolf Kemper" <KemperR@ee.nec .de> wrote in message
      news:bbd6fe79.0 409140731.5c444 2a9@posting.goo gle.com...[color=blue]
      > 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.[/color]

      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.

      [color=blue]
      > How can this be solved better ?[/color]

      What exactly are you trying to do? (i.e. why send the email in the trigger?)

      [color=blue]
      >
      >
      > Any hint is highly welcome
      > Regards
      > Rolf[/color]


        Comment

        • Raju
          #3
          Re: xp_sendmail blocks trigger ?

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

          Thank you
          Raju

            Comment

            • Rolf Kemper
              #4
              Re: xp_sendmail blocks trigger ?

              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\)" <mooregr_delete th1s@greenms.co m> wrote in message news:<7hK1d.261 53$2s.25525@twi ster.nyroc.rr.c om>...[color=blue]
              > "Rolf Kemper" <KemperR@ee.nec .de> wrote in message
              > news:bbd6fe79.0 409140731.5c444 2a9@posting.goo gle.com...[color=green]
              > > 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.[/color]
              >
              > 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.
              >
              >[color=green]
              > > How can this be solved better ?[/color]
              >
              > What exactly are you trying to do? (i.e. why send the email in the trigger?)
              >
              >[color=green]
              > >
              > >
              > > Any hint is highly welcome
              > > Regards
              > > Rolf[/color][/color]

                Comment

                • Rolf Kemper
                  #5
                  Re: xp_sendmail blocks trigger ?

                  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\)" <mooregr_delete th1s@greenms.co m> wrote in message news:<7hK1d.261 53$2s.25525@twi ster.nyroc.rr.c om>...[color=blue]
                  > "Rolf Kemper" <KemperR@ee.nec .de> wrote in message
                  > news:bbd6fe79.0 409140731.5c444 2a9@posting.goo gle.com...[color=green]
                  > > 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.[/color]
                  >
                  > 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.
                  >
                  >[color=green]
                  > > How can this be solved better ?[/color]
                  >
                  > What exactly are you trying to do? (i.e. why send the email in the trigger?)
                  >
                  >[color=green]
                  > >
                  > >
                  > > Any hint is highly welcome
                  > > Regards
                  > > Rolf[/color][/color]

                    Comment

                    • Erland Sommarskog
                      #6
                      Re: xp_sendmail blocks trigger ?

                      Rolf Kemper (KemperR@ee.nec .de) writes:[color=blue]
                      > 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.[/color]

                      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, esquel@sommarsk og.se

                      Books Online for SQL Server SP3 at
                      Microsoft SQL Server
                      http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
                      Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                        Comment

                        • Greg D. Moore \(Strider\)
                          #7
                          Re: xp_sendmail blocks trigger ?


                          "Rolf Kemper" <KemperR@ee.nec .de> wrote in message
                          news:bbd6fe79.0 409142127.73877 a02@posting.goo gle.com...[color=blue]
                          > 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.[/color]

                          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.

                          [color=blue]
                          >
                          > 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\)" <mooregr_delete th1s@greenms.co m> wrote in[/color]
                          message news:<7hK1d.261 53$2s.25525@twi ster.nyroc.rr.c om>...[color=blue][color=green]
                          > > "Rolf Kemper" <KemperR@ee.nec .de> wrote in message
                          > > news:bbd6fe79.0 409140731.5c444 2a9@posting.goo gle.com...[color=darkred]
                          > > > 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.[/color]
                          > >
                          > > 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[/color][/color]
                          than[color=blue][color=green]
                          > > one as you've discovered.
                          > >
                          > >[color=darkred]
                          > > > How can this be solved better ?[/color]
                          > >
                          > > What exactly are you trying to do? (i.e. why send the email in the[/color][/color]
                          trigger?)[color=blue][color=green]
                          > >
                          > >[color=darkred]
                          > > >
                          > > >
                          > > > Any hint is highly welcome
                          > > > Regards
                          > > > Rolf[/color][/color][/color]


                            Comment

                            Previous template Next
                            Working...