TEXT field cut of at 8000 characters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TEXT field cut of at 8000 characters

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-06-01 : 07:59:01

I have a table tblUploadRequest which has a field XMLInhoud. XMLInhoud is a TEXT field. I fill this TEXT field with the code provided under this post.

For some reason my Text field XMLInhoud is cut of a 8000 characters.

Can you see if the problem is in my code?


	UPDATE tblUploadRequest SET UploadStatus = 315, XMLBestand = @XMLBestandId,  XMLInhoud  =
		 '<Request status ="' + CAST (ISNULL (Requeststatus,'') AS VARCHAR) + '"' + CASE WHEN RequestNumber IS NULL THEN '' ELSE ' number="' + CAST(RequestNumber  AS varchar(10)) + '"' END + '>'
		+ '<RequestType number="' + CAST (RequestTypenumber AS VARCHAR) +'" />'  
		+ ISNULL ('<Description>' + [Description] +'</Description>' ,'')
		+ ISNULL ('<DateStart>' + CONVERT(VARCHAR,DateStart,120) + '</DateStart>','')
		+ ISNULL ('<DateEnd>' + CONVERT(VARCHAR,DateEnd,120) + '</DateEnd>','')
		+ ISNULL('<Resource number="' + CAST(Resourcenumber AS varchar(20))  + '" />','')
		+ ISNULL('<Account code="' + RTRIM(Accountcode)  + '"/>','')
		+ '<FreeFields>'
		+ '<FreeTexts>' 
		+ ISNULL ('<FreeText number="1">' + FreeTextnumber1 + '</FreeText>','')
		+ ISNULL ('<FreeText number="2">' + FreeTextnumber2 + '</FreeText>','')
		+ ISNULL ('<FreeText number="3">' + FreeTextnumber3 + '</FreeText>','')
		+ ISNULL ('<FreeText number="4">' + FreeTextnumber4 + '</FreeText>','')
		+ ISNULL ('<FreeText number="5">' + FreeTextnumber5 + '</FreeText>','')
		+ ISNULL ('<FreeText number="6">' + FreeTextnumber6 + '</FreeText>','')
		+ ISNULL ('<FreeText number="7">' + FreeTextnumber7 + '</FreeText>','')
		+ ISNULL ('<FreeText number="8">' + FreeTextnumber8 + '</FreeText>','')
		+ ISNULL ('<FreeText number="9">' + FreeTextnumber9 + '</FreeText>','')
		+ ISNULL ('<FreeText number="10">' + FreeTextnumber10 + '</FreeText>','')
		+ ISNULL ('<FreeText number="11">' + FreeTextnumber11 + '</FreeText>','')
		+ ISNULL ('<FreeText number="12">' + FreeTextnumber12 + '</FreeText>','')
		+ ISNULL ('<FreeText number="13">' + FreeTextnumber13 + '</FreeText>','')
		+ ISNULL ('<FreeText number="14">' + FreeTextnumber14 + '</FreeText>','')
		+ ISNULL ('<FreeText number="15">' + FreeTextnumber15 + '</FreeText>','')
		+ ISNULL ('<FreeText number="16">' + FreeTextnumber16 + '</FreeText>','')
		+ ISNULL ('<FreeText number="17">' + FreeTextnumber17 + '</FreeText>','')
		+ ISNULL ('<FreeText number="18">' + FreeTextnumber18 + '</FreeText>','')
		+ ISNULL ('<FreeText number="19">' + FreeTextnumber19 + '</FreeText>','')
		+ ISNULL ('<FreeText number="20">' + FreeTextnumber20 + '</FreeText>','')
		+ '</FreeTexts>'
		+ '<FreeNumbers>' 
		+ ISNULL( '<FreeNumber number ="1">' + CAST (FreeNumbernumber1 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="2">' + CAST (FreeNumbernumber2 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="3">' + CAST (FreeNumbernumber3 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="4">' + CAST (FreeNumbernumber4 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="5">' + CAST (FreeNumbernumber5 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="6">' + CAST (FreeNumbernumber6 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="7">' + CAST (FreeNumbernumber7 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="8">' + CAST (FreeNumbernumber8 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="9">' + CAST (FreeNumbernumber9 AS VARCHAR)+ '</FreeNumber>','')
		+ ISNULL( '<FreeNumber number ="10">' + CAST (FreeNumbernumber10 AS VARCHAR)+ '</FreeNumber>','')
		+ '</FreeNumbers>' 
		+ '<FreeDates>'

		+ ISNULL('<FreeDate number = "1">' + CONVERT(VARCHAR,FreeDatenumber1,120)+ '</FreeDate>','')
		+ ISNULL('<FreeDate number = "2">' + CONVERT(VARCHAR,FreeDatenumber2,120)+ '</FreeDate>','')
		+ ISNULL('<FreeDate number = "3">' + CONVERT(VARCHAR,FreeDatenumber3,120)+ '</FreeDate>','')
		+ ISNULL('<FreeDate number = "4">' + CONVERT(VARCHAR,FreeDatenumber4,120)+ '</FreeDate>','')
		+ ISNULL('<FreeDate number = "5">' + CONVERT(VARCHAR,FreeDatenumber5,120)+ '</FreeDate>','')
		+ '</FreeDates>'
		+ '<FreeYesNos>'
		+ ISNULL( '<FreeYesNo number ="1">' + CAST (FreeYesNonumber1 AS VARCHAR)+ '</FreeYesNo>','')
		+ ISNULL( '<FreeYesNo number ="2">' + CAST (FreeYesNonumber2 AS VARCHAR)+ '</FreeYesNo>','')
		+ ISNULL( '<FreeYesNo number ="3">' + CAST (FreeYesNonumber3 AS VARCHAR)+ '</FreeYesNo>','')
		+ ISNULL( '<FreeYesNo number ="4">' + CAST (FreeYesNonumber4 AS VARCHAR)+ '</FreeYesNo>','')
		+ ISNULL( '<FreeYesNo number ="5">' + CAST (FreeYesNonumber5 AS VARCHAR)+ '</FreeYesNo>','')
		+ '</FreeYesNos>'
		+ '</FreeFields>'
		+ '<Workflow>'
		+ ISNULL ('<Note>' + CAST (WorkflowNote AS VARCHAR(8000)) + '</Note>','')
		+ '</Workflow>'
		+ ISNULL ('<Note>' + CAST (RequestNote AS VARCHAR(8000)) + '</Note>','')
		+ '</Request>'
	WHERE UploadStatus = 310

Henri
~~~~
It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float.
-Al Pacino

mjia
Starting Member

2 Posts

Posted - 2004-06-01 : 09:59:38

you can check the text size by 'select @@TEXTSIZE' and see if it set to 8000, if it is you can reset it by
'SET TEXTSIZE <bigger number>'

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-06-01 : 10:17:57

Thx for the suggestion, but the textsize = 64512.
The last two line (WorklowNote and RequestNotes) are TEXT fields. I have to cast them to use them in a query (else I get the error that TEXT is not allowed in this operation).

If I do INSERT INTO tblUploadRequest (XMLInhoud) VALUES ( ' a very long text, a lot more than 8000 characters...')
than more than 8000 characters are inserted.

Henri
~~~~
It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float.
-Al Pacino

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-01 : 10:25:51

The output of the whole concatenation expression is probably going to be limited to varchar(8000), as it is the largest datatype used in the expression.

Can you split the update into two? Maybe you can do all the "Free----" columns in one go, and then append the other columns in a second update statement.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources