January 16, 2004 at 6:26 am
Hi there!
I give it a new try...
Could not even understand this post myself...
The problem more short is:
I have a @c_body(varachar(8000)) variable which i pass into the procedure which sends the mail.
Problem is that @c_body isnt large enought for my string.
The procedure takes an "text" variable but i cant declare an @c_body (text) locally which would be more than enought to keep my message...
So i thought i create a temporary table with a field declared as "text" and then store my message in that, but how do i select my data from the temporary table and pass it to my procedure?
I have some small problem with varchar(8000)...
I about to send an mail with a procedure i´ve created (which works perfectly fine) but my body text gets truncated, my variable @c_body (varchar(8000)) gets to long... The mailsending-procedure accepts an @body (text) so the problem lies in my own @c_body variable.
I builds the body dynamic by searching a set of tables and store the results in my variable @c_body.
Ive tried to add all text into a temporary table (create table #tmp (data text)) and then send this to my procedure which sends the mail.
Exec SP_SendMail @To='me@home.com', @FromName='rob@home.com', @Subject='Test', @Body = Select data From #tmp, @Importance=1, @HTMLFormat=1
How do i "Select" my temporary body into the procedure who takes a text-variable?
Best regards
Robert
January 16, 2004 at 9:36 am
I've basically had a similar problem where I'm parsing out xml that is stored in a text column - and the xml can be well over 8000 chars. I had create 2 seperate variables, one for the first 8000 chars and a second variable for the next 8000 chars. the sql looks something like this.... select @XML_STRING1 = substring(xmldata, 1, 8000) from processlogdetail
January 16, 2004 at 9:37 am
oops - got cut off.... the second variable would be..... select @XML_STRING2= substring(xmldata, 8001, 8000) from processlogdetail
January 16, 2004 at 10:06 am
First off, you can't simply select text data into a varchar variable (or field). Text data is in binary format. Selecting the text field will only return the amount of string data specified by the TEXTSIZE property of the server.
The default for TEXTSIZE is 4096 (4 KB) and the maximum is 2 GB, but then that's still too large for a varchar(8000).
You need to combine TEXTPTR, DATALENGTH, AND READTEXT into a process to read the data piece by piece. Then you can assign each piece to a variable or insert them into a temp table and then select each piece into a variable.
January 19, 2004 at 12:36 am
Efter reading your replies i guess i have to do something similar to:
Declare @c_tmpBody varchar(8000),
@c_body1 varchar(8000),
@c_body2 varchar(8000),
@c_body3 varchar(8000)
Create Table #tmp (data text) -- Create Table #tmp (data ntext)
Set @c_tmpBody = 'Damn long string... At least 4000 chars...'
Insert Into #tmp (data) Values (@c_tmpBody)
Set @c_tmpBody = 'Next long string... At least 6500 chars...'
Insert Into #tmp (data) Values (@c_tmpBody)
* Perheps i should do an "Update", or append if there is a nice way of doing that, instead of an "Insert", must be easier to split the string into one or more variables?
Exec SP_SendMail @To='me@home.com', @FromName='rob@home.com', @Subject='Test', @Body = @c_body1 + @c_body2 + @c_body3, @Importance=1, @HTMLFormat=1
Drop Table #tmp
//Robert
January 19, 2004 at 2:43 am
Ive solved the problem!!!
And here´s the code.
Thanx for your replies i wouldnt made it without them!
Declare @c_tmpBody varchar(8000),
@c_body1 varchar(8000),
@c_body2 varchar(8000),
@c_body3 varchar(8000),
@c_body4 varchar(8000)
Create Table #tmp (data text) -- Create Table #tmp (data ntext)
Set @c_tmpBody = 'Damn long string... At least 4000 chars...'
Select @aPointer = textptr(data) From #tmp
UpdateText #tmp.data @aPointer NULL 0 With log @c_tmpBody
Set @c_tmpBody = 'Next long string... At least 6500 chars...'
Select @aPointer = textptr(data) From #tmp
UpdateText #tmp.data @aPointer NULL 0 With log @c_tmpBody
Select
@c_body1 = substring(data, (0*8000)+1, 8000),
@c_body2 = substring(data, (1*8000)+1, 8000),
@c_body3 = substring(data, (2*8000)+1, 8000),
@c_body4 = substring(data, (3*8000)+1, 8000)
From #tmp
Exec ('
Exec master..Sp_SendMail
@To="me@home.com",
@FromName="Rob <rob@home.com>",
@Subject="Test",
@Body= ''' + @c_body1 + @c_body2 + @c_body3 + @c_body4 + ''',
@Importance=1,
@HTMLFormat=1
')
Drop Table #tmp
Best regards
Robert
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply