Showing posts with label powershell. Show all posts
Showing posts with label powershell. Show all posts

Execute Powershell Commands inside StoredProcedure

To show an example for executing powershell commands inside a stored procedure, i am going to get the current data and time using Get-Date cmdlet.

EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.

--create stored procedure 

EXEC master..xp_cmdshell 'powershell.exe Get-Date'

--Execute StoredProcedure
EXEC SP_EXEC_Powershell

Using Powershell commands inside SQL Agent Jobs

In this post,I will show an example of using powershell commands inside SQL Agent jobs.

For Example ,I am going to add the below powershell commands in a job as one of the step and schedule it.

Get-EventLog System -Newest 5 | Out-File c:\scripts\Eventlog.txt

Get-Counter -ComputerName $env:COMPUTERNAME -ListSet 'MSSQL*' | Out-File c:\scripts\MSSQLCounters.txt

$wmiQuery = @"SELECT systemName,Name,DriveType,FileSystem,FreeSpace,Capacity,Label FROM Win32_Volume
Get-wmiObject -ComputerName "Sathya-PC" -Query $wmiQuery | Out-File c:\scripts\Drivesproperties.txt

Get-WinEvent -ListLog * | Format-Table -Autosize | Out-File c:\scripts\ListofWinEvent.txt

Like shown in below images ,we have to select the type as Powershell to schedule the execution of powershell commands.

I have piped the output to text file but in Advanced setting of job we have option to specify the job output location.

We can also send mails using powershell commands inside SQL Agent job when previous job step fails

Handling strings using Powershell commands

This post is just about handling strings using powershell commands

##to split string that are separated with space

"Powershell is really cool" -split " "

##to join strings with pipe symbol

"Powershell 1.0","Powershell 2.0"-join "|"

##to compare string values

$A = "Powershell is really cool"
$B = "Powershell is really hot"

Compare-Object $A $B -includeequal

$C = "Powershell is really cool"
$D = "Powershell is really cool"

Compare-Object $C $D -includeequal

Executing Powershell commands using SSIS

In this post ,we are going to see two about two cases

i)saving powershell commands inside a file with file extension as ps1 and then invoking that file to execute the powershell commands

ii)executing powershell commands using SSIS - Execute Process Task

As a first step, i am going to open a notepad and place the below powershell commands inside it and then save it as .ps1 under path location "C:\Scripts\test.ps1"

$UserInput = Read-Host "Is Powershell really a cool feature"
##moving file to archieve folder
Move-Item D:\myflatfile.txt D:\archieve

And in my Execute Process Task i have entered only Executable & Arguments

And when you execute the package

Reference -

Reading XML file using Powershell

This post deals with reading XML file and then traversing through the nodes of XML and fetching node values using powershell commands.

consider the below xml fragment with file path location

<student ID="1" name="Sathya">
<student ID="2" name="Deepak">

#loading xml from mentioned path location into xml variable
[xml]$Xmlvar = Get-Content D:\myxml.xml 

#fetching values of xml nodes

$firststudent = $Xmlvar.STUDENTINFO.student[0]

$secondstudent = $Xmlvar.STUDENTINFO.student[1]
foreach( $student in $Xmlvar.STUDENTINFO.student)


Write-host $
Write-host $student.Id




Handling files using Powershell

In this post i have covered about handling files using powershell commands

For below examples i have created flatfile in the path location "D:\myflatfile.txt"
with content as shown below:


##test whether file exists in that path
Test-Path D:\myflatfile.txt

##reading comma separated values from file
##and storing it in an variable which can hold list of values
$tmptable = @()
$flatfile = Get-Content D:\myflatfile.txt
foreach ( $values in $flatfile )
$tmptable += @{id="${id}";name="$name"}}

##to search particular value in a file
Get-Content D:\myflatfile.txt | Select-String "powershell"

##this is really cool,it displays values as separate column
Import-Csv D:\myflatfile.txt

##To export as CSV
Import-Csv D:\myflatfile.txt | Export-Csv D:\myflatfilecsv.txt

##to append text or date to the end of file
$Appendate= Get-Date; Add-Content D:\*.txt

#to compare two files
$A = D:\myflatfile.txt
$B = D:\myflatfilecsv.txt
Compare-Object $A $B

##to clear content of a file
Clear-Content D:\myflatfilecsv.txt

##deleting all text files,you can also exclude files using -exclude *test*
Remove-Item c:\scripts\* -include *.txt

##using powershell we can rename,copy,move,create & delete files
##save output as HTML,text,csv or xml file

Reference -

Powershell - Create Database,Table and Storedprocedure using SMO

Below commands are used to create database , table & stored procedure using SMO objects

#create database
$srv= new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -argumentlist $srv, "PowerDatabase"

#Create the Table
$db= New-Object Microsoft.SqlServer.Management.Smo.Database
$db= $srv.Databases.Item("PowerDatabase")
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "PowerTable")
$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb,"Name", [Microsoft.SqlServer.Management.Smo.DataType]::NChar(50))
$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "ID", [Microsoft.SqlServer.Management.Smo.DataType]::Int)$tb.Columns.Add($col1)
# Create stored procedure
$sp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure `
-argumentlist $db, "PowerStoredProcedure"
$sp.TextMode = $false
$type = [Microsoft.SqlServer.Management.SMO.DataType]::NVarChar(50)
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter `
-argumentlist $sp,"@retval",$type
$param.IsOutputParameter = $true
$sp.TextBody = " SELECT @retval = (SELECT Name FROM dbo.PowerTable)"


Reference -

Powershell Command to get status of SQL Server services

Powershell Command to get status of SQL Server services in a machine.

#to get stopped services
Get-Service -name *sql* | Where-object{$_.Status -eq "Stopped"} | Format-Table -AutoSize

#to get Running services
Get-Service -name *sql* | Where-object{$_.Status -eq "Running"} | Format-Table -AutoSize

Getting Started with SQL Server Powershell

sqlps utility starts a Windows PowerShell  session with the SQL Server PowerShell provider and cmdlets loaded and registered.

You can enter PowerShell commands or scripts that use the SQL Server PowerShell components to work with instances of SQL Server and their objects.

You can use the sqlps utility to do the following:
  • Interactively run PowerShell commands.
  • Run PowerShell script files.
  • Run SQL Server cmdlets.
  • Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.

To start sqlps utility : -

To start sqlps utility : -

Through Windows PowerShell ,we can interact with SQL Server components by importing the sqlps module into Windows PowerShell environment .

The sqlps module loads in the SQL Server PowerShell provider and cmdlets, and the SQL Server Management Object (SMO) assemblies used by the provider and cmdlets. 

Powershell Commands:

Get-Module -listAvailable

##setting execution policy
Set-ExecutionPolicy unrestricted

## Importing  the SQLPS Module with -DisableNameChecking to suppress warnings
Import-Module “sqlps” -DisableNameChecking

Get-Command –module "sqlps"

Get-PSSnapin -registered

##if not added,to add sql server snapins
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

##to get commands in a snapin
Get-Command -pssnapin SqlServerCmdletSnapin100
Get-Command -pssnapin SqlServerProviderSnapin100