Query SQL database from PowerShell

I’ve been working on a larger script where I needed to query a database to see if a value was present or not, something I thought was pretty much straight forward.

However, this turned out to be a bit harder than I thought it would be, but when I thought it through once again, it turned out to be almost as simple as I thought it would be. 

The first step is to import the module to be able to query the database, this done by running the following command:

if(-not(Get-Module SQLPS)) {Import-Module SQLPS -DisableNameChecking}

If the script is to be run on a server that hasn’t got an installation of SQL, you need to install three packages from Microsoft. Follow this post on how to perform this.

Next, we need to specify the database-server and the name of the database:

$SQLServer = "SQL-Server.domain.com" #use Server\Instance for
named SQL instances! 
$SQLDBName = "DatabaseName"

Then we need the query that should be executed (This example query checks if there is an entry in the table “dbo.person” with the value “test” under the column “name”).

$name = 'test'
$SQlQuery = "SELECT name FROM dbo.person WHERE name='" + $name + "'"

The last step is to execute the query and store the result in a variable:

$datatable = Invoke-Sqlcmd -Query $SQlQuery -Server $SQLServer -Database $SQLDBName

And that’s all there is to it. Quite easy, but there are some places where things can go wrong and it might not be as logical as one might think.