Friday, March 29, 2013

PowerShell - List Data Migration

Scenario:
I had to migrate data from ListA to ListB with the condition that values in Created By, Create Date, Updated By and Update Date columns must not be changed in ListB.

So I wrote this little PowerShell script to do the job.

Solution:
cls;

[void][reflection.assembly]::Loadwithpartialname("Microsoft.SharePoint")

$Site=[Microsoft.SharePoint.SPSite]("http://sharepoint01:1100")
$SiteWeb = $Site.openWeb()
$SourceList = $SiteWeb.Lists["ListA"]
$DestinationList = $SiteWeb.Lists["ListB"]

$ItemsColl = $SourceList.items

write-host "Total items found: " $SourceList.items.count

foreach ($Item in $ItemsColl) 
{

    write-host "Copying Item: " $Item["Title"]

    $NewItem = $DestinationList.items.Add()
    $NewItem["Title"] = $Item["Title"]
    $NewItem["Author"] = $Item["Author"]
    $NewItem["Editor"] = $Item["Editor"]
    $NewItem["Modified"] = $Item["Modified"]
    $NewItem["Created"] = $Item["Created"]
    $NewItem.Update()

}

$Web.dispose
$Site.dispose

write-host "All items have been copied successfully."

Wednesday, March 6, 2013

SharePoint 2007 - Top 100 Versioned Documents via SQL Query

In our local SharePoint environment the Content Database's size was going out of control and we have to identify the documents/items with the highest number of versions so that we can delete them and apply some sort of versioning limit to the document libraries.

I came across a treasure of SQL Queries at this post where Syed Adnan Ahmed has already written few excellent queries to analyze the CDB. My fav is to view the Top 100 versioned documents. 
SELECT TOP 100
Webs.FullUrl As SiteUrl, 
Webs.Title 'Document/List Library Title', 
DirName + '/' + LeafName AS 'Document Name',
COUNT(Docversions.version)AS 'Total Version',
SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024 As 
   decimal(10,2))/1024) AS Decimal(10,2)) )  AS  'Total Document Size (MB)',
CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As 
   decimal(10,2))/1024) AS Decimal(10,2))   AS  'Avg Document Size (MB)'
FROM Docs INNER JOIN DocVersions ON Docs.Id = DocVersions.Id 
   INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 
AND (LeafName NOT LIKE '%.stp')  
AND (LeafName NOT LIKE '%.aspx')  
AND (LeafName NOT LIKE '%.xfp') 
AND (LeafName NOT LIKE '%.dwp') 
AND (LeafName NOT LIKE '%template%') 
AND (LeafName NOT LIKE '%.inf') 
AND (LeafName NOT LIKE '%.css') 
GROUP BY Webs.FullUrl, Webs.Title, DirName + '/' + LeafName
ORDER BY 'Total Version' desc, 'Total Document Size (MB)' desc

Tuesday, March 5, 2013

SharePoint 2010 - Security Trimming

I came across a requirement where I have to hide the Ribbon for the readers in SharePoint 2010.
Following is the snippit of the control to do the magic:

<Sharepoint:SPSecurityTrimmedControl runat="server" Permissions="ManageLists">
      Your ASP.NET control goes here
</SharePoint:SPSecurityTrimmedControl>

Permission values could be following:
  • ManageLists
  • ManageWeb

Official SharePoint Documentation

I have recently contributed to the official SharePoint documentation for developement. Check it out here: https://docs.microsoft.com/en-us...