May 12, 2011 at 9:00 am
Hello All,
SQL Server 2000 here...sigh.
I am building an html string and then using the string as the body of an email. The data being returned to my string is occasionally greater than 8000 characters resulting in truncation in my email
DECLARE @Body varchar(8000)
SET @Body = '<TR>'
SET @Body = @Body + '<TD>'+
etc...
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = ....etc
@Message = @body ,
etc
Any suggestions or tricks around this limitation?
May 12, 2011 at 9:19 am
Also. Can anyone help me purposefully build a string greater than 8000 characters?
I imagine I need the LEN function and some type of loop...
May 12, 2011 at 9:23 am
before SQL 2005 there is nothing larger than 8000. In 2005 there is Varchar(max) but that feature is not available in 2000. You can generate a string larger than 8000 but that may not be helpful since the answer was a negative to your first questions. If you still need to generate the string let me know.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 12, 2011 at 9:29 am
Yes if you could help me generate the string that would be appreciated.
I want to try something like this so I can determine if xp_smtp_sendmail will accept something greater than 8000.
So I will need to populate @body1 to its max.
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = ....etc
@Message = @body1 + @body2 ,
etc
May 12, 2011 at 9:46 am
just occurred to me that since you are in 2000 you would not be able to puch more than 8000 characters into the variable. My Bad!
Basically if you wanted to fill it to the max though you could do something like.
Declare @string varchar(8000)
set @string='X'
while len(@string)<8000
begin
set @string=@string+'X'
end
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 12, 2011 at 10:19 am
Oh thanks, that's simple, somehow I never used WHILE before.
This syntax is not allowed with xp_smtp_sendmail.
@message = @string + @string2
I'll seems like I will need to migrate this to a different server.
thanks
May 12, 2011 at 10:32 am
your other option would be to output to a file and then send the result as an attachment.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 12, 2011 at 3:25 pm
Hmmm yeah thats a good idea. I could include the embedded html and the file in every email. I could also check the length of the string and if it is 8000 change it to a message instructing the user to open the attachment.
May 12, 2011 at 7:44 pm
You may wish to use sp_makewebtask to build an HTML file from your query.
Then you may send that file as a message body.
Not sure if xp_smtp_sendmail can do this, never used this procedure.
We used sendemail.exe (http://caspian.dotconf.net/menu/Software/SendEmail/) on SQL2000 servers, it allows to embed a file into a message body.
_____________
Code for TallyGenerator
May 12, 2011 at 8:08 pm
Sergiy (5/12/2011)
You may wish to use sp_makewebtask to build an HTML file from your query.Then you may send that file as a message body.
Not sure if xp_smtp_sendmail can do this, never used this procedure.
We used sendemail.exe (http://caspian.dotconf.net/menu/Software/SendEmail/) on SQL2000 servers, it allows to embed a file into a message body.
I would not recommend using this approach - I believe that functionality has been deprecated and is no longer available in later versions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2011 at 4:07 am
Jeffrey Williams-493691 (5/12/2011)
I would not recommend using this approach - I believe that functionality has been deprecated and is no longer available in later versions.
True, sp_makewebtask is replaced with SSRS in 2005.
But the question was about a solution in specifically 2000.
And the solution is just right.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply