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