Written by Altınok Darıcı on 26 - Nov - 2014

Query Sql Server using PowerShell

##define server ip or name, database name, username, password
$server="192.168.1.187"
$databaseName="TestDb"
$userName="sql_user"
$password="12345678"
 
 
$connection = New-Object System.Data.SqlClient.SqlConnection # create an instance of SqlConnection
$connection.ConnectionString = "Server=$server;Database=$databaseName;User=$userName;password=$password;" # set the connection string
 
$connection.Open() # open sql connection
 
$command = $connection.CreateCommand() # create a new command by using connection
$command.CommandText = "select * from Cities where  Name like 'A%' order by Name" # set the query string
 
$result = $command.ExecuteReader() # run the query
$table = new-object "System.Data.DataTable" # create a new data table
$table.Load($result) # load query result in to data table
$connection.Close() # close the sql connection
 
$format = @{Expression={$_.Id};Label=”City Id”;width=20},@{Expression={$_.Name};Label=”City Name”; width=40} # format columns
 
$table  | format-table $format # format and show result table

The result is shown as below

comments powered by Disqus