Geeks With Blogs
Tangible Thoughts SharePoint, MOSS? and all the other questions

First to fend off any SPS discriminations I might be accused of- Mike I am writing this with my knowledge of SPS, do not know if it applies to WSS, since I do not have a WSS Configuration to try it out on separately.

Secondly fiddling with SPS database is not recommend, do it at your own risk K

OK, I had this scenario recently (actually about 2 weeks back). An intern colleague of mine had to do a bulk upload of a bunch of documents to a SharePoint team site, which is not very unusual if you are trying to consolidate a whole heap of documents on to SharePoint. The issue was that my colleagues name gets registered as the author of document, instead of the original author. Well…….. that’s not really an issue, it is “expected behavior” and there is nothing much you can do about it. If you upload a document, you are its author – at least typicallyJ. But in our scenario it is an issue especially since search results would then be misleading.

In order to sort this I(after a huge deal of pleading) with the help of a buddy of mine was left with the most un-desirable task of fiddling with SharePoint’s Database. So here is what we figured out.

The _Site database contains the following tables amongst others.

The Docs table that contains data about every artifact on SharePoint

Docs (DoclibRowId, SiteId, LeafName, ListId …)

The User Info table that keeps data about every SharePoint user

UserInfo (tp_siteId, tp_ID, tp_Login, tp_Title, tp_Email ….)

And then there is the UserData table, which maintains the relationship between user and document.

UserData (tp_ID, tp_SiteId, tp_Author, tp_Editor, nVarChar2 ….)

The link is the DoclibRowId field in the Docs table is the foreign key in the UserData table as tp_ID. Further filtering is possible if by matching the SiteId field in Docs to tp_SiteId in UserData.

The tp_Author, tp_Editor fields in the UserData table denote the user ids of the author and last person who modified the document. The nVarChar2 column usually contains the Domain Login of the creator – I am not very sure about nVarChar2 business.

In order change the authorship of documents, need to

1. Find the DoclibRowId in the docs table. For this you can filter on LeafName which would contain the documents name in its contents.

2. You would need to extract the user id from the UserInfo table. You could use the SiteId field in the Docs table to match up with the tp_SiteId field in UserInfo table to get a list of the site’s members.

3. Then match up the DoclibRowId and SiteId your found earlier with the tp_Id and tp_SiteId fields in the UserData table and do the relevant changes.

Hope this helps any of you who needs to do this in future.

Posted on Wednesday, April 14, 2004 9:35 AM SharePoint | Back to top


Comments on this post: SharePoint Database - Finding a relationship between a document and its creator

# re: SharePoint Database - Finding a relationship between a document and its creator
Requesting Gravatar...
Just a note - unless you explicitly add your new owner to the site, they will not be availible in the UserInfo table. Very avoidable, but a condition you definately need to check.
Left by Philip Wheat on Apr 14, 2004 3:22 PM

# re: SharePoint Database - Finding a relationship between a document and its creator
Requesting Gravatar...
FYI, if you use code, you can upload the document and take the created by and modified by from the file system when importing the document using the object model. My SPExport tool has an import portion that lets you bulk import documents into sharepoint (using the object model) and it will preserve these attributes from the filesystem and promote them to sharepoint. I also have the full code listing if you want to modify the code and build whatever you want.

Just in case you want to avoid editing the database in future ;) (esp. since editing the database is not recommended, even by me, and can lead to inconsistencies).
Left by James Edelen on Apr 14, 2004 9:46 PM

# re: SharePoint Database - Finding a relationship between a document and its creator
Requesting Gravatar...
Actually,
The scenario was after 200+ documents had been already uploaded, and a whole heap of meta data (custom columns) had already been set.

SPSExport can set that data through code? hmmm wonder how you managed that. Isnt the "Vti_author" field read only? I actually tried to change the property through code before having to resort to the database approach.

James would appreciate if you could share with us a code snippet or insights on how to set the author property for a document on SharePoint.
Left by Tariq on Apr 15, 2004 12:26 AM

# re: SharePoint Database - Finding a relationship between a document and its creator
Requesting Gravatar...
I wonder who that nasty Mike is.
Left by Mike Walsh on Apr 15, 2004 3:58 AM

# re: SharePoint Database - Finding a relationship between a document and its creator
Requesting Gravatar...
You can also edit the doc lib in datasheet mode and change any of the fields. It might be a little easier. =0

But thanks for the information on the DB. Good stuff.
Left by james redmore on Sep 16, 2004 6:52 PM

# re: SharePoint Database - Finding a relationship between a document and its creator

# re: SharePoint Database - Finding a relationship between a document and its creator
Requesting Gravatar...
I am just investigating the tables and trying to create a query to grab the subsites, but I cannot find a field to identify a subsite. There is noway in the webs table to grab just a portion of the url to just grab the subsites. So my question to anyone is there a way to identify subsites in the sql tables? Any help would be appreciated
Left by Cynthia on Mar 15, 2006 1:08 PM

# re: SharePoint Database - Finding a relationship between a document and its creator
Requesting Gravatar...
Is the Sharepoint database available for download somewhere.

successin06@hotmail.com
Left by Newbie on May 25, 2006 5:16 PM

Your comment:
 (will show your gravatar)


Copyright © Tariq | Powered by: GeeksWithBlogs.net