How do I pause PowerShell to see an error?

Question

I have tried every recommendation from the link below, but nothing works.

How do you do a ‘Pause’ with PowerShell 2.0?

Here is my code:

Read-Host -Prompt "Press Enter to continue"
Invoke-Sqlcmd -Query "Select * from TBL_AGG_BOC" -ServerInstance "server_name\SQL2008" -Database "db_name" > "C:\Users\TBL_AGG_BOC.txt"
Read-Host -Prompt "Press Enter to continue"

The script keeps failing, and it shuts down so fast that I can't see what is actually happening. How can I force this to stay open for a few seconds so I can see what is happening here?

The file is saved and named 'test_export.ps1', and I right-click the file and then click 'Run with PowerShell' to fire it off. I don't have administrator rights on this machine. I hope that's not a problem.

Type the `Invoke-Sqlcmd` command you want to run at a PowerShell prompt and press `Enter`. Read error. — Bill_Stewart, Apr 23 '18 at 17:35
I added 'Invoke-Sqlcmd' right at the top, saved the file, and re-ran it. Same thing. It opens and almost immediately shuts down. All I am doing is right-clicking this ps1 file and clicking 'Run with PowerShell'. I think there may be another step that I am missing. — ASH, Apr 23 '18 at 17:43
PowerShell ISE would be the quickest solution, `Start-Transcript -Path "C:\transcripts\transcript.txt"` may also help. — Jacob, Apr 23 '18 at 18:03
No. Do not right-click the file to run it. (Read my comment carefully and try again.) — Bill_Stewart, Apr 23 '18 at 18:56

score 0 · Accepted Answer · edited Jun 11 '19 at 17:40

Finally I got this working. It was a combination of a few things.

Open PowerShell as Administrator and run Set-ExecutionPolicy -Scope CurrentUser
Provide RemoteSigned and press Enter
Run Set-ExecutionPolicy -Scope CurrentUser
Provide Unrestricted and press Enter

This link helped a lot!

PowerShell says "execution of scripts is disabled on this system."

Then, run this:

$server = "SERVERNAME\INSTANCE"
$database = "DATABASE_NAME"
$tablequery = "SELECT name from sys.tables"

# Declare connection variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection

# Load up the tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# Loop through all tables and export a CSV of the table data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [$($Row[0])]"

    # Specify the output location of your dump file
    $extractFile = "C:\mssql\export\$($Row[0]).csv"

    $command.CommandText = $queryData
    $command.Connection = $connection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation
}

It turned out to be this: $server = "SERVERNAME". Not this: $server = "SERVERNAME\INSTANCE"

After I incorporated these changes, everything worked fine.

How do I pause PowerShell to see an error?

1 Answers1