Karthik's SharePoint Blog

Tuesday, 12 January 2016

Powershell Workflow: Install Powershell Vesrion 4 and Import SQLPS module

I had a requirement to collect the required site and list details from SharePoint sites and storing in SQL database. I have developed Powershell scripts and Powershell Workflows(for Multiple threading) to get the better Performance and then to reduce execution time.

In my environment Powershell Version 3.0 has installed already in the Server. I implemented powershell workflow scripts as below,

GetSubSites.PS1 (Main script file calls other child PS1 files)

Add-PsSnapin Microsoft.SharePoint.PowerShell

## SharePoint DLL

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

$serverName="DEFxxxx"

$databaseName="SharePointAdminDB"

$invocation = (Get-Variable MyInvocation).Value

$directorypath = Split-Path $invocation.MyCommand.Path

function Main {

param
(
[parameter(mandatory=$true)][string]$serverName,

[parameter(mandatory=$true)][string]$databaseName,

[parameter(mandatory=$true)][string]$WebApplicationUrl

)

$webApp = Get-SPWebApplication $webApplicationURL

foreach($site in $webApp.Sites)

{
foreach($subWeb in $site.AllWebs)

{
IterateSubSites($subWeb)

$SelectSQLCmd="Select COUNT(*) as _count from SPSITES where WebapplicationUrl='"+$webApplicationURL+"' and SitecollectionUrl='"+$site.Url+"' and SubSiteUrl='"+$subWeb.Url+"'";

$recordExists = Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $SelectSQLCmd

if($recordExists._count -gt 0)

{

$SQLCmd="UPDATE SPSITES SET WebapplicationUrl='"+$webApplicationURL+"' where WebapplicationUrl='"+$webApplicationURL+"' and SitecollectionUrl='"+$site.Url+"' and SubSiteUrl='"+$subWeb.Url+"'";

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $SQLCmd

"Update: Site URL - "+$subWeb.Url

}
else

{
$SQLCmd="INSERT INTO SPSITES (WebapplicationUrl, SitecollectionUrl, SubSiteUrl) VALUES ('" + $webApplicationURL + "','"+$site.Url+"','"+$subWeb.Url+"')"

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $SQLCmd

"Insert: Site URL - "+ $subWeb.Url

}

}
$subWeb.Dispose()

}
$site.Dispose()

}

#Recursive function iterates all subsites

function IterateSubSites ([Microsoft.SharePoint.SPWeb]$subSite)

{
if ($subSite -ne $null)

{
if($subSite.Webs -ne $null)

{
foreach($subsites in $subSite.Webs)

{
IterateSubSites($subsites)

}

}

}

}

#Get all webapplications from SPWebapplications sql table
function GetAllWebApplicationsFromTable {

param
(
[parameter(mandatory=$true)][string]$serverName,

[parameter(mandatory=$true)][string]$databaseName,

[parameter(mandatory=$true)][string]$SPFarmName,

[parameter(mandatory=$true)][string]$SQLCmd

)
$DBConnectionString = "Data Source=$serverName; Initial Catalog=$databaseName; Integrated Security=SSPI"

$conn = New-Object System.Data.SqlClient.SqlConnection($DBConnectionString)

$conn.Open()

$cmd = new-object system.data.sqlclient.sqlcommand($SQLCmd,$conn)

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $cmd

$dsSites = New-Object System.Data.DataSet

$adapter.Fill($dsSites) | Out-Null

$conn.Close()

if($dsSites.Tables[0].rows.count -gt 0)

{
$ret=$dsSites.Tables[0]

foreach ($set in $ret)

{
$webAppUrl = $set.WebapplicationUrl

Main $serverName $databaseName $webAppUrl

}

}

}

　

　
Start-Transcript

Set-ExecutionPolicy Unrestricted

#get Farm Name and match with SQL records get webapplication urls
$SPFarm = Get-spfarm

$SPFarmName = $SPFarm.Name

#Iterate all site collections and sub sites, store in SPSITES table
GetAllWebApplicationsFromTable $serverName $databaseName $SPFarmName "select * from SPWebApplications where FarmName='$SPFarmName'"

#Iterate all list details and store in SPLISTS table
."$directorypath\GetListDetails.ps1" $serverName $databaseName

#Iterate all list details and store in SPSiteCollectionDetails table
."$directorypath\GetSiteCollectionDetails.ps1" $serverName $databaseName

#Iterate all list workflow details and store in SPListWorkflowDetails table
."$directorypath\GetListWorkflowDetails.ps1" $serverName $databaseName

