SQL Server : read full ntext column value

Question

I have aspnet_Tablename. kindly see below pic for data design.

enter image description here

Now, here on PropertyValuesString column, data is storing as a xml format.

When I run this query.

SELECT TOP 10 [PropertyValuesString] FROM Table

I am getting results, but not the whole record of PropertyValuesString column.

So, any one let me know. how can i get whole value of an ntext property column?

Also tried with

select top 10 (max (convert(nvarchar(4000), PropertyValuesString))) FROM Table

or

DECLARE @ptrval varbinary(16)

SELECT @ptrval = TEXTPTR(data)
FROM TABLE1

READTEXT TABLE1.data @ptrval 0 500

this also gives only part of the text and not complete text.

3) I even tried looking at data from Enterprise manager it displays

<Long Text>

I also tried changing text size by "set testsize 60000" so that I can see the text.

Actually the text in isnerted by asp file and it is seen on the browser but I want to read it in sql server.

But no luck.

Thanks

First of all - `ntext` is deprecated - don't use it anymore, and replace it with `nvarchar(max)` instead as soon as you can. Secondly: you're getting the whole contents of the column - be assured! It's only Management Studio that doesn't *show* all of it. You can go to the Options menu and decide how much of such a column you want to see. Also: if this is XML - why aren't you using the `XML` datatype?? — marc_s, Aug 14 '13 at 05:01
suppose i have more than 8000 column size...thn what happen if i replace ntext to nvarchar(max) ? — Kvadiyatar, Aug 14 '13 at 05:11
`nvarchar(max)` (and `XML`, too!) can hold **up to 2 GByte** of data (that's **one BILLION characters** in 2-byte-per-character Unicode) ! That's more than **100 times the whole "War and Peace" book by Leo Tolstoj** - is that enough for you?? — marc_s, Aug 14 '13 at 05:24
it will be not work...bocz i m using aspnet dll...there is web service...suppose i will be change it will be not work at my c# code.so, i must have to put ntext.... — Kvadiyatar, Aug 14 '13 at 05:42
Sorry, but you're **absolutely and totally** wrong on this point: I have tons of ASP.NET applications and they all work very well with `nvarchar(max)` or `XML` - there's **absolutely no need** for `ntext` — marc_s, Aug 14 '13 at 06:13

score 1 · Answer 1 · edited Aug 14 '13 at 12:16

Finally i got the answer.

CREATE FUNCTION [dbo].[GetProfilePropertyValue] (  
    @PropertyName as varchar(max)
    , @PropertyNamesString as varchar(max)
    , @PropertyValuesString as varchar(max)) 
RETURNS varchar(max)
AS
BEGIN
    DECLARE @StartIndex int
    DECLARE @EndIndex int
    DECLARE @StartPos int
    DECLARE @Length int

    -- First we find the starting position
    Set @StartIndex = PatIndex('%' + @PropertyName + ':%', @PropertyNamesString) + LEN(RTRIM(@PropertyName)) + 3
    Set @EndIndex = PatIndex('%:%', Right(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex))
    Set @StartPos = Cast(Substring(@PropertyNamesString, @StartIndex, @EndIndex) As Int)

    -- Now we need to know how long it is
    Set @StartIndex = @StartIndex + @EndIndex + 1
    Set @EndIndex = PatIndex('%:%', Right(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex))
    Set @Length = Cast(Substring(@PropertyNamesString, @StartIndex, @EndIndex) As int)

    -- Now we get the value we want
    RETURN SUBSTRING(@PropertyValuesString, @StartPos + 1, @Length)
END

That was easy, now all we need to do is run a query that gets the info.

SELECT
    dbo.GetProfilePropertyValue('LastName', PropertyNames, PropertyValuesString)
    , dbo.GetProfilePropertyValue('FirstName', PropertyNames, PropertyValuesString)
    , dbo.GetProfilePropertyValue('Phone', PropertyNames, PropertyValuesString)
FROM aspnet_Profile

Joining with the aspnet_Users on UserID will give you the user name and email. Enjoy.

score 0 · Answer 2 · answered Aug 14 '13 at 05:01

0

Have a look at Options (Query Results/SQL Server/Results to Grid Page)

and more specifically at

Maximum Characters Retrieved

Enter a number from 1 through 65535 to specify the maximum number of characters that will be displayed in each cell.

answered Aug 14 '13 at 05:01

Adriaan Stander

162,879
31
289
284

SQL Server : read full ntext column value

2 Answers2