SQL Mail system stored procedure 1

[jhall156]

When I execute the following user stored procedure the message output (for the print) gives me the entire text of the email message body but the grid into which I select @msgtot is truncated (I assume at 255 chars). What am I doing wrong?

CREATE PROCEDURE usp_ProcessMail AS
DECLARE @status int, @message_id varchar(255)
DECLARE @originator varchar(255), @query varchar(255)
DECLARE @msgsubject varchar(255), @skip_bytes int, @msg_length int
DECLARE @msgtot varchar(8000)
SET @msgtot=' '
EXEC @status = master.dbo.xp_findnextmsg @msg_id = @message_id OUTPUT
SELECT @message_id
WHILE (1 = 1)
BEGIN
EXEC @status = master.dbo.xp_readmail @msg_id = @message_id,
@originator = @originator OUTPUT,
@subject = @msgsubject OUTPUT,
@message = @query OUTPUT,
@skip_bytes = @skip_bytes OUTPUT,
@msg_length = @msg_length OUTPUT
IF @status <> 0 BREAK
SELECT 'msg_id' = @message_id, 'msg_part' = @query
SELECT @msgtot = @msgtot + @query
IF @skip_bytes = @msg_length BREAK
END
PRINT @msgtot
SELECT @originator, @msgsubject,@msgtot
GO

 

[tlbroadbent]

I assume you are executing the SP in Query Analyzer. Select Tools | Options. Open the Results tab and set the maximum characters per column to a value higher than the default of 256.

If the size of the message is larger than 4096 characters, you'll need to set the textsize.

SELECT @@TEXTSIZE /* display the current textsize */
SET TEXTSIZE 8000 /* set the new textsize */
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[jhall156]

Thanks very much. I'll check out @@TEXTSIZE in BOL. I gather from your post that outside the auspices of Query Analyzer the default is 4096? At any rate thanks again for sharing the information!