How to execute TSQL using Power shell

Being a DBA many times it happens that we need to execute few queries in SQL Server, the following is one of the way to execute SQL Script in PowerShell.

1. Copy this entire script into a PS file ( say C:\Test\sample.ps1)

2. Please edit you Server,DB, and query details

3. Open power shell window and execute C:\Test\sample.ps1

$ServerInstance = "Manish"
$Database = "MASTER"
$ConnectionTimeout = 30
$Query = "SELECT getdate()"
$QueryTimeout = 120

$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

Thanks

Manish