SQL Server Performance

programming not inserting email address

Discussion in 'General Developer Questions' started by simflex, Oct 20, 2003.

  1. simflex New Member

    I have been struggling with this code all day today.
    I could have sworn everything was working fine when I left here on Friday.
    Per the below code(forgive me it is rather long).
    The top half of the code selects records from a few tables where completion date is 0.
    Then those records are inserted into a table called Notification.
    Eventually, an email program will retrieve these records from Notification table and sends an email to those concerned.
    The emails come in batches of 3 - emp, his/her supervisor and the supervisor's supervisor.
    So far, the records that are inserted into notification table always enters 0 into the 3rd email address instead of the real email address.
    I cannot for the life of me figure out what is happening.
    Can someone, please, please help.
    Here is the code and thanks in advance.

    CREATE PROCEDURE dbo.NOTIFY

    AS

    BEGIN

    DECLARE Notify_Cursor CURSOR FOR

    SELECT theEmp.fname+' '+theEmp.lname as fullName,

    tblEmployeeType.Type,

    tblEmployAccident.TrackingNumber,

    tblEmployAccident.Completed,

    theEmp.Email,

    tblAccidentInfo.staffMem,

    TimeOfAccident,

    DateOfAccident

    FROM tblEmployeeType,

    theEmp,

    tblEmployAccident,

    tblAccidentInfo

    WHERE tblEmployeeType.TypeID = theEmp.TypeID AND

    theEmp.empID = tblEmployAccident.empID

    AND tblAccidentInfo.TrackingNumber = tblEmployAccident.TrackingNumber

    AND tblEmployAccident.Completed = 0

    AND DateAdd(hour, -24, getdate()) > DateOfAccident

    ORDER BY tblEmployeeType.TypeID DESC



    OPEN Notify_Cursor

    Declare @fullName nvarchar(50)

    Declare @Type nvarchar(50)

    Declare @trackingNumber varchar(20)

    Declare @Completed nvarchar(50)

    Declare @email nvarchar(1000)

    Declare @staffMem nvarchar(50)

    Declare @TimeOfAccident nvarchar(50)

    Declare @DateOfAccident nvarchar(50)

    -- Get the current MAX ID

    Declare @mailID as int

    -- Start reading each record from the cursor.

    FETCH Notify_Cursor into @fullName,

    @Type,

    @trackingNumber,

    @Completed,

    @email,

    @StaffMem,

    @TimeOfAccident,

    @DateOfAccident

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @mailID = (SELECT max(mailID)+ 1 from notification)

    INSERT into notification (mailid,

    fullName,

    Type,

    trackingNumber,

    mailContent,

    sender,

    emailDate,

    email,

    Completed,

    staffMem,

    TimeOfAccident,

    DateOfAccident,

    adminName)

    VALUES (

    @mailid,

    @fullName,

    @Type,

    @TrackingNumber,

    'This is a computer generated email message.

    Please do NOT use the REPLY button above to respond to this email.

    Dear '+ @fullName +'

    Please be advised, the Accident Hotline has initiated Accident Case number '+ @trackingNumber +'.

    The accident occurred on '+@DateOfAccident +' at '+@TimeOfAccident +' by '+@staffMem +'.

    For more details on this case please click here:

    http://hq1/safe/paging.asp?trackingNumber=' @TrackingNumber '.

    This is an effort to efficiently and effectively track the progress of the accident information packet to be sent to Risk Management. To learn more about the steps involved with this process please click here: \c03pwpublicsafe\AcT_Report



    Please be advised, the following deadlines must be adhered:



    Step 1 is due on: '+ CAST(DateAdd(d,1,@DateOfAccident) as VARCHAR)+'

    Step 2 is due on: '+ CAST(DateAdd(d,2,@DateOfAccident) as VARCHAR)+'

    Step 3 is due on: '+CAST(DateAdd(d,3,@DateOfAccident) as VARCHAR)+'



    Failure to adhere to the above deadlines may result in disciplinary action.

    For more information, please contact the Accident Hotline at xxx-xxx-xxxx or the Safe Administrator at xxx-xxx-xxxx.

    Regards,

    The Administrator',

    'admin' ,

    getdate(),

    @email,

    'Open',

    @staffMem,

    @TimeOfAccident,

    @DateOfAccident,

    'admin'

  2. ykchakri New Member

    What is the structure of the notification table ? You seem to be inserting into 2 columns (mailid and email) with the emailid. Where's the third one ?
  3. simflex New Member

    Oh, thank you for your response, I almost gave up.
    the structure of the notification table follows:

    mailID int
    fullName nvarchar(50)
    TrackingNumber int
    mailContent nvarchar(4000)
    sender nvarchar(50)
    Completed nvarchar(50)
    email nvarchar(500)
    adminName nvarchar(50)
    type nvarchar(50)
    emailDate datetime
    DateOfAccident datetime
    TimeOfAccident nvarchar(50)
    staffMem varchar(50)

    Please let me know if you have an additional question
    and thanks again for your interest to help.
  4. simflex New Member

    Is there someone in this forum who can assist me?
  5. Twan New Member

    What is the value of the 3rd email if you run the following from Query Analyser?

    SELECT theEmp.fname+' '+theEmp.lname as fullName,
    tblEmployeeType.Type,
    tblEmployAccident.TrackingNumber,
    tblEmployAccident.Completed,
    theEmp.Email,
    tblAccidentInfo.staffMem,
    TimeOfAccident,
    DateOfAccident
    FROM tblEmployeeType,
    theEmp,
    tblEmployAccident,
    tblAccidentInfo
    WHERE tblEmployeeType.TypeID = theEmp.TypeID AND
    theEmp.empID = tblEmployAccident.empID
    AND tblAccidentInfo.TrackingNumber = tblEmployAccident.TrackingNumber
    AND tblEmployAccident.Completed = 0
    AND DateAdd(hour, -24, getdate()) > DateOfAccident
    ORDER BY tblEmployeeType.TypeID DESC


    The code you have looks fairly simple and I can't see any other reason why it would set @email to 0

    Cheers
    Twan
  6. simflex New Member

    Hi Twan!
    Thanks a bunch for the response.
    When I run this on query analyzer, all email addresses are returned correctly.
    Here is a copy of the email sent out so you can see my problem

    Sorry I could not copy and paste the message being sent out but on the TO block, it shows 0 as the email address to.
    However, on the body of the message, it shows the correct individual referenced in the email.
    For instance, instead of showing the email address as someName@domain.com, it shows 0 as you can see but on the message, it references the individual correctly as Dear someName.
    Please see what you can do to help me resolve this because not all of them are receiving emails.
  7. Twan New Member

    and the email id is already missing from the notification table? or is it the process that takes it from this table which is causing a problem?<br /><br />Perhaps you could run Profiler to capture SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted and check to see what actually happens, failing that you could try debugging the stored procedure via Query Analyser?<br /><br />Cheers<br />Twan
  8. simflex New Member

    the process that takes it from this table which is causing a problem?

    I think you touched on the problem area.
    I know that the sql statement (which you asked me to test in query analyser) is correct.
    I decided to include it in the post so anyone attempting to help will get the big picture.
    Now there are two email programs here.
    This is the first one; the other one is the sp_sendmail stored proc but I wanted to see if I could isolate the problem.
    By that I mean I wanted to ensure that the code I pasted on this thread is not the problem; so then I can focus on the other code.
    I am doing real well on that so far.
    At this point, I thing the problem lies from this line of code:
    OPEN Notify_Cursor
    downwards.
  9. Twan New Member

    <br />When the procedure completes, what is in table "notification"? Is it correct?<br /><br />What if instead of doing the insert into te notifcation table you select all of the variables to check that they show the correct values?<br /><br />There are no triggers on the notification table which may reset the emailid for some reason?<br /><br />Use Profiler to give you all of the sp<img src='/community/emoticons/emotion-7.gif' alt=':s' />tatements completed. This will show you the sql that is fired the whole way through...<br /><br />Cheers<br />Twan<br /><br />
  10. simflex New Member

    When the procedure completes, the notification table contains all the correct information as coded in the select statement.
    The only thing wrong is the email address whereby the first 2 email addresses are correct but the last one contains a zero (0).
    During testing, I created a temp table and have data inserted into them and everything was fine.
    select all of the variables to check that they show the correct values?

    Not sure what you mean here.

    There are no triggers on the notification table which may reset the emailid for some reason?

    Are you suggesting that the emailID may be reset to an unknown value for some reason?

    Believe it or not but I have never used profiler before.
    So I will look into it.
    You have been helpful and patient, thank you.
  11. bambola New Member

    A shot in the dark. You say "The emails come in batches of 3 - emp, his/her supervisor and the supervisor's supervisor."
    I'm wondering about how you get the email of the supervisor's supervisor (is this where you get the 0?). It is possible that you
    are missing a join to get the email (self join of theEmp maybe)? You do have the rest of the info and it's just the email that's
    missing. Though that doesn't explain the 0...

    Bambola.
  12. bambola New Member

    &gt; During testing, I created a temp table and have data inserted into them and everything was fine.<br /><br />I've just seen this response. Do you mean the third email was ok and not 0?<br />In this case, could you post the rest of the code? it might be some little mistake that after looking at it for 2 days you can no longer see <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Bambola.<br />
  13. simflex New Member

    I am awfully impressed with the quality of help I am getting here.
    Thank you all very much!!

    Ok, here is the entire live code. The only 2 things I changed are email addresses and phone numbers so I don't get into trouble.
    It is rather long, sorry about that.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_ACCIDENT_NOTIFY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[SP_ACCIDENT_NOTIFY]
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    CREATE PROCEDURE dbo.SP_ACCIDENT_NOTIFY
    AS
    BEGIN
    DECLARE Notify_Cursor CURSOR FOR
    SELECT theEmp.fname+' '+theEmp.lname as fullName,
    tblEmployeeType.Type,
    tblEmployAccident.TrackingNumber,
    tblEmployAccident.Completed,
    theEmp.Email,
    tblAccidentInfo.staffMem,
    TimeOfAccident,
    DateOfAccident
    FROM tblEmployeeType,
    theEmp,
    tblEmployAccident,
    tblAccidentInfo
    WHERE tblEmployeeType.TypeID = theEmp.TypeID AND
    theEmp.empID = tblEmployAccident.empID
    AND tblAccidentInfo.TrackingNumber = tblEmployAccident.TrackingNumber
    AND tblEmployAccident.Completed = 0
    AND DateAdd(hour, -24, getdate()) > DateOfAccident
    ORDER BY tblEmployeeType.TypeID DESC

    OPEN Notify_Cursor
    Declare @fullName nvarchar(50)
    Declare @Type nvarchar(50)
    Declare @trackingNumber varchar(20)
    Declare @Completed nvarchar(50)
    Declare @email nvarchar(1000)
    Declare @staffMem nvarchar(50)
    Declare @TimeOfAccident nvarchar(50)
    Declare @DateOfAccident nvarchar(50)
    -- Get the current MAX ID
    Declare @mailID as int
    -- Start reading each record from the cursor.
    FETCH Notify_Cursor into @fullName,
    @Type,
    @trackingNumber,
    @Completed,
    @email,
    @StaffMem,
    @TimeOfAccident,
    @DateOfAccident
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @mailID = (SELECT max(mailID)+ 1 from notification)
    INSERT into notification (mailid,
    fullName,
    Type,
    trackingNumber,
    mailContent,
    sender,
    emailDate,
    Completed,
    email,
    staffMem,
    TimeOfAccident,
    DateOfAccident,
    adminName)
    VALUES (
    @mailid,
    @fullName,
    @Type,
    @TrackingNumber,
    'This is a computer generated email message.
    Please do NOT use the REPLY button above to respond to this email.
    Dear '+ @fullName +'
    Please be advised, the DPW Accident Hotline has initiated Accident Case number '+ @trackingNumber +'.
    The accident occurred on '+@DateOfAccident +' at '+@TimeOfAccident +' by '+@staffMem +'.
    For more details on this case please click here:
    http://pw2/safety/paging.asp?trackingNumber=' @TrackingNumber '.
    This is an effort to efficiently and effectively track the progress of the accident information packet to be sent to Risk Management. To learn more about the steps involved with this process please click here: \fulsoReport03

    Please be advised, the following deadlines must be adhered:

    Step 1 is due on: '+ CAST(DateAdd(d,1,@DateOfAccident) as VARCHAR)+'
    Step 2 is due on: '+ CAST(DateAdd(d,2,@DateOfAccident) as VARCHAR)+'
    Step 3 is due on: '+CAST(DateAdd(d,3,@DateOfAccident) as VARCHAR)+'

    Failure to adhere to the above deadlines may result in disciplinary action.
    For more information, please contact the Accident Hotline at xxx-xxx-xxxx or the Safety Administrator at xxx-xxx-xxxx.
    Regards,
    The Administrator',
    'name@domain.com' ,
    getdate(),
    'Open',
    @email,
    @staffMem,
    @TimeOfAccident,
    @DateOfAccident,
    'name@domain.com'
    )

    FETCH Notify_Cursor into @fullName,@Type, @trackingNumber,
    @email, @Completed, @staffMem,@TimeOfAccident, @DateOfAccident
    END

    CLOSE Notify_Cursor
    DEALLOCATE Notify_Cursor
    END

    BEGIN
    DECLARE MAIL_CURSOR CURSOR FOR

    select mailid, adminName, email,trackingNumber, mailcontent
    from notification
    where Completed = 'Open'

    Declare @mail1 int
    Declare @admin1 nvarchar(100)
    declare @content1 nvarchar(4000)
    Declare @trackingNumber1 nvarchar(20)
    Declare @email1 nvarchar(1000)

    OPEN MAIL_CURSOR

    FETCH MAIL_CURSOR into @mail1, @admin1, @email1,@trackingNumber1,@content1

    WHILE @@FETCH_STATUS = 0
    BEGIN

    exec sp_send_cdontsmail @admin1, @email1,@trackingNumber1,@content1,null,null

    --Logically delete the record in notification table where Completed = 'Open'.
    --This ensures the same email is not sent more than once

    Update Notification SET Completed = 'Closed' WHERE Completed = 'Open'

    FETCH MAIL_CURSOR INTO @mail1, @admin1, @email1,@trackingNumber1,@content1
    END

    CLOSE MAIL_CURSOR
    DEALLOCATE MAIL_CURSOR
    END

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
  14. bambola New Member

    I thought it would be something like this... Here is your error<br /><br />First fetch<br />@fullName,@Type, @trackingNumber, <b>@email, @Completed,</b> @staffMem,@TimeOfAccident, @DateOfAccident<br /><br />following fetches<br />@fullName,@Type, @trackingNumber, <b>@Completed, @email,</b> @StaffMem, @TimeOfAccident, @DateOfAccident<br /><br />you switched the order of the parameters. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.
  15. simflex New Member

    That indeed was the problem.
    You have saved me, NO,you guys in this forum have saved and I am truly and sincerely grateful for everyone's contribution.
    I am sure I will be back.

    it might be some little mistake that after looking at it for 2 days you can no longer see

    How true!!!!!!!!
  16. Twan New Member

    now who finds the obsure mistake huh? well done Bambola <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Twan<br />

Share This Page