Powershell to report memory leaks and assembly info on SharePoint 2013/2010 custom solutions.


Recently I was involved in a task to troubleshoot performance related issues on a SharePoint farm. On analyzing the farm I found more than 50 WSP’s are deployed. On further analysis some of these solutions had two major performance related issues:

SharePoint Objects are not disposed properly:

Windows SharePoint Services object model contains objects which are not managed by ASP.Net windows garbage collector. Developers must take precautions when using these objects to avoid their long-term retention in memory in the Microsoft .NET Framework.

In scenarios in which you use SharePoint objects extensively—for example, in SharePoint sites that use custom Web Parts—you can cause the following unusual behaviors by not disposing of SharePoint objects when you are finished with them. This results in

  • Frequent recycles of the Windows SharePoint Services application pool, especially during peak usage
  • Application crashes that appear as heap corruption in the debugger
  • High memory use for Microsoft Internet Information Services (IIS) worker processes
  • Poor system and application performance

For more details refer this link


 Production solutions built in debug mode instead of release mode:

The default build setting during development is “Debug” mode and it is a common mistake to allow this development time setting to find its way onto production servers during deployment.  This can often leads to

  • The compilation of ASP.NET pages takes longer (since some batch optimizations are disabled)
  • Code can execute slower (since some additional debug paths are enabled)
  • Much more memory is used within the application at runtime
  • Improper recycles of App pools etc

More details here: http://weblogs.asp.net/scottgu/Don_1920_t-run-production-ASP.NET-Applications-with-debug_3D001D20_true_1D20_-enabled


Though the only known solution is re-build these custom WSP’s using best practices, its important to develop a detailed report on each custom assemblies on GAC and provide a report to fix these issues. So idea is to build a powershell script to

1) Export all the WSP solutions

2) Extract the WSP’s

3) Run SPDispose check on each DLL

4) Identify the assembly is in “Debug” or release mode

5) Export the report to CSV




Before running the script install SPDisposeCheck tool from here


Download this script here:


#Creates SP Dispose report on all WSP's

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

$wspdir = "D:\WSP\WSPReport1" #Location to export all WSP's
$exportdir = "$wspdir\Export-$((get-date).toString('yyyyMMdd'))\"
$CSVLocation = "$wspdir\SPDisposeResults.csv"

#create directory

#Function to export all WSP's
function Export-AllWSPs
 Write-Host Exporting solutions to $wspdir
 foreach ($solution in Get-SPSolution)
 $id = $Solution.SolutionID
 $title = $Solution.Name
 $filename = $Solution.SolutionFile.Name
 Write-Host "Exporting $title to ¦\$filename" -nonewline
 try {
 Write-Host "– done" -foreground green
 Write-Host "– error : $_" -foreground red


#Function to extract all WSP Packages
function Extract-AllWSPs
 #Retrieve the wsp files in this folder and subfolders
 $s = [system.IO.SearchOption]::AllDirectories
 $fileEntries = [IO.Directory]::GetFiles($wspdir,"*.wsp",$s);
 foreach($fullFileName in $fileEntries)
 $fileName = $(Get-Item $fullFileName).Name;
 $dirName = $fileName.Replace(".wsp","");
 $extractPath = $exportdir + $dirName;
 $dir = [IO.Directory]::CreateDirectory($extractPath) 

 Write-Host "Export $fileName started" -ForegroundColor Red
 $destination = $extractPath
 C:\Windows\System32\extrac32.exe $fullFileName /e /Y /L $destination


#Function to get Assembly details
function Get-AssemblyCustomProperty
 $Property = $null

 $value = $null
 foreach ($attribute in $assembly.GetCustomAttributes($false))
 if ($attribute.GetType().ToString() -like "*$TypeNameLike*")
 if ($Property -ne $null)
 # Select-Object -ExpandProperty fails if property value is $null
 try {
 $value = $attribute | Select-Object -ExpandProperty $Property
 catch {
 $value = $null
 $value = $attribute


#Function to report SP Dispose check results
Function Get-SPDisposeResults()

 #Pause for 5 secs to ensure extract is complete
 Start-Sleep -s 5
 $Dir = get-childitem $wspdir -recurse
 $List = $Dir | where {$_.extension -eq ".dll"}
 $List | ForEach-Object {

 [string]$report = & "C:\Program Files (x86)\Microsoft\SharePoint Dispose Check\SPDisposeCheck.exe" $_.fullname
 #remove repetitive strings
 $report = $report -replace "Note: This tool may report errors which are not actually memory leaks, otherwise known as false positives. Further investigation should be done to identify and correct real errors. It is designed to assist developers in making sure their code adheres to best practices for memory allocation when using SharePoint APIs. Please see the following for more information: http://blogs.msdn.com/rogerla/ http://msdn2.microsoft.com/en-us/library/aa973248.aspx http://msdn2.microsoft.com/en-us/library/bb687949.aspx", ""
 $build = "RELEASE";
 try {
 $info = @{}
 $assembly = [Reflection.Assembly]::LoadFile($_.FullName)
 $attr = $assembly.GetCustomAttributes([Diagnostics.DebuggableAttribute], $false)
 $info.IsJITTrackingEnabled = Get-AssemblyCustomProperty -Assembly $assembly -TypeNameLike 'System.Diagnostics.DebuggableAttribute' -Property 'IsJITTrackingEnabled'
 #$info.IsJITOptimizerDisabled = Get-AssemblyCustomProperty -Assembly $assembly -TypeNameLike 'System.Diagnostics.DebuggableAttribute' -Property 'IsJITOptimizerDisabled'
 #$info.DebuggingFlags = Get-AssemblyCustomProperty -Assembly $assembly -TypeNameLike 'System.Diagnostics.DebuggableAttribute' -Property 'DebuggingFlags'
 #Write-Host $_.FullName: +" IsJITTrackingEnabled "+ $info.IsJITTrackingEnabled
 #Write-Host $_.FullName: +" IsJITOptimizerDisabled "+ $info.IsJITOptimizerDisabled
 #Write-Host $_.FullName: +" DebuggingFlags "+ $info.DebuggingFlags

 } catch {
 throw $_

 Write-Host $report

 # Create a hash table with all the data
 $hash = @{
 "Assembly" = $_.name
 "Report" = $report
 "Debug Mode enabled" = $info.IsJITTrackingEnabled

 # Convert the hash to an object and output to the pipeline
 New-Object PSObject -Property $hash

#Function calls
Get-SPDisposeResults | Export-Csv -NoTypeInformation -Path $CSVLocation


Azure hosted SharePoint apps using AngularJS and WebAPI – Part 3

This is final post of a three part series on exploring Azure hosted SharePoint apps using AngularJS and WebAPI.

Part 1: Why Cloud and AppModel?

Part 2: SQLAzure data via WebAPI:

Part 3: Azure hosted apps using AngularJS and WebAPI

Why AngularJS:

 Traditionally managed server-side programming used to develop enterprise apps. But now this pattern is changing in the enterprise application development industry.  It’s a change away from logic on the server side, and towards logic on the client side. To address these complex business logic Javascript should evolve from just a language to make DOM manipulations to powerful client side MVC/MVVM framework. AngularJS framework makes this shift at ease.





There are also few other frameworks on the same course but AngularJS is a clear leader owing to these facts:

1)       Dependency injection:

This is huge and my personal favorite. Using this, custom modules can be injected just like adding assemblies on the server side code. This makes life little easier design MVC/MVVM based projects with separation of concern, modularity etc. An Analogy to server side code is represented below




2)      HTML templating:

Over the past few years, Single Page App’s (SPA’s) are gaining of a lot of traction from front end developers and end users. HTML templating feature in Angular makes it possible to easily switch the HTML in SPA’s.

 3)      Two way binding:

Fellow Silverlight developers can understand the complexity of two-way binding using propertychange events back in the old days. But AngularJS databinding makes this right OOB

 4)      Community support:

Google continues to develop and maintain the library. Large community of developers has embraced the framework so ample support is available on Stackoverflow and AngularJS official site.

5)      Advance error handling and logging features

Why AngularJS on Provider hosted App:

 Instead of using server side code on Provider hosted app, client side code is used for a sole reason to utilize AngularJS framework. Some of the advantages include

  •  Single Page App (SPA)
  • Clean URL for easy navigation
  • Responsive design
  • No server side code
  • Scalable for further enhancements

Future enhancements may include:

1)       Adding Chrome controls to give SharePoint look and feel

2)      Authentication for WebAPI

3)      Functions to delete/update O365 and WebApi Data. etc


