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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-01 : 13:40:42
|
AD writes "Hi All,
SQL SERVER 2000 - SQL Mail 2000.
The way xp_findnextmsg works has changed in SQL Server 2000. In SQL Server 7 xp_findnextmsg returned one handler for one mail in the Inbox everytime. This means that if one types in xp_findnextmsg 5 times in the Query Analyzer, it will return 5 different consequtive handlers (@msg_id)for 5 distinct mails in the Inbox. In SQL Server 2K, one single xp_findnextmsg returns ALL the different handlers (@msg_id) for all mails in the Inbox at once.
Due to this functionality change, procedures I used in SQL Server 7.0 don't work in 2000 as I cannot isolate specific messages in the Inbox due to the above mentioned change in functionality.
The following procedures work in Sql Server 7.0 and not in Sql Server 2000.
Eg:
Declare @hMessage varchar(255)
Declare @sender varchar(255)
Declare @subject varchar(255)
EXEC master.dbo.xp_findnextmsg @msg_id=@hMessage OUTPUT
WHILE @hMessage IS NOT NULL
BEGIN
EXECUTE master.dbo.xp_readmail @msg_id =@hMessage
, @originator = @sender OUTPUT
, @subject = @subject OUTPUT
-- Finds Mail based on Sender and Subject
IF (@sender = 'XYZ' AND @subject = 'MNO'
BEGIN
PRINT @sender
END
EXEC master.dbo.xp_findnextmsg @msg_id=@hMessage OUTPUT
END
The following code which is given as an example for demostrating xp_findnextmsg on this site by SQLGURU does not work in SQL Server 2K either as SQL Server 2000 cannot find NULL when it reaches the end of the messages in the Inbox.
EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
--put your own message handling code here
SELECT 'Message handle:' + @hMessage
EXEC xp_findnextmsg @msg_id=@hMessage OUT
END
Is there a work around ?
Thanks a lot
AD" |
|
|
|
|
|
xp_findnextmsg functionality in SQL Server 2000