Execute an SQL query from Powershell – Digital Pixies Technologies

Recently had to deal with accessing a microsoft SQL database for a task. Using this script avoids installing native sql powershell files. note: the command is different from Invoke-SQLcmd!

function Invoke-SQL { param( [string] $ServerInstance = ".\SQLEXPRESS", [string] $Database = "master", [string] $Query = $(throw "Please specify a query.") )


    $connectionString = "Data Source=$ServerInstance; " +

            "Integrated Security=SSPI; " +

            "Initial Catalog=$Database";
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString);

    $command = new-object system.data.SqlClient.SQLCommand($Query,$connection);

    $connection.Open();
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command;

    $dataset = New-Object System.Data.DataSet;

    $adapter.Fill($dataSet) | Out-Null;
    $connection.Close();

    $dataSet.Tables;
}
$Database = "master";

$ServerInstance = ".\SQLEXPRESS";

$Query = "SELECT GETDATE() AS TimeOfQuery;";

Invoke-Sql -Query $Query -ServerInstance $ServerInstance -Database $Database;