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

Why are you using `Invoke-Expression`? Just call sqlcmd like normal. `PS> sqlcmd.exe -d TestDB -U $user -P $pw -I E:\SQLQuery.sql1`. If you are seeing an "error or exception" please post the full text of this error or exception. — latkin, Aug 08 '13 at 06:04
You might also explain why you can't use invoke-sqlcmd. Maybe there's a workaround. — noam, Aug 08 '13 at 11:58
+1 for needing an explanation on `invoke-sqlcmd`. IIRC, if you've got a system where `sqlcmd` is installed, you'll also have `invoke-sqlcmd` (as long as it's the tools from SQL Server 2008+) — alroc, Aug 08 '13 at 12:47

score 12 · Answer 1 · answered Aug 08 '13 at 13:02

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.

See here if you have **GO** in your statement: https://stackoverflow.com/a/25681013/1747983 — Tilo, Mar 02 '18 at 20:59

score 12 · Answer 2 · edited May 23 '17 at 12:24

12

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 $_ }

edited May 23 '17 at 12:24

Community

1
1

answered Apr 23 '14 at 11:52

Alex

2,681
3
28
43

score 2 · Answer 3 · edited Aug 08 '13 at 12:55

2

Use invoke-sqlquery module, available at this website.

edited Aug 08 '13 at 12:55

Rubens

14,478
11
63
92

answered Aug 08 '13 at 12:34

Jimbo

2,529
19
22

Run SQL script file from powershell

3 Answers3

Linked