Run SQL script file from powershell
powershell
Solution
To answer the question
If some error or exception occurred when executing .sql file how can I get that into my PowerShell script? How can I get the script output?"
`Invoke-Expression` returns the output of the expression executed. However, it may only capture `STDOUT`, not `STDERR` (I haven't tested, as I don't use this method), so you might not get the actual error message.
From the Help:
The Invoke-Expression cmdlet evaluates or runs a specified string as a command and returns the results of the expression or command
The better route is to use the PowerShell method you already have available - `Invoke-SQLCmd` is installed if you have installed any of the SQL Server 2008 (or newer) components/tools (like SSMS). If you've got SQL Server 2012, it's very easy: `import-module sqlps`. For 2008, you need to add a Snap-In, `add-pssnapin SqlServerCmdletSnapin`. And since you have `sqlcmd.exe`, the PowerShell components should be there already.
If all else fails, go the `System.Data.SQLClient` route:
$Conn=New-Object System.Data.SQLClient.SQLConnection "Server=YOURSERVER;Database=TestDB;User Id=$user;password=$pw";
$Conn.Open();
$DataCmd = New-Object System.Data.SqlClient.SqlCommand;
$MyQuery = get-content "e:\SQLQuery1.sql";
$DataCmd.CommandText = $MyQuery;
$DataCmd.Connection = $Conn;
$DAadapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$DAadapter.SelectCommand = $DataCmd;
$DTable = New-Object System.Data.DataTable;
$DAadapter.Fill($DTable)|Out-Null;
$Conn.Close();
$Conn.Dispose();
$DTable;
With both this and `Invoke-SQLCmd`, you'll be able to use `try/catch` to pick up and handle any error that occurs.
Problem
I am trying to run queries stored in a text file from PowerShell. I use following to do that; ``` Invoke-Expression 'sqlcmd -d TestDB -U $user -P $pw -i "E:\SQLQuery1.sql"' ``` If an error or exception occurs when executing the queries from the `.sql` file, how can I capture that in my Powershell script? How can I get the script output? NOTE: I cannot use `invoke-sqlcmd`