Sunday, June 26, 2016

Export SharePoint List Data to SQL Server Table using PowerShell

Requirement:
For an in-house built business intelligence tool, had a requirement to export SharePoint list data into SQL Server table. As directly querying SharePoint content databases is not supported by Microsoft and it also could cause performance issues to your SharePoint farm - Lets not think about it further! Of course, there are 3rd party products available on the market to do this, however PowerShell can be utilized to fulfill this requirement.

This PowerShell script retrieves given SharePoint List(s), iterate through all available fields and dynamically creates SQL Server Table and then inserts the data after translating it, and inserts into the SQL Server database table.

Here is my PowerShell script to Convert SharePoint list into SQL Server table and extract data from SharePoint to SQL:

PowerShell Script to Extract SharePoint List Data into SQL Server Table:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

Function Create-List2Table($ListName,$TableName, $WebURL)
{
#Configuration Variables
$DatabaseServer="SP16-SQLServer01"
$DatabaseName="SharePointBI"
#SQL Authentication 
$UserID="UserID"
$Password="Password"

#Log File
$CurrentDir=Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
$LogFile = $CurrentDir+ "\" + $(Get-Date -format "yyyyMMdd_hhmmss")+".txt"

#Get Web, List and Fields
$Web= Get-SPWeb $WebURL
$List= $Web.Lists[$ListName]
#Get all required fields from the lists 
$ListFields = $List.Fields | Where { ($_.Hidden -ne $true ) -and ($_.ReadOnlyField -ne $true ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") }

#Function to Execute given SQL Query
Function Execute-SQLQuery([string]$Query)
{
    #Write-Host "Executing SQL: ", $Query
    #Connection object
    $Connection = New-Object System.Data.SqlClient.SqlConnection("server=$DatabaseServer;database=$DatabaseName;User Id=$UserID;Password=$Password")
    try
    {
        $Connection.open() 
        $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $Connection)
        $ReturnValue = $cmd.ExecuteNonQuery()
    }
    catch  { Write-Host "Error: ", $error[0];  "$($Query): $($_.Exception.Message)" >> $LogFile }
    finally{ $Connection.close() }

}

#Function to Drop Table, if exists!
Function Drop-Table([string]$TableName)
{
  $Query = "IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NOT NULL) DROP TABLE [dbo].[$($TableName)]"
  
  #Run the Query 
  Execute-SQLQuery $Query
}

#Get SQL column Definition for SharePoint List Field
Function Get-ColumnDefinition([Microsoft.SharePoint.SPField]$Field) 
{
    $ColumnDefinition=""
    Switch($Field.Type)
    {
      "Boolean" { $ColumnDefinition = '['+ $Field.InternalName +'] [bit] NULL '}
      "Choice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar](MAX) NULL '}
      "Currency" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "DateTime" { $ColumnDefinition = '['+ $Field.InternalName +'] [datetime] NULL '}
      "Guid" { $ColumnDefinition = '['+ $Field.InternalName +'] [uniqueidentifier] NULL '}
      "Integer" { $ColumnDefinition = '['+ $Field.InternalName +'] [int] NULL '}
      "Lookup" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "MultiChoice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] (MAX) NULL '}
      "Note" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] NULL '}
      "Number" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "Text" { $ColumnDefinition = '['+ $Field.InternalName +'] [nVarchar] (MAX) NULL '}
      "URL" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "User" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (255) NULL '}      
      default { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (MAX) NULL '}
  }
  return $ColumnDefinition
}

################ Format Column Value Functions ######################
Function Format-UserValue([object] $ValueToFormat)
{
     $Users = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $Users = $Users -replace "'", "''"
     return "'" + $Users + "'"
}

Function Format-LookupValue([Microsoft.SharePoint.SPFieldLookupValueCollection] $ValueToFormat)
{
     $LookupValue = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $LookupValue = $LookupValue -replace "'", "''"
     return "'" + $LookupValue + "'"
}

Function Format-DateValue([string]$ValueToFormat)
{
  [datetime] $dt = $ValueToFormat
  return "'" + $dt.ToString("yyyyMMdd HH:MM:ss") + "'"
}

Function Format-MMSValue([Object]$ValueToFormat)
{
  return "'" + $ValueToFormat.Label + "'"
}

Function Format-BooleanValue([string]$ValueToFormat)
{
  if($ValueToFormat -eq "Yes") {return 1} else { return 0}
}

Function Format-StringValue([object]$ValueToFormat)
{
  [string]$result = $ValueToFormat -replace "'", "''"
  return "'" + $result + "'"
}

#Function to get the value of given field of the List item
Function Get-ColumnValue([Microsoft.SharePoint.SPListItem] $ListItem, [Microsoft.SharePoint.SPField]$Field) 
{
    $FieldValue= $ListItem[$Field.InternalName]
    
    #Check for NULL
    if([string]::IsNullOrEmpty($FieldValue)) { return 'NULL'}
    
    $FormattedValue = ""
    
    Switch($Field.Type)
    {
    "Boolean"  {$FormattedValue =  Format-BooleanValue($FieldValue)}
    "Choice"  {$FormattedValue = Format-StringValue($FieldValue)}
    "Currency"  {$FormattedValue = $FieldValue}
    "DateTime"  {$FormattedValue = Format-DateValue($FieldValue)}
    "Guid" { $FormattedValue = Format-StringValue($FieldValue)}
    "Integer"  {$FormattedValue = $FieldValue}
    "Lookup"  {$FormattedValue = Format-LookupValue($FieldValue) }
    "MultiChoice" {$FormattedValue = Format-StringValue($FieldValue)}
    "Note"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "Number"    {$FormattedValue = $FieldValue}
    "Text"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "URL"  {$FormattedValue =  Format-StringValue($FieldValue)}
    "User"  {$FormattedValue = Format-UserValue($FieldValue) } 
     #Check MMS Field
     "Invalid" { if($Field.TypeDisplayName -eq "Managed Metadata") { $FormattedValue = Format-MMSValue($FieldValue) } else { $FormattedValue =Format-StringValue($FieldValue)}  }
    default  {$FormattedValue = Format-StringValue($FieldValue)}
  }
  Return $FormattedValue
}

#Create SQL Server table for SharePoint List
Function Create-Table([Microsoft.SharePoint.SPList]$List)
{
    #Check if the table exists already
    $Query="CREATE TABLE [dbo].[$($TableName)]([ID] [int] NOT NULL PRIMARY KEY, "
    foreach ($Field in $ListFields)
    {
        $Query += Get-ColumnDefinition($Field) 
        $Query += ","
    }
    $Query += ")"

    #Run the Query   
    Execute-SQLQuery $Query  
}

#Insert Data from SharePoint List to SQL Table
Function Insert-Data([Microsoft.SharePoint.SPList]$List)
{ 
    #Iterate through each row from the list 
    $ListItems= $List.Items # | where {$_["ID"] -eq 820}

    #Progress bar counter
    $Counter=0
    $ListItemCount=$ListItems.Count
    
    Write-host "Total SharePoint List Items to Copy:" $ListItemCount    
    foreach ($Item in $ListItems) 
    {

        Write-Progress -Activity "Copying SharePoint List Items. Please wait...`n`n" -status "Processing List Item: $($Item['ID'])" -percentComplete ($Counter/$ListItemCount*100)

        $sql = new-object System.Text.StringBuilder
        [void]$sql.Append("INSERT INTO [dbo].[$($TableName)] ( [ID] ")
        $vals = new-object System.Text.StringBuilder
        [void]$vals.Append("VALUES ("+ $Item["ID"])
        
        foreach ($Field in $ListFields) 
        {
            [void]$sql.Append(",[$($Field.InternalName)]")
            $ColumnValue =  Get-ColumnValue $Item $Field
            [void]$vals.Append( ","+ $ColumnValue)
        }
       
    [void]$sql.Append(") ")
    [void]$vals.Append(") ")
    
    #Combine Field and Values
    $SQLStatement = $sql.ToString() + $vals.ToString()
    
    #Run the Query   
    Execute-SQLQuery $SQLStatement 
    
    $Counter=$Counter+1;  
    }
"Total SharePoint List Items Copied: $($ListItemCount)" >> $LogFile
}

 #Call functions to export-import SharePoint list to SQL table
 Drop-Table $TableName
 Create-Table $List
 Insert-Data $List
}

#Call the function to Create SQL Server Table from SharePoint List
Create-List2Table -ListName "Projects" -TableName "ProjectData" -WebURL "https://portal.crescent.com/projects"
#Create-List2Table -ListName "Documents" -TableName "Documents" -WebURL "https://portal.crescent.com"

Make sure you have the database created (in my case, its "SharePointBI") and the User Name provided in the script has DBO or similar access on the database before running the script.

Credits: My sincere thanks to http://www.terrymarshall.com.au/Blog/tabid/162/EntryId/164/SharePoint-2010-Exporting-Lists-to-SQL-Server-Part-1.aspx

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, June 21, 2016

Add Site Collection Administrator to All Sites in SharePoint using PowerShell

Site collection administrators have God like Power within a Site Collection in SharePoint 2016. Apart from Primary & Secondary site collection administrators, we can add additional site collection administrator on any SharePoint site collection.

How to Add a Site Collection Administrator in SharePoint 2016?
To Add a Site Collection Administrator follow these steps:
  • Site Settings Gear >> Click on "Site Settings" Menu Item
  • Click on "Site collection administrators link under "Users and Permissions"
  • Enter the New user in Site Collection Administrators Field. Click OK to save your changes.
    PowerShell to Add Site Collection Administrator to All Sites in SharePoint
OK! Now, How about adding a same user as administrator in all site collections?

PowerShell Script to Add a Site Collection Administrator to All Site Collections in SharePoint:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#User Account to add as Site Collection Admin
$UserAccount="Crescent\Salaudeen"

Get-SPSite -Limit "All" | ForEach-Object { 
$User = $_.RootWeb.EnsureUser($UserAccount)
    if($User.IsSiteAdmin -ne $True)
    {
        $User.IsSiteAdmin = $True
        $User.Update()
        Write-Host "Added Site Collection Administrator for Site Collection:" $_.URL -ForegroundColor Green
    }
    else
    {
        Write-Host "User is already an Site Collection Administrator for Site Collection:" $_.URL -ForegroundColor Yellow
    }
} 
You can use Web Application User Policy in Central Admin to give Full control to the Entire web application, instead of adding users into individual site collections!


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, June 14, 2016

Fix "Unable to render the data. If the problem persists, contact your web server administrator." Issue in SharePoint BCS

Problem:
Tried created an external list SharePoint 2016 using business data connectivity services, configured secure store target application and created external list in SharePoint. All went good, however the external list displayed this error message with a correlation ID.
Unable to render the data. If the problem persists, contact your web server administrator.
Unable to render the data. If the problem persists, contact your web server administrator.
Ran ULS Log viewer and found this message in the logs:
Error while executing web part: Microsoft.BusinessData.Infrastructure.BdcException: The shim execution failed unexpectedly - Unable to obtain the application proxy for the context.. ---> Microsoft.Office.SecureStoreService.Server.SecureStoreServiceException: Unable to obtain the application proxy for the context.   

Solution:
The root cause of the problem is: The web application is not associated with the secure store service application. So, the solution is:
  • Go to SharePoint 2013/2016 Central Administration site
  • Application management >> Click on Configure service application associations under Service Applications.
  • Pick the web application in which your site exist, Check the "Application proxy group" column >> Make sure the BDC and secure store service applications check boxes checked.service application association in sharepoint 2016


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, June 12, 2016

Create Bulk Alerts from CSV File in SharePoint using PowerShell

Requirement: Create alerts on bunch of libraries in different site collections of a SharePoint Web application!

Solution: Identified and exported list of Sites and Document Library names in the CSV file and used this PowerShell script to create multiple alerts.

PowerShell script to crate bulk alerts from CSV File:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

#Function to Create alert on Given List to given users
Function Create-Alert([Microsoft.SharePoint.SPList]$List,[Microsoft.SharePoint.SPUser]$User)
{
    $newAlert = $user.Alerts.Add()
    $newAlert.Title = "Minitoring Alert for: "+$List.Title
    $newAlert.AlertType=[Microsoft.SharePoint.SPAlertType]::List
    $newAlert.List = $List
    $newAlert.DeliveryChannels = [Microsoft.SharePoint.SPAlertDeliveryChannels]::Email
    $newAlert.EventType = [Microsoft.SharePoint.SPEventType]::Add
    $newAlert.AlertFrequency = [Microsoft.SharePoint.SPAlertFrequency]::Immediate
    $newAlert.Update()
}

#Configuration parameters
$CSVPath= "C:\Alerts.csv"  
$UserAccount= "Crescent\Salaudeen" 
 
#Get the CSV Data
$CSVData = Import-CSV -path $CSVPath

#Iterate through each Row in the CSV
foreach ($row in $CSVData) 
{
    Write-host Creating Alerts on $Row.URL, Library: $Row.LibraryName
    #Get the Web and List
    $Web = Get-SPWeb $Row.URL.TRIM()
    $List = $Web.Lists[$Row.LibraryName.Trim()]  
    $user = $web.EnsureUser($UserAccount)
    
    #Call the function to create Alert
    Create-Alert $List $User    
}     
Here is my CSV File:
Create Bulk Alerts from CSV File in SharePoint using PowerShell


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


You might also like:

Related Posts Plugin for WordPress, Blogger...