I have come across a problem with VARCHAR(MAX)
lancymohan
I Have a stored procedure in which I am dynamically creating a query and executing it. At times the length of the query may exceed 8000 charaters. So I tried with VARCHAR(MAX) for declaring the variable to store the query string. But the length of string is not going beyond 8000. Following is a simple simulation where also I am getting this problem.
DECLARE @MESSAGE VARCHAR(MAX)
SET @MESSAGE = REPLICATE('1234567890',5000)
SELECT LEN(@MESSAGE)
Can anyone tell me if there is any other way out of it?
Thanks in advance
Lancy
EggHeadCafe - Software Developer Portal of Choice
POLL: What is the best book about c# 3.0 and LINQ?
http://www.eggheadcafe.com/tutorials/aspnet/e63caa12-4752-4283-a9b8-2e20e697383a/poll-what-is-the-best-bo.aspx
Tibor Karaszi
will be treated as VARCHAR(8000) instead of VARCHAR(max). Force the string
to a varchar(max) and you will see expected result:
DECLARE @MESSAGE VARCHAR(MAX)
SET @MESSAGE = REPLICATE(CAST('1234567890' AS varchar(max)),5000)
SELECT LEN(@MESSAGE)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Lancy Mohan" wrote in message news:200911184512...@gmail.com...
lancymohan
In my previous post it was just a simple simulation to reproduce the scenario.
But my real working scenario is a bit more complex. I am combining a few values dynamically and the final result is inserted into a column of a table. Like the following query.
CREATE TABLE #TEMP1 (MESSAGE VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
DECLARE @QRY VARCHAR(MAX)
SET @QRY =REPLICATE (CAST ( CHAR(39) + 'ABCDEF' + CHAR(39) + '+' AS VARCHAR(MAX)) ,2000)
SET @QRY = SUBSTRING(@QRY,1,LEN(@QRY)-1)
SET @QRY = CAST ('INSERT INTO #TEMP1 VALUES(' + @QRY +')' AS VARCHAR(MAX))
EXEC (@QRY)
SELECT LEN(MESSAGE) FROM #TEMP1
DROP TABLE #TEMP1
I think the reason should be during the casting of value to VARCHAR(MAX), SQL Server expects a single string entity to be more than 8000 charaters then only the actual casting is happening.
Is there any work around for this scenario or I have to rewrite the whole logic to something new?
Awaiting your reply
Regards
Lancy
Tibor Karaszi wrote:
REPLICATE returns the same type as you pass in, and the literal
18-Nov-09
REPLICATE returns the same type as you pass in, and the literal '1234567890'
will be treated as VARCHAR(8000) instead of VARCHAR(max). Force the string
to a varchar(max) and you will see expected result:
DECLARE @MESSAGE VARCHAR(MAX)
SET @MESSAGE = REPLICATE(CAST('1234567890' AS varchar(max)),5000)
SELECT LEN(@MESSAGE)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
Previous Posts In This Thread:
EggHeadCafe - Software Developer Portal of Choice
Dr. Dotnetsky's Cool .NET Tips and Tricks #17
http://www.eggheadcafe.com/tutorials/aspnet/f6b198d6-5475-47f6-ad65-add4829715f8/dr-dotnetskys-cool-net.aspx
Tom Cooper
than 8000 characters. But when you do the EXEC, the statement the EXEC
passes to SQL Server looks like
INSERT INTO #TEMP1 VALUES('ABCDEF'+'ABCDEF'+ ... 1998 more times)
Notice that you are now attempting to concatenate 2000 constants each 6
characters long. None of these are varchar(MAX), so the result won't be
varchar(MAX). So the string that gets put into #TEMP1 will be truncated to
8000 characters.
In theory, you could fix this by changing
SET @QRY =REPLICATE (CAST ( CHAR(39) + 'ABCDEF' + CHAR(39) + '+' AS
VARCHAR(MAX)) ,2000)
to
SET @QRY ='CAST(''ABCDEF'' AS VARCHAR(MAX)) +' +REPLICATE (CAST ( CHAR(39)
+ 'ABCDEF' + CHAR(39) + '+' AS VARCHAR(MAX)) ,1999)
then your exec statement would be passing the following to SQL Server
INSERT INTO #TEMP1 VALUES(CAST('ABCDEF' AS VARCHAR(MAX))+'ABCDEF'+ ... 1998
more times)
that will force the whole expression concatenating the strings to return
VARCHAR(MAX) and you would get the whole string inserted into the table.
EXCEPT, there is a maximum length/complexity before the optimizer will not
be able to handle the expression and will fail with because it runs out of
stack space and return error #8621. Concatinating these 2000 strings
together will give you an error on SQL 2005, although it works on SQL 2008.
However, there would be some length and complexity of the expression which
would cause SQL 2008 to return an error as well.
Furthermore, unless you are extremely careful what you put into this string
you are passing to EXEC, you may be exposing your server to a SQL injection
attack.
I would recommend you do rewrite the logic so that you avoid these problems
and use sp_executesql instead of EXEC. You may want to review the following
article if you are not familiar with sql injection attacks and/or
sp_executesql.
http://www.sommarskog.se/dynamic_sql.html
Tom
"Lancy Mohan" wrote in message news:200911202195...@gmail.com...
p bhavsar
declare @temp as varchar(max)
@temp = @temp + 'string'
@temp = @temp + 'string 1'
@temp = @temp + 'string 2'
@temp = @temp + 'string 3'
instead of
@temp = 'string' + 'string1' + 'string2'+ 'string3'+ 'string4'
> On Wednesday, November 18, 2009 4:51 AM Lancy Mohan wrote:
> Hi,
>
>
>
> I Have a stored procedure in which I am dynamically creating a query and executing it. At times the length of the query may exceed 8000 charaters. So I tried with VARCHAR(MAX) for declaring the variable to store the query string. But the length of string is not going beyond 8000. Following is a simple simulation where also I am getting this problem.
>
>
>
> DECLARE @MESSAGE VARCHAR(MAX)
>
> SET @MESSAGE = REPLICATE('1234567890',5000)
>
> SELECT LEN(@MESSAGE)
>
>
>
> Can anyone tell me if there is any other way out of it?
>
>
>
> Thanks in advance
>
> Lancy
>> On Wednesday, November 18, 2009 5:16 AM Tibor Karaszi wrote:
>> REPLICATE returns the same type as you pass in, and the literal '1234567890'
>> will be treated as VARCHAR(8000) instead of VARCHAR(max). Force the string
>> to a varchar(max) and you will see expected result:
>>
>> DECLARE @MESSAGE VARCHAR(MAX)
>> SET @MESSAGE = REPLICATE(CAST('1234567890' AS varchar(max)),5000)
>> SELECT LEN(@MESSAGE)
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>> On Friday, November 20, 2009 2:19 AM Lancy Mohan wrote:
>>> Thank you very much for your reply
>>>
>>>
>>>
>>> In my previous post it was just a simple simulation to reproduce the scenario.
>>>
>>>
>>>
>>> But my real working scenario is a bit more complex. I am combining a few values dynamically and the final result is inserted into a column of a table. Like the following query.
>>>
>>>
>>>
>>> CREATE TABLE #TEMP1 (MESSAGE VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
>>>
>>> DECLARE @QRY VARCHAR(MAX)
>>>
>>> SET @QRY =REPLICATE (CAST ( CHAR(39) + 'ABCDEF' + CHAR(39) + '+' AS VARCHAR(MAX)) ,2000)
>>>
>>> SET @QRY = SUBSTRING(@QRY,1,LEN(@QRY)-1)
>>>
>>> SET @QRY = CAST ('INSERT INTO #TEMP1 VALUES(' + @QRY +')' AS VARCHAR(MAX))
>>>
>>> EXEC (@QRY)
>>>
>>> SELECT LEN(MESSAGE) FROM #TEMP1
>>>
>>>
>>>
>>> DROP TABLE #TEMP1
>>>
>>>
>>>
>>>
>>>
>>> I think the reason should be during the casting of value to VARCHAR(MAX), SQL Server expects a single string entity to be more than 8000 charaters then only the actual casting is happening.
>>>
>>>
>>>
>>> Is there any work around for this scenario or I have to rewrite the whole logic to something new?
>>>
>>>
>>>
>>> Awaiting your reply
>>>
>>> Regards
>>>
>>> Lancy
>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>> ASP.NET 4.0 browser capabilities
>>> http://www.eggheadcafe.com/tutorials/aspnet/668c15e2-9fe5-4a7a-94ac-001af0bf4d1b/aspnet-40-browser-capabilities.aspx
p bhavsar
declare @temp as varchar(max)
@temp = @temp + 'string'
@temp = @temp + 'string 1'
@temp = @temp + 'string 2'
@temp = @temp + 'string 3'
instead of
@temp = 'string' + 'string1' + 'string2'+ 'string3'+ 'string4'
> On Wednesday, November 18, 2009 4:51 AM Lancy Mohan wrote:
> Hi,
>
>
>
> I Have a stored procedure in which I am dynamically creating a query and executing it. At times the length of the query may exceed 8000 charaters. So I tried with VARCHAR(MAX) for declaring the variable to store the query string. But the length of string is not going beyond 8000. Following is a simple simulation where also I am getting this problem.
>
>
>
> DECLARE @MESSAGE VARCHAR(MAX)
>
> SET @MESSAGE = REPLICATE('1234567890',5000)
>
> SELECT LEN(@MESSAGE)
>
>
>
> Can anyone tell me if there is any other way out of it?
>
>
>
> Thanks in advance
>
> Lancy
>> On Wednesday, November 18, 2009 5:16 AM Tibor Karaszi wrote:
>> REPLICATE returns the same type as you pass in, and the literal '1234567890'
>> will be treated as VARCHAR(8000) instead of VARCHAR(max). Force the string
>> to a varchar(max) and you will see expected result:
>>
>> DECLARE @MESSAGE VARCHAR(MAX)
>> SET @MESSAGE = REPLICATE(CAST('1234567890' AS varchar(max)),5000)
>> SELECT LEN(@MESSAGE)
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>> On Friday, November 20, 2009 2:19 AM Lancy Mohan wrote:
>>> Thank you very much for your reply
>>>
>>>
>>>
>>> In my previous post it was just a simple simulation to reproduce the scenario.
>>>
>>>
>>>
>>> But my real working scenario is a bit more complex. I am combining a few values dynamically and the final result is inserted into a column of a table. Like the following query.
>>>
>>>
>>>
>>> CREATE TABLE #TEMP1 (MESSAGE VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
>>>
>>> DECLARE @QRY VARCHAR(MAX)
>>>
>>> SET @QRY =REPLICATE (CAST ( CHAR(39) + 'ABCDEF' + CHAR(39) + '+' AS VARCHAR(MAX)) ,2000)
>>>
>>> SET @QRY = SUBSTRING(@QRY,1,LEN(@QRY)-1)
>>>
>>> SET @QRY = CAST ('INSERT INTO #TEMP1 VALUES(' + @QRY +')' AS VARCHAR(MAX))
>>>
>>> EXEC (@QRY)
>>>
>>> SELECT LEN(MESSAGE) FROM #TEMP1
>>>
>>>
>>>
>>> DROP TABLE #TEMP1
>>>
>>>
>>>
>>>
>>>
>>> I think the reason should be during the casting of value to VARCHAR(MAX), SQL Server expects a single string entity to be more than 8000 charaters then only the actual casting is happening.
>>>
>>>
>>>
>>> Is there any work around for this scenario or I have to rewrite the whole logic to something new?
>>>
>>>
>>>
>>> Awaiting your reply
>>>
>>> Regards
>>>
>>> Lancy
>>>> On Monday, November 01, 2010 5:33 AM p bhavsar wrote:
>>>> following will be work in my case
>>>>
>>>>
>>>>
>>>> declare @temp as varchar(max)
>>>>
>>>>
>>>>
>>>> @temp = @temp + 'string'
>>>>
>>>> @temp = @temp + 'string 1'
>>>>
>>>> @temp = @temp + 'string 2'
>>>>
>>>> @temp = @temp + 'string 3'
>>>>
>>>>
>>>>
>>>> instead of
>>>>
>>>>
>>>>
>>>> @temp = 'string' + 'string1' + 'string2'+ 'string3'+ 'string4'
>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>> Silverlight 3D Animated Topic Selector With Titled Menu Items
>>>> http://www.eggheadcafe.com/tutorials/aspnet/1add6e1c-6c70-4c2f-8b1a-5187961e5414/silverlight-3d-animated-topic-selector-with-titled-menu-items.aspx
p bhavsar
declare @temp as varchar(max)
@temp = @temp + 'string'
@temp = @temp + 'string 1'
@temp = @temp + 'string 2'
@temp = @temp + 'string 3'
instead of
@temp = 'string' + 'string1' + 'string2'+ 'string3'+ 'string4'
> On Wednesday, November 18, 2009 4:51 AM Lancy Mohan wrote:
> Hi,
>
>
>
> I Have a stored procedure in which I am dynamically creating a query and executing it. At times the length of the query may exceed 8000 charaters. So I tried with VARCHAR(MAX) for declaring the variable to store the query string. But the length of string is not going beyond 8000. Following is a simple simulation where also I am getting this problem.
>
>
>
> DECLARE @MESSAGE VARCHAR(MAX)
>
> SET @MESSAGE = REPLICATE('1234567890',5000)
>
> SELECT LEN(@MESSAGE)
>
>
>
> Can anyone tell me if there is any other way out of it?
>
>
>
> Thanks in advance
>
> Lancy
>> On Wednesday, November 18, 2009 5:16 AM Tibor Karaszi wrote:
>> REPLICATE returns the same type as you pass in, and the literal '1234567890'
>> will be treated as VARCHAR(8000) instead of VARCHAR(max). Force the string
>> to a varchar(max) and you will see expected result:
>>
>> DECLARE @MESSAGE VARCHAR(MAX)
>> SET @MESSAGE = REPLICATE(CAST('1234567890' AS varchar(max)),5000)
>> SELECT LEN(@MESSAGE)
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>> On Friday, November 20, 2009 2:19 AM Lancy Mohan wrote:
>>> Thank you very much for your reply
>>>
>>>
>>>
>>> In my previous post it was just a simple simulation to reproduce the scenario.
>>>
>>>
>>>
>>> But my real working scenario is a bit more complex. I am combining a few values dynamically and the final result is inserted into a column of a table. Like the following query.
>>>
>>>
>>>
>>> CREATE TABLE #TEMP1 (MESSAGE VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
>>>
>>> DECLARE @QRY VARCHAR(MAX)
>>>
>>> SET @QRY =REPLICATE (CAST ( CHAR(39) + 'ABCDEF' + CHAR(39) + '+' AS VARCHAR(MAX)) ,2000)
>>>
>>> SET @QRY = SUBSTRING(@QRY,1,LEN(@QRY)-1)
>>>
>>> SET @QRY = CAST ('INSERT INTO #TEMP1 VALUES(' + @QRY +')' AS VARCHAR(MAX))
>>>
>>> EXEC (@QRY)
>>>
>>> SELECT LEN(MESSAGE) FROM #TEMP1
>>>
>>>
>>>
>>> DROP TABLE #TEMP1
>>>
>>>
>>>
>>>
>>>
>>> I think the reason should be during the casting of value to VARCHAR(MAX), SQL Server expects a single string entity to be more than 8000 charaters then only the actual casting is happening.
>>>
>>>
>>>
>>> Is there any work around for this scenario or I have to rewrite the whole logic to something new?
>>>
>>>
>>>
>>> Awaiting your reply
>>>
>>> Regards
>>>
>>> Lancy
>>>> On Monday, November 01, 2010 5:33 AM p bhavsar wrote:
>>>> following will be work in my case
>>>>
>>>>
>>>>
>>>> declare @temp as varchar(max)
>>>>
>>>>
>>>>
>>>> @temp = @temp + 'string'
>>>>
>>>> @temp = @temp + 'string 1'
>>>>
>>>> @temp = @temp + 'string 2'
>>>>
>>>> @temp = @temp + 'string 3'
>>>>
>>>>
>>>>
>>>> instead of
>>>>
>>>>
>>>>
>>>> @temp = 'string' + 'string1' + 'string2'+ 'string3'+ 'string4'
>>>>> On Monday, November 01, 2010 5:35 AM p bhavsar wrote:
>>>>> following will be work in my case
>>>>>
>>>>>
>>>>>
>>>>> declare @temp as varchar(max)
>>>>>
>>>>>
>>>>>
>>>>> @temp = @temp + 'string'
>>>>>
>>>>> @temp = @temp + 'string 1'
>>>>>
>>>>> @temp = @temp + 'string 2'
>>>>>
>>>>> @temp = @temp + 'string 3'
>>>>>
>>>>>
>>>>>
>>>>> instead of
>>>>>
>>>>>
>>>>>
>>>>> @temp = 'string' + 'string1' + 'string2'+ 'string3'+ 'string4'
>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>> Review of Redgate ANTS Performance Profiler 6
>>>>> http://www.eggheadcafe.com/tutorials/aspnet/945b0f4a-55b9-4799-aaa3-bcbed4131446/review-of-redgate-ants-performance-profiler-6.aspx