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
}

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

Now you are familiar with the power of powershell & can use SQL as back-end when things go complex in a project or you are running a big migration.

Thanks for reading

Vikas Sukhija

 

Advertisements

One thought on “Powershell and SQL Server

  1. Pingback: Powershell and SQL Server – PLUTO DIGITEC LTD

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s