How to handle varchar(8000) limit for large quary

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to handle varchar(8000) limit for large quary

Author

Topic

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-25 : 17:36:05

I am converting my quary into stored procedure, but it gets truncated due to VARCHAR(8000) limit.
I tried splitting into 2 strings -

SET @sql1 = N'sqlstring_part1
SET @sql2 = N'sqlstring_part2
SET @SQL = (@sql1+@sql2)

exec(@SQL)

Print @SQL shows; it still took only @sql1 and truncated the rest.
Please help.

Niki

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-25 : 18:50:27

Use varchar(max)

-Chad

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-25 : 18:58:16

Tried...it did not work either. Thanks for responding.

Niki

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-25 : 20:35:21

Have you tried EXEC(@sql1+@sql2)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 20:37:12

quote:
Originally posted by Niki

Tried...it did not work either. Thanks for responding.

Niki

how are you finding that its getting truncated?
if its results in query window then its wil show only max of 8000 + chars in text mode.

try using results to file option and check file content

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-10-25 : 20:37:45

It may be the "maximum number of character displayed in each column" setting in: query options | results | Text .
try "select len(@sql)" to see how big it actually is.

Be One with the Optimizer
TG

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-25 : 23:30:03

robVolk, I will try that tomorrow at work.
Visakh, I did following-

commented EXEC(@SQL)
instead added
PRINT @sql1
print @sql2
PRINT @SQL

First two prints give me complete query that I can run in successfully (no errors)
BUT last print gives me only what it shows in my first string @sql1.

I can work around it by deleting few columns which are optional at this time, but in future I may actually have data that I do not want to miss.

Read lots of solutions to this, but not simple enough that would make sense to me at my skills level.

Niki

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-25 : 23:54:09

TG, your response reminded me to check the column headers that I am using in my query, may be there is some KEYWORD I am using that may be the culprit. I have come across this before & splitting of strings did work. Will let you know tmrw.
Thanks All!

Niki

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-26 : 01:24:07

Did you try results to file and see if it is really getting truncated, or if it is just a limitation of SSMS?

-Chad

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-26 : 06:58:39

What are the declarations for @sql1 and @sql2? Unless at least one of them is also varchar(max) the concatenation will get truncated at 8000.

DECLARE 
	@sql1 VARCHAR(8000), 
	@sql2 VARCHAR(8000), 
	@sql VARCHAR(MAX);

SET @sql1 = REPLICATE('a',8000);
SET @sql2 = REPLICATE('b',8000);

SET @sql = @sql1 + @sql2;
SELECT LEN(@sql1),LEN(@sql2),LEN(@sql);

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-26 : 10:16:24

EXEC(@sql1+@sql2) worked!!!!!! Thank you robvolk!
Happy Friday to all!

Niki

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources