Geeks With Blogs
Jason Row .NET Development on the Canadian East Coast

<reposted from my old blog>

My CommunityServer site is acting up a bit as it won't allow me to edit any of my posts. I thought it was a permissions error but after digging around for a bit I still can't find what it could be.

I was going to spend some more time finding out what is wrong then I figured I could just use SQL Enterprise Manager to view and edit the text, which sounded simple enough. Instead I ended up learning a bit more about nText columns in SQL Server and how to write text to them.

First off I open the cs_Posts table in Enterprise Manager and found the posting I wanted to edit. The <Long Text> entries in the screen shot meant that I'm rambling again and my posts are way too long.

<Long Text> means that I'm rambling again

I start looking into how I can view and edit the entry short of building an .NET app that allows me to do it.  Since a SELECT statement is not showing all the text I had to figure out another way.

A quick Google search provided the answer of using SQL Query Analyzer. "Awesome!"  I said to myself and so I threw in my query and looked at the result that was returned. Things looked great until I reached the end and noticed that not all of my posting was returned.

Next step was to go to the Options window and bump up the Maximum characters per column option to the max of 8192 and hope my posting wasn't longer than that.

Now I could see my entire posting and just had to figure out how to edit it and submit it back into the database.

Here's where I found out about the WRITETEXT statement and figured I'd try to use it. From the SQL Books Online WRITETEXT "Permits nonlogged, interactive updating of an existing text, ntext, or image column. This statement completely overwrites any existing data in the column it affects. WRITETEXT cannot be used on text, ntext, and image columns in views." And using the example given I came up with this bit of T-SQL code.

01 DECLARE @ptrval2 binary(16)
02 SELECT @ptrval2 = TEXTPTR(Body)
03 FROM cs_Posts pr
04 WHERE (PostID = 9999)
05 WRITETEXT cs_Posts.Body @ptrval2 '<p>Insert freakin'' long bit of text
06  here to be added back to the table'

First off I had to declare a text pointer, @ptrval2, and then use it in a SELECT statement to retrieve a pointer to the row in the the column I was looking for. Then I could call WRITETEXT and pass parameters for the column, the text pointer, and finally the new data I want to save.  I also had to remember any single quotes in my text had to be prefixed with another single quote.

And with that, I was able to edit my posting. Not the most elegant but it was an interesting learning experience.

Posted on Monday, May 7, 2007 11:26 PM | Back to top

Comments on this post: Editing Community Server posts the hard way

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Jason Row | Powered by: