Emailing long messages from SQL2000

Hello!
The problem is this.

I have many short messages in a table. I need to tigh them together in one long email message and email to the users.
But xp_sendmail is limited to 7,790.
How could I send longer messages? Or how could I devide the long message and send it in parts (i.e. separate consequtive emails)?

This doesn't work, it still cuts the messages off at around 7,790:

E. Send messages longer than 7,990 characters

This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @.query parameter.

CREATE TABLE ##texttab (c1 text)

INSERT ##texttab values ('Put your long message here.')

DECLARE @.cmd varchar(56)

SET @.cmd = 'SELECT c1 FROM ##texttab'

EXEC master.dbo.xp_sendmail 'robertk',

@.query = @.cmd, @.no_header= 'TRUE'

DROP TABLE ##texttab

you can output the query to a file

then have it as an attachment

|||

Thank you very much for the quick response!
I just tried using @.attach_results with xp_sendmail, it properly created a file and emailed it, but the text in the file was still cut off at the same point as before.
Is there any any method of outputting a query to a file?

Thanks again!

|||

hi,

im referrring to this not the other one

xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

Arguments

[@.attachments =] 'attachments [;...n]'

Is a semicolon-separated list of files to attach to the mail message.

hope it helps.

regards,

joey

|||Yes, the problem is I don't know how to output data from a stored procedure to a file...|||

use bcp.

call bcp from xp_cmdshell

|||

you could use a Job to move your email into a file and later, send your email.

is it work for you ?

Regards.

|||Did you try some code similar to the one in the sample? Can you check the actual datalength of the value you are trying to return in your query? Using above example, you can do "select datalength(c1) from ##texttab" to verify the length. The query functionality supports text/ntext data so you should be able to create email message >8000 characters.sql