Storing data in varchar max size

Question

Post reply

Storing data in varchar max size

TRACEY-320982

SSChampion

Points: 13653
More actions
September 30, 2010 at 7:07 pm

#241203

I am writing to a table that has a column defined as text.
I need to string the data into a variable. Row 1 has 13112 and 2 Row has 23452 and row 800 has 88812 (approx 800 row in the table).
I will end up with '13112;/23452'/...............88812;/ where 88812 is the last row in table.
I only seem to get the string output for the 600 record and it never continues to go to end of table .
It seems the variable cannot hold the amount of data I am stringing together.
Any ideas ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Please provide how you are trying to put the data into this variable.

A sample of your script is essential to good help.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Don't be confused by a limit on how much of the data you can DISPLAY at one time. That may be a setting in SSMS.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Yeah, it is a setting within SSMS that allows you to display varying amounts of large data fields (text, VARCHAR(MAX), etc.) but only up to a limit. Past, I think, 8000 characters you'll need to set up a program and stream the data out, just as you would in code.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

I am just reading a table that has the data in it.

I still need to get the string back into SQL to update a table with the concatentated string. I changed the output to display in text .

I think it is the print command does not show more than 8000 characters. I wanted to be able to see the data before I insert it into my table. Is there a way to view the data when it larger than 8000 if you cannot print it to the screen.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

TRACEY-320982 (10/1/2010)
I am just reading a table that has the data in it.
I still need to get the string back into SQL to update a table with the concatentated string. I changed the output to display in text .

Did that work for you? I believe there is still a limit on output as text when displaying on the screen.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

No i even saved it to a physical text file and brought it back in and the variable is not holding all the data.

winash SSChampion Points: 11446 More actions · Answer 7

Use the SP available here http://www.sqlservercentral.com/scripts/Print/63240/ to print out your variable in chunks of 8000 characters.

DECLARE @max-2 varchar(max);

SET @max-2 = REPLICATE('X',8000);

SET @max-2 = @max-2+'----Finished';

PRINT @max-2

PRINT CHAR(13)+CHAR(13)

PRINT REPLICATE('*',30)

EXEC dbo.LongPrint @max-2 -- will get you everything stored in the variable

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Please provide a snippet of your script so we can see how you are dumping data into this variable.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

Thanks I think I have got it now it was the printing .

Here the entire code and it works a treat.

DECLARE @START INT,

@END INT,

@@CONCAT_PROJ VARCHAR(MAX),

@PROJ_ID NVARCHAR(30),

@ID INT,

@CREATE_FL NVARCHAR(1)

SET @START = '1'

Select @END = max(ID)+1 From DBO.PARAM_PSR

WHILE @start < @End

BEGIN --START LOOP

SELECT @PROJ_ID = PROJ_ID, @ID = ID

FROM DBO.PARAM_PSR

WHERE ID = @start

--i am adding a difference character if start or end record.

IF @START = '1'

BEGIN

SET @@CONCAT_PROJ = ':' + @PROJ_ID + '\'

END

IF @START = @END

BEGIN

SET @@CONCAT_PROJ = @@CONCAT_PROJ + ';' + @PROJ_ID + '\'

END

IF @START <> @END AND @START <> '1'

BEGIN

SET @@CONCAT_PROJ = @@CONCAT_PROJ + ';' + @PROJ_ID + '\'

END

Select @START = @START +1

END --END LOOP

--Then I use this print out procedure

http://www.sqlservercentral.com/articles/varchar(max)/67057/

EXEC dbo.spPrintOutLongSQL

@SQLInput = @@CONCAT_PROJ,

@VariableName = '@SQL',

@Debug = 0

and it works reads my entire records.

Thanks everyone.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks for the Update - glad it is working for you now.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events