[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL Server - HTML in text column, data truncating - need work around

Hi,

I have inherited a system where emails have to be sent from a database table on an SQL 2000 server. The emails are in html format and therefore fairly long.

I am having to build the email text in sql, and then insert it into a table for sending at a later date. The problem is that I cannot get the data into the column (data type text) without it truncating. At first I thought the problem was that the max length had been reached for the row.

I have now tried a couple of work arounds such as inserting the data into a table on a linked SQL 2005 server, but still find that the data is truncated, even with less columns in the row.

I'm now wondering if the issue is the row length, or some other limit I'm hitting. Whatever way I go about it, I have to pass the data from a table or temporary table on the SQL 2000 server. Could this be where the data is being truncated.

I thought that you could have columns longer than 8060 bytes in SQL 2005 - am I right in this assumption?

Any advice appreciated.
0
Cognize
Asked:
Cognize
  • 6
  • 4
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
how did you recognise that the text is truncating ? there is a limit of 8060 bytes in sql server, but if you are using a tex/varchar(max) types , sql stores an address in the original table which is usually 16 bytes; the actual storage location will be on a different location
0
 
CognizeAuthor Commented:
So I have read. I know the data is truncating because when I select out of the table I have inserted into, a large portion of the text (html) is missing.

Am I right in thinking the field extention addressing only works in SQL 2005? I have even created a mirror version of the query that runs on the SQL 2005 database, selects the required variables from the SQL Server 2000 tables, and builds the text locally, and then inserts into the local table.

I still get the truncation. (Hope that all makes sense)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the sql server query tools can show upto 8092 characters per column at the maximum, but you can see the entire data in the front end .. or else try checking the length of the column using DATALENGTH

select textColumn, DATALENGTH (textColumn)
from yourTable
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CognizeAuthor Commented:
Interesting, the length of each is 8000 bytes.

The full html length is closer to 12000 bytes.
0
 
CognizeAuthor Commented:
I had used a PRINT of the data previously as another way of checking the same, just in case.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
did you by any chance inserted only a trimmed data ? try inserting using REPLICATE('a', 12000) and check the datalenght of that row
0
 
CognizeAuthor Commented:
Same with REPLICATE('a', 12000), but you've helped me work out what the issue is - it's not the column length, it's the string concatenaction that I'm doing before hand - the max string length is 8000 bytes, and thats my problems.

I've solved the problem on the SQL 2005 server now using a temporary table and an update statement with varchar(max) fields.

I now need to go back to the SQL 2000 database and repeat the same. The issue that I'm having now is that I don't know how to concatenate text datatypes. I see the Updatetext funcation seems to be the solution... bu the syntax is wierd....
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
hmm I got it ... the varchar column can store a maximum of 8000 characters... but text / varchar(max) can store upto 2GB.
try updating the text column using the update statement , in case it throws an error try this method

http://sequelserver.blogspot.com/2006/03/update-text1-text2-text3.html
0
 
CognizeAuthor Commented:
Your correct. I could only use text as on SQL Server 2000. This meant I had to use UPDATE, in conjunction with UPDATETEXT, with TXTPTR to get a pointer to the column, and DATALENGTH to get the index at which to insert. All of this modified a value in a temporary table.

I think that was the biggest fight I've had with SQL Server so far (I'm not a DB admin)!

In the end the solution was repetition of code such as:

UPDATETEXT #TempTable.textString @ptrval @startPos 0 @idString

SELECT @startPos =  DATALENGTH(textString) FROM #TempTable

UPDATETEXT #TempTable.textString @ptrval @startPos 0 'Long text to append here'


Thanks for all your help. I'm going to partially mark this as the solution for anyone using this post, but obviously award most of the points to you for your much appreciated help. Thanks.

0
 
CognizeAuthor Commented:
Thanks (see next post for code that worked for me in the end)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now