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


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


Wednesday, April 13, 2016

SharePoint 2016 Installation Guide - Step by Step

SharePoint 2016 is in RTM finally with all new features. Lets walk-through SharePoint 2016 installation process step by step:

System Requirements: What's my Server Farm Configuration?
Here is my SharePoint 2016 farm's server configuration (Hardware/software).
  • Windows Server 2012 R2 Standard as Server Operating system and SQL Server 2014 Standard SP1 as my SQL Server instance. 
  • All my servers running with 24 GB of RAM (minimum requirement: 16 GB. For single server installation: 24 GB) and Quad core processors. (64 bit of course!) and 120 GB in system drive and 250 GB as secondary drive (Min: 80 GB for system drive and 100 GB for applications).
Service Accounts:
  • Crescent\SP16_Setup - Setup  account used for installation. You must manually assign Member of Local Administrator group on all SharePoint Servers and SQL server and SysAdmin role at SQL Server instance. (In fact, it just needs: DBcreator & SecurityAdmin server roles and DBO rights on each SharePoint Databases).
  • Crescent\SP16_Farm - Farm account / Database access account. Necessary rights will be automatically granted on the SQL Server instance when you run SharePoint products configuration wizard.
Whats new in SharePoint 2016 Installation?
Its pretty much same as SharePoint 2013 installation, except the "Server Role" selection page. MinRole is introduced newly in SharePoint 2016.

SharePoint 2016 Server installation steps at high level:
  1. Install SharePoint 2016 prerequisites
  2. Install SharePoint  Server 2016
  3. Run Products configuration wizard
    SharePoint 2016 Server Farm Topology:
    Here is my small server farm topology for SharePoint 2016 with three servers. All these servers joined to my domain: Crescent.com
    Where to start Installation? Start with your Application Server! Your Application server hosts SharePoint 2016 central administration site and other necessary service applications.

    Step 1: Install Prerequisites

    We need to install SharePoint 2016 prerequisites before installing SharePoint Server 2016. Login with Setup account and start your SharePoint Server 2016 installation by installing the prerequisites. Prerequisites installer will add all necessary server roles (Application Server Role, Web Server (IIS) Role) ,downloads and installs all necessary components for SharePoint 2016.
    • Mount the ISO on the drive, launch "default.hta" file (or prerequisiteinstaller.exe). You'll see the same old Splash screen! Click on "Install software prerequisites" link under "Install"
      sharepoint 2016 installation guide
    • Accept License agreement and click "Next" button
      sharepoint 2016 installation step by step
    • Wait for the installation to proceed
      sharepoint 2016 install instructions
    • Prerequisites installer takes some time and will prompt for restart. After restart, re-run the prerequisites again! Restart and resume back.
    • Wait for the installation completed message. Click on "Finish" button to complete prerequisites installation.
      sharepoint 2016 install standalone

    Step 2: SharePoint Server 2016 Installation

    • After completing prerequisites installation, run the splash.hta file again and click on "Install SharePoint Server". sharepoint 2016 installation steps
    • You'll be prompted to enter the product key. I've used the 180 day Trial key: NQGJR-63HC8-XCRQH-MYVCH-3J3QR
      how to install sharepoint 2016 step by step
    • Accept license agreement
      install sharepoint server 2016
    • Choose a file location: Choose a file location for SharePoint Server and Search server index. Make sure there is sufficient disk space on the selected drive. You can use your secondary drive to store search index files. Click on "Install Now" button to start the installation process.
    • Wait for installation to complete. The installation wizard takes few minutes to complete.
    • Once completed, You'll be taken to Run Configuration wizard page. Tick the "Run the SharePoint Products Configuration Wizard now" check box and click on Close.

    Step 3: Run SharePoint Products Configuration Wizard

    • You'll be presented with the "Welcome to SharePoint Products" wizard. Run SharePoint Products configuration wizard by clicking "Next" button.
    • Click Yes for products configuration wizard restart services confirmation
    • On Connect to a server farm, choose Create a new server farm. If you are already have an existing SharePoint farm created, you'll have to choose the option "connect to an existing server farm". We'll be selecting this when we run products configuration wizard on our WFE server.
    • Specify configuration database settings: Enter your Database server name and proposed SharePoint farm's configuration database names. Enter the Farm account which will be used for database access.
    • On Farm security settings page, enter the Passphrase. You'll need this when you need to join the new server to an existing SharePoint server farm.
    • Specify Server Role. There are six roles listed in this page. You can select whichever applicable to you. This is critical as in previous versions:  If you selected Single-Server Farm, then you can't scale out your SharePoint farm to Multi-Server environment! So if you plan to extend your farm to Multi-Server farm in future, make sure you have selected the Custom (MinRole) or Application server role. In my case, I've selected Application server role. Click on Next to proceed.
    • Enter the Port for SharePoint 2016 Central Administration site. Choose the authentication provider. Click Next to continue.
    • You'll see SharePoint products configuration wizard performing several tasks as in the below progress screen. Wait until all 10 installation tasks to complete.
    • Once completed, SharePoint products configuration wizard presents you the verify screen. You can click Back in case, you want to change any setting. Click Next to proceed.
    • Wait for the Configuration successful page. Click on "Finish" to complete SharePoint 2016 installation process.
    Once you click "Finish" button, you'll be taken to Initial Farm Configuration Wizard page in SharePoint 2016 Central Administration site.

    Repeat all these steps in your Web Front end Server. Choose "Front-End" Server role for Web Front end. Once done, you can proceed with creating web applications, Service applications and site collections.

    FAQ's and Known issues in SharePoint 2016 Installation:
    • Can I use a Single Server for SharePoint 2016 installation?
      Sure, You can use a single server. However, You need to have SQL Server 2014 installed on the server first! Unlike SharePoint 2013, SharePoint 2016 doesn't install SQL Server Express automatically as part of Single server mode.
    • Prerequisiteinstaller.exe: The system cannot find the drive specified
      This happens when you mount an ISO file and after server reboot, it doesn't get mounted automatically. Remedy is simple: Mount the ISO and run:Start "Launch SharePoint preparation tool" "E:\Prerequisiteinstaller.exe" /continue


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


    Tuesday, March 15, 2016

    SharePoint 2016 RTM is Released!

    So the waiting period is over! SharePoint 2016 is in its RTM now!
    sharepoint 2016 RTM download

    Download SharePoint 2016 RTM Trial from here: https://www.microsoft.com/en-us/download/details.aspx?id=51493

    180 day Trial key: NQGJR-63HC8-XCRQH-MYVCH-3J3QR

    What's New: Fast Site Collection Creation, Durable Links, Hybrid Features, MinRoles, Large File support, etc.

    What's Lost: No more SharePoint Foundation, No Stand-alone Install Mode (with SQL Express), No FIM, etc.

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


    Sunday, March 13, 2016

    The Database name located at the specified database server contains user-defined data.- Error in SharePoint Products Configuration Wizard

    Error:
    SharePoint Products Configuration Wizard:  
    The Database name located at the specified database server contains user-defined data. Databases must be empty before they can be used. Delete all of the tables, stored procedures, and other objects, or use a different database.
    The Database name located at the specified database server contains user-defined data

    Root cause:
    This is due to the fact, previous installation failed in between and left the SharePoint_Config database in SQL Server!

    Solution:
    Delete the SharePoint_Config database created by the previous installation!


    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...