Use SQL to query SharePoint list history

Is there a way to use SQL (SSIS or Access) to query a list table in SharePoint 3.0 to get the version history?  I can easily query the core table, but I can't see where the history is stored.  I'm trying to write an audit report to find when someone has changed the due date on a deliverable.
jimbob_sfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ronney_leslieCommented:
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
0
jimbob_sfAuthor Commented:
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.
0
cyberkiwiCommented:
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)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

ronney_leslieCommented:
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
0
ronney_leslieCommented:
Hi

Did my above query help?

Ronney
0
jimbob_sfAuthor Commented:
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.
0
ronney_leslieCommented:
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
0
jimbob_sfAuthor Commented:
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".
0
ronney_leslieCommented:
Can you send me a query that you used to query the list data?
Ronney
0
jimbob_sfAuthor Commented:
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")));
0
ronney_leslieCommented:
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
0
jimbob_sfAuthor Commented:
Yes.  I have a few developers who work for me who could figure it out. Thanks!
0
ronney_leslieCommented:
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
0
jimbob_sfAuthor Commented:
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
0
jimbob_sfAuthor Commented:
We develop .net / sql applications for financial services, but none of my guys are SP gurus.
0
ronney_leslieCommented:
I have the code ready. Can you provide an email for me to send the files?

Ronney
0
jimbob_sfAuthor Commented:
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.
0
ronney_leslieCommented:
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)?
0
jimbob_sfAuthor Commented:
Absolutely!  I already looked into doing that.  If I could give more, I would.
0
jimbob_sfAuthor Commented:
bumped up
0
ronney_leslieCommented:
You can find the code and instructions at the following link.

http://www.ronney.net/comments.php?id=182_0_1_0_C

Thanks for the points.

Let me know if you need more info.

Ronney
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.