kwizcom banner advertisement

How to Run SQL Server Query from PowerShell Script?

For a migration project, there was a requirement to read from SQL Server table data , process the migration and then insert / update the tables back from PowerShell. We got to run SQL Server query from PowerShell. Here is how to query SQL Server using PowerShell:
How to Run SQL Server Query from PowerShell Script

PowerShell to Run SQL Server Query
Function QuerySQLServer([string]$DBServer, [string]$DBName, [string]$Query)
{
 <#
  .SYNOPSIS
   Queries SQL Server from PowerShell
  .DESCRIPTION
  This PowerShell function Queries SQL Server from PowerShell
 .EXAMPLE
  QuerySQLServer "G1VWFE01" "MigrationDB" "SELECT [SourceSiteUrl], [TargetSiteUrl], [SiteType] FROM [MigrationData]"
      This example Gets table entries from the database "MigrationDB" in server "G1VWFE01"
 .INPUTS
  DBServer - Name of the Database Sever where the  target database is located
  DBName - Name of the Database from which the query to be executed
         Query - Query to Execute
 .OUTPUTS
  Rows from the prodefined table "MigrationData"
 #>

 try
 {
     $ErrorActionPreference = "Stop"
  
     #Connection object
     $cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=SSPI;Initial Catalog=$DBName")
     $cn.open() 

     #SQL Query to retrieve Table rows
     $cmd = new-object "System.Data.SqlClient.SqlCommand" ($Query , $cn)
     $reader = $cmd.ExecuteReader()
    
     #Process the Data
     while ($reader.Read()) 
     {
        #Iterate through Rows
        for ($i = 0; $i -lt $Reader.FieldCount; $i++) 
        {
            #Retrieve the Field (Column) values
            $Reader.GetValue($i)
        }
    }
 }
 catch
 { 
     #Write error message on screen and to a LOG file
     write-host $_.Exception.Message
     $_.Exception.Message >> "d:\error.log"
 }
 finally
 {
     $ErrorActionPreference = "Continue"
 }
}
#Call the function
QuerySQLServer "GIS-WFE01" "MigrationData"
     "SELECT [SourceSiteUrl], [TargetSiteUrl], [SiteType] FROM [MigrationSiteMapping]"
Its also possible to process the Queried data like:
 
  Function ProcessSQLServerData([string]$DBServer, [string]$DBName, [string]$Query)
     {
 try
        {
  $ErrorActionPreference = "Stop"
  
  #Connection object
  $cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=SSPI;Initial Catalog=$DBName")
  $cn.open() 

  #SQL Query to retrieve Table rows
  $cmd = new-object "System.Data.SqlClient.SqlCommand" ($Query , $cn)
  $reader = $cmd.ExecuteReader()
                #Process the Data
   while ($reader.Read()) 
    {
      for ($i = 0; $i -lt $Reader.FieldCount; $i++) 
      {
          #Retrieve the parameters from SQL Server "MigrationMapping" table
          $SourceSiteUrl = $Reader.GetValue(0)
          $TargetSiteUrl =$Reader.GetValue(1)
          $SiteType = $Reader.GetValue(2)

       #Check the SiteType
       if($SiteType  -eq "client")
       {
           Write-Host "Processing Client Site URL: $($SourceSiteUrl)"
                                                            #call a PowerShell function from an External script to process the given parameters
           MigrateClientSite $SourceSiteUrl $TargetSiteUrl
       }
       else #it a Case site
       {
           Write-Host "Processing Case Site URL: $($SourceSiteUrl)"
           #call a PowerShell function from an External script to process the given parameters
           MigrateCaseSite $SourceSiteUrl $TargetSiteUrl
       }
      }
    }
   }
 catch
        { 
     #Write error message on screen and to a LOG file
            write-host $_.Exception.Message
     $_.Exception.Message >> "d:\error.log"
        }
        finally
        {
            $ErrorActionPreference = "Continue"
        }
 }
 
The above PowerShell SQL Server query example retrieves and process the SQL server table rows.

PowerShell script Execute SQL Server Query which Performs Insert/Update:
Similarly, for Insert and update, the PowerShell script goes like:
Function RunSQLQuery([string]$DBServer, [string]$DBName, [string]$Query )
{
 <#
  .SYNOPSIS
   Executes SQL Query, such as Insert, Update, Delete, etc 
  .DESCRIPTION
   This PowerShell function executes the provided SQL query on the provided Database.
  .EXAMPLE
   powershell sql server query update: " RunSQLQuery "G1VWFE01" "MigrationData" "UPDATE [Table-name] SET [SiteStatus]='CREATED' WHERE [SiteID] = '$SiteID' AND [Category] = '$Category' "
                 powershell sql server query insert: use the SQL query as: "INSERT INTO [TableName] ( [SourceSiteUrl], [TargetSiteUrl],  [SiteType] ) VALUES ('http://sharepoint2007.crescent.com','http://sharepoint2010.crescent.com', 'case')"
  .INPUTS
   DBServer - Name of the Database Sever where the "LVMAdmin" database is located
   DBName - NameNo value of the cases table
   Query - Query to execute. Such as Insert, Delete
  .OUTPUTS
   None
 #>
 try
        {
  #Connection object
  $cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=SSPI;Initial Catalog=$DBName")
  $cn.open() 

  $cmd = new-object "System.Data.SqlClient.SqlCommand" ($Query, $cn)
  $cmd.ExecuteNonQuery()
 }
 catch
        {
               #Write error message on screen and to a LOG file
               write-host $_.Exception.Message
               $_.Exception.Message >> "d:\error.log"
        }
        finally
        {
            $ErrorActionPreference = "Continue"
        }
}
This will run SQL Server query from PowerShell.
How to Run SQL Server Query from PowerShell Script? How to Run SQL Server Query from PowerShell Script? Reviewed by Salaudeen Rajack on 6:30 PM Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.