Run SQL script file from powershell

Question

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

Answer 1

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.

Answer 2

As seen in this question's answers, there is a method built into Powershell to invoke SQLCMD called, unsurprisingly, Invoke-Sqlcmd.

It's very easy to use for individual files:

Invoke-sqlcmd -ServerInstance $server -Database $db -InputFile $filename

Or groups:

$listOfFiles | % { Invoke-sqlcmd -ServerInstance $server -Database $db -InputFile $_ }