April 26, 2016 at 8:16 pm
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
April 26, 2016 at 9:42 pm
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
Change is inevitable... Change for the better is not.
April 26, 2016 at 9:58 pm
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
April 26, 2016 at 9:59 pm
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
April 26, 2016 at 10:33 pm
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
April 26, 2016 at 10:40 pm
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.
April 26, 2016 at 10:53 pm
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
April 26, 2016 at 10:57 pm
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.
April 26, 2016 at 11:07 pm
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
April 26, 2016 at 11:31 pm
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
April 27, 2016 at 5:14 am
Jeffery Williams (4/26/2016)
In morning i can get to it. But count on the followingSet @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
April 27, 2016 at 7:49 am
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
April 27, 2016 at 9:54 am
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
April 27, 2016 at 10:20 am
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
April 27, 2016 at 11:30 am
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
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply