Powershell–Run Sql queries and returning data-sets

Here is how you can run sql queries that return data-sets:

function execute-Sql{
    param($server, $databaseName, $sql )
   
    $sqlConn = new-object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = 'server=' + $server + ';integrated security=TRUE;database=' + $databaseName
    $sqlConn.Open()
    $sqlCmd = new-object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = 120
    $sqlCmd.Connection = $sqlConn
    $sqlCmd.CommandText= $sql
    $text = $sql.Substring(0, 50)
    Write-Host "Executing SQL => $text..."
   
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $sqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
   
    $sqlConn.Close()
   
    return $DataSet.Tables[0]
}

Example on how to call above script

cls;

$sqlQuery = "select sub.DomainName from SystemUserBase sub join `
SystemUserRoles sur on sub.SystemUserId = sur.SystemUserId join Role rb on rb.RoleId = sur.RoleId `
where sub.IsDisabled = 0 and rb.Name = 'System Administrator'"

$result = execute-Sql -server "CrmDbServer" -databaseName "National_Cinemedia_MSCRM" -sql $sqlQuery

$result