Query SQL with PowerShell without a Module

Leave a Comment / Powershell, SQL

Most of you should be aware of “Invoke-SQLCMD” to run a SQL Query to a SQL Server and get the Result in PowerShell.

Invoke-SQLCMD needs to be installed with the PowerShell Module SQLServer. But what to do when you cannot install the Module or even need to run a SQL Query in System Center Orchestrator, which is still a 32bit. The Invoke-SQLCMD is 64bit.

There is a simple way to solve this. Yes, you need some more lines in your code, but it works.

$SQLServer="SQLServer1.domain.local"
$SQLDB="DB1"

$sqlquery="SELECT * from Table"

    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$SQLServer';database='$SQLDB';trusted_connection=false; integrated security='true'"
    $Connection.Open()
    $command = $Connection.CreateCommand()
    $command.CommandText = $sqlquery
    $Datatable = New-Object "System.Data.Datatable"
    $result = $command.ExecuteReader()
    $Datatable.Load($result)

    
    $Result=$Datatable

The result is now stored in the Variable $Datatable

Michael Seidl aka Techguy

Leave a Comment