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:
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 predefined 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]"
It’s 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 to Perform Insert/Update:
Similarly, for Insert and update, the PowerShell script goes like this:
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 ('https://sharepoint2007.crescent.com','https://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.
