programming not inserting email address

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'

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 ?

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.

Is there someone in this forum who can assist me?

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

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.

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

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.

<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 />

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.

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.

&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 />

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

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.

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!!!!!!!!

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