Re: Casting to VARCHAR(MAX)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

@QRY is being successfully loaded with a varchar(MAX) value and is longer 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:2009112021959lancy.mohan@xxxxxxxxxxxx
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



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

.

Relevant Pages

  • Re: BETWEEN in dynamic SQL
    ... I have a mess in printed SQL. ... > Instead of EXEC sp_executesql @sql ... >> Syntax error converting datetime from character string. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Parameterized Command versus Custom String
    ... I would use the EXEC String ... command object is trying to execute so you can test it against SQL ... SQL Profiler? ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Dynamic column specification in table update
    ... you need braces to EXEC a string. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Oracle NULL vs revisited
    ... (p_msg VARCHAR2) ... SQL> exec distinguish_emptiness; ... String with length 50 ...
    (comp.databases.oracle.server)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)