xp_sendmail - 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)

xp_sendmail

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-28 : 08:03:34

Daniel writes "Hi!

I don't speak and spell english very well. So if somebody anderstand me, please read my question...

Let's go!!!

I need to send some mails form MS SQL Server where the parameter @message in master.dbo.xp_sendmail needs more thne 8000 bytes.

In BOL we have one example with sugests one possible solution, but i think its wrong.

/**

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

*/

In my test i have that:

USE master
GO

DECLARE @cmd varchar(200)

CREATE TABLE ##TAB_TESTE(ID INT IDENTITY(1,1), MESSAGE TEXT)

INSERT INTO ##TAB_TESTE(MESSAGE) VALUES('PUT HERE ANYTHING VALUE MORE THAN 8000')

SELECT ID, DATALENGTH(MESSAGE)[DATALENGTH], MESSAGE FROM ##TAB_TESTE

SET @cmd = 'DECLARE @val varbinary(16), @intX int SELECT @intX = (DATALENGTH(MESSAGE) - 1) FROM ##TAB_TESTE SELECT @val = textptr(MESSAGE) FROM ##TAB_TESTE READTEXT ##TAB_TESTE.MESSAGE @val 1 @intX'

EXEC master.dbo.xp_sendmail
@recipients = 'dskeff@mutua.com.br',
@query = @cmd,
@no_header= 'TRUE',
@width = 40000

DROP TABLE ##TAB_TESTE
GO

When i receve the message, i count just 8000 caracters.

If somebody have one suggestion for my problem...

thanks,
Daniel Skeff"

levsha
Starting Member

4 Posts

Posted - 2006-10-25 : 11:30:55

Dear Daniel!
It's been one year since you asked your question. Have you ever been able to resolve the problem? I have the same trouble now and would appreciate any help.
You could reply here or email me levsha_z@hotmail.com
Thank you!
Vita

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources