Cursor help 2

[jcisco]

I have a big problem i'm hoping someone can help me.

I have a Store procedure that is called by a trigger. and it procedure runs this code.

declare md_cursor_1 CURSOR FOR SELECT PWMS..tblUsers.email, PWMS..tbl_EmailLookup.message FROM PWMS..tblUsers INNER JOIN PWMS..tbl_EmailLookup ON PWMS..tbl_EmailLookup.emailType = 'A02'

--open cursor and return all rows
open md_cursor_1

FETCH NEXT FROM md_cursor_1 into @vcTo, @vcBody

While(@@fetch_status <> -1)
begin
--SEND OUT EMAIL TO ALL USERS OF THAT TYPE
EXEC Master..sp_SQLSMTPMail @vcTo, @vcBody, 'Message From System', null,null,'',@vcFrom
--WRITE TRANSACTION TO EMAIL TABLE
insert into PWMS..tblUsers.UserID = PWMS..tbl_EmailLookup.userid WHERE PWMS..tbl_Alerts (
AlertType,
Message,
datetimeTag,
emailSentTo,
TerminalID,
comment,
PalletTagCode,
Quantity) values ('A02', @vcBody, getdate(), @vcTo, @TerminalID, 'An error occured. The amount pulled is greater than the amount found in that location', @PalletTagCode, @Quantity)
FETCH NEXT FROM md_cursor_1 into @vcTo, @vcBody
end
CLOSE md_cursor_1

Now my problem is when i run the procdure it works for one time. then i get this message when i try and run it again.
erver: Msg 16915, Level 16, State 1, Procedure sp_TransTrigger, Line 52
A cursor with the name 'md_cursor_1' already exists.
The statement has been terminated.

anyone have any ideas?

thanks. (if any more data is needed i can provide it.())


--------------

 

[jcisco]

think i just solved my own problem..
i needed to do something like this.

declare @mycur cursor

set @mycur = Cursor For select blah blah..



--------------

 

[Lekar]

Try adding Local to the scope of the cursor:

declare md_cursor_1 CURSOR LOCAL FOR SELECT

 

[jcisco]

what does the Local give you? just wondering.



--------------

 

[philhege]

jcisco,

I'd get rid of the cursor. Remember this: cursors bad; sets good:

SELECT PWMS..tblUsers.email, PWMS..tbl_EmailLookup.message, 0 as processed INTO #mail_recordset FROM PWMS..tblUsers INNER JOIN PWMS..tbl_EmailLookup ON PWMS..tbl_EmailLookup.emailType = 'A02'



WHILE (select count(*) from #mail_recordset where processed = 0)>0
begin
select top 1 @vcTO = email, @vcBody = message from
#mail_recordset where processed = 0
--SEND OUT EMAIL TO ALL USERS OF THAT TYPE
EXEC Master..sp_SQLSMTPMail @vcTo, @vcBody, 'Message From System', null,null,'',@vcFrom
--WRITE TRANSACTION TO EMAIL TABLE
insert into PWMS..tblUsers.UserID = PWMS..tbl_EmailLookup.userid WHERE PWMS..tbl_Alerts (
AlertType,
Message,
datetimeTag,
emailSentTo,
TerminalID,
comment,
PalletTagCode,
Quantity) values ('A02', @vcBody, getdate(), @vcTo, @TerminalID, 'An error occured. The amount pulled is greater than the amount found in that location', @PalletTagCode, @Quantity)
update #mail_recordset set processed = 1 where mail = @vcTo and message = @vcBody end




HTH,


Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[DFC4000]

You can get the message:

Server: Msg 16915, Level 16, State 1, Procedure sp_TransTrigger, Line 52
A cursor with the name 'md_cursor_1' already exists.
The statement has been terminated.

If you don't deallocate the cursor from memory. Especially if you execute twice within the same session.

Add in DEALLOCATE md_cursor_1 after your CLOSE statement.