Duplicated E-Mails Problem

Question

Duplicated E-Mails Problem

Nick-253640

SSC Rookie

Points: 27
More actions
August 19, 2005 at 10:12 am

#110593

Hi All,

I have SQL Server 7. I turn on the E-mail futures and I am using outlook at the server side. E-mails are generated automatically with a simple SP. When E-mail sent, it is duplicated. Even thought SP is executed only one time. I am sending my SP code that you can review. I do not know why same E-Mail sent twice

CREATE procedure SendShipperInfo
@EMailVal integer,
@CompCode nvarchar(20),
@ERecipient nvarchar(100),
@ShipperNo nvarchar(12)
AS
DECLARE @Now varchar(20), @VAL1 varchar(150) , @VAL2 varchar(150),@VAL3 varchar(100)
SET @Now = GETDATE()
select @VAL3= ''+@ERecipient+''
SET @VAL1 = 'The contents of shipment Information. Shipper # ' + @ShipperNo + ' ---> It was sent on ' + @Now
SET @VAL2 = 'New Shipment from ' + @CompCode + ' ---> Shipper # ' + @ShipperNo
IF @EMailVal = 1
    BEGIN
      SET @VAL1 = 'This Error message generated automatically -- Please check Barcode Systems Error Log - Customer Record Does Not exist!! '
      EXEC master.dbo.xp_sendmail @recipients = @ERecipient,
         @copy_recipients = 'nick',
         @subject = 'Convert Problem Report',
         @message = @VAL1
    END
ELSE IF @EMailVal = 2
    BEGIN
    CREATE TABLE ##TblShipper (
[Qty] int NULL ,
[partID] [nvarchar] (16) NULL ,
[Desc1] [nvarchar] (25) NULL ,
[IPN] [nvarchar] (16) NULL ,
              [PCS] [nvarchar] (4) NULL,
[PON] [nvarchar] (12) NULL )
      insert into ##TblShipper
      SELECT DT.quantity, LEFT(DT.BuyerPartNumber,16), LEFT(CP.PartDesc,25), LEFT(DT.IntPartID,16), DT.ReqCont, DT.ponum FROM tblInternalPickListDt DT, CPART CP
      WHERE CP.ShipTo = DT.ShiptoCode AND CP.PartID = DT.BuyerPartNumber
                     AND CP.intpartid = DT.IntPartID AND DT.ShipperNo = @ShipperNo AND CP.OBS = 0
      DECLARE @cmd varchar(200)
      SET @cmd = 'SELECT cast(IPN as char(16)) as IPN, cast(partid as char(16)) as CPN, cast(Desc1 as char(25)) as Description, qty as Quantity FROM ##TblShipper ORDER by IPN'
    EXEC master.dbo.xp_sendmail @recipients = @VAL3 ,
      @subject = @VAL2,
      @message = @VAL1 ,
      @query = @cmd, @no_header= 'FALSE'
      DROP TABLE ##TblShipper
    END
GO

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Have you looked at the WHERE you call this from to ensure that this is not in a loop??? I don't see anything wrong with the SP.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Nick-253640 SSC Rookie Points: 27 More actions · Answer 2

There is no loop at all. Also, I am running same application from my pc and one E-mail is generated. But, when the other users run same application from different pc, two E-mail is generated