Powershell and SQL Server

For one of my powershell project(EOL/Skob migration) that has become huge and I required to use sql table to report /Log about each migration from start till end,

along with any errors that are encountered. This can be helpful in checking the status for particular user for past dates, querying in a fast manner, reporting by using select queries.

In this post I will share how you can connect SQL thru power-shell, Create table, insert , query & update data in tables.

First step is to download the SQL management package 🙂

You can get it from below link:

https://www.microsoft.com/en-us/download/details.aspx?id=22985

After this is done ,you will have access to SQL /DB & you are all set. Now let’s add the SQL snapin to the script:

———————————————————————————————————————————

If ((Get-PSSnapin | where { $_.Name -match “SqlServerCmdletSnapin100” }) -eq $null)
{
Add-PSSnapin SqlServerCmdletSnapin100
}

If ((Get-PSSnapin | where { $_.Name -match “SqlServerProviderSnapin100” }) -eq $null)
{
Add-PSSnapin SqlServerProviderSnapin100
}

———————————————————————————————————————————-

Ones the SQL SNAPIN has been added you have to add below variables so that you can connect to SQL server from PowerShell

———————————————————————————————————————————-

$sql_instance_name =”SQLSERVER\DEV”
$db_name = “O365_User_Migration”
$tbname = “[O365_User_Migration].[dbo].[o365_Migration_table]”

———————————————————————————————————————————-

Now you can use below code sample for Query,insert & update the SQL table.

For querying data in SQL table use :

———————————————————————————————————————————-

try
{
$query1 = “select UMAddress1 from $tbname where UserPrincipalName = ‘$user'”
$Result = invoke-sqlcmd -Database $db_name -Query $query1 -serverinstance $sql_instance_name
}
catch
{
Write-Host “Sql exception occured while checking the $User” -ForegroundColor Yellow
$_.Exception
}

Now you can use the value that you got our of SQL by using below shell variable

$ext = $Result | select -exp UMAddress1

———————————————————————————————————————————-

For Inserting data in SQL table use:

———————————————————————————————————————————-

try
{
$mcollupn = $mcoll.UserPrincipalName
$mcollAlias = $mcoll.Alias
$mcollSamaccountname = $mcoll.SamaccountName
$mcollprereqdatetime = get-date

$queryinsert = @”
INSERT INTO $tbname (User_U, UserPrincipalName, Alias, SamaccountName, PrereqR_date)
VALUES (‘$user’, ‘$mcollupn’, ‘$mcollAlias’, ‘$mcollSamaccountname’, ‘$mcollprereqdatetime’)
“@
Write-Host “Inserting the $user values into SQL table” -ForegroundColor Magenta
invoke-sqlcmd -Database $db_name -Query $queryinsert -serverinstance $sql_instance_name
$Error.clear()
}
catch
{
Write-Host “Sql exception occured while inserting $user” -ForegroundColor Yellow
$_.Exception
}

———————————————————————————————————————————-

For Updating data in SQL table use:

———————————————————————————————————————————-

try
{
$mcollupn = $mcoll.UserPrincipalName
$mcollAlias = $mcoll.Alias
$mcollSamaccountname = $mcoll.SamaccountName
$mcollprereqdatetime = get-date

$queryupdate = @”
Update $tbname
SET UserPrincipalName = ‘$mcollupn’,
Alias = ‘$mcollAlias’,
SamaccountName = ‘$mcollSamaccountname’,

PrereqLR_date = ‘$mcollprereqdatetime’
where User_U = ‘$user’
“@
Write-Host “Updating the $user values into SQL table” -ForegroundColor Magenta
invoke-sqlcmd -Database $db_name -Query $queryupdate -serverinstance $sql_instance_name
$Error.clear()
}
catch
{
Write-Host “Sql exception occured while inserting $user” -ForegroundColor Yellow
$_.Exception
}