CODEWITHSUNDEEP
sql-server-2005ssmssqlcmd
UB.
UB.

Reputation: 367

SSMS and SQLCMD displays only the first 8000 characters

In SSMS when I try to execute: 

SELECT CONVERT(VARCHAR(MAX), REPLICATE('a',9000))

I see only the first 8000 characters displayed. The settings Tool >> Options >> Query Results >> Sql Server >> Results to Grid is set to 65534 and Results to Text is set to 8192.

Also when I try to run this from SQLCMD 

sqlcmd -S Server -E -y 0 -Q "SELECT CONVERT(VARCHAR(MAX), REPLICATE('a',9000))" -o out.txt

I see only 8000 charecters.

The flag -y 0 is supposed to set it up to 1 MB. But I do not more than 8000 characters.

What could be the problem?

thanks,
_UB

Upvotes: 2

Views: 2564

Answers (3)

gbn
gbn

Reputation: 432712

REPLICATE output is based on the datatype input. So this explains sqlcmd.

If string expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string expression must be explicitly cast to the appropriate large-value data type.

So, use this SELECT REPLICATE(CONVERT(VARCHAR(MAX), 'a'), 9000)

And SSMS has never shown all text data (nor did Query Analyzer)

Upvotes: 4

CodeMonkey1313
CodeMonkey1313

Reputation: 16031

VARCHAR(MAX) is only capable of holding 8000 characters

see here

From MSDN:

varchar [ ( n | max ) ] - Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes

As Joseph (below) says, to hold more, use a text or ntext data type, but if you want to be able to search that, then you'd need some form of full-text indexing enabled.

Better Link

Upvotes: 0

MartW
MartW

Reputation: 12538

See here :

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

Upvotes: 2

Related Questions