How to call stored procedure using powershell scripting with try and catch

Hello Techie's

I know you are not the powershell scripting pro but your manager has given you the task to call the stored procedure in sql database using the powershell scripting. Now you are wondering where to start and how to proceed, not to worry we got you covered.

you might be wondering why powershell scripting, why not shellscripting?

Techie's we all know that one of the advantages of the powershell is that it has the .NET stack. once we have the call stack, if we face any errors we know where the issue is coming from.

Problem Statement:

1. you need to call the stored procedure present in sql database or MSaaS DB

2. you need to add TRY & CATCH in your powershell script to capture the Errors.

3. you need to write a logfile into the filesystem to check the log level info

Prerequisties:

 

1. You must have windows powershell Installed in your machine

2. you must know the sql database instance name

3. you must know the sql dababase name

4. you must know the stored procedure name

What the below Script will do ?

If you have above mentioned details, we are cool & good to go..!
The powershell script will call the stored procedure present in the DB, If it throws any errors like stored procedure not available or instance not reachable it will catch the exception and throws us the error information from .NET call stack.
Entire log level information will be captured in the logfile using the log function which we create in the script.

NOTE:

Replace below three varibles in the script with your own variables.

env:VDB_INSTANCE_NAME = "your_db_instance_name"
env:VDB_DATABASE_NAME = "your_database_name"
fqspname = "your_stored_procedure_name"

Script:

#File: Runsp.ps1
#Type: Powershell script
#Description: Script to call the stored procedure
#owner: Vichetechie
#----------------------------------------------------------------------------------------
#  Variables Assignment
#----------------------------------------------------------------------------------------
$env:VDB_INSTANCE_NAME = "your_db_instance_name"
$env:VDB_DATABASE_NAME = "your_database_name"
$fqspname = "your_stored_procedure_name"
$ConnectionTimeout = 30
$QueryTimeout = 120
#----------------------------------------------------------------------------------------
#  Creating logfile
#----------------------------------------------------------------------------------------
$dirPath = "C:\logs\"
$LogFile = $dirPath + "\" + $env:VDB_DATABASE_NAME +"_"+ (Get-Date -UFormat "%d-%m-%y") + ".log"
#----------------------------------------------------------------------------------------
#  Creating log function
#----------------------------------------------------------------------------------------
Function Write-error-log
{
    param (
        [Parameter(Mandatory=$True)]
        [array]$logoutput,
        [Parameter(Mandatory=$True)]
        [string]$Path
)
$currentDate = (Get-Date -UFormat "%d-%m-%y")
$currentTime = (Get-Date -UFormat "%T")
$logoutput = $logoutput -join (" ")
$event = "ERROR"
$LogMessage = "$Stamp $event $logoutput"
Add-Content $LogFile -Value $LogMessage
}
Function write-info-log
{
param ([string]$Logstring)
$currentDate = (Get-Date -UFormat "%d-%m-%y")
$currentTime = (Get-Date -UFormat "%T")
$stamp = "[$currentDate $currentTime]"
$event = "INFO"
$LogMessage = "$Stamp $event $LogString"
Add-Content $LogFile -Value $LogMessage 
}
#----------------------------------------------------------------------------------------
#  output the assignes varibles to the logfile
#----------------------------------------------------------------------------------------
write-info-log "***START-EXECUTION***"
write-info-log "dirpath = '$dirPath'"
write-info-log "fqspname = '$fqspname'"
write-info-log "env:VDB_INSTANCE_NAME = '$env:VDB_INSTANCE_NAME'"
write-info-log "env:VBD_DATABASE_NAME = '$env:VDB_DATABASE_NAME'"
write-info-log "ConnectionTimeout = '$ConnectionTimeout'"
write-info-log "QueryTimeout = '$QueryTimeout'"
#----------------------------------------------------------------------------------------
#  HouseKeeping: remove any existing log files older than 10 days
#----------------------------------------------------------------------------------------
write-info-log "Removing log files which are older than 10 days"
$agelimit = (Get-Date).AddDays(-10)
$logFilePattern = $env:VDB_DATABASE_NAME +"_"+ (Get-Date -UFormat "%d-%m-%y") + ".log"
write-info-log "logFilePattern = '$logFilePattern'"
Get-ChildItem -Path $dirPath -Recurse -Include $logFilePattern | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $agelimit } | Remove-Item
#----------------------------------------------------------------------------------------
#  Run the Stored procedure
#----------------------------------------------------------------------------------------
write-info-log "Running stored procedure '$fqspname' within the databse '$env:VDB_DATABASE_NAME' on the server '$env:VDB_INSTANCE_NAME'."
try {
write-info-log "open SQL Server Connection.."
$sqlserver = $env:VDB_INSTANCE_NAME
write-info-log "sqlserver = '$sqlserver'"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.sqlserver.Smo") | Out-Null;
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$sqlserver; Database=$env:VDB_DATABASE_NAME; Integrated Security= True;"
write-info-log "conn.ConnectionString = '$conn.ConnectionString'"
$conn.Open()
$cmd1 = New-Object System.Data.SqlClient.SqlCommand($fqspname, $conn)
$cmd1.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables
write-info-log "***Completed stored procedure '$fqspname' within database '$env:VDB_DATABASE_NAME' Successfully***"
}
Catch{
     Write-error-log -logoutput (":{0}" -f $_) -Path $LogFile
     }
#----------------------------------------------------------------------------------------
#  Exit with Success Status
#----------------------------------------------------------------------------------------
exit 0