varchar(max) / 8192 chars - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

varchar(max) / 8192 chars

Author

Topic

cbeharry
Starting Member

3 Posts

Posted - 2011-03-09 : 21:38:11

I'm trying to create a stored procedure that returns a bunch of text (it actually returns the text for creating another stored proc so I need the carriage returns, spacing etc).

The stored procedure I made needs to return about 10 thousand or so characters and as most of you probably know, it returns only max 8192 characters.

Through some searching online I've been able to get it working so that all the text goes into a single cell..yes over the 8192 limit (see below code). But the problem here is that I loose all formatting (carriage returns and spacing) so it looks like a mish mash of insert and update statements along with do while loops and so on.

If I change it to return everything to text or a file for some reason the 8192 max kicks in and it cuts off. I get the formatting so it looks readable but it gets cut off.

For reference I did set Tools/Options/Result to text = 8192 and this is the code example that got me past 8192 in the first place:

DECLARE @MESSAGE VARCHAR(MAX)
SET @MESSAGE = REPLICATE('1234567890',300)
SET @MESSAGE = @MESSAGE + @MESSAGE
SET @MESSAGE = @MESSAGE + @MESSAGE
SELECT LEN(@MESSAGE)
-- SELECT @MESSAGE

I tried text too but that didn't seem to make a difference.
I'm really close but I'm just limited by 8192 when I try to return it to text....any ideals? Code examples would be great.

Thanks in advance.

~ Chris

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-03-10 : 00:03:04

SSMS has a limit on how many chars a cell in the query results window can contain. That limit has nothing to do with how much data is actually in a column in the database. if your column is varchar(max) then you can have MANY more chars than 8192 in the column. more like 2gb worth.

if you exec your proc from code (like C# or whatever your client's language is) instead of running it in SSMS, then you should get all the chars back.

elsasoft.org

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-10 : 08:07:52

The limit for varchar(max) is 4GB so you should be safe :) Try and display the contents in a web page or something instead...you will see that everything is displayed as it should. Unless something else is wrong of course.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

cbeharry
Starting Member

3 Posts

Posted - 2011-03-10 : 08:25:06

So try putting this into a webpage or app huh? Good thoughts actually because I'm actually creating a winform app (C#) for the DB we're working with. One day I may change it to a website app.

For now I just wanted everything in the query window but perhaps this will work for what I need.

Thanks very much for the input.

~ Chris

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-10 : 08:49:57

This behavior also has something to do with the funny way in which SQL does implicit conversions. Change your code as follows and try again?

DECLARE @MESSAGE VARCHAR(MAX)
SET @MESSAGE = REPLICATE(cast('1234567890' as varchar(max)),300)
SET @MESSAGE = @MESSAGE + @MESSAGE 
SET @MESSAGE = @MESSAGE + @MESSAGE 
SELECT LEN(@MESSAGE)

Edit: On second thoughts - ignore what I said here. The problem I have seen happens when you do

DECLARE @MESSAGE VARCHAR(MAX)
SET @MESSAGE = REPLICATE('1234567890',5000)

only to find that it is not 50,000 characters long.

cbeharry
Starting Member

3 Posts

Posted - 2011-03-10 : 21:16:03

Thanks for the input sunitabeck, but the thing is the code I uses works, for what I want it to do, when I select it to a normal DataGrid (ctrl+d) but if I select it to text (ctrl+t) it cuts off at 8192. I need it to text though because it will retain the carriage returns. I tried temp tables and many other ideals but no luck.

So look at it this way:
If we SET @MESSAGE = 5000 it won't retain everything
If we SET @MESSAGE = 600 it will retain everything and if we add it repeatidly it still retains everything...a little odd but it works...only for Datagrid though....sigh.

I'll try the other ideals above. It will take some time but if this forum is still open I'll try to post my results

Any other thoughts are welcome...thanks again!

~ Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources