November 23, 2010 at 12:49 pm
My issue is that I have dynamic sql that concatenates various values and fields from our database. In the sql, all the concerned variables are declared as nVarchar(max) at this time. We started out with just one variable being nVarchar(max) and have continued to all of them. However, I have found that even without having one string with no variables being selected into a variable that is nVarchar(max) it is truncated at 4000. Basically building an html page on the fly. What I have set in here is just a simple declaration of an nVarchar(max) variable and a select to it of a string that is more than 4000 characters. It gets truncated at 4000. I am running on SQL Server 2008 R2 at home and SQL Server 2005 sp3 at the office. I am pasting the code white spaces and all since I have no control over what will be "fed" into this variable. Can someone help me? I am sure it must be something quite simple, but.. Why is this being truncated even without the additional values and variables?
And thank you for any help!
DECLARE @EmailMessage1 nvarchar(max)
select @EmailMessage1 = N'
<!DOCTYPE HTML PUBLIC " -//W3C//DTD HTML 4.01 Transitional//EN" " http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Thank you for your interest in ' + N'TitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopicTitleTopic' + 'solutions
</title>
</head>
<body style="overflow-x: auto; overflow-y: auto;">
<style type="text/css">
.style2 {
font-family: Arial, Helvetica, sans-serif;
font-weight: bold;
font-size: 13px;
color: #FFFFFF;
text-decoration: none;
overflow: auto;
}
.style3 {
font-family: Arial, Helvetica, sans-serif;
font-size: 13px;
color: #333333;
overflow: auto;
}
</style>
<table align="center" border="0" cellpadding="0" cellspacing="0" width="585">
<tr>
<td width="585">
<img alt="XXXXXXX XXX XXX" height="80" src="' + N'EmailTopImageEmailTopImageEmailTopImageEmailTopImageEmailTopImageEmailTopImageEmailTopImageEmailTopImageEmailTopImageEmailTopImageEmailTopImage' + '" style="DISPLAY: block" width="585">
</td>
</tr>
<tr>
<td bgcolor="#bd3b1d" height="79" valign="top">
<table border="0" cellpadding="0" cellspacing="0" width="585">
<tr>
<td>
<img alt="XXXXXXX XXX XXX" border="0" height="17" src="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.gif" style="DISPLAY: block" width="7"></td>
<td>
<img alt="XXXXXXX XXX XXX" border="0" height="17" src="bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb.gif" style="DISPLAY: block" width="571"></td>
<td>
<img alt="XXXXXXX XXX XXX" border="0" height="17" src="ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc.gif" style="DISPLAY: block" width="7"></td>
</tr>
<tr>
<td>
<img alt="XXXXXXX XXX XXX" border="0" height="62" src="ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd.gif" style="DISPLAY: block" width="7"></td>
<td background="eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee.jpg" height="62">
<table align="center" border="0" cellpadding="0" cellspacing="0" width="540">
<tr>
<td align="right" style="FONT-WEIGHT: bold; FONT-SIZE: 30px; COLOR: #ffffff; FONT-FAMILY: Arial, Helvetica, sans-serif">
1234567 12345678 0123456789</td>
</tr>
<tr>
<td align="right" style="FONT-SIZE: 16px; COLOR: #ffffff; FONT-FAMILY: Arial, Helvetica, sans-serif">
with XXX 1234567890 <strong>BLAHBLAHBLAHBLAH12</strong></td>
</tr>
</table>
</td>
<td>
<img alt="1234567 and 123" border="0" height="62" src="fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff.gif" style="DISPLAY: block" width="7"></td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="585">
<tr>
<td bgcolor="#b93c28" rowspan="3" valign="bottom">
<img alt="" border="0" height="132" src="ggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg.jpg" style="DISPLAY: block" width="364"></td>
<td bgcolor="#b93c28" style="PADDING-RIGHT: 5px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px">
<img alt="" height="10" src="hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh.gif" style="DISPLAY: block"></td>
<td rowspan="2" width="6">
<img alt="" border="0" src="iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii.gif" style="DISPLAY: block" width="6"></td>
<td bgcolor="#c3c3c3" rowspan="2">
<img alt="" src="jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj.gif" style="DISPLAY: block" width="1"></td>
</tr>
<tr>
<td bgcolor="#b93c28" style="PADDING-RIGHT: 5px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px">
<table border="0" cellpadding="0" cellspacing="0" width="199">
<tr>
<td colspan="4">
<img alt="" height="11" src="kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk.jpg" style="DISPLAY: block" width="199"></td>
</tr>
<tr>
<td bgcolor="#ffffff" width="2">
<img alt="" border="0" src="lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll.gif" style="DISPLAY: block" width="2"></td>
<td bgcolor="#ff9900" style="PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px" width="135">
<a class="style2"'
Select @EmailMessage1
November 23, 2010 at 12:55 pm
Are you selecting it, or executing it somewhere, or what?
SQL Server Management Studio defaults to only presenting a small amount of text that you select all at once. If you want to increase it, it's in Tools - Options - Query Results. It'll display more, up to 65,535 characters, if you tell it to.
Is that what you're running into?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2010 at 1:42 pm
Thanks for the quick reply. Yes, this is a script that is running through SSMS, however, the variable will truncate at 4000 and THEN it continues to pick up the rest of the data, i.e., @EmailMessage1 = 'blahblahblah for 6000 characters' + @Variable2, it will truncate @EmailMessage1 at 4000 characters and THEN continue along with @Variable2 as expected .. it seems like the first variable @EmailMessage1, declared at nVarchar(max) truncates, however, concatenating additional variables (also nVarchar(max)) it continues along just fine, it just truncates after the very first 4000 characters are picked up, truncates, and then at the next variable it continues on as expected. Is that understandable? I can try to put together more of the example if that would help. Thank you!
November 23, 2010 at 1:47 pm
What source are you using for the values for your variables?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2010 at 2:24 pm
Hello again 🙂 Some of them are hardcoded strings and others are coming from the database. However, I think I have found at least a temporary fix. I am testing it now. I have a new variable also nVarchar(max) and I have split the beginning of the string that was being truncated, the first part just short of the 4000 characters is going into the 1st variable and the rest of it is going into the 2nd variable, I am then concatenating those 2 variables, and updating the table and it is being checked now. However, I still find that a little weird. And would still love to know how I could do this without the second variable. Basically, set @EmailMessage1 = 'first 3500 or so chars' set @EmailMessage2 = 'the rest of the 10,000 character string' set @EmailMessage1 = @EmailMessage1 + @EmailMessage2, update table. I love SQL
November 23, 2010 at 3:30 pm
The problem is that the base string value must be assigned a datatype in order to be processed before it can be assigned to your variable. Even though that variable is nvarchar(max), the string value itself will default to nvarchar(4000).
If that value is being assigned concatenated with an nvarchar(max) value, then it will be upconverted itself to nvarchar(max) in order to make the assignment possible to the same datatype.
The trick here is to concat your big string value to an nvarchar(max) string value before assigning it to the variable, causing the query processor to coerce it to an nvarchar(max):
DECLARE @EmailMessage1 nvarchar(max)
select @EmailMessage1 = convert(nvarchar(max), N'') + N' ...lots of text here...'
Select len(@EmailMessage1)
Eddie Wuerch
MCM: SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply