473,325 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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'*******@myco.co.uk',
@FROM_NAME = N'Edward Collier',
@replyto = N'*******@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

Jul 23 '05 #1
1 2020


te********@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

Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: scott | last post by:
In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm...
4
by: Radu | last post by:
Hi. It seems to be very simple, actually, but I don't know if it is feasible in TSQL. I have a sproc which gathers in one place many calls to different other sprocs, all of them taking a...
3
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to...
7
by: Mike L. | last post by:
Hi, I got this 'EXECUTE permission denied on object <mySproc>' error message everytime I try executing my SQL server Sproc. What's this and how to fix this err? many thnaks in advance, mike
3
by: mghale | last post by:
I'm trying to create a cursor and complete some processing against DB2. I'm using DB2 V8.2 on AIX. I don't wont to create a stored procedure, I just want to use a regular query editor such as...
0
by: seevion | last post by:
My first post (beginner).. I hope it is clear enough and appreciate your taking the time to consider helping. I have an existing sproc that takes a cart transaction from a table and inserts shared...
5
by: =?Utf-8?B?UlBhcmtlcg==?= | last post by:
I used the wizard to generate a typed dataset for my table and let it create my SPROCs. It created everything, and the GetData() method and the custom GetByUserName query works great, but when I...
1
by: Looch | last post by:
Hi All, I originally wrote a sproc with one of the parameters set as SqlDBType.nvarchar,8. The parameter in the sproc was defined as 8 cahracters and the column in the table was also set to...
5
by: vamsioracle | last post by:
Hi Folks I have written a code to send emails from oracle. Below is the code. I have registered this is Apps and scheduled the concurrent program to run every day. Now i want implement logic, such...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.