CODEWITHSUNDEEP
sqlsql-server-2008flat-file
user2913493
user2913493

Reputation:

Text column not storing more than 8000 characters

I researched this and found that a text column in SQL Server can store a lot more than 8000 characters. But when I run the following insert in the text column, it only inserts 8000 characters: 

UPDATE  a
SET [File] = b.Header + CHAR(13) + CHAR(10) + d.Detail + c.Trailer + CHAR(13) + CHAR(10) + CHAR(26)
FROM Summary a
JOIN #Header b ON b.SummaryId = a.SummaryId
JOIN #Trailer c ON c.SummaryId = a.SummaryId
JOIN #Detail d ON d.SummaryId = a.SummaryId
WHERE   
    a.SummaryId = @SummaryId

I am trying to generate a fixed width flat file and every row should be 3900 characters long, and they are in the respective temp tables. But when I do the insert in the permanent table, the Trailer data gets truncated.

I am adding char(10) + char(13) to add carriage return and line feed and char(26) for end of file, and it seems like they are adding characters to the fixed width layout.

Upvotes: 2

Views: 2504

Answers (4)

Hart CO
Hart CO

Reputation: 34774

Your source fields aren't VARCHAR(MAX), so there is an 8000 character limit when concatenating them together, you can fix this by casting the first source field in the concat list as VARCHAR(MAX):

UPDATE  a
SET [File] = CAST(b.Header AS VARCHAR(MAX)) + CHAR(13) + CHAR(10) + d.Detail + c.Trailer + CHAR(13) + CHAR(10) + CHAR(26)
FROM Summary a
JOIN #Header b ON b.SummaryId = a.SummaryId
JOIN #Trailer c ON c.SummaryId = a.SummaryId
JOIN #Detail d ON d.SummaryId = a.SummaryId
WHERE a.SummaryId = @SummaryId

If you concat a thousand VARCHAR(25) fields together, the length of the resulting string would be 8000, as that's the limit of the VARCHAR() type when supplied a numeric length. VARCHAR(MAX) does not share this limit, but the concat list inherets the type of the first string supplied. It's an interesting behavior, but that's how it works.

Upvotes: 1

Guffa
Guffa

Reputation: 700252

The problem with your code is not the data type of the field that you store the value in, it's the type of the value that you put together to store in it.

The type of b.Header is not text but varchar, which is used as type for the whole expression. When the strings are concatenated, the result will be truncated to fit in a varchar value.

If you cast the first string to text, the whole expression gets that type, and can become longer than 8000 characters: 

SET [File] = cast(b.Header as text) + CHAR(13) + CHAR(10) + d.Detail + c.Trailer + CHAR(13) + CHAR(10) + CHAR(26)

Naturally you should transition into using the new type varchar(max) instead of text, but that is not the reason for your problem.

Upvotes: 2

marc_s
marc_s

Reputation: 754398

TEXT is deprecated - don't use it! Use VARCHAR(MAX) instead!

I think you need to explicitly cast all columns that you use in your UPDATE statement to VARCHAR(MAX) in order for this to work:

UPDATE  a
SET [File] = CAST(b.Header AS VARCHAR(MAX)) + CHAR(13) + CHAR(10) + 
             CAST(d.Detail AS VARCHAR(MAX)) + CAST(c.Trailer AS VARCHAR(MAX))) + CHAR(13) + CHAR(10) + CHAR(26)
FROM Summary a
JOIN #Header b ON b.SummaryId = a.SummaryId
JOIN #Trailer c ON c.SummaryId = a.SummaryId
JOIN #Detail d ON d.SummaryId = a.SummaryId
WHERE   
    a.SummaryId = @SummaryId

Upvotes: 0

Tripp Kinetics
Tripp Kinetics

Reputation: 5439

According to http://msdn.microsoft.com/en-us/library/ms187993.aspx TEXT fields are deprecated. Use VARCHAR(MAX) fields instead. They should support 2GB in text.

Upvotes: 3

Related Questions