Varchar(Max) is not working in Exec
Asked Answered
K

2

6

I have a variable which has SQL string stored in it and am executing it through exec()

Declare @sql varchar(max)

set @sql = Concat('select...',@var,'..') -- large string 

exec (@sql)

but am getting error saying

Incorrect syntax near sometext

It is because the variable @sql cannot hold the entire string. So I fixed by splitting the string into two different variables and executed it

Declare @sql1 varchar(max),@sql2 varchar(max)

set @sql1 = 'select...' 
set @sql2 = ' from sometable join....'

exec (@sql1+@sql2)

I checked the data length of @sql1+ @sql2

Select Datalength(@sql1+ @sql2)

It returned 14677

Now question is why varchar(max) cannot store 14677 bytes of information? When the documents says it can store upto 2GB of data

Karalynn answered 2/11, 2016 at 10:2 Comment(12)
That is odd and unlikely. Did you select @sql to check if the string is really correct.Magnuson
You're probably hiding some sins where you've just written --long string, such as combining multiple short string literals using +. A varchar(max) variable can actually hold even more than 2GB, assuming SQL Server 2008 or later.Garlan
@juergend - yes, When I do Select @sql it is returning only the part of stringEnow
This is the sin I'm referring to: "If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur." - if you just have two string literals, neither will be a large value type in itself, and the variable you'll eventually store the result in is not part of the equation.Garlan
Possible duplicate of SQL NVARCHAR and VARCHAR LimitsBluepencil
@Garlan - But I did not use + for string concatenation inside my dynamic sql. I have used CONCAT functionEnow
Well, the documentation for CONCAT is hardly mysterious about this either - "If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type". It would be much easier for us to point to these type of errors, though, if we could see the code which contains the error, rather than being forced to guess about it.Garlan
@Garlan - Got it.. That is the problem. ThanksEnow
@Garlan - Code is so big that's why didn't post it. Now I understood the reason. It is not because of @sql variable and it is due to CONCAT function. Fixed it by adding set @sql = CONCAT(@sql,'select..')'Enow
but where you have used concat function in your question? @PrdpBilbe
@Damien_The_Unbeliever, Here user is not using Concat function knowBilbe
@Srinath - Am using concat function. Updated the question. I have mentioned it in my previous comment so he clarified itEnow
B
6

It is probably this you are running against:

DECLARE @part1 VARCHAR(5000)=REPLICATE('a',5000);
DECLARE @part2 VARCHAR(5000)=REPLICATE('a',5000);

SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(@part1+@part2);

The result is 5000,5000,8000

If one of the summands is a MAX type, you'll get the expected result

SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(CAST(@part1 AS VARCHAR(MAX))+@part2);

The result is 5000,5000,10000

This is often seen in connection with

  • string concatenation
  • usage of (older) functions returning VARCHAR(8000) as former max length
  • column definitions

UPDATE Same with CONCAT

SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(CONCAT(@part1,@part2));
SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(CONCAT(CAST(@part1 AS VARCHAR(MAX)),@part2));
Burford answered 2/11, 2016 at 10:14 Comment(0)
C
0

The approach of creating two varchar(max) data elements and combining them via "exec (@sql1+@sql2)" works and I appreciate the suggestion. I ran into the same issue and will be using this trick in the future.

For me, one varchar(max) data element got truncated when attempted to be executed. Split it into two varchar(max) data elements (no syntax change) and executed without issue.

Catchweight answered 28/11, 2018 at 15:38 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.