Logic problem in cursor/SPROC

SQL Server 2000

I have a stored procedure that uses an extended SPROC to send an email
notification to my customers when a document is distributed.

However, the SPROC has an unexpected side effect.

If I run it in its current incarnation, it only sends one email and
then exits.  However, if I remove or comment out the block

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @JobID)

then it runs through the whole list as anticipated.  Conceptually, it
seems that the records in the cursor are changed if the underlying
table is updated.

Here is pseudo-code for what the SPROC does - whole SPROC below (with
part of the "WHERE" clause removed for readability).  I haven't
included any table schemae but I don't think they're relevant.

1.  Open a cursor and fetch a list of all companies that need email
notification for pending jobs.

2.  While records in the cursor...

a) Format and send email from the cursor
b) Write a record to the audit table
c) Update the jobs table for the current record

3) Fetch next from cursor


There is an update trigger on the tblJobs table thus:

CREATE TRIGGER "tblJobs_UTrig" ON dbo.tblJobs FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN  'tblDistributionMast
er' */
IF  UPDATE(fldDistributi
onID)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM  tblDistributionMaste
r, inserted WHERE
 (tblDistributionMast
er.fldDistributionID = inserted.fldDistributionID))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
 ''tblDistributionMas
ter''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblPrinterJobs' */
IF UPDATE(fldJobID)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tblPrinterJobs WHERE
(deleted.fldJobID = tblPrinterJobs.fldJobID)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or
changed. Since related records exist in table ''tblPrinterJobs'',
referential integrity rules would be violated.'
ROLLBACK TRANSACTION
END
END

I can't see that this is relevant - I think it's something to do with
where I'm updating the fldEmailProcessed field, but I need to do this
here, as outside the loop the fldJobID might be lost.

Sorry it's all such a mess.  Hope someone can help!

Thanks

Edward

/*
Checks all Jobs that were set to Despatched more than 24 hours ago,
selects those that the companies elected to get email notification, and
sends them emails.
*/

CREATE  PROCEDURE  stpSendEmailNotifica
tion

AS

DECLARE @rc int
DECLARE @JobID int
DECLARE @CompanyID int
DECLARE @DocumentNumber varchar(50)
DECLARE @Email varchar(50)
DECLARE @DocumentURL varchar(750)
DECLARE @Dat varchar(20)
DECLARE @Subject varchar(100)

SET @Dat =  LEFT((CONVERT(varcha
r, GETDATE(), 100)), 11)


DECLARE MailList CURSOR FOR

SELECT
tblJobs.fldJobID,
tblJobs.fldDocumentNumber,
tblCompany.fldEmail,
tblCompany.fldCompanyID,
tblJobHistory.fldDocumentURL
FROM
tblJobHistory INNER JOIN
tblJobs ON tblJobHistory.fldJobID = tblJobs.fldJobID
INNER JOIN
 tblDistributionMaste
r ON tblJobHistory.fldDistributionID =
 tblDistributionMaste
r.fldDistributionID INNER JOIN
tblCompany ON tblJobHistory.fldCompanyID =
tblCompany.fldCompanyID
WHERE
(tblJobs.fldEmailProcessed = 0)

OPEN MailList


FETCH NEXT FROM MailList INTO
@JobID,
@DocumentNumber,
@Email,
@CompanyID,
@DocumentURL

WHILE @@FETCH_STATUS = 0
BEGIN

/* Format and send the email to the customer here */
SET @Subject = N'Document Distribution No: ' + @DocumentNumber +
N'  - Date: ' + @Dat

exec @rc = master.dbo.xp_smtp_sendmail
@FROM          = N'techlib@myco.co.uk',
@FROM_NAME     = N'Edward Collier',
@replyto       = N'techlib@myco.co.uk',
@TO            = @Email,
@CC            = N'',
@BCC           = N'',
@priority      = N'NORMAL',
@subject       = @Subject,
@type          = N'text/plain',
@message       = @DocumentURL,
@messagefile   = N'',
@attachment    = N'',
@attachments   = N'',
@codepage      = 0,
@server   	    = N'12.34.5.67',
@timeout       = 10000
select RC = @rc

/* Write result to audit table */
INSERT INTO tblEmailAudit
(
fldRCNo,
fldEmail,
fldDocumentNumber,
fldDate,
fldCompanyID
)
VALUES
(
@rc,
@Email,
@DocumentNumber,
GETDATE(),
@CompanyID
)

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @JobID)

FETCH NEXT FROM MailList INTO
@JobID,
@DocumentNumber,
@Email,
@CompanyID,
@DocumentURL
END

CLOSE MailList
DEALLOCATE MailList
GO

teddysnips@hotmail.com  wrote:
> SQL Server 2000
>
> I have a stored procedure that uses an extended SPROC to send an email
> notification to my customers when a document is distributed.
>
> However, the SPROC has an unexpected side effect.

[...]

I've sorted it.  The cursor needed to be declared STATIC.  Panic over.

Edward