Length greater than Nvarchar(max)--Need help

Question

Length greater than Nvarchar(max)--Need help

antonyp 46888

SSChasing Mays

Points: 640
More actions
September 23, 2010 at 5:04 am

#226748

Hello,
We are facing the problem that
one of the table field length more than Nvarchar(max).
We couldnt take that full length,if we tried following query also
"declare @sql_txt varchar(max)
set @sql_txt = '.........need to get this string...... '
print(@sql_txt)
if (len(@sql_txt) > 8000)
print(substring(@sql_txt, 8001, len(@sql_txt) - 8000))
PRINT '> 8000'
if (len(@sql_txt) > 16000)
print(substring(@sql_txt, 16001, len(@sql_txt) - 16000))
PRINT '> 16000'
if (len(@sql_txt) > 24000)
print(substring(@sql_txt, 24001, len(@sql_txt) - 24000))
PRINT '> 24000'
if (len(@sql_txt) > 32000)
print(substring(@sql_txt, 32001, len(@sql_txt) - 32000))
PRINT '> 32000'"
we are not able to get the full length.
How can we take the string if greater than Nvarchar(max).
Give me the suggestion on this.
Thanks,
ARP

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What is the data type of this column which is causing problems?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

antonyp 46888 SSChasing Mays Points: 640 More actions · Answer 2

antonyp 46888

SSChasing Mays

Points: 640

September 23, 2010 at 5:12 am

#1226220

Hi.

The datatype for that column Nvarchar(Max)

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

antonyp 46888 (9/23/2010)
Hi.
The datatype for that column Nvarchar(Max)
Thanks

It's not at all clear what you are trying to do.

Are you attempting to insert a string into a Nvarchar(Max) column?

How are you doing this?

Do you need nvarchar rather than varchar?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

antonyp 46888 (9/23/2010)
We are facing the problem that
one of the table field length more than Nvarchar(max).

nVarchar(max) can store over 1 billion characters in it. That's 2 GB of data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VivekTheWisdom Valued Member Points: 61 More actions · Answer 5

Better to use 'Text' DataType whenever we need to store large texts in our database.

Cory E. SSCertifiable Points: 7076 More actions · Answer 6

wisdom.vivek (9/23/2010)
Better to use 'Text' DataType whenever we need to store large texts in our database.

BOL:

ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Careful what you suggest. 🙂

-- Cory

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

wisdom.vivek (9/23/2010)
Better to use 'Text' DataType whenever we need to store large texts in our database.

Please explain why, in a SQL Server 2008 forum section?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lowell SSC Guru Points: 323518 More actions · Answer 8

wisdom.vivek (9/23/2010)
Better to use 'Text' DataType whenever we need to store large texts in our database.

no, the TEXT datatype is in the process of being deprecated in favor of the varchar(max)/nvarchar(max) data types. the current MS recommendation is to migrate existing code to no longer use the TEXT datatype, which may be completely dropped in SQL 2011's version, i think?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Arjun SreeVastsva SSCertifiable Points: 7141 More actions · Answer 9

you can try using the nvarchar(max) it stores large data or else if you use text

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

antonyp 46888 (9/23/2010)
"declare @sql_txt varchar(max)

Learner DBA (9/23/2010)
you can try using the nvarchar(max) it stores large data

If you read the initial post, you'd see he's already using varchar(max)

or else if you use text

From Books Online (as posted by Cory)

ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass