Get Unique/Distinct file types/extensions from directory tree

 


Get-ChildItem -Recurse |

Select-Object Extension |

Sort-Object -Property Extension -Unique

Reference: https://robertbigec.wordpress.com/2011/01/07/determining-unique-file-extensions-recursively-using-powershell/

 

Advertisements

Execute stored procedure in Powershell and get print statements output on host

I had a requirement to execute the stored procedure from Powershell. I cannot use SQLCMD as it was not installed on the application server. I had to use Microsoft .Net framework library to execute the stored procedure.

The below code snippet was used to execute the stored procedure and it works just fine.

$connectionString = "Data Source=SRV01; Integrated Security=SSPI; Initial Catalog=DB01"
$id = 12345
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$sqlCommand = "EXEC [usp_get_details] $id, 0"
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$connection.Close()

But the PRINT messages from the procedure were not displayed on the screen, which are required to identify the progress or status of the procedure execution. I have searched in internet and found useful post by Jonathan at https://www.sqlskills.com/blogs/jonathan/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell/

After implementing the event handler as described in the above link the code looks like below and works as expected.

$connectionString = "Data Source=SRV01; Integrated Security=SSPI; Initial Catalog=DB01"
$id = 12345
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

## Attach the InfoMessage Event Handler to the connection to write out the messages
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };
$connection.add_InfoMessage($handler);
$connection.FireInfoMessageEventOnUserErrors = $true;

$sqlCommand = "EXEC [usp_get_details] $id, 0"
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$connection.Close()