SharePoint - unlock a file that is exclusively locked by another user

It appears that both SharePoint 2010 and 2013 still have the bug to potentially lock a file even away from the very person who last checked out the file.

This buggy behavior is described in this Microsoft article.

Basically, when a document in SharePoint is opened by a client program like Word, Excel, SharePoint places a lock on the document on the server. The write lock times out after 10 minutes. Users cannot modify the document during the time when the document is locked.

In a scenario where the program (Word, Excel, etc) that opens the document unexpectedly quits or crashes and you try to open the document again before the write lock times out (10 minutes), you may receive the message that says the document is (exclusively) locked by another user. Even if you are the last person who opened the document, you may receive this message, too.

Now the problem may continue even if you wait for 10 minutes. When the document is coninued to be in locked status, we can use Powershell script to remove the lock as described here.

## To find out about locked info of the file
$web = Get-SPWeb "https://sharepoint.mycompany.com/sites/mysite"
$list = $web.Lists["Shared Documents"]
$item = $list.GetItemById(3)
$file = $item.File
$file

The LockType property from script above may show "None", yet the file may still be locked away from the last person who opened it. Now use Powershell to clear the lock.

$fileTime = New-Object System.TimeSpan(10000)
## Create a new file lock on the file
$file.Lock([Microsoft.SharePoint.SPFile+SPLockType]::Exclusive, "Test Lock", $fileTime)
## Remove the lock from the file
$file.UndoCheckOut()

If the script is actually locked by another user and the lock is not being released for any reason, you can use the following script to remove the lock via impersontion, too.

$web = Get-SPWeb "https://sharepoint.mycompany.com/sites/mysite"
$list = $web.Lists["Shared Documents"]
$item = $list.GetItemById(3)
$file = $item.File
$userID = $file.LockedByUser.ID
$user = $web.AllUsers.GetByID($userID)

$impersonatedSite = New-Object Microsoft.SharePoint.SPSite($web.Url, $user.UserToken)
$impersonatedWeb = $impersonatedSite.OpenWeb();
$impersonatedList = $impersonatedWeb.Lists[$list.Title]
$impersonatedItem = $impersonatedList.GetItemById($item.ID)
$impersonatedFile = $impersonatedItem.File
$impersonatedFile.ReleaseLock($impersonatedFile.LockId)

Change Title (linked to item with edit menu) to a different column in SharePoint List

By default, the automatically created Title column of custom SharePoint List functions as the link to item detail view and carries the ellipse button (item edit menu).

In order to hide Title column and associate the "link to item with edit menu" to another column:

1. Go to List Settings --> Advanced Settings --> "Yes" on Allow management of content types. On the List Settings, go to Content Types --> Item --> Hide "Title" column. Afterwards, "Title" column will not appear in Create, Edit and Details forms.
2. Go to the View(s) correcponding to List View and hide "Title" column.
3. Use SharePoint 2013 Designer, open AllItems.aspx page or corresponding list page, look for <ViewFields> tag. Add linkToItem="TRUE" linkToItemAllowed="TRUE" listItemMenu="TRUE" to the column that should function as link to details view and carry the ellipse button (item edit menu). Highlighted column below is an example. Beware that these attributes and their values are case-sensitive!

<View Name="{EDF62A70-F0D9-4B36-B3C4-F35017C57868}" 
    DefaultView="TRUE" 
    MobileView="TRUE" 
    MobileDefaultView="TRUE" 
    Type="HTML" 
    DisplayName="All Items" 
    Url="/developer/Ken/Demo1/Lists/Classroom/AllItems.aspx" Level="1" 
    BaseViewID="1" 
    ContentTypeID="0x" 
    ImageUrl="/_layouts/15/images/generic.png?rev=23" >
    <Query><OrderBy><FieldRef Name="ID"/></OrderBy></Query>
    <ViewFields>
        <FieldRef Name="Title"  linkToItem="TRUE" linkToItemAllowed="TRUE" listItemMenu="TRUE" />
        <FieldRef Name="Subject"/>
        <FieldRef Name="Teacher"/>
    </ViewFields>
    <RowLimit Paged="TRUE">30</RowLimit>
    <JSLink>clienttemplates.js</JSLink>
    <XslLink Default="TRUE">main.xsl</XslLink>
    <Toolbar Type="Standard"/>
</View>

Recursive Example of Common Table Expression - GetAcronym

Below is an example of SQL function that takes text with space as separator and returns acronym with first letter of each seperated word.

Select dbo.GetAcronym('Hello World  Whats Up', ' ') 
/* There are 2 spaces between World and Whats */

will return HW WU

The function definition is as follows. It uses Common Table Expression with Union All to run a recursive query.

CREATE function dbo.GetAcronym
(
  @str varchar(4000), @separator char(1) = ' '
)
returns varchar(4000)
as
begin

declare @retVal varchar(4000)
if @separator is null
  begin
  set @separator = ' '
  end

;with tokens(p, a, b)
as
(
  select  1, 1, charindex(@separator, @str)
  union all
  select p + 1, b + 1, charindex(@separator, @str, b + 1)
  from tokens /* Recursive Query via Common Table Expression is used along with Union All */
  where b > 0
)

select @retVal = convert(
  varchar(4000), 
  (select substring (@str, a, 1) from tokens for xml path (''), type ), 
  1
)
return @retVal

end

Recursive example of Common Table Expression - SplitString

Below is an example of Recursive query using SQL Server's Common Table Expression.

declare @s varchar(2000)
set @s = 'Hello World SQL DBAs'

select *
from dbo.SplitString(@s, ' ');
go

will return

index	value
0	Hello
1	World
2	SQL
3	DBAs

Function definition using recursive query via Common Table Expression and Union All:

CREATE function [dbo].[SplitString] 
(
  @str nvarchar(4000), 
  @separator char(1)
)
returns table
AS
return (
        
  with tokens(p, a, b) AS 
  (
    select  1, 1, charindex(@separator, @str)
           
    union all
            
    select p + 1, b + 1, charindex(@separator, @str, b + 1)
    from tokens /* Recursive use of common table expression */
    where b > 0
    )
        
    select 
      p-1 as [index]
      ,substring(@str, a, case when b > 0 then b-a ELSE 4000 end) AS [value]
    from tokens
)