Export Large Query Result to File (PowerShell)

Export query output to file using PowerShell. Suitable for scenarios when large result set or single column value cannot be exported using SSMS.

$query = "SELECT 1 AS COL1"

$connectionString = "Server=***;Database=***;Integrated Security=false;User=***;Password=***"

$outputFile = "/***/export.json"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$command.CommandTimeout = 500

$connection.Open()
$reader = $command.ExecuteReader()

$results = @()
while ($reader.Read()) {
    $results += $reader["COL1"]
}

$connection.Close()

$results | Out-File -FilePath $outputFile -Encoding UTF8