#Iterate all list workflow details and store in SPListWorkflowDetails table
."$directorypath\GetSiteGroupsandUsers.ps1" $serverName $databaseName

While executing the PowerShell scripts it throws the Out of memory exception error because of the default memory size(MaxMemoryPerShellMB) is 150MB.

I have increased the default memory(MaxMemoryPerShellMB) to 4GB,

http://blogs.technet.com/b/heyscriptingguy/archive/2013/07/30/learn-how-to-configure-powershell-memory.aspx

However Windows Remote Management (WinRM) service does not use the customized value of the MaxMemoryPerShellMB quota. Instead, the WinRM service uses the default value, which is 150 MB.

https://support.microsoft.com/en-us/kb/2842230

https://social.technet.microsoft.com/Forums/windows/en-US/51810d3b-6236-44f8-99fd-10f004ad8002/winrm-quotas-setting-ignored-since-kb2506143

None of above is worked for me. So finally i have installed Powershell Version 4.0 and then installed required Features back for using Invoke SQL commands in the powershell window.

You can install Powershell Version 4.0(x64 or x86) and restart the Server,
https://www.microsoft.com/en-us/download/details.aspx?id=40855

To run invoke-sqlcmd, the below three files are required,
Microsoft® Windows PowerShell Extensions for SQL Server® 2012
Microsoft® SQL Server® 2012 Shared Management Objects
Microsoft® System CLR Types for SQL Server® 2012

https://www.microsoft.com/en-us/download/details.aspx?id=29065

Then navigate to "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS"

run .\SQLPS.PS1 file in SharePoint powershell window

Execute Import-Module SQLPS

At last i can able to execute SQL commands in the powershell Version 4.0

Wednesday, 2 December 2015

SharePoint 2013: Set Default Values Enterprise Keywords

We will create a Document Library:

Then we add two new columns. One Enterprise Keyword column and later a self-managed metadata column.

Now we modify the default view for AllItems.aspx to visualize the default values effect.

The result looks like this:

The standard behavior is that only Name, Modified and Modified by are filled by default.

Now we create a simple folder:

The result looks like this:

Now we choose the option “change default column values”. In the Settings page for default values we will see our Document Library folder structure:

Navigate to the folder where we will set default values:

Now just click on the column you want to set a default value. See the samples:

The result looks like this:

Now we are able to add a document by dragging and dropping into out folder with default settings:

After uploading is finished, we see that our document have the default values adopted.

So what do you expect if you will change the default settings? Just try it.

Now we have an additional Default Value for the column Enterprise Keywords.

First, we will have a look what will happen when an item changed after the Default Settings are changed. Therefore we Check Out our sample document and Check In again.

This update of the item have no effect on the Enterprise Keywords. The new default values are not published to a changed item.

So we will try it with a new document.

As you can see, the Default Settings for the folder only works for new items.

SharePoint : Resource File Deployment

Deployment of resource files with SharePoint has always been a little tricky. With MOSS we used features to provision resource files. With SharePoint 2010 it was a little easier but not enough.
There are blogs with some ideas how to make it easier to deploy resources in multiple Locations They did not anyways fill all my requirements.
I do not want to:
1. Use feature to deploy resources
2. Keep two copies of the same resource file in the project
3. Edit the manifest.xml in visual studio.
4. To have “Resources” subfolder inside App_GlobalResources folder.
With the help of the blog post above, I managed to create solution where you only keep one copy of the resource file and that it is easy to manage and easy to deploy to the whole farm.
Add a mappedfolder to your project and point it to 14\Resources folder. Then add your custom resource file(s) to it:

Add a Empty Element to your project and name it App_GlobalResources:

Delete the feature it created automatically, you will not need it:

Delete the Elements.xml file it automatically created, you will not need that:

Edit the SharePointProjectItem.spdata file inside the App_GlobalResources folder (If you cannot see it, press “Show All Files” button from the Solution Explorer). Replace the empty

node with the following xml, with your own path and file name:

If you add new resource files later remember to update this file too.

Double click the Package folder and add the App_GlobalResource item to the Package:

Build and deploy, all resource files will be deployed to 14Hive and WebApplication.
14Hive:

WebApplication:

When editing the resource files, just a normal Update-SPSolution command with PowerShell will update the resources to the whole Farm. I hope this helps someone struggling with the manual copies or other hacks with deployment of resource files.

Ref: http://www.sharepointblues.com/2012/05/28/sharepoint-2010-resource-file-deployment-done-easy/