Use SQL to query SharePoint list history

This article has the code to do what you want. However it is in C#. I am not sure what you mean by SQL query. Are you running the query directly on the content database?

http://www.sharepointkings.com/2008/09/working-with-versions-in-list.html

Give me a shout if you need more info.

Ronney

I was looking to query a SP table directly using a query tool like SQL Management Studio, SSRS/SSIS, Access....  I actually found your page on Google, but was looking to see if you can query the MS SQL Server database directly.  Oddly, or perhaps not odd at all, there was nothing that Google could find on the subject.

Hi,

I don't believe there is a history table. It simply keeps the latest copy.  It's not a wiki! (although there is a wiki function for sharepoint which will store history)

Here is the query you wante:

SELECT      
  dbo.UserData.tp_ID,
  dbo.UserData.tp_UIVersion as VersionID,
  dbo.UserData.tp_UIVersionString As VersionLabel,
  dbo.UserData.tp_ListId,
  dbo.UserData.tp_Author,
  dbo.UserData.nvarchar1,
                dbo.UserData.nvarchar2,
  dbo.UserData.nvarchar3,
  dbo.UserData.nvarchar4,
  dbo.UserData.nvarchar5,
                dbo.UserData.nvarchar6,
  dbo.UserData.nvarchar7,
  dbo.UserData.nvarchar8,
  dbo.UserData.nvarchar9 ,
                dbo.UserData.nvarchar10,
  dbo.UserData.nvarchar11,
  dbo.UserData.nvarchar12,
  dbo.UserData.*
FROM            dbo.Lists
INNER JOIN
                dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId

The columns that I have labelled as VersionID and VersionLable are the columns you are loking for.

This means If the ListItem is modified for 3 times, you will get the VersionId as 512,1024,1536 Likewise. VersionLabel as 3.0, 2.0, 1.0.

You can filter it for your list using
where tp_ListID =  'Your List GUID'

BTW: cyberkiwi:
Yes there is history for Lists and you just need to enable it

Leslie

Hi

Did my above query help?

Ronney

Ronney,

I found out that the "tables" I see in MS Access are actually views created by the SP API (as told to me by the SP admin).  I actually don't have permission to hit the SQL db directly, so I'm sorry for incorrectly describing what I had available to me.  I'd have to go through political hell to get access to the SQL database directly.

I guess this takes me back to your first proposal.  I am allowed to put in script into a Web Part, but I'm not a SP developer and don't have Designer.  Is there a step by step on how to use your code in a Web Part?  I read your post, but it assumes I know what Web Part to start with and how to embed the code.

I know this goes a bit beyond the simple question I started with, but this would be a huge benefit to my project (I'm a Program Manager for a huge 22 stream effort).  I'm willing to do the reading, but I'm feeling around in the dark as to where to start.

In your originl question you stated that you can acess the original list tables and your recent post says that you have views in Access for the list tables in Sql server. If that is true you could query the "Access views" to get your list history. Let me know your thoughts on this. If this is not feasible then I would help you with a web part.

Ronney

Before I knew that what I saw in MS Access was actually the API representations, I thought I was hitting SQL directly and had full access to all tables.  So, no, I don't have full access to the raw tables.  UserData isn't one of the items I can "see".

Can you send me a query that you used to query the list data?
Ronney

Here is the query.

SELECT Tasks.[Status], Tasks.[Issue Type], Tasks.Priority, Tasks.Description, Tasks.[Notes], Tasks.[Assigned To], Tasks.[Due Date], Tasks.Stream.Value
FROM Tasks
WHERE (((Tasks.[Status])<>"Completed") AND ((Tasks.[Issue Type]) Not In ("Bug","Enhancement","Comment/Observation")));

Do you have Visual Studio 2008? If yes I could send you webpart code that you can modify according to your requirement. Actually I wanted to write the exact code for you but my Sharepoint environment creashed and I will not be able to get it fixed very soon.

Ronney

Yes.  I have a few developers who work for me who could figure it out. Thanks!

I got the List histoey code workig as a web part. There are few more minor things to do. Is is ok if I send the code on Tuesday since I am on vacation. If it is really urgent I can send the current (not 100% complete) code.
Ronney

Not urgent.  I've got my guys cranking on a project, so next week will be ok.  Thank you again.  You've got the points!

-Phil

We develop .net / sql applications for financial services, but none of my guys are SP gurus.

I have the code ready. Can you provide an email for me to send the files?

Ronney

Ronny,

There isn't a way I can get you an address privately and posting an address here will guarantee I'll have to close it b/c of all the spam I'll get.  I spoke with EE and the only option is for you to upload the file/code through the EE links below the "Comment" box we type in.

I will send a link to download the code tomorrow.

BTW don't you think that this solution(s) deserves 500 A grade points (& not just 250)?

Absolutely!  I already looked into doing that.  If I could give more, I would.

bumped up