Access Database of SQL Server using Powershell

 ################    Authentication Required to access DB    #########################

$dataSource = "SQLSERVER"

$user = "XYZ"

$pwd = 'XYZ@123'

$database = "Test" 

$conn = New Object System.Data.SqlClient.SqlConnection

("Server=$dataSource; uid=$user; pwd=$pwd; Database=$database; Integrated Security=False;")

$conn.Open(); 

$query = ("Select * from Test")

#####################################################################                     

$dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);                        

$dt = new-object System.Data.DataTable;                        

$dap.Fill($dt);

foreach($row in $dt.Rows)

{

    # Iterate each row 

}

Enable Nintex Workflow Feature.

$DestSite =  http://server:port/Site"

$SPWeb = Get-SPWeb -Identity $DestSite

Enable-SPFeature -Identity NintexWorkflowWeb -Url $SPWeb.Url 

Add Link in Quick Launch bar of a Sharepoint Site/Subsites.

Add-PSSnapin Microsoft.SharePoint.PowerShell 

$SPWeb = Get-SPWeb "http://server:port/Site"

$LinkURL = "http://google.com"

$QuickLaunch = $SPWeb.navigation.quicklaunch

$Node = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Google", $LinkURL, $true)

$SPSubsiteWeb.Navigation.QuickLaunch.AddAsLast($Node)

$SPSubsiteWeb.update()

Migrate Nintex Workflow from one Sharepoint List to another.

# Keep new/updated .nwf Files in a Physical Drive.

# Below code import .nwf file from a physical location and publish to a destination List by invoking “/_vti_bin/nintexworkflow/workflow.asmx” service

Add-PSSnapin Microsoft.Sharepoint.Powershell

############ Clear the contents of the Text File to maintain Logs #############

Write-Output "" > D:\NintexWFAutodeployment.txt

##################################################################

$SPDestSites = @("<Destination Site URL 1>","<Destination Site URL 2>","<Destination Site URL N>")

$Count = 0

foreach($SPDestSite in $SPDestSites)

{

$Count++

Write-Output "######## $($Count)) SiteURL: $($SPDestSite) #########"  >>  D:\AutoDeployment_Status\NintexWFAutodeployment.txt

     $DestSite = $SPDestSite

     $SPWeb = Get-SPWeb -Identity $DestSite

     $SPList = $SPWeb.Lists["Source List Name"]

     #Nintex Web Service URL

     $WebSrvUrl=$SPWeb.Url+"/_vti_bin/nintexworkflow/workflow.asmx"

     try{

         $proxy=New-WebServiceProxy -Uri $WebSrvUrl -UseDefaultCredential

     }

    catch{

    write-Output "Exception: $($_.Exception.Message)" >> D:\NintexWFDeployment.txt

     }

     $proxy.URL=$WebSrvUrl

############## Location of Workflow files #################

     $NWFcontent = Get-Content "D:\NintexWorkflowDeploy\MigrateNintexWF.nwf"

     #Location of  CreateTask_WF.nwf file

     $WorkflowName = "MigrateNintex_WF" #Workflow Name   

                                                           

       #####################################################

     $proxy.PublishFromNWFXml($NWFcontent, $SPList ,$WorkflowName, $true)

     Write-Output "Workflow Published to: "$SPList.Title >> D:\NintexWFDeployment.txt

    

}

Add a column in a Custom View of a Sharepoint List.

Add-PSSnapin Microsoft.Sharepoint.Powershell

$SPDestSites = @("<Destination Site URL 1>","<Destination Site URL 2>","<Destination Site URL N>")

foreach($Subsite in $SPDestSites)

{

    $SPWeb = Get-SPWeb -Identity $Subsite

    $SPList = $SPWeb.Lists["List Name"]

    $SPView = $SPList.Views["Custom View Name"]

    $SPView.ViewFields.Add("Title")

    $SPView.Update()

}

Hide a column in a Custom View of a Sharepoint List.

Add-PSSnapin Microsoft.Sharepoint.Powershell

$SPDestSites = @("<Destination Site URL 1>","<Destination Site URL 2>","<Destination Site URL N>")

foreach($Subsite in $SPDestSites)

{

    $SPWeb = Get-SPWeb -Identity $Subsite

    $SPList = $SPWeb.Lists["List Name"]

    $SPView = $SPList.Views["Custom View Name"]

    $SPView.ViewFields.Delete("Title")

    $SPView.Update()

}

Migrate Custom View along with its Fields/Columns from one Sharepoint List to Other.

Add-PSSnapin Microsoft.SharePoint.PowerShell

$SPSourceWeb = Get-SPWeb "http://server:port/SourceSite" # Source Site

$SPSourceList = $SPSourceWeb.Lists["Source List Name"]

$SPSourceListViewSchema = $SPSourceList.Views

$SPSourceListViewSchema = $SPSourceList.Views["Custom View"].SchemaXml

Write-Output $SPSourceListViewSchema > D:\View\CustomView.xml

$ViewTemplatePath = "D:\AutoDeployment\CustomView.xml"

$ViewTemplateXML = [xml](get-content $ViewTemplatePath)

$SPDestSites = @("<Destination Site URL 1>","<Destination Site URL 2>","<Destination Site URL N>")

$Count = 0

Write-Output "WorkFlow Task View" > D:\View\CustomViewErrorLog.txt

foreach($SPDestSite in $SPDestSites)

{

    try

    {

        $SPDestWeb = Get-SPWeb -Identity $SPDestSite

    

        $SPDestList = $SPDestWeb.Lists["Destination List Name"]

        $Viewfields = $WFTaskViewTemplateXML.View.ViewFields.FieldRef.Name

        $viewRowLimit = "100"

        $viewPaged = $true

        $viewDefaultView = $false

        # Setting the Query for the View

         $viewQuery = ""

         $viewName = "Workflow Task View"

        # Finally – Provisioning the View

         $myListView = $SPDestList.Views.Add($viewName, $viewFields, $viewQuery, $viewRowLimit, 

         $viewPaged,    $viewDefaultView)

        # Update the View for changes made to the view

         $myListView.Update()

         $SPDestList.Update()

         $SPDestWeb.Dispose()

    }

    catch

    {

   Write-Output "$($SPDestSite) ==> Exception: $($_.Exception.Message)" > D:\View\CustomViewErrorLog.txt

    }

}