SQL Server Performance

TEXTPTR

Discussion in 'Performance Tuning for DBAs' started by kioteh, Mar 8, 2004.

  1. kioteh New Member

    I am having trouble getting a valid textptr. I have a column that is ntext. I have updated the column to a null, but everytime I run TEXTVALID it comes back with a 0. Any ideas on what I need to do to get a valid textptr?

    Greg
  2. satya Moderator

    Post the code used.
    Have you used text in row option for that table?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. kioteh New Member

    No, I hadn't set the "text in row" option on because my understanding from the documentation was that it was only used for small to medium sized data in the column and my data could get very large. I did however go ahead and set that option to see if that would fix my problem after your post and now I get the following error when I run my query.

    select textvalid(logshipinfo.backupfile,textptr(backupfile)) from logshipinfo

    Server: Msg 7101, Level 16, State 1, Line 1
    You cannot use a text pointer for a table with option 'text in row' set to ON.
  4. satya Moderator

    My intention was to ensure whether you've used so, set it to OFF now as the error represents clearly.

    As you're using TEXTVALID, initially try to use TEXTPTR as defined in books online with examples.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. kioteh New Member

    Ok, I've set the option off again. I took the code for textptr from BOL and converted it to my database. I then try to verify that the pointer is valid by using the textvalid and it gives me a return code of 0 (invalid textptr). Here is the code I'm using

    DECLARE @pointer VARBINARY(100)
    SELECT @Pointer=TEXTPTR(logshipinfo.backupfile) FROM logshipinfo
    SELECT TEXTVALID(logshipinfo.backupfile,@pointer) FROM logshipinfo

  6. kioteh New Member

    Well, I went ahead and took it to the next step and used my writetext command to put data into that column and it worked! I'm a little confused as to why it is showing a textvalid return code of 0 instead of 1, but I guess I should just be happy it is working. All of the BOL and MSDN stuff I've found so far says that you must have a valide textptr in order to performa a writetext. Any ideas on why I'm getting the results that I am?
  7. satya Moderator

    Out of curiosity, does the table hold any data?

    WHen you get the value from TEXTPTR why to check with TEXTVALID, with the value from TEXTPR you can use in WRITETEXT anyway.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. kioteh New Member

    The table has 6 total columns. All of the columns except the ntext column has data right now. I was getting an error on my writetext last week, so I started using the textvalid to try to troubleshoot why I was getting the error. My interpretation of the BOL section about using BLOBs was that you had to have a valid textptr in order to use writetext, so my thought was that I would verify that it was getting a valid textptr first and that is when I started using textvalid. Since I was getting a 0 return code for textvalid, I was assuming that was my issue, but apparently I'm misreading something.
  9. satya Moderator

    As I said you can go straightaway with WRITETEXT by obtaining value from TEXTPTR and I don't think its necessary to interpret TEXTVALID.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. kioteh New Member

    Ok, great, thanks for your help so far. I've got my sample query working fine. Now I've added that concept into my C# code and am getting the following error message:

    "Cannot convert data type image to ntext"

    I don't see anywhere that I tell it the data is supposed to be image so I'm confused. I have the column setup as ntext. Any ideas?

    Again thanks for your help.
  11. satya Moderator

  12. kioteh New Member

    The column is setup as ntext. The program is supposed to read a file from my machine and place it in this ntext column using updatetext.

  13. satya Moderator

    I don't have any exp. on C#, but have you tried the process in SQL Server without any issues.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. kioteh New Member

    Yes, it worked just fine in SQL Server. It ended up being a c# coding error that I found and have fixed. Everything works great now. Thanks alot for your help, it is very appreciated!

  15. satya Moderator

  16. mv_carvalho New Member

    I think this can help: the .Net Framework has a ntext datatype for using with SQL Server that would do the issue.

    Try:

    System.Data.SqlDbType.NText


    Marcus Vinicius Carvalho
    Consultant
    Brás e Figueiredo Informática

Share This Page