Increase Max characters retrieved in SQL SERVER 2005

Question

Increase Max characters retrieved in SQL SERVER 2005

Sanz

Hall of Fame

Points: 3412
More actions
June 25, 2008 at 1:32 pm

#381368

Hi,
I have a column of text datatype. Sql server Query result does not allow me to view the complete data. i.e, the result is truncated.
I need to increase the maximum characters retrieved (by default its 65535 for Non XML data). When I try to increase it does not increase.
I can view the complete data through other applications but is there a way I can view the complete result using Sql server ??
Thanx in Advance !
Santhosh
Sanz

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

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

Steve Jones - SSC Editor SSC Guru Points: 736116 More actions · Answer 1

How are you retrieving the data? If you select the data, SQL Server returns all of it, but the client application may only show part of it.

In SSMS there are options you can set for how much data is displayed.

Sanz Hall of Fame Points: 3412 More actions · Answer 2

I'm retrieving the data through SSMS. The Result to Grid limits Non XML data to 65535. My text field has more data than this.

Sanz

Catalin Enescu SSC Veteran Points: 276 More actions · Answer 3

cast the field as xml:

select cast('<FieldContent><![CDATA['+FieldName+']]></FieldContent>' as xml) as FieldName from ....

I don't know how to increase non XML data length more than 65535 but you can increase XML data length to unlimited. At least you can see the column content

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

Why on Earth are you still using the TEXT datatype on 2k5? Convert the text to VARCHAR(MAX) and you're done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sanz Hall of Fame Points: 3412 More actions · Answer 5

Hi Jeff,

Done ?? Will converting text to varchar(max) allow me to view the complete data using a select statement in SSMS ??

Well I doubt !

Santhosh

Sanz

Steve Jones - SSC Editor SSC Guru Points: 736116 More actions · Answer 6

varchar(max) is recommended as text will go away. It doesn't affect your use of SSMS.

you can change the limits, but not sure if they'll get above 65k. Why do you need to view this in SSMS? Can you pull it back through SQLCMD to a file?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

I face this problem quite often with the procedure text that I read and script out from SQL. The solution that I came up with was to parse it into multiple rows, breaking on new-lines. That in itself required some work, but with Jeff Moden's help I have a very convenient little table-valued function that does the job for me.

Perhaps this technique can be modified for your situation.

Here is the forum where Jeff & I recently discussed this problem with the Function that we came up with to address it (next to last post has the function listing).

http://www.sqlservercentral.com/Forums/Topic518718-203-1.aspx

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 8

That was a great idea you had, Barry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Thanks for the feedback, Jeff.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 10

You're gonna think I'm nuts, but that's the kind of stuff that you should turn around and write an article about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (6/28/2008)
You're gonna think I'm nuts, but that's the kind of stuff that you should turn around and write an article about.

Yeah, actually I do know that... 🙁

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]