Casting to VARCHAR(MAX)
in [Programming SQL]
|
Prev: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Next: Select Query Problem
From: Lancy on 20 Nov 2009 02:20 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
From: Tom Cooper on 20 Nov 2009 05:29
@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(a)gmail.com... > 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 |