Design of this app is based on Hot Towel app from John Papa and the pluralsight course from Andrew Connell.

It requires a bit of learning to understand the AngularJS. Go through these resources for deep understanding




SPAngular APP:

1)       Create a new provider host app project in VS2013

2)      New Project -> App for SharePoint -> Provider Hosted App -> ASP.NET Web Forms Application -> Use Windows Azure Access Control Service ->finish


3)      Now click the web project -> Manage NuGet Packages


4)      Search “hot towel” and install HotTowel.Angular.



5)      Main benefit of using this project template is all the heavy lifting of implementing MVC project structure, logging, exception handling, angularJS, configuration comes OOB. This makes this bootstrap project easily extensible for custom functionalities.


6)      By default App project creates Query String tokens to store URL’s of Appweb , hostweb etc for reference in the future.


7)      AngularJS navigation is based on the URL manipulations. Its little hard to implement the navigation with these long URL’s. So the idea is to use a applauncherpage to store the Querystring values to cookies for future use and redirect to the main page with clean URL.

8)      Create applauncher.html and applauncher.js. Create an empty controller and all the JS references from index.html.

<!DOCTYPEhtml><!--// ** Step 1: Add AppLauncher:  **//-->



<title>App Launcher</title>


<bodydata-ng-controller="applauncher as vm">


<!--// ** Step 2: Add Angular libs:  **//-->

<!-- Vendor Scripts -->















<!-- Bootstrapping -->






<!-- common Modules -->





<!-- common.bootstrap Modules -->



<!-- app -->






<!--// ** Step 5: Create Add references **// -->

<!-- app Services -->








9)      In the applauncher.js call spappcontext angular service.

// ** Step 3: Add AppLauncher JS:  **//


'use strict';

var controllerId ='applauncher';

var app = angular.module('app');

app.controller(controllerId,['common','spappcontext', applauncher]);


function applauncher(common, spappcontext){

var getLogFn = common.logger.getLogFn;

var log = getLogFn(controllerId);


function activate(){

common.activateController([], controllerId)

.then(function(){ log('Activated App Launcher View');});





10)   Create SPappcontext.js and create createSPAppContext()and loadSPAppContext() functions to read the query string and store it to cookies. This also redirects to \index.html, a Single page app.


'use strict';

var serviceId ='spappcontext';

var app = angular.module('app');

app.service(serviceId,['common','$window','$location', spappcontext]);

function spappcontext(common, $window, $location){

var getLogFn = common.logger.getLogFn;

var log = getLogFn(serviceId);

var service =this;

var spweb ={





service.hostWeb = spweb;


function init(){

var test = jQuery.getQueryStringValue('SPHostUrl');










function loadSPAppContext(){

log('loading spcontext cookie');

service.hostWeb.providerUrl = $.cookie("ProviderUrl");

service.hostWeb.SPAppWebUrl = $.cookie("SPAppWebUrl");

service.hostWeb.SPHostUrl = $.cookie("SPHostUrl");


function createSPAppContext(){

log('writing spcontext cookie');

var ProviderUrl = $window.location.protocol +"//"+ $window.location.host;

$.cookie("ProviderUrl", ProviderUrl,{ path:'/'});

var appWebUrl = decodeURIComponent(jQuery.getQueryStringValue('SPAppWebUrl'));

$.cookie("SPAppWebUrl", appWebUrl,{ path:'/'});

var url = decodeURIComponent(jQuery.getQueryStringValue('SPHostUrl'));

$.cookie('SPHostUrl', url,{ path:'/'});

$window.location.href = ProviderUrl +"/index.html";




11)    Now add host web SP JS libraries to index.html to for make JSOM calls against host web.

<!--// ** Step 7: Add SharePoint libs for JSOM calls **// -->





Now add the following code to web.config remove the static file issue on described here http://rainerat.spirit.de/2012/09/03/SharePoint-2013-App-HTTP-Error-405.0/

<!--// ** Step: 8: To Resolve IIS Static file issue**//-->




<addname="AspNetStaticFileHandler" path="*" verb="*" type="System.Web.StaticFileHandler" />

<addname="StaticHTML" path="*.html" verb="GET,HEAD,POST,DEBUG,TRACE" modules="StaticFileModule" resourceType="File" requireAccess="Read" />




12)   Now use JSOM to call host web and REST calls to WebAPI. $q.defer() objects are used to  pass promise to the calling function. This enables clients to complete the DOM structure without waiting for results.( Host web contains a demo contact list with title “DemoCustomer”

// ** Step 6: Create Datacontext Service **//


'use strict';


var serviceId ='datacontext';

angular.module('app').factory(serviceId,['$rootScope','$resource','spappcontext','common', datacontext]);


function datacontext($rootScope,$resource, spappcontext,common){

var $q = common.$q;



var service ={

getPeople: getPeople,

getMessageCount: getMessageCount,

getUserName: getUserName,

getCustomers: getCustomers,

getSPContacts: getSPContacts



return service;


function getCustomers(){


var dfd = $q.defer();

var webApi = $resource('http://customerwebapi.cloudapp.net/api/Customer/:Id',{ Id:"@Id"},{ update:{ method:'PUT'}});

//   var webApi = $resource('http://localhost/AppWeb/api/Customer/:Id', { Id: "@Id" }, { update: { method: 'PUT' } });


var customers = data;







return dfd.promise;



function getUserName(){


var dfd = $q.defer();


// Standard function to get hostweb

var ctx =new SP.ClientContext(spappcontext.hostWeb.SPAppWebUrl);

var factory =new SP.ProxyWebRequestExecutorFactory(spappcontext.hostWeb.SPAppWebUrl);


var hostWebctx =new SP.AppContextSite(ctx, spappcontext.hostWeb.SPHostUrl);


var appWeb = ctx.get_web();

var hostWeb = hostWebctx.get_web();

var currentAppWebUser = appWeb.get_currentUser();

var currentHostWebUser = hostWeb.get_currentUser();








var userName = currentAppWebUser.get_title();


},function(sender, args){

console.log(args.get_message()+" "+ args.get_stackTrace());




return dfd.promise;



function getSPContacts()


var dfd = $q.defer();

// Standard function to get hostweb

var ctx =new SP.ClientContext(spappcontext.hostWeb.SPAppWebUrl);

var factory =new SP.ProxyWebRequestExecutorFactory(spappcontext.hostWeb.SPAppWebUrl);


var hostWebctx =new SP.AppContextSite(ctx, spappcontext.hostWeb.SPHostUrl);


var appWeb = ctx.get_web();

var hostWeb = hostWebctx.get_web();


var ContactList = hostWeb.get_lists().getByTitle('DemoCustomer');

var contactListItems = ContactList.getItems(SP.CamlQuery.createAllItemsQuery());




var enumerator = contactListItems.getEnumerator();

var SPContacts =[];


var currentItem = enumerator.get_current();

SPContacts.push({"FirstName": currentItem.get_item('FirstName'),"LastName": currentItem.get_item('Title')});


var SPContactsObj = SPContacts;



},function(sender, args){

console.log(args.get_message()+" "+ args.get_stackTrace());



return dfd.promise;




13)   Now modify dashboard.js to call data service functions defined above and add spinner while waiting for results.


'use strict';

var controllerId ='dashboard';

angular.module('app').controller(controllerId,['common','datacontext', dashboard]);


function dashboard(common, datacontext){

var getLogFn = common.logger.getLogFn;

var log = getLogFn(controllerId);


var vm =this;

vm.news ={

title:'Hot Towel Angular',

description:'Hot Towel Angular is a SPA template for Angular developers.'


vm.messageCount =0;

vm.people =[];

vm.SPContacts =[];

vm.Customers =[];

vm.title ='Dashboard';

vm.busyMessage ='Please wait ...';

vm.isBusy =true;

vm.spinnerOptions ={













function activate(){

var promises =[getMessageCount(), getPeople(), getSPContacts(), getCustomers()];

common.activateController(promises, controllerId)

.then(function(){ log('Activated Dashboard View');});



function getCustomers(){


return datacontext.getCustomers().then(function(data){


return vm.Customers = data;




function getMessageCount(){

return datacontext.getMessageCount().then(function(data){

return vm.messageCount = data;




function getPeople(){

return datacontext.getPeople().then(function(data){

return vm.people = data;





function getSPContacts(){


return datacontext.getSPContacts().then(function(data){

return vm.SPContacts = data;





function toggleSpinner(on){ vm.isBusy = on;}



14)   Now add remote endpoint http://customerwebapi.cloudapp.net to the appmanifest.xml


15)   Now navigate to /_layouts/15/appregnew.aspx to generate client Id and client secret. Note down these values.


16)   Now right click the web project and select publish to select a new publishing profile


17)   Seed the values of the ClientID and Client Secret noted earlier.


18)   Now select the created profile and deploy to Azure Website.


19)   Add a site name and click create


20)   Keep the default settings and click publish.


21)    Now package the app and upload the app to the Office 365 site.


22)   Now clicking the app will navigate to the provider hosted app with displays data from both Office 365 and WebAPI.


This project is hosted in codeplex for download: https://spangular.codeplex.com/ 

Azure hosted SharePoint apps using AngularJS and WebAPI – Part 2

This is second post of a three part series on exploring Azure hosted SharePoint apps using AngularJS and WebAPI.

Part 1: Why Cloud and AppModel?

Part 2: SQLAzure data via WebAPI:

Part 3: Azure hosted apps using AngularJS and WebAPI

SQLAzure data via WebAPI:

This is the continuation of this post on creating Azure hosted enterprise apps using AngularJS and WebAPI.

WebAPI is a framework for building RESTful applications which enables client browsers to tap in to database using HTTP  GET, POST, DELETE requests.  This involves following steps:

Note: Following steps require valid Azure subscription. Microsoft offers free credits for MSDN subscribers. http://azure.microsoft.com/en-us/pricing/member-offers/msdn-benefits-details/

1)       Create a SQL server instance in Azure management portal https://manage.windowsazure.com -> SQL Databases -> New



2)      Create SQL Database. I am using AdventureWorksLT2012 database for this demoapp. This is downloaded from http://msftdbprodsamples.codeplex.com/releases/view/55330 . After restoring this app on local instance, create a database in SQLAzure with the same name.


3)      Once the database is created, the properties can be accessed by clicking through it.


4)      Now migrate the SQL DB from local instance to SQL azure using SQLAzure Migration Wizard. http://sqlazuremw.codeplex.com/


5)      SQLAzure supports only a subset of SQL features. Some of the limitations are listed here

http://msdn.microsoft.com/en-us/library/azure/ff394115.aspx . XML Schema collections are not supported so uncheck it.


6)      Now click next to generate SQL script to create database. Script displays non supported items in RED.

Fix these items and re-generate the script until all these errors are cleared. Follow this article to resolve some of the most common errors http://oakleafblog.blogspot.com/2010/01/using-sql-azure-migration-wizard-v313.html


7)      Now run this script on SQLAzure instance by keying the connection details of Azure DB created earlier


8)      Now connect to SQLAzure using SSMS


9)      Now to expose the database via WebAPI.

To do this install Azure SDK by following this link


Install Entity Framework Powertools to convert SQL objects to .net entities


10)   Create new “Windows Azure Cloud Service” project. This creates a project with web role. Refer “Cloud Services” section in the article http://azure.microsoft.com/en-us/documentation/articles/fundamentals-introduction-to-azure/ to understand more on this. Basically this project creates a VM and IIS in Azure to host WebAPI web services on deployment.



11)    Now Right Click WebRole project -> Entity framework -> Reverse Engineer Code First and connect to SQLAzure DB created earlier. This will generate all the required entities.


12)   This generates table, table mapping and database context classes.


13)   Now create a controller of customer class using Scaffolding. Scaffold templates are used to generate code for basic CRUD operations for WebApi project against database using DB entity objects generated earlier.







14)   Add  navigate to App Start -> WebAPIConfig.cs and add the following code highlighted under step 2 to enable JSON

public static void Register(HttpConfiguration config)


// ** Step: 2: Enable JSON **//

// Web API configuration and services

var json = config.Formatters.JsonFormatter;

json.SerializerSettings.PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.Objects;


// Web API routes




name: "DefaultApi",

routeTemplate: "api/{controller}/{id}",

defaults: new { id =RouteParameter.Optional }


// ** Step: 3: Enable CORS **//


var cors =newEnableCorsAttribute("*", "*", "GET, POST, OPTIONS");




15)   Now Right Click WebRole Project -> Manage Nuget Packages ->Type Cors and install the WebAPI 2.1 cross origin support. This API is used to enable cross origin services for the WebAPI.



