locked
SharePoint 2010 Content database RRS feed

  • Question

  • In SharePoint 2010 the data type of “AllLists.tp_Field” column in WSS_Content database is changed to custom tCompressedString type which is varbinary. Earlier it was ntext (in WSS 2 and 3). I use this field to retrieve property data as a XML string in my application. How do I convert this tCompressedString data to XML/text data?
    • Moved by Mike Walsh FIN Friday, December 18, 2009 5:05 AM Use ONLY the 2010 forums for 2010 questions (From:SharePoint - Design and Customization)
    Friday, December 18, 2009 4:44 AM

Answers

  • Figured it out.  

    Skip the first 12 bytes based on http://msdn.microsoft.com/en-us/library/dd973866(PROT.13).aspx, then skip another 2 bytes based on http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=97064, then use System.IO.Compression.DeflateStream to decompress the remaining bytes and voila -- readable text.

    Of course this means you'll need to use some .Net code for this -- it can't be done in raw TSql.


    Thursday, January 21, 2010 12:28 AM

All replies

  • I am guessing that you are using .NET in some manner.  The varbinary is likely some kind of Encoded Binary string simply dropped right into the DB.  In which case you could call one of the Encoding.ASCII.GetString(byte[]) methods and get your string out.  I'm not sure it is ASCII, but it is likely one of them ASCII/UTF7/UTF8, etc.

    Chris
    Chris Givens CEO, Architecting Connected Systems Blog Twitter
    Friday, December 18, 2009 4:56 AM
  • I noted this change as well and this is going to impact a number of my processes.  I've been leveraging this data in the content DB for the past 2 versions.  If anyone finds out how to access this data directly in the DB, please let us know.  I've run a few different decrypt and decode routines, but no luck yet.
    Monday, January 11, 2010 4:55 AM
  • My response won't fix the issue, however, I feel that it is important to make an good point.

    This is a perfect example of why developers should not be writing code that runs queries directly against the SharePoint databases.  It really goes against all best practices for SharePoint and results in problems exactly as you are stating.   You could also end up in an unsupported situation where Microsoft will refuse to provide support until you remove all such customizations.

    Microsoft has and will continue to make schema changes to SharePoint's databases.  There is even a very good possibility that the schema for SharePoint 2010 will change from beta to RTM.   It could even change during service pack updates.  

    Best advice is to stick to published SharePoint APIs.  I have yet to come across a situation where I couldn't accomplish what I needed with the APIs.   Maybe you have a very unique situation and the APIs won't do the job.   In that case you run the risk of having your code break anytime Microsoft makes an update to SharePoint.   

    I just want to make sure new SharePoint developers don't try to shortcut the system and go directly to the database when an existing API could handle the job.  

    Monday, January 11, 2010 7:44 PM
  • Figured it out.  

    Skip the first 12 bytes based on http://msdn.microsoft.com/en-us/library/dd973866(PROT.13).aspx, then skip another 2 bytes based on http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=97064, then use System.IO.Compression.DeflateStream to decompress the remaining bytes and voila -- readable text.

    Of course this means you'll need to use some .Net code for this -- it can't be done in raw TSql.


    Thursday, January 21, 2010 12:28 AM
  • There is a company called Sharpest Tool Software that has a product called SPViews that generates SQL Views that do this for you.  They put their 2010 beta version out there and it is working for me.  Then you don't have to deal with the compressed string or trying to parse the list XML.
    Monday, May 10, 2010 7:07 PM
  • Have You tried to Compress the string and put it back to tp_Fields?

    I tried it with my custom list and sharepoint failed to read the tp_Fields. MS must have used other method of compression instead of using DeflateStream to compress, because I notice the size of the original compressed string created by sharepoint is much smaller than my compressed string using DeflateStream.

     

    Friday, November 5, 2010 3:46 PM
  • Here is the code
    byte[] doc = reader.GetSqlBinary(0).Value;
    System.IO.Compression.DeflateStream str = new System.IO.Compression.DeflateStream(new MemoryStream(doc, 14, doc.Length - 14), CompressionMode.Decompress);
    
    StreamReader s = new StreamReader(str);
    string xml = s.ReadToEnd();
    //Remove the 14.0.0.4.bla bla bla
    xml = xml.Substring(xml.IndexOf('<'));
    //Add a root element
    xml = "<root>" + xml;
     xml = xml + "</root>";
    //TADA!!!
    XElement ele = XElement.Parse(xml);
    

    Monday, January 30, 2012 11:29 PM
  • It is not possible to decode tCompressedString using TSQL, but I found this blog with simple utility that can decode it

    http://www.digitude.net/blog/?p=362

    Think this will be helpful
    Tuesday, July 17, 2012 8:58 AM