Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

Yesterday I started looking at the tables in my SharePoint 2010's Content databases for the first time and I noticed something a little unnerving: There were files that were checked out and the web front end was not displaying them in any way.

More specifically I ran this query against one of my CDBs:

USE WSS_Content_IT
SELECT  
    AD.DirName, 
    AD.LeafName, 
    UI.tp_Login 
FROM 
    dbo.AllDocs AS AD FULL JOIN 
    dbo.UserInfo AS UI ON 
        UI.tp_ID = AD.CheckoutUserId 
WHERE 
    UI.tp_Login IS NOT NULL AND
    AD.DirName IS NOT NULL

...and it returned just over a thousand rows. About a dozen of them were files I expected to be checked out, but the rest all belonged in the same folder, one I remember populating weeks ago. When I used the web interface to find the offending folder it appeared to not exist. Even when I was logged in with the account listed under tp_Login the folder simply wasn't there to be browsed. These files are acting specifically like they've never had a version checked-in and the "Manage files which have no checked in version" link under Document Library Settings doesn't display these files either.

So I ran the same query against my other CDBs and discovered that this has happened to all of them. Clearly this is a problem.

So since I can't check these documents in by using the web interface, and they don't show up in SharePoint Designer either, how can I use a SQL query to force a check-in of these files?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

YOU SHOULD NOT MODIFY A CONTENT DATABASE USING SQL

Even reading from the content databases is not considered good practice, you may cause locks which will cause SharePoint to fail.

The moment you change anything in a SharePoint database (except the usage ones) your SharePoint installation becomes unsupported.

Loop through the pages/files using PowerShell and check them in, but don't use SQL.

Read: Support for changes to the databases that are used by Office server products and by Windows SharePoint Services and yes it applies to 2010.

An example of checking in a file using Powershell could be:

$web = Get-SPWeb http://sharepoint
$file = $web.GetFile("Shared Documents/MyDoc.docx")
$file.CheckIn("Checked in using PowerShell", [Microsoft.SharePoint.SPCheckinType]::MajorCheckIn)
share|improve this answer
    
PowerShell sounds promising. I didn't realize you could use PS to force item check-ins. Do you have any recommendations for good resources about the best way to do that? –  newuser Apr 10 '12 at 18:34
    
I'm having some trouble finding good resources for PowerShell online. Ideally I'd like to programatically identify files that are checked-out to a specific user and only check-in those files, and I'd like my function to check an entire document library in one go, if possible. Can you give me any pointers to helpful resources for PS methods? –  newuser Apr 11 '12 at 12:40
1  
Here is an example of finding all checked out files –  Per Jakobsen Apr 11 '12 at 13:08
1  
Has AD.DeleteTransactionId any value for these? If so then they're deleted –  Per Jakobsen Apr 12 '12 at 18:55
1  
0x isn't deleted, but those that have something after has been deleted and are now in one of the recycle bins –  Per Jakobsen Apr 12 '12 at 19:04

EDIT: Didn't see Per's edit in time!

I am going to post this as an answer, but you really should select Per Jackobson's as the accepted answer to the question. Here is how you can check in documents in library using PoSh. You can also used the PowerShell shell window to examine these files a little more closely. Test the code as I am posting from memory...

$web = Get-SPWeb "http://domain.com"
$list = $web.Lists["Library Name"]

foreach($item in $list.Items)
{
    if($item.File.CheckedOutType -ne "None")
    {
        $item.File.CheckIn()
        $item.File.CheckInComments = "Automated checkin."
        $item.Update()
    }
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.