16)   Now add the code to highlighted under “step 3” enable CORS

17)   This will generate required WebApi controller to service Read, Delete and update operations. Right click WebApi project and publish.


18)   This will take few minutes to spin up a VM and publish these resources in Azure. Once the deployment is complete, data can be accessed from WebApi service.


19)   Now click the new cloud service created via Azure management portal


20)   Navigate to “Instances” tag and click “Connect” button to remote into VM


21)   Install “IP and Domain restrictions” role on the VM


22)   Now open IIS and Navigate to WebAPI service web and click “IP Address and Domain restrictions” and add all the client IP’s which requires access to WebAPI.



This project is hosted in codeplex for download: https://spangular.codeplex.com/ 





Azure hosted SharePoint apps using AngularJS and WebAPI – Part 1

This is first post of a three part series on exploring Azure hosted SharePoint apps using AngularJS and WebAPI.

Part 1: Why Cloud and AppModel?

Part 2: SQLAzure data via WebAPI:

Part 3: Azure hosted apps using AngularJS and WebAPI

 Why Cloud?

After SharePoint conference 2014 I became a supporter of cloud based infrastructure and app model. With the sophistication and attractive pricing models from cloud providers, most of the IT enablers started thinking about cloud fork lifts and migrating the existing apps, DB’s and other artifacts to cloud.

Why AppModel?

Over the past few years, App model rapidly became a standard in developing apps and adopted by few big names such as Facebook, Twitter, Dropbox, LinkedIn and yes Microsoft. App model enables custom apps to consume these services via REST\O-Data interfaces.  App authorization process is fairly similar across all these platforms where app registration process generates AppID and AppSecret. Then these custom apps call the services with these tokens for initial authorization.


What is different with SharePoint App Model?

Though all the other services provides JavaScript libraries to consume their resources virtually anywhere on the internet with basic xxx.client.authenticate() function, SharePoint\Office365 JSOM models requires SP.ClientContext() with appweb reference to access its resources. Behind the scenes it downloads ~appweburl/_layouts/15/AppWebproxy.aspx (server side code) in to an IFrame to check the request origin. If the client call originates from startpage which is not registered by the app, Office365 doesn’t service the request. This start page could be anything ranging from ASPX or PHP which supports HTTP GET and POST.


<?xml version="1.0" encoding="utf-8"?>



<App xmlns="http://schemas.microsoft.com/sharepoint/2012/app/manifest" Name="SharePointApp4" ProductID="{bd366f4e-9a41-4486-9328-476097e36187}" Version="" SharePointMinVersion="">






<RemoteWebApplication ClientId="929f258f-1a01-44ff-b7aa-57ff32755db9" />





Azure based enterprise Apps:

But still there are there are quite a few options to design cloud hosted apps. This article explains azure hosted enterprise app integrated with SQL azure. Basic architecture is shown below.


Some of the features are

1)       App is Hosted on Azure website

2)      Consumes WebApi services from SQL azure

3)      Utilizes AngularJS framework to create SPA’s

4)      Clean URL for easy navigation

5)      Responsive design using Hot Towel solution from John Papa

6)      No server side code

7)      Scalable for further enhancements






Powershell to send E-Mail alerts from DB


Send  an automated E-Mail every 30 mins to sweep all the accepted/rejected  items in DB table. C# cannot be used to achieve this since SMTP is not installed on web front end.


1) Install SMTP on DB server.

2)Initiate SMTP connection

3) Initiate DB connection string

4)Create T-SQL query to sweep all the updated items over past 30 mins

5) Construct E-mail message

6) Deploy the powershell as SQL agent job to run every 30 mins.


#Sends email on rejection or accepatance of reports

#variables start

#$errorFile = "C:\Script\RejectionErrorLog.csv"
$errorLimit = 1
$errorTime = 30 #Error Time in minutes
$rejectedUsers = $null #string array for rejected users
$acceptedUsers = $null #string array for rejected users
$tempDate = $null
#Gmail SMTP settings
$emailTo = "receipient@gsi.com"
$subject = "consent form notification"
$SMTPServer = "smtp.gmail.com" #using GMAIL SMTP address
$SMTPPort = "587"
$Username = "test01@gmail.com"
$Password = "password@123"

#AD SMTP settings
$emailTo = "receipient@gsi.com,receipient1@gsi.com,receipient2@gsi.com"
$subject = " consent notification"
$SMTPServer = "smtp-relay.us.gsi.biz" #using SMTP address
$SMTPPort = "25"
$Username = "sharepoint@gsi.com"
$Password = "p@ssword@123"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "server=localhost; database=ExtranetMembership; uid=ProviderUser; password=password@123;"
$SqlQuery = "select * from dbo.UserAdditionalInfo where TermsDisagreeDate < CURRENT_TIMESTAMP and TermsDisagreeDate > DateADD(mi, -$errorTime, Current_TimeStamp) or TermsAgreeDate < CURRENT_TIMESTAMP and TermsAgreeDate > DateADD(mi, -$errorTime, Current_TimeStamp)"

#variables end

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$rows = $SqlAdapter.Fill($DataSet)

if($rows -ge $errorLimit)

foreach ($Row in $DataSet.Tables[0].Rows)
 IF( $Row["Funds TermsAgreeDate"] -is [DBNull])
 $rejectedUsers += "`n"
 $rejectedUsers += "Consent form Rejected by: "
 $rejectedUsers += $Row["FileAs"] + " ("
 $rejectedUsers += $Row["Email"] + ") "
 #$rejectedUsers += "`n"
 IF( $Row["Funds TermsDisagreeDate"] -is [DBNull])
 #$acceptedUsers += "`n"
 $acceptedUsers += "Consent form Approved by: "
 $acceptedUsers += $Row["FileAs"] + " ("
 $acceptedUsers += $Row["Email"] + ") "
 #$acceptedUsers += "`n"
$Emailbody = @"
Hello Administrator,

Below listed user(s) agreed/disagreed consent forms.


$message = New-Object Net.Mail.MailMessage($Username, $emailTo, $subject, $Emailbody)
$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort)
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
#$smtp = New-Object Net.Mail.SmtpClient($SMTPServer)


Powershell to Change Modified data for published documents


Some of the documents are published or overwritten accidentally.  This made some of older documents show upfront in the ContentQuery or Content Search webparts which is configured sort latest.


To update the modified date  to an older date (created date) of the published documents and make sure the published status is not changed.


1) Identify the published date of accidentally overwritten documents

2) Disable any eventhandler associated with the document library

3) Use $listitem.UpdateOverwriteVersion() to change the modified date to created date

4) Enable event handler.


#Resets the modified date to created date for list of items when created date is between
# Jan 1 2014 to feb 1 2014 and modified date is April 4 2014
Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue

$web = Get-SPWeb -Identity "http://gsidev/sites/site"
$list = $web.GetList("http://gsidev/sites/list/")
$startDate = Get-Date "3/12/2014" #Select all the items with start date ranging from
$interval = [TimeSpan] "30.00:00:00" #Range in days
$setModifiedDate = Get-Date "5/2/2014" #with modified date
$Logfile = "C:\Scripts\ModifiedReport.log"

# End of variables

Function LogWrite
 Param ([string]$logstring)

 Add-content $Logfile -value $logstring

