Saturday, April 24, 2010

Extracting data from Sharepoint Version table

When using SharePoint 2007, if we turn the Version Control settings to On for a List/Library, then different versions of the file or listitem get stored in the Content DB.

There are multiple ways to retrieve this information:
  1. Using MOSS 2007 API
  2. Via Versions.asmx - An OOB SharePoint Webservice
  3. Or Querying the Docs & DocVersions table directly in using T-SQL statements
Now through SP API, you can try something like this -->
SPWeb web = new SPSite("your site url").OpenWeb();

SPList lib = web.Lists["your library"];

foreach (SPListItem item in lib.Items)


SPFile file=item.File

SPFileVersionCollection versions = file.Versions;

//Add your code to loop through the version collection & add custom logic here..


Coming to WebService approach:

SharePoint has a Versions.asmx web service to work with versions in document libraries.

For introduction, check out how to utilize this to get some information :

Incase, you want to go ahead querying the SQL tables directly, then you could try a query as below:

SELECT DocVersions.version
FROM Docs INNER JOIN DocVersions ON DocVersions.Id = Docs.Id

Although it is not recommended that you query the SP DB directly. Any modifications or changes to the Content DB tables in unsupported.

Also the data is stored in UTF-8 Encoded format. After the query runs, it returns some data like this:


The above data is difficult to interpret in terms of Version numbers. Anyway, just to let you know

512 = version 1,
1024 =version 2,
1025 =version2.1,
2048= version 3,
1=version 0.1
and so on..

I guess the pattern is clear to you. Hope this article helps you.

No comments:


Related Posts with Thumbnails