Cleaning mailbox - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cleaning mailbox

Author

Topic

Alexey V Matveev
Starting Member

1 Post

Posted - 2002-09-05 : 09:35:58

declare @hMessage varchar(255)
exec master..xp_findnextmsg @msg_id=@hMessage OUT
while @hMessage is not null
begin
exec master..xp_deletemail @hMessage
exec master..xp_findnextmsg @msg_id=@hMessage OUT
end
----------------------------------------------------
This code delete only one message (the first), why?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2002-09-09 : 14:23:04

Try This.

SET NOCOUNT ON

--Create temp table for all Message IDs
CREATE TABLE #hMessageIDs
(
hMessage varchar(255)
)
GO

declare @@hMessage varchar(255) ,
@@Status tinyint

exec master..xp_findnextmsg @msg_id=@@hMessage OUT

WHILE ( @@hMessage is not null )
BEGIN
--Populate Temp Table with all message IDs
INSERT INTO #hMessageIDs VALUES(@@hmessage)
EXEC master..xp_findnextmsg @msg_id=@@hMessage OUT
END

DECLARE @hMessageID varchar(255)

--Create cursor on Temp Message ID table
DECLARE message_cursor CURSOR FOR SELECT hMessage FROM #hMessageIDs

OPEN message_cursor

FETCH NEXT FROM message_cursor INTO @hMessageID

IF @@FETCH_STATUS <> 0

PRINT "No Messages Found"

WHILE @@FETCH_STATUS = 0

BEGIN

--Delete Message
EXEC master..xp_deletemail @hMessageID
FETCH NEXT FROM message_cursor INTO @hMessageID

END

CLOSE message_cursor
DEALLOCATE message_cursor

DROP TABLE #hMessageIDs
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources