varchar length

Question

varchar length

bilko73

Old Hand

Points: 377
More actions
April 11, 2003 at 8:40 am

#79835

I am trying to send an email in a stored procedure using html format but the length of the html string is greater than the 8000 limit of the varchar type. Is there a way to pass a string larger than this. I haven't had any luck building it with a text datatype.
thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

You could save the string in multiple varchar (8000) variables, and then concatenate them when executing the xp_sendmail command.

bilko73 Old Hand Points: 377 More actions · Answer 2

concatenating does not work (not using xp_sendmail)

code is :

EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

this doesn't work:

EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body + @Body2

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 3

What error are you receiving? Check that the concatenation returns the result you expect. Use print to see if the body is right.

sarita Garg SSC Eights! Points: 824 More actions · Answer 4

Microsoft books online tells that var char length can be upto 8000 characters. Does this mean that if you have defined a field as var char length 600 it will allow 600 characters of data populated in that field in the table. Because its not happening in my case. I have defined field as var char with 600 as length but its storing only 255 characters even though I see on GUI side more than 300+ character string entered. Please help how do I resolve the issue so that I can store upto 1000 characters of data.

Thanks for your help.

Greg Larsen SSC-Insane Points: 20966 More actions · Answer 5

How are you displaying the results? If you are using QA then you might need to adjust the "maximum characters per column" on the results tab on the options menu, under tools.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 6

Sarita,

From QA go to Tools > Options > Results tab. More than likely you still have it set as DEFAULT 254 length. You can change that to be up to 8k.

bilko73,

What you may have to wind up doing is parsing the 8k pieces to text files (via VB) and then recompiling them with a merge and then sending via xp_sendmail.

I am sure there are other ways however, this is just off the top of my head.

Good Luck

AJ Ahrens

SQL DBA

Custom Billing AT&T Labs

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Rick Todd Hall of Fame Points: 3302 More actions · Answer 7

What about accepting the email text as a TEXT parameter, than parsing it into a series varchar's and referencing it as a pointer?

Rick Todd

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

No, you can not if you are using xp_sendmail.

the @Message parameter of xp_sendmail only accepts up to 8000 bites.

If you must use xp_sendmail, you have 2 options:

1. Attach an html page to the email using the @attachments parameter

2. Put the actual html page online and in the HTML code passed through to the @message parameter would be a frameset with the online URL for the page as the source value of the frame.

I don't use xp_sendmail, but I still implement option #2 in my HTML emails.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

shamshad SSC Veteran Points: 209 More actions · Answer 9

I got the same problem and afterall i got this as my own solution of that problem.

Create a temp table and put one column in it with varchar 8000, now put line by line with HTML table formating in that col by inserting new rows in that temp table, run bcp command and create the HTML formatted file, after that put that file as a body text in ur email options. If u need this by code, I will send u an example.

😛

Regards,

Shamshad Ali.

DavidGutierrez Grasshopper Points: 12 More actions · Answer 10

shamshad,

Please post you solution, your solution sounds intriguing.

Thanks,

Dave

shamshad SSC Veteran Points: 209 More actions · Answer 11

create proc shortHTMLemails

as

CREATE TABLE [dbo].[tblHTML] (field1 int IDENTITY (1, 1) NOT NULL ,txt varchar(8000) )

DBCC CHECKIDENT (tblHTML, RESEED, 1)

insert into tblHTML values('<html><head></head><body><table border=0 cellspacing=1 width=800><tr><td bgcolor=#DDDDFF><div align=center><table border=0 cellspacing=1 width=800>')

insert into tblHTML values('<tr>

<td width=20% nowrap bgcolor=#ABC0E4 align=left><font size=2 face=Verdana><b>Locations</b></font></td>

<td width=20% nowrap bgcolor=#ABC0E4 align=center><font size=2 face=Verdana><b>Total files received</b></font></td>

<td width=20% nowrap bgcolor=#ABC0E4 align=center><font size=2 face=Verdana><b>Completed Files</b></font></td>

<td width=20% nowrap bgcolor=#ABC0E4 align=center><font size=2 face=Verdana><b>Total Revenue </b></font></td>

<td width=20% nowrap bgcolor=#ABC0E4 align=center><font size=2 face=Verdana><b>Freely Negotiable</b></font></td>

</tr>')

insert into tblHTML values ('<tr>

<td width=20% nowrap bgcolor=#ABC0E4 align=left><font size=1 face=Verdana><b>Houston</b></font></td>

<tr>

<tr>

</tr>

</table>

</div>

</td>

</tr>

</table>

</center>

</div>

</td>

</tr>')

insert into tblHTML values('</table></div></td></tr></table><br><br></body></html>')

exec master..xp_cmdshell 'bcp "select txt from Final18March.dbo.tblHTML order by field1" queryout "c:\HTMLPage.html" -S"DAWN" -c -U"sa" -P"testtest" -t',No_Output

declare @rc int

exec @rc = master.dbo.xp_smtp_sendmail

@FROM = 'ForExecutives@dontreply.com',

@FROM_NAME = 'For Executives',

@TO = 'sali@rhdc.com.pk',

@subject = 'Daily Report' ,

@messagefile = N'c:\HTMLPage.html',

@type = 'text/html',

@server = 'sun.rhdc.com.pk'

select RC = @rc

drop table tblHTML

for mail component http://sqldev.net/xp/xpsmtp.htm