Migrate data from one SQL table to another database table

Sometimes you may have to migrate data from one SQL database to another SQL database on another environment, e.g. Live to test and dev environment and vice versa.

You may argue the easiest solution is a migration of the backup of database. Unfortunately it does not work well in environments where data in Live is classified as sensitive and are not allowed to be copied across into a dev / test machine where the security is not as strict as a live machine. There are some data from live which are not classified as sensitive or restricted and might have to be migrated to Test and DEV for testing or development purposes, for example list of values.

A copy of the data can be exported as a csv or dat file from the SQL Table using “select * from table_value” statement from source database.

The data can be bulk imported into a temp table and MERGE statement can be used to insert missing records and update records. The sample script which you can use is below.

The script can be downloaded from TechNet.

Delete all list Item and file versions from site and sub sites using CSOM and PowerShell

The versions property is not available from client object model on the ListItem class as with server object model.  I landed on the article SharePoint – Get List Item Versions Using Client Object Model that describes how to get a list item versions property using the method GetFileByServerRelativeUrl by passing the FileUrl property. The trick is to build the list item url as “sites/test/Lists/MyList/30_.000” where 30 is the item id for which the version history needs to be retrieved. Using that information I created a PowerShell to loop through all lists in the site collection and sub sites to delete all version history from lists.

The script below targets a SharePoint tenant environment.

Please note that I have used script Load-CSOMProperties.ps1 from blog post Loading Specific Values Using Lambda Expressions and the SharePoint CSOM API with Windows PowerShell to help with querying object properties like Lambda expressions in C#. The  lines below demonstrate use of the  Load-CSOMProperties.ps1  file which I copied in the same directory where the script DeleteAllVersionsFromListItems.ps1 is.

         Set-Location $PSScriptRoot

        $pLoadCSOMProperties=(get-location).ToString()+”\Load-CSOMProperties.ps1″
       . $pLoadCSOMProperties

  •  Retrieve ServerRelativeURL property from file object

 Load-CSOMProperties -object $file -propertyNames @(“ServerRelativeUrl”); 

You can download the script from technet.

SQL script batch execution using sqlcmd in PowerShell

There is often a mismatch between needs of the development team (multiple discreet T-SQL files for separate concerns) and the release team (the requirement  for one step automated deployment) . The script bridges the requirement by using sqlcmd.exe to run a batch of SQL scripts.

A text file is used listing all sql files that need to run in a particular order to avoid errors which may occur if there are dependencies between the scripts. Instead of using a text file a number can be prefixed to the scripts based on the order they need to run.

The script expects two parameters –

  • Path of folder containing the set of T-SQL files (and the manifest file, see below)
  • Connection string

The script can be downloaded from technet gallery.

## Provide the path name of the SQL scripts folder and connnection string
##.\SQLBatchProcessing.ps1 -SQLScriptsFolderPath "C:\Sql Batch Processing\SQLScripts" -ConnectionString "DEV-DB-01"
Param(
[Parameter(Mandatory=$true)][String]$ConnectionString ,
[Parameter(Mandatory=$true)][String]$SQLScriptsFolderPath
)
Set-ExecutionPolicy -ExecutionPolicy:Bypass -Force -Confirm:$false -Scope CurrentUser
Clear-Host
#check whether the SQL Script Path exists
$SQLScriptsPath = Resolve-Path $SQLScriptsFolderPath -ErrorAction Stop
#a manifest file will exisit in the SQL scripts folder detailing the order the scripts need to run.
$SQLScriptsManifestPath = $SQLScriptsFolderPath + "\Manifest.txt"
#Find out whether the manifest file exists in the the SQL Scripts folder
$SQLScriptsManifestPath = Resolve-Path $SQLScriptsManifestPath -ErrorAction Stop


#if manifest file found iterate through each line , validate if corresponding SQL script exists in file before running each of them
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
Resolve-Path $SQLScriptsPath -ErrorAction Stop
}
$SQLScriptsLogPath = $SQLScriptsFolderPath + "\" + "SQLLog.txt"
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "Started processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
$text = "Running script " + $_.ToString();
Add-Content -Path $SQLScriptsLogPath -Value $text
sqlcmd -S "DEV-DB-01" -i $SQLScriptsPath | Out-File -Append -filepath $SQLScriptsLogPath
}
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "End processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""

Instantiate CRMServiceClient using current user’s login

The Get-CrmConnection method can be used to  return connection to a CRM instance. The syntax to call the method is

Parameter Set: OnLine
Get-CrmConnection [-OnLineType] <OnlineType> [[-Credential] <PSCredential> ] 
[-DeploymentRegion] <String> [[-ProfileName] <String> ] -OrganizationName <String> [ <CommonParameters>]

Parameter Set: OnPrem
Get-CrmConnection [-ServerUrl] <Uri> [[-Credential] <PSCredential> ] 
[-OrganizationName] <String> [[-HomeRealmUrl] <Uri> ] [[-ProfileName] <String> ] 
[ <CommonParameters>]

Parameter Set: UIOnly
Get-CrmConnection [[-InteractiveMode]] [ <CommonParameters>]

I wanted to get the crm connection with the current user’s credentials without any prompts. The first and second options required the object PSCredential which can’t be created using logged current user’s credentials. The third option with the switch InteractiveMode  displays a dialog box prompting to enter connection details. All three options were not appropriate for the requirement.

The method returns the object Microsoft.Xrm.Tooling.CrmServiceClient. From the msdn article, it can be constructed using the NetworkCredential object.

The constructor’s definition in C#

public CrmServiceClient(
	NetworkCredential credential,
	AuthenticationType authType,
	string hostName,
	string port,
	string orgName,
	bool useUniqueInstance = false,
	bool useSsl = false,
	OrganizationDetail orgDetail = null
)

 

In PowerShell, the current user’s credentials can be retrieved using [System.Net.CredentialCache]::DefaultNetworkCredentials.

There is no way the [System.Net.CredentialCache]::DefaultNetworkCredentials can be converted to the PSCredential object.

The below syntax can be used to create the Microsoft.Xrm.Tooling.Connector.CrmServiceClient using logged in user’s credentials. Replace the variables $serverName, $serverPort, $organizationName with the respective values.

 $crmConnection = New-Object `
 -TypeName Microsoft.Xrm.Tooling.Connector.CrmServiceClient `
 -ArgumentList ([System.Net.CredentialCache]::DefaultNetworkCredentials), 
 ([Microsoft.Xrm.Tooling.Connector.AuthenticationType]::AD),
 $serverName,
 $serverPort, 
 $organizationName, 
 $False,
 $False,
 ([Microsoft.Xrm.Sdk.Discovery.OrganizationDetail]$null)

Instantiate SharePoint Client Context using current user credentials in PowerShell

In C# managed code, SharePoint Client Context can be created using System.Net.CredentialCache to pass logged in user credentials.

ICredentials credentials = CredentialCache.DefaultCredentials;
clientContext.Credentials = credentials;

I could not find anywhere how to achieve it in PowerShell.

If using PnP PowerShell module, the switch parameter CurrentCredentials can be used with the cmdlet Connect-Online.

Connect-SPOnline -Url “http://dev-sp-001a:1214/Teams/Legal” -CurrentCredentials 
$ctx= Get-SPOContext

In most of my CSOM code without use of PnP I used to get current user name using [Environment]::UserName

[Environment]::UserName

I used to prompt the current user to enter password

$AdminPassword = Read-Host "Enter password: " -AsSecureString

Lately I discovered I could use  system.net.credentialcache in PowerShell  to pass current user credentials when instantiating the ClientContext object.

 $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl) 
 $Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
 $ctx.Credentials = $Credentials;
 $web = $ctx.Web
 $ctx.Load($web);
 $ctx.ExecuteQuery();

This means you can start Windows PowerShell as the user having appropriate permissions to the SharePoint Environment and run CSOM code without prompting credentials.

External Sharing of Documents with Nintex in SharePoint Online

The “Office 365 update item permissions” step available in Nintex Workflow updates item permissions. However if external users are given permissions using this step, it fails with message “failed to resolve user “. The issue with Office 365 update item permissions is that it does not allow updating permissions for external users.

With the SharePoint 2016 Remote API Sharing with external users can be achieved using method UpdateDocumentSharingInfo from class SPDocumentSharingManager which fortunately can be called using REST.

The tenant and site collection sharing property needs to be updated to either option “Allow external users who accept sharing invitations and sign in as authenticated users” or “Allow sharing with all external users, and by using anonymous links” to be able to add external users. If the first option is selected the user needs to be given access to the site.

allowsharingwithallexternalusers

The workflow will work against a list having columns Email and SharePointLink

listcolumnconfig

The first step in the workflow is to get the RequestDigest from targeted site collection. App Step to get request digest

The steps to add in the workflow are as below

  • Add “App Step” action
  • Add “Web Request” action. Fill in the following fields
    • URL: <siteURL> /_api/ContextInfo
    • Method : POST
    • Content type : text/xmlcontextinforequest
    • Body : Choose “Content” option  and enter “{}” in text field
    • UserName: Credentials who has access to targeted site
    • Password: password of above UserName
    • Store response content in : Create a variable “RequestDigest”
  • Add “Query XML” action. Fill in the following fields
    • XML source: Choose “Content” and pick variable “digest token”
    • XPath query: /d:GetContextWebInformation/d:FormDigestValuegetrequestdigest
    • Return result as :Text
    • Query result in : create a text variable strDigestInfo
  • Add “Log to History List” step. Print the strDigestInfo to make sure valid request digest are used.

Please note that workflow app permissions need to amended to give site collection full control access.

The second step is to call the UpdateDocumentSharingInfo REST API method to grant appropriate access to the external user.

appsteptoaddreadpermissiontodocument

  • Add “App Step” action
  • Add Build Dictionary step.  Rename is to “Request Headers”.
    • Add key “accept” and Value “application/json;odata=verbose”
    • Add key “content-type” and Value  “application/json;odata=verbose”
    • Add key “X-RequestDigest” and Value ‍{Variable:strDigestInfo}
    • Add Output to variable RequestHeadersPerm.

Request Headers build-request-headers

  • Add Build Dictionary step.  Rename is to “Build Metadata”.
    • Add key “type” and value “SP.Sharing.UserRoleAssignment”.
    • Output in variable MetadaPerm.

buildmetadata

  • Add Build Dictionary step.  Rename is to “Build UserRoleAssignments”.
    • Add key “__metadata” and Value of type Dictionary to variable MetadataPerm
    • Add key “Role” and value of type Integer 1. The Role property represents the level of permission you want to grant. Possible values are  1 =  View, 2 =  Edit, 3 = Owner, 0 = None.
    • Add UserId to ‍{Current Item:Email}
    • Add Output to UserRoleAssignments

build-userroleassignments

  • Add Item to Collection Step
    • Set Target collection to  collection variable RoleAssignmentCol
    • Set Index 0
    • Set Value to dictionary variable UserAssignmentts
    • Set Output to RoleAssignmentCol

roleassignmentstocollection

  • Add a Build  Dictionary step and label to “Build Parameters”
    • Add Key “userRoleAssignments” and Value of type Dictionary to variable RoleAssignmentCol
    • Add Key “resourceAddress” and Value ‍{Workflow Context:Current site URL}‍‍{Current Item:SharePointLink}
    • Add Key “validateExistingPermissions” and Value of type Boolean set to No
    • Add key “additiveMode” and Value of type Boolean set to Yes
    • Add Key “sendServerManagedNotification” and Value of type Boolean set to Yes
    • Add Key “customMessage” and Value “Document has been shared with you”
    • Add Key “includeAnonymousLinksInNotification” and Value of type Boolean set to Yes
    • Add Key “propagateAcl” and Value of type Boolean set to Yes

 

buildparameters

  • Add a Call Http Web Service step to assign permissions using SP.Sharing.DocumentSharingManager.UpdateDocumentSharingInfolistcolumnconfig
    • Set Address to ‍{Workflow Context:Current site URL}‍/_api/SP.Sharing.DocumentSharingManager.UpdateDocumentSharingInfo
    • Set Request Type to “Http Post”
    • Set Request Headers to variable RequestHeadersPerm
    • Set Request Content to variable ParametersPerm
    • Set Response Headers to variable ResponseContentPerm
    • Set Response Content to variable ResponseHeadersPerm
    • Set Response Status Code to ResponseStatusCode

 

callhttpwebservice_updatedocumentsharinginfo

You can use the Log to History List step to log the ResponseStatusCode,  ResponseContentPerm and ResponseHeadersPerm.

An email will be sent to the external user when run with a link to access the resource.

emailgeneratedwithlink