function UpdateModifiedDate {
 $folder = $web.GetFolder($folderUrl)
 Write-Host(" ")
 Write-Host("**-- Updating Carry holder folder: " + $folder.Name +" --*")
 LogWrite(" ")
 LogWrite("**-- Updating Carry holder folder: " + $folder.Name +" --*")

 foreach ($file in $folder.Files) {
 $listItem = $file.Item
 $modifiedDate = ([DateTime]$listItem["Modified"]).Date
 $createdDateTime = ([DateTime]$listItem["Created"]).DateTime
 $createdDate = ([DateTime]$listItem["Created"]).Date
 $days = $createdDate - $startDate 
 if(($days -ge 0)-and($days -le $interval)-and ($modifiedDate -eq $setModifiedDate))
 #if(($days -ge 0)-and($days -le $interval))
 if( $listItem.File.Level -eq "Published")
 Write-Host("--> Updating Carry holder file: " + $file.name +" <--")
 LogWrite("--> Updating Carry holder file: " + $file.name +" <--")
 $web.AllowUnsafeUpdates = $true
 $listItem["Modified"]= $createdDateTime
 $listItem.File.Publish("Added carryholder Name");
 $web.AllowUnsafeUpdates = $false
 Write-Host("--> Updating Carry holder file: " + $file.name +" <--")
 LogWrite("--> Updating Carry holder file: " + $file.name +" <--")
 $web.AllowUnsafeUpdates = $true
 $listItem["Modified"]= $createdDateTime
 $web.AllowUnsafeUpdates = $false

foreach ($folder in $list.Folders) {

Key takeaways from a SharePoint developer @ #SPC2014

Why this post?

I am sure there will be millions of blog posts summarizing #SPC2014 and the vision of Microsoft on driving the SharePoint community.  This blog post mainly covers new key areas a SharePoint developer should be focusing moving forward.

WSP or App?

Coming from the territory of On-premise/WSP developers I came to this conference with the doubts around WSP vs APP?…On-premise vs Office365…How mature is this app model?…Is this the right time invest my time on app development training or should I buy few more months until SharePoint API’s and app developer community gets more traction.. etc  After few hours in to the first day I realized that the whole conference is themed around app model and I slowly understood why Microsoft thriving us to evolve from WSP to Apps.

Why App?

Though deploying DLL’s in GAC is projected as the major reason for shifting to app model, the real X-factor IMHO is O-Data and REST. Though these terms may sound intimidating,  O-Data and REST protocols are the industry standards for data exchange for quite some time and adopted by fellow software giants such as Google, Facebook etc. Most important feature of REST service is the ability to query any granular element from any O-Data compliant data-source with simple ajax http get/post calls from the browser eliminating any server dependence. Since most of the data sources including SharePoint is O-Data compliant, data can be accessed from outside in a contrasting way to server side solutions where sp webapp worker process do all the heavy lifting. So to isolate the app web worker process and access O-Data services paradigm change to app solution packaging and hosting becomes inevitable.

As my thought process towards open inter-operability standards expanded, its a no-brainer to understand app-model is the game-changer and it opened many doors to integrate SharePoint with other systems. Now being determined to learn app model, I took most of the developer sessions to understand the big-picture of this model.

So the idea is to learn best practices/tips to retrieve the data and present it using the app model:

App model and SharePoint apps

Since Infopath is going to suffer a slow death and list forms are not going to provide rich GUI its all going to be all HTML5 forms to send data to SharePoint. And the days of designing page long HTML forms accompanied with a javascript is gone. Its high time for us to welcome AngularJS. For starters AngularJS  is a open source MVW (Model View Whatever) framework from Google which let us to create dymamic HTML web templates with two way binding. This video is a good reference to understand basic concepts of AngularJS. Outcome of this pattern is extraordinarily expressive, readable, and quick to develop Web forms which will be added as app pages in the app solution.

Microsoft upgraded most of their office 365 REST API’s to get data out of SharePoint online and Exchange online from any platform such as Android/IOS. This link is worth having a look.

So the future SharePoint project life cycle is going to evolve from WSP’s to developing webpages using AngularJS packaged in to an app which is running under different worker process and utilizes Office 365 REST API’s for data transfer.

This post from SharePoint MVP Jeremy Thake explains a little about this model.

Preparing Business Line Apps to integrate with SharePoint:

Now with these changes to SharePoint apps, data providers to SharePoint needs to feed the data via custom REST services. The days of developing web parts/_layouts and calling store procedures directly from data access layers is over now. Data providers such as SQL/Oracle now should be wrapped with REST/ODATA compliant HTTP web services to expose them to browsers/mobile etc. so that CRUD REST calls can be easily made from AJAX JS libraries. ASP.NET Web API is a framework for building web APIs on top of the .NET Framework using MVC patterns.

This solution from SharePoint MVP Scot Hillier is an example of this implementation. Now with data services ready, BCS apps can be used to connect to SharePoint.

Presentation and Mobile support:

Another big gotcha for a developer is to rethink  the UI design strategy to be “Mobile First”. Which means the CSS design process should start from devices to browsers. With the high expectations from the user community for SharePoint sites with interactive and easily available from any device with any form factor. To service such a complex requirement, Responsive Web Design (RWD) is introduced to leverage CSS3 and HTML5.

SharePoint specific RWD frameworks should be adopted to aid such a need.



Follow this blog from Eric Overfield, a leading expert in SharePoint Branding to learn more about this.

Also to help with the CSS design workloads, SASS (Syntactically Awesome StyleSheets) language built on RUBY which compiles into CSS. This enables to use variables, nested rules etc on CSS.