从 powershell 运行 SQL 脚本文件 [英] Run SQL script file from powershell

问题描述

我正在尝试从 PowerShell 运行存储在文本文件中的查询.我使用以下方法来做到这一点；

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"'

如果从 .sql 文件执行查询时发生错误或异常，我如何在我的 Powershell 脚本中捕获它?如何获得脚本输出?

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?

注意:我不能使用 invoke-sqlcmd

推荐答案

回答问题

如果在执行 .sql 文件时发生一些错误或异常，我如何将其输入到我的 PowerShell 脚本中?如何获得脚本输出?"

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 返回执行的表达式的输出.但是，它可能只捕获 STDOUT，而不是 STDERR(我没有测试过，因为我没有使用这种方法)，所以你可能不会得到实际的错误信息.

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.

来自帮助:

Invoke-Expression cmdlet 将指定的字符串作为命令计算或运行，并返回表达式或命令的结果

The Invoke-Expression cmdlet evaluates or runs a specified string as a command and returns the results of the expression or command

更好的方法是使用您已有的 PowerShell 方法 - 如果您安装了任何 SQL Server 2008(或更新的)组件/工具(如 SSMS)，则安装 Invoke-SQLCmd.如果您有 SQL Server 2012，则非常简单:import-module sqlps.对于 2008，您需要添加一个 Snap-In，add-pssnapin SqlServerCmdletSnapin.并且由于您有 sqlcmd.exe，PowerShell 组件应该已经存在.

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.

如果所有其他方法都失败，请转到 System.Data.SQLClient 路线:

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;

使用此和 Invoke-SQLCmd，您将能够使用 try/catch 来拾取和处理发生的任何错误.

With both this and Invoke-SQLCmd, you'll be able to use try/catch to pick up and handle any error that occurs.

这篇关于从 powershell 运行 SQL 脚本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！