SQL Server : read full ntext column value

Question

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

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

Answer 1

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.

Answer 2

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.