strange problem with varchar(8000) getting cut at 4000 characters

Question

strange problem with varchar(8000) getting cut at 4000 characters

Jeffery Williams

SSCertifiable

Points: 6441
More actions
April 26, 2016 at 8:16 pm

#313538

Ok I am on a project that has lots of dynamic sql. So I have a case where I have statements that could get close to that 8000 limit. I have something strange happening. I get my statement built in a variable and when I store it I am only getting the first 4000 characters; please help.
Now little background. The data is stored in two variables that are now 4000 in length but they were 8k, makes no difference. Then I set them to a third variable (don't need to do this way, have tried just one variable same results. So it is a dynamic insert / and select with joins.
at that end I say update this table... set XXXX = @ThisVariable.
Only get the first 4k characters????
Now I print the first two to screen during processing and they look great. Then I join them together
SET @ThisVariable = @SQL1 + ' ' + @SQL2
then print @ThisVariable
and guess what it looks great as well. BUT When I update the table / field with contents of @ThisVariable it is chopped off at 4k characters?
The field I am updating has data in it is it is Data + @ThisVariable to append it.
IF I take the new data (@ThisVariable) And put it in a new field I made in said table THEN Update Field1 = Field1 + Field2 it works!!!! WIthout shortending.
Problem is I can't do that because there may be multiple cycles to this...
Anyway trying not to labor over to many details as I am sure this is something simple and silly.
I print the variable PRINT cast(@ThisVariable as varchar(8000)) and it prints fine on screen. I update a table column value with it and it is shortened.
Any ideas here? Was thinking something with UNICODE but can't figure that out either. Under a real timeline on this and already exceeded that trying to fix this darn thing. If someone has the answer that would be great and I will use that and investigate why I had to go your recommended route later.
This really makes no sense at all from my perspective;p never ran into this one before.
Thanks in advance community really appreciate your help.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 15 posts - 1 through 15 (of 50 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Jeffery Williams (4/26/2016)
I print the variable PRINT cast(@ThisVariable as varchar(8000)) and it prints fine on screen. I update a table column value with it and it is shortened.

How are you checking the content of the table column? Visually through SSMS? If so, what is the return column width set to under {Tools}{Options}? And what do you get back when you run the following?

SELECT LEN(ColumnInQuestion)

FROM dbo.YourTable

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

Get back 4k. Sorry, yes tried that. Destination physical field is varchar 8000. Actual expected field len about 4200

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 3

Checking by doing len like you suggested and simply copy paste in SSMS. Either way same result

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Can you post the full UPDATE statement?

Conversion could be caused by anything:

CASE, ISNULL, concatenation with NVARCHAR, etc.

Actually, inspect the execution plan - you may find where implicit conversion is happening.

_____________
Code for TallyGenerator

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 5

sounds like something with the NVARCHAR datatype somewhere along the way it might be converting it. you've mentioned it was VARCHAR(8000) so I can only assume somewhere hidden.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 6

No nvarchar anywhere for sure. Hardly ever use, like never. And aware cuts length in half. Paste code in morning. Need permission as I'm a contractor. But it is really straight forward update

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 7

Jeffery Williams (4/26/2016)
No nvarchar anywhere for sure. Hardly ever use, like never. And aware cuts length in half. Paste code in morning. Need permission as I'm a contractor. But it is really straight forward update

can you post the update / table def... if its pretty easy just scramble the data. we can try to replicate it.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 8

In morning i can get to it. But count on the following

Set @preparedStatement = @preparedStatement + ' ' + @sql1 + ' ' + @sql2

Then

Update xxxtable

Set preparedStatement = @preparedStatement

Where xxxxx

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Shekhu Right there with Babe Points: 780 More actions · Answer 9

Can you try below steps

1. Ensure that the 3 variables are declared as below

DECLARE @SQL1 Varchar(4000)

DECLARE @SQL2 Varchar(4000)

DECLARE @ThisVaraible Varchar(8000)

2. After assignment of @sql1 and @sql2 you can write your update statement for updating the column (lets say tbCol1) as below

UPDATE tb

SET tbCol1=tbCol1+@ThisVaraible

where <>

3. Also check that while assignment to @sql1 and @sql2 are you appending it with N'

something like this

@sql1=N'hello world'

If yes then you need to remove that suffix

If this does not work then send

- the datatypes you are using for all of your 3 varaibles

- the length and datatype of column you are updating

- Sample value of how you are assigning the values to variables

- Code of how you are adding these 2 variables and how you are updating the final column of your table

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Jeffery Williams (4/26/2016)
In morning i can get to it. But count on the following
Set @preparedStatement = @preparedStatement + ' ' + @sql1 + ' ' + @sql2
Then
Update xxxtable
Set preparedStatement = @preparedStatement
Where xxxxx

Set @preparedStatement = @preparedStatement + ' ' + @sql1 + ' ' + @sql2

PRINT LEN (@preparedStatement)

Update xxxtable

Set preparedStatement = @preparedStatement

Where xxxxx

That's fot the beginning.

_____________
Code for TallyGenerator

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Maybe this?

UPDATE ... SET field = @string1 + cast('' as varchar(8000)) + @string2

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 12

Hi guys.

Here is how the variables are being DECLARED * Denotes the variables in question here

,@PreparedStatementIDas int

,@PreparedStatement as varchar(8000) *

,@ETLMappingIDAS int

,@SQL1as varchar(4000) *

,@SQL2as varchar(4000) *

Here is how they are being set:

SET @SQL1 = ' JOIN [' + @SourceServerName + '].[' + @SourceDBName + '].['

+ @SourceSchemaName + '].[' + @PKTableName

+ '] as ' + @Alias + 'Table on ' + @Alias + 'Table.' + @PKColumnName + ' = st.' + @FKColumnName

END

SET @SQL2 = ' JOIN [ETL].[dbo].[ETLCorrelation] ' + @Alias + ' on ' + @Alias + '.SourceGUIDVal = st.' + @FKColumnName

+ ' AND ' + @Alias + '.SourceTable = ''' + @PKTableName + '''' + ' AND ' + @Alias + '.BatchID = ' + cast(@BatchID as varchar(10))

PRINT '@SQL1: ' + cast(@SQL1 as varchar(4000))

PRINT '@SQL2: ' + cast(@SQL2 as varchar(4000))

The above print statements display exactly what I expect to see, no problem.

Next I am doing this:

SET @PreparedStatement = @PreparedStatement + ' ' + cast(@SQL1 as varchar(4000)) + ' ' + cast(@SQL2 as varchar(4000))

Then I print it to screen:

PRINT '@PreparedStatement: ' + cast(@PreparedStatement as varchar(8000))

AND it looks perfectly fine, the entire string is there.

THEN I do this:

BEGIN

UPDATE ETLPreparedStatement

SET PreparedStatement = cast(@PreparedStatement as varchar(8000)), JoinProcessed = 1

WHERE PreparedStatementID = @PreparedStatementID

END

And I only get the first 4000 characters.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 13

Sample output from Print Statements:

Profile for ETLMappingID: 597

JOIN SQL1 Len: 141

JOIN SQL2 Len: 151

PreparedStatement Len: 3862

JOIN STATEMENTS CREATED IN ETLProcessTableFieldsDynamic_JOIN

@SQL1: JOIN [addb20].[Source_MergeAMD1_WithData].[dbo].[ImportExportBatch] as FK1Table on FK1Table.ImportExportBatchGUID = st.ImportExportBatchGUID

@SQL2: JOIN [ETL].[dbo].[ETLCorrelation] FK1 on FK1.SourceGUIDVal = st.ImportExportBatchGUID AND FK1.SourceTable = 'ImportExportBatch' AND FK1.BatchID = 4083

FINAL PreparedStatement Len: 4157

FINAL Join Statement Created in ETLProcessTableFieldsDynamic_JOIN

@PreparedStatement: INSERT INTO [addb20].[Target_MergeAMD2_WithData].[dbo].[ImportExportBatchData] ([DataColumn1], [DataColumn10], [DataColumn100], [DataColumn11], [DataColumn12], [DataColumn13], [DataColumn14], [DataColumn15], [DataColumn16], [DataColumn17], [DataColumn18], [DataColumn19], [DataColumn2], [DataColumn20], [DataColumn21], [DataColumn22], [DataColumn23], [DataColumn24], [DataColumn25], [DataColumn26], [DataColumn27], [DataColumn28], [DataColumn29], [DataColumn3], [DataColumn30], [DataColumn31], [DataColumn32], [DataColumn33], [DataColumn34], [DataColumn35], [DataColumn36], [DataColumn37], [DataColumn38], [DataColumn39], [DataColumn4], [DataColumn40], [DataColumn41], [DataColumn42], [DataColumn43], [DataColumn44], [DataColumn45], [DataColumn46], [DataColumn47], [DataColumn48], [DataColumn49], [DataColumn5], [DataColumn50], [DataColumn51], [DataColumn52], [DataColumn53], [DataColumn54], [DataColumn55], [DataColumn56], [DataColumn57], [DataColumn58], [DataColumn59], [DataColumn6], [DataColumn60], [DataColumn61], [DataColumn62], [DataColumn63], [DataColumn64], [DataColumn65], [DataColumn66], [DataColumn67], [DataColumn68], [DataColumn69], [DataColumn7], [DataColumn70], [DataColumn71], [DataColumn72], [DataColumn73], [DataColumn74], [DataColumn75], [DataColumn76], [DataColumn77], [DataColumn78], [DataColumn79], [DataColumn8], [DataColumn80], [DataColumn81], [DataColumn82], [DataColumn83], [DataColumn84], [DataColumn85], [DataColumn86], [DataColumn87], [DataColumn88], [DataColumn89], [DataColumn9], [DataColumn90], [DataColumn91], [DataColumn92], [DataColumn93], [DataColumn94], [DataColumn95], [DataColumn96], [DataColumn97], [DataColumn98], [DataColumn99], [ImportExportBatchGUID] ,ImportIdentity, GUIDIdentity) SELECT st.[DataColumn1], st.[DataColumn10], st.[DataColumn100], st.[DataColumn11], st.[DataColumn12], st.[DataColumn13], st.[DataColumn14], st.[DataColumn15], st.[DataColumn16], st.[DataColumn17], st.[DataColumn18], st.[DataColumn19], st.[DataColumn2], st.[DataColumn20], st.[DataColumn21], st.[DataColumn22], st.[DataColumn23], st.[DataColumn24], st.[DataColumn25], st.[DataColumn26], st.[DataColumn27], st.[DataColumn28], st.[DataColumn29], st.[DataColumn3], st.[DataColumn30], st.[DataColumn31], st.[DataColumn32], st.[DataColumn33], st.[DataColumn34], st.[DataColumn35], st.[DataColumn36], st.[DataColumn37], st.[DataColumn38], st.[DataColumn39], st.[DataColumn4], st.[DataColumn40], st.[DataColumn41], st.[DataColumn42], st.[DataColumn43], st.[DataColumn44], st.[DataColumn45], st.[DataColumn46], st.[DataColumn47], st.[DataColumn48], st.[DataColumn49], st.[DataColumn5], st.[DataColumn50], st.[DataColumn51], st.[DataColumn52], st.[DataColumn53], st.[DataColumn54], st.[DataColumn55], st.[DataColumn56], st.[DataColumn57], st.[DataColumn58], st.[DataColumn59], st.[DataColumn6], st.[DataColumn60], st.[DataColumn61], st.[DataColumn62], st.[DataColumn63], st.[DataColumn64], st.[DataColumn65], st.[DataColumn66], st.[DataColumn67], st.[DataColumn68], st.[DataColumn69], st.[DataColumn7], st.[DataColumn70], st.[DataColumn71], st.[DataColumn72], st.[DataColumn73], st.[DataColumn74], st.[DataColumn75], st.[DataColumn76], st.[DataColumn77], st.[DataColumn78], st.[DataColumn79], st.[DataColumn8], st.[DataColumn80], st.[DataColumn81], st.[DataColumn82], st.[DataColumn83], st.[DataColumn84], st.[DataColumn85], st.[DataColumn86], st.[DataColumn87], st.[DataColumn88], st.[DataColumn89], st.[DataColumn9], st.[DataColumn90], st.[DataColumn91], st.[DataColumn92], st.[DataColumn93], st.[DataColumn94], st.[DataColumn95], st.[DataColumn96], st.[DataColumn97], st.[DataColumn98], st.[DataColumn99], FK1.DestinationGUIDVal, st.ImportExportBatchDataID, '0000' FROM [addb20].[Source_MergeAMD1_WithData].[dbo].[ImportExportBatchData] st JOIN [addb20].[Source_MergeAMD1_WithData].[dbo].[ImportExportBatch] as FK1Table on FK1Table.ImportExportBatchGUID = st.ImportExportBatchGUID JOIN [ETL].[dbo].[ETLCorrelation] FK1 on FK1.SourceGUIDVal = st.ImportExportBatchGUID AND FK1.SourceTable = 'ImportExportBatch' AND FK1.BatchID = 4083

Returning from ETLProcessTableFieldsDynamic_SELECT

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 14

The LEN when saved to table is 4k.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh