PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute scripts or test code and it helps to know some of the parameters we’ll often use. In addition, because we may want a custom script using some of the underlying .NET objects, we’ll look at an alternative where we will be able to create a custom PowerShell script that connects to SQL Server in order to run commands. The latter can be useful because one-line scripts have a tendency to change in future versions of PowerShell and working with the library directly can sometimes avoid this challenge.

PowerShell具有许多用于SQL Server的单行命令，其中之一是Invoke-SqlCmd。 该工具在许多需要快速执行脚本或测试代码的开发环境中很有用，并且有助于了解我们经常使用的一些参数。 另外，由于我们可能希望使用一些底层.NET对象的自定义脚本，因此我们将介绍一种替代方法，在该方法中，我们将能够创建连接到SQL Server的自定义PowerShell脚本以运行命令。 后者很有用，因为单行脚本倾向于在PowerShell的未来版本中进行更改，并且直接使用库有时可以避免这一挑战。

In all these examples where we call Invoke-Sqlcmd, we are not executing saved scripts, but executing these scripts through PowerShell ISE without saving the script. PowerShell ISE is included in most versions of Windows along with the PowerShell command line. For the examples in this article, we are using PowerShell version 5.

在我们称为Invoke-Sqlcmd的所有这些示例中，我们不执行已保存的脚本，而是通过PowerShell ISE执行这些脚本而不保存该脚本。 Windows的大多数版本中都包含PowerShell ISE以及PowerShell命令行。 对于本文中的示例，我们正在使用PowerShell版本5。

何时使用Invoke-SqlCmd (When to Use Invoke-SqlCmd)

When we consider one-line scripts, we want to consider where these tend to be the most helpful for us:

在考虑单行脚本时，我们要考虑这些脚本对我们最有帮助的地方：

• When we need to run a test quickly, such as testing code execution outside SSMS (like executing code on TestServerOne that runs against TestServerTwo)
  当我们需要快速运行测试时，例如在SSMS外部测试代码执行（例如在针对TestServerTwo的TestServerOne上执行代码）
• When we’re running a standard script without custom execution, like a stored procedure that executes a transform of our ETL layer, and the execution matches our flow
  当我们运行没有自定义执行的标准脚本时，例如执行ETL层转换的存储过程，并且执行与我们的流程匹配
• When we have no alternative that is as quick or effective for our needs
  当我们没有其他选择可以满足我们的需求时一样快捷或有效

In some cases, we may want a custom timeout that exceeds the limit, or we may not want to allow user input for the connection string (or other details). These are examples where we may want a custom script. We also want to be careful about using more tools than we need to avoid development complexity. Using ETL as an example, if most of our ETL is T-SQL, we should be careful about introducing extra tools that add more work in troubleshooting. Invoke-SqlCmd can be a useful tool in some contexts where we’re already using PowerShell, or if we’re running steps through SQL Server Job Agent, where we can run PowerShell scripts.

在某些情况下，我们可能希望自定义超时超过限制，或者我们可能不想允许用户输入连接字符串（或其他详细信息）。 这些是我们可能需要自定义脚本的示例。 与避免开发复杂性所需的工具相比，我们还希望谨慎使用更多的工具。 以ETL为例，如果我们的大多数ETL是T-SQL，我们应该谨慎地引入额外的工具，这些工具会增加故障排除的工作量。 在某些已经使用PowerShell的上下文中，或者如果我们正在通过SQL Server Job Agent运行步骤（可以在其中运行PowerShell脚本），Invoke-SqlCmd可能是一个有用的工具。

涵盖基础 (Covering the Basics)

When we connect to SQL Server, we will generally either use a trusted connection or a SQL Server authenticated user. If we try to log onto SQL Server, we see these as the first two options when connecting.

连接到SQL Server时，通常将使用受信任的连接或经过SQL Server身份验证的用户。 如果尝试登录到SQL Server，则在连接时将它们视为前两个选项。

For running our first tests with Invoke-SqlCmd, we’ll connect with three one-line calls each labelled with Query and the number. The reason for this is to compare how we can run connections to our database. Our two queries connect with integrated security (note how credentials are not specified), which means the account running the script has access to the SQL Server and database. We also see that our first query doesn’t have verbose enabled, which means when the script runs, we get no output – and our T-SQL script solely prints out This is output. Our second call to Invoke-SqlCmd prints out the output. Our third call, prints the output and uses SQL Server Authentication (these are placeholder credentials – do not use).

为了使用Invoke-SqlCmd运行我们的第一个测试，我们将连接三个单行调用，每个调用均标有Query和数字。 这样做的原因是比较我们如何运行与数据库的连接。 我们的两个查询具有集成的安全性（请注意如何不指定凭据），这意味着运行脚本的帐户可以访问SQL Server和数据库。 我们还看到我们的第一个查询没有启用冗长的查询，这意味着当脚本运行时，我们没有输出-我们的T-SQL脚本仅打印出此输出 。 我们对Invoke-SqlCmd的第二次调用将输出输出。 第三次调用，打印输出并使用SQL Server身份验证（这些是占位符凭据-不使用）。

$SQLServer = "TestServerOne"$db3 = "TestDB3"$qcd = "PRINT 'This is output'" Write-Host "Query 1:"Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd Write-Host "Query 2:"Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -Verbose Write-Host "Query 3:"Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -Username "User" -Password "Password" -Verbose

From these three side-by-side examples we see two important basics of calling this function – how we specify our credentials and whether we want output. Regardless of whether we use integrated security or SQL Server authentication, our users must have permission to the objects they call, such as tables, views, stored procedures, etc. Likewise, verbose may help us see output of what’s happening as we’re calling Invoke-SqlCmd and without it, we may not get the confirmation we need.

从这三个并排的示例中，我们看到了调用此函数的两个重要基础-我们如何指定凭据以及是否需要输出。 不管我们使用集成安全性还是SQL Server身份验证，我们的用户都必须对他们调用的对象（例如表，视图，存储过程等）具有权限。同样，冗长的内容可能有助于我们了解调用过程中发生的情况如果没有Invoke-SqlCmd，则可能无法获得所需的确认。

运行CRUD操作 (Running CRUD Operations)

From creating objects to running CRUD operations, we can use this one-line function to run many SQL commands. In the below code, we run five statements where we complete the following in each all: create a table, insert data, update data, delete data, and select some data. Our final output (shown in the image below this) reflects all our CRUD operations where we add data, update one value, remove another value and finally select the entire data set.

从创建对象到运行CRUD操作，我们可以使用此单行函数来运行许多SQL命令。 在下面的代码中，我们运行五个语句，每个语句均完成以下操作：创建表，插入数据，更新数据，删除数据以及选择一些数据。 我们的最终输出（如下图所示）反映了我们所有的CRUD操作，我们在其中添加数据，更新一个值，删除另一个值并最终选择整个数据集。

$SQLServer = "TestServerOne"$db3 = "TestDB3"$droptable = "DROP TABLE invokeTable"$createtable = "CREATE TABLE invokeTable (Id TINYINT, IdData VARCHAR(5))"$insertdata = "INSERT INTO invokeTable VALUES (1,'A'), (2,'B'), (3,'C'), (4,'E'),(5,'F')"$updatedata = "UPDATE invokeTable SET IdData = 'D' WHERE Id = 4"$deletedata = "DELETE FROM invokeTable WHERE Id = 5"$selectdata = "SELECT Id, IdData FROM invokeTable" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $createtableInvoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $insertdataInvoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $updatedataInvoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $deletedataInvoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $selectdata

While we can see that we are able to run T-SQL directly in the script, we will more than likely use the inputfile parameter instead, as most T-SQL files involve multiple lines of code rather than being one-line or a few line calls. In the below two calls to Invoke-SqlCmd, we pass in a file using the inputfile parameter with a file that runs both an insert and an update together. After that call, we call our function again to return our data.

尽管我们可以直接在脚本中运行T-SQL，但我们更有可能使用inputfile参数，因为大多数T-SQL文件涉及多行代码，而不是一行或几行电话。 在下面的两次Invoke-SqlCmd调用中，我们使用inputfile参数传入一个文件，该文件同时运行插入和更新操作。 调用之后，我们再次调用函数以返回数据。

File: C:\Files\TSQL\run.sql

档案：C：\ Files \ TSQL \ run.sql

INSERT INTO invokeTable VALUES (5,'E')  , (6,'F')  , (7,'G')  , (8,'H') UPDATE invokeTableSET IdData = IdData + '-' + CAST(Id AS VARCHAR(1))

Script run from PowerShell ISE:

从PowerShell ISE运行脚本：

$SQLServer = "TestServerOne"$db3 = "TestDB3"$selectdata = "SELECT Id, IdData FROM invokeTable" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -InputFile "C:\Files\TSQL\run.sql"Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $selectdata

In most cases with T-SQL from creating objects to running CRUD operations, we’ll use the input file parameter since most of our T-SQL will involve multiple lines of code and multiple operations. For an example, we may use files to run data operations on a regular basis, but change the files from time to time and in these cases, our input file would stay the same on each call.

在大多数情况下，对于T-SQL，从创建对象到运行CRUD操作，我们都将使用输入文件参数，因为大多数T-SQL将涉及多行代码和多个操作。 例如，我们可能会使用文件定期运行数据操作，但是会不时更改文件，在这种情况下，我们的输入文件在每次调用时都将保持不变。

返回文件中的数据 (Returning Data in Files)

Sometimes we’ll use Invoke-SqlCmd to return a data set in a file that will be read by an application, such as Excel or Tableau reading a CSV file returned from a call to this function. Using our same table and data set that we’ve created, we’ll use our function to create a CSV file that we can read in applications that can work with CSV files, such as Excel. When we call our function, we can pipe the output – which is the result set to Out-File or Export-Csv to save a file of these data. Since we want a CSV file, we’ll use the Export-Csv function. We specify the delimiter as a comma in this case – though we could specify any custom delimiter here and pass in the command to avoid outputting type information, which would normally add type information to our header (we don’t want in our case). As we see in the below image, we have our CSV file saved to our reporting location.

有时，我们将使用Invoke-SqlCmd返回文件中的数据集，该文件将由应用程序（例如Excel或Tableau）读取，该数据集将读取对此函数调用返回的CSV文件。 使用我们创建的相同表和数据集，我们将使用函数创建一个CSV文件，我们可以在可以使用CSV文件的应用程序（例如Excel）中读取该文件。 当我们调用函数时，我们可以通过管道输出-将结果集设置为Out-File或Export-Csv，以保存这些数据的文件。 由于我们需要CSV文件，因此我们将使用Export-Csv函数。 在这种情况下，我们将定界符指定为逗号-尽管我们可以在此处指定任何自定义定界符并传递命令以避免输出类型信息，这通常会将类型信息添加到标头中（在这种情况下，我们不需要）。 如下图所示，我们将CSV文件保存到了报告位置。

$SQLServer = "TestServerOne"$db3 = "TestDB3"$selectdata = "SELECT Id, IdData FROM invokeTable" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $selectdata | Export-Csv "C:\files\reports\run.csv" -Delimiter "," -NoTypeInformation

替代脚本 (Alternative Script)

Using Invoke-SqlCmd comes with a few drawbacks and we may prefer to use a custom script. One example of this is the parameter -QueryTimeout, which is the length of time a query must run before timing out. To avoid an error from returning, we must specify an integer between 1 and 65535 according to Microsoft. There may be situations in which we want the command timeout to be specified at 0. In a similar manner, we may want the connection string to be hardcoded with only commands allowed, which we can achieve through a custom function or wrapping a custom function around Invoke-SqlCmd.

使用Invoke-SqlCmd有一些缺点，我们可能更喜欢使用自定义脚本。 一个示例就是参数-QueryTimeout，它是查询在超时之前必须运行的时间长度。 为了避免返回错误，根据Microsoft，我们必须指定1到65535之间的整数。 在某些情况下，我们希望将命令超时指定为0。以类似的方式，我们可能希望仅在允许的命令下对连接字符串进行硬编码，这可以通过自定义函数或将自定义函数包装来实现。调用SqlCmd。

In the below code, we create a custom function that will execute write-based or construct based commands, such as creating objects, inserting, deleting or updating data. This custom function restricts input to only accept a command and we also see that it uses integration security and does not specify a timeout for either the connection or command. We use this script to clean up our test objects by first removing the data (DML) and then dropping the object (DDL).

在下面的代码中，我们创建一个自定义函数，该函数将执行基于写或基于构造的命令，例如创建对象，插入，删除或更新数据。 此自定义函数将输入限制为仅接受命令，并且我们还看到它使用集成安全性，并且未为连接或命令指定超时。 我们使用此脚本来清理测试对象，方法是先删除数据（DML），然后删除对象（DDL）。

Function Execute-SqlWriteConstruct {    Param(        [Parameter(Mandatory=$true)][string]$command    )    Process    {        $gscn = New-Object System.Data.SqlClient.SqlConnection        $gscn.ConnectionString = "Data source= TestServerOne;Initial Catalog= TestDB3;Integrated Security=true;Connection Timeout=0"                $wrCmd = New-Object System.Data.SqlClient.SqlCommand        $wrCmd.Connection = $gscn        $wrCmd.CommandTimeout = 0        $wrCmd.CommandText = $command         try        {            $gscn.Open()            $wrCmd.ExecuteNonQuery() | Out-Null        }        catch [Exception]        {            Write-Warning $_.Exception.Message            Write-Warning $command        }        finally        {            $gscn.Dispose()            $wrCmd.Dispose()        }    }} $deletethendrop = "DELETE FROM invokeTable DROP TABLE invokeTable" Execute-SqlWriteConstruct -command $deletethendrop

Should we create custom functions over using a built-in function? In addition to having full control over parameters including restricting some parameters, custom functions that use underlying .NET libraries seldom experience as much changes as built-in functions. If we choose to use built-in functions like Invoke-SqlCmd, we must be aware that these may change and any call to them must be reworked. With underlying .NET libraries, some of these may change if bugs or security holes are discovered, but we don’t see as many changes to these as we see with built-in PowerShell functions. The SqlClient namespace is a great example of this – I’ve been using this for years and while Microsoft has made it stronger over the years, the underlying structure matches. This isn’t to say that it won’t ever be deprecated, but functions built on top of it have changed, whereas it has experienced less change.

我们是否应该使用内置函数来创建自定义函数？ 除了完全控制参数（包括限制某些参数）外，使用基础.NET库的自定义函数很少会遇到与内置函数一样多的更改。 如果我们选择使用Invoke-SqlCmd之类的内置函数，则必须意识到它们可能会更改，并且对它们的任何调用都必须重新设计。 使用基础.NET库，如果发现错误或安全漏洞，则其中一些可能会更改，但是与内置PowerShell函数相比，我们看不到这些更改。 SqlClient命名空间就是一个很好的例子–我使用它已有多年了，尽管Microsoft多年来使它变得更强大，但底层结构却是匹配的。 这并不是说它永远不会被弃用，而是建立在它之上的功能已经发生了变化，而变化却很少。

结论 (Conclusion)

As we’ve seen, PowerShell’s Invoke-SqlCmd is a convenient tool in which we can create objects, read and write data to and from SQL Server (with direct or file input), and save queries to files without significant development work. While we’ve looked at a few examples that we’ll use frequently, this function has more capability than what’s covered. In the rare situations where we may need to write or use a custom function, we can use the .NET library to read and write to our database as well.

如我们所见，PowerShell的Invoke-SqlCmd是一种方便的工具，我们可以在其中创建对象，在SQL Server中进行数据读写（使用直接或文件输入）以及将查询保存到文件而无需进行大量开发工作。 尽管我们查看了一些经常使用的示例，但是此功能比所介绍的功能更多。 在极少数情况下，我们可能需要编写或使用自定义函数，我们也可以使用.NET库来读写数据库。

目录 (Table of contents)

Working with PowerShell’s Invoke-SqlCmd

使用PowerShell的Invoke-SqlCmd

翻译自: