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 2005 Forums
 SQL Server Administration (2005)
 Problem in Reading Mails - Database Mail

Author  Topic 

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-11-13 : 11:52:06
Hi

I have configue all and i am able to send mails. when i try to read mail i get the following error. "Msg 17985, Level 16, State 1, Line 0
xp_findnextmsg: Procedure expects parameter @user, which was not supplied."

I have tried all way to check

EXECUTE dbo.sysmail_help_status_sp ; -- this returns "STARTED"

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ; --this return 1 which is working...

please fidn the code below, what i am missing...



-- Set up variables.

DECLARE @status INT,
@message_part VARCHAR(255),
@msg_id VARCHAR(255),
@message_length INT,
@skip_bytes INT,
@message VARCHAR(MAX) ;

-- Find the next message
EXEC master.dbo.xp_findnextmsg @msg_id=@msg_id OUT, @unread_only='true'

--EXEC @status = dbo.xp_findnextmsg @msg_id = @msg_id OUTPUT ;


-- If xp_findnextmsg completed successfully and
-- there is a message in the inbox, read the message.

IF (@status = 0 AND @msg_id IS NOT NULL)
BEGIN
WHILE (1=1)
BEGIN
EXEC @status = dbo.xp_readmail
@msg_id = @msg_id,
@msg_length = @message_length OUTPUT,
@skip_bytes = @skip_bytes OUTPUT,
@message = @message_part OUTPUT ;


IF @status <> 0 BREAK ;

SET @message = ISNULL(@message, '') + @message_part ;

PRINT @message_length ;
PRINT @skip_bytes;

IF @message_length = @skip_bytes BREAK ;

END ;

IF @status = 0
BEGIN
SELECT 'Message ID' = @msg_id, 'Message Body' = @message ;
END ;
ELSE
SELECT 'Could not read message.' ;
END;

GO




--exec usp_Test


========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
   

- Advertisement -