Version History in SharePoint via SQL

Recently I posted about how to get check-in comments with Nintex via MS SQL – turns out there was a bit more complexity involved in the structure of the version history then first thought (surprise surprise). Below is the stored procedure created to reliably extract the highest MAJOR version of a SharePoint document. So, if a document is currently v5.4 in your SharePoint library, this will grab the 5.0 version:

USE [MySharePoint_Content_DB]
GO
/****** Object:  StoredProcedure [dbo].[proc_GetDocVersion]    Script Date: 02/17/2012 13:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_GetDocVersion](
@LeafName nvarchar(260)
)
AS
SET NOCOUNT ON

SELECT TOP 1 x.UIVersion FROM (

SELECT
AllDocVersions.UIVersion
AS UIVersion
FROM
AllDocVersions
JOIN AllDocs ON AllDocs.[ID]= AllDocVersions.[ID]
WHERE
AllDocs.LeafName = @LeafName
AND ((CONVERT([nvarchar],AllDocVersions.UIVersion/(512),0)+'.')+ CONVERT([nvarchar],AllDocVersions.UIVersion%(512),0)) LIKE '%.0'
UNION ALL
SELECT
AllDocs.UIVersion
As UIVersion
FROM
AllDocs
WHERE
AllDocs.LeafName = @LeafName
AND ((CONVERT([nvarchar],AllDocs.UIVersion/(512),0)+'.')+ CONVERT([nvarchar],AllDocs.UIVersion%(512),0)) LIKE '%.0'

) x
ORDER BY UIVersion Desc;

Accessing the SQL DB in SharePoint 2010 directly (as opposed to using the SharePoint API’s etc.) is generally considered a cowboy maneuver and can get in you in lot’s of trouble with inconsistent results as well as performance hits. Use this SQL at your own risk, if not as just a means to better understand the plumbing that goes on in the basement of SharePoint.

Additionally, note that if you are accessing version history via the /vti_history// method, there are some major caveats as described in the following (note it’s referring to SP 2007 which uses single-digit version numbers but the description of the potential run-on situation still applies): http://blogs.msdn.com/b/roberthorvick/archive/2007/01/04/wss-rant-linking-to-the-latest-version-of-a-sharepoint-document-considered-harmful-lessons-6-and-7.aspx

Microsoft SharePoint, SQL, Stored procedure, version history

Comments (2)

  • Hi Mr. Tuomi,

    I’ve searched the web and don’t seem to be able to find an answer to this. Is it possible, either via MS Access or SQL, to access the version history of a sharepoint entry. I want to be able to run a search on all entries for a given week and see all the people who changed the value of a specific field. I have SharePoint 2007 and Access 2010.

    Thanks,
    Mike

    • Hi Mike,

      Your requirements are definitely achievable using the SharePoint Object Model, however would likely require custom development to achieve. For example, the project on Codeplex at http://exportversionhistory.codeplex.com/, is an example of a SP 2010 solution that does something similar to what you’re asking. It could most likely be back-ported to SP 2007 without excessive difficulty.

      Email me at ktuomi@itgroove.net if you would like to pursue this as a project with itgroove, we’d be happy to explore your options further!

Leave a Reply

Your email address will not be published. Required fields are marked *