PowerShell Snippet #3

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]" 

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s