By Richie Lee

Hello!

Today’s snippet is a two-for-one type deal: connecting to SQL Server and running a query to get datetime. Really, it’s just useful for checking connectivity issues.

$dataSource = “[serverInstance]”

$database = “[database]”
$connectionString = “Server=$dataSource;Integrated Security=True;Database=$database;”
write-host $connectionString -Verbose

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()

$query = “SELECT GETDATE() AS TimeOfQuery;”

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$table

Alternative to use InvokeSqlCmd:

Import-Module sqlps
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "[serverInstance]; Trusted_Connection=True; Initial Catalog=[database]"