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 'tblDistributionMaster' */
IF UPDATE(fldDistributionID)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDistributionMaster, inserted WHERE
(tblDistributionMaster.fldDistributionID = inserted.fldDistributionID))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tblDistributionMaster''.'
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 stpSendEmailNotification

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(varchar, 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
tblDistributionMaster ON tblJobHistory.fldDistributionID =
tblDistributionMaster.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