在 SQL Server 代理中运行 Windows PowerShell 步骤Run Windows PowerShell Steps in SQL Server Agent

适用于: SQL Server Azure SQL 数据库 Azure Synapse Analytics 并行数据仓库

使用 SQL Server 代理以在计划时间运行 SQL Server PowerShell 脚本。Use SQL Server Agent to run SQL Server PowerShell scripts at scheduled times.

若要从 SQL Server 代理运行 PowerShell,请使用: PowerShell 作业步骤命令提示符作业步骤To run PowerShell from SQL Server Agent, using: PowerShell Job Step, Command Prompt Job Step

备注

SQL Server PowerShell 模块有两种;SqlServer 和 SQLPS 。There are two SQL Server PowerShell modules; SqlServer and SQLPS.

SqlServer 模块是当前要使用的 PowerShell 模块。The SqlServer module is the current PowerShell module to use.

虽然 SQL Server 安装附带了 SQLPS 模块(用于实现后向兼容性),但该模块不再更新。The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

SqlServer 模块不仅包含 SQLPS 更新版本的 cmdlet,还包含新的 cmdlet 以支持最新的 SQL 功能 。The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

PowerShell 库安装 SqlServer 模块。Install the SqlServer module from the PowerShell Gallery.

有关详细信息,请参阅 SQL Server PowerShellFor more information, see SQL Server PowerShell.

共有多种类型的 SQL ServerSQL Server 代理作业步骤。There are several types of SQL ServerSQL Server Agent job steps. 每种类型都与用来实现特定环境(如复制代理或命令提示环境)的子系统关联。Each type is associated with a subsystem that implements a specific environment, such as a replication agent or command prompt environment. 您可以对 Windows PowerShell 脚本进行编码,然后使用 SQL ServerSQL Server 代理将这些脚本包括在按计划时间运行或者为了响应 SQL ServerSQL Server 事件而运行的作业中。You can code Windows PowerShell scripts, and then use SQL ServerSQL Server Agent to include the scripts in jobs that run at scheduled times or in response to SQL ServerSQL Server events. 可以使用命令提示作业步骤或 PowerShell 作业步骤运行 Windows PowerShell 脚本。Windows PowerShell scripts can be run using either a command prompt job step or a PowerShell job step.

  • 使用 PowerShell 作业步骤以便让 SQL ServerSQL Server 代理子系统运行 模块的每个 实用工具,该实用工具将启动 PowerShell 并导入 模块的每个 模块。Use a PowerShell job step to have the SQL ServerSQL Server Agent subsystem run the sqlps utility, which launches PowerShell and imports the sqlps module.

  • 使用命令提示作业步骤以便运行 PowerShell.exe,并且指定导入 sqlps 模块的脚本。Use a command prompt job step to run PowerShell.exe, and specify a script that imports the sqlps module.

关于内存占用的警告Caution about memory consumption

通过 sqlps 模块运行 PowerShell 的每个 SQL ServerSQL Server 代理作业步骤都启动进程,大约占用 20MB 内存。Each SQL ServerSQL Server Agent job step that runs PowerShell with the sqlps module launches a process, which consumes approximately 20 MB of memory. 同时运行大量的 Windows PowerShell 作业步骤会对性能产生负面影响。Running large numbers of concurrent Windows PowerShell job steps can adversely impact performance.

期待您的反馈 :如果在本文中发现过时或不正确的内容(如步骤或代码示例),请告诉我们。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 可以单击此页底部的“反馈” 部分中的“本页” 按钮。You can click the This page button in the Feedback section at the bottom of this page. 我们通常在第二天阅读有关 SQL 的每项反馈。We read every item of feedback about SQL, typically the next day. 谢谢。Thanks.

创建 PowerShell 作业步骤Create a PowerShell Job Step

创建 PowerShell 作业步骤To create a PowerShell job step

  1. 展开“SQL Server 代理” ,创建一个新作业或右键单击一个现有作业,再单击“属性” 。Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties. 有关创建作业的详细信息,请参阅 创建作业For more information about creating a job, see Creating Jobs.

  2. “作业属性” 对话框中,单击 “步骤” 页,再单击 “新建”In the Job Properties dialog, click the Steps page, and then click New.

  3. “新建作业步骤” 对话框中,键入作业的 “步骤名称”In the New Job Step dialog, type a job Step name.

  4. “类型” 列表中单击 PowerShellIn the Type list, click PowerShell.

  5. “运行身份” 列表中,选择该作业将要使用的代理帐户和凭据。In the Run as list, select the proxy account with the credentials that the job will use.

  6. “命令” 框中,输入将为该作业步骤执行的 PowerShell 脚本语法。In the Command box, enter the PowerShell script syntax that will be executed for the job step. 或者,单击 “打开” ,选择包含脚本语法的文件。Alternately, click Open and select a file containing the script syntax.

  7. 单击 “高级” 页设置以下作业步骤选项:当该作业步骤成功或失败时将执行的操作、 SQL ServerSQL Server 代理应该尝试执行该作业步骤的次数以及重试的时间间隔。Click the Advanced page to set the following job step options: what action to take if the job step succeeds or fails, how many times SQL ServerSQL Server Agent should try to execute the job step, and how often retry attempts should be made.

创建命令提示作业步骤Create a Command Prompt Job Step

创建 CmdExec 作业步骤To create a CmdExec job step

  1. 展开“SQL Server 代理” ,创建一个新作业或右键单击一个现有作业,再单击“属性” 。Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties. 有关创建作业的详细信息,请参阅 创建作业For more information about creating a job, see Creating Jobs.

  2. “作业属性” 对话框中,单击 “步骤” 页,再单击 “新建”In the Job Properties dialog, click the Steps page, and then click New.

  3. “新建作业步骤” 对话框中,键入作业的 “步骤名称”In the New Job Step dialog, type a job Step name.

  4. “类型” 列表中,选择 “操作系统(CmdExec)”In the Type list, choose Operating system (CmdExec).

  5. “运行身份” 列表中,选择具有作业将使用的凭据的代理帐户。In Run as list, select the proxy account with the credentials that the job will use. 默认情况下,CmdExec 作业步骤在 SQL Server 代理服务帐户的上下文中运行。By default, CmdExec job steps run under the context of the SQL Server Agent service account.

  6. “成功命令的进程退出代码” 框中,输入一个介于 0 到 999999 之间的值。In the Process exit code of a successful command box, enter a value from 0 to 999999.

  7. “命令” 框中,输入 powershell.exe 以及指定要运行的 PowerShell 脚本的参数。In the Command box, enter powershell.exe with parameters specifying the PowerShell script to be run.

  8. 单击 “高级” 页设置以下作业步骤选项,例如:当该作业步骤成功或失败时将执行的操作、 SQL ServerSQL Server 代理应该尝试执行该作业步骤的次数,以及 SQL ServerSQL Server 代理将作业步骤输出写入哪个文件。Click the Advanced page to set job step options, such as: what action to take if the job step succeeds or fails, how many times SQL ServerSQL Server Agent should try to execute the job step, and the file where SQL ServerSQL Server Agent can write the job step output. 只有 sysadmin 固定服务器角色的成员才可以将作业步骤输出写入到操作系统文件中。Only members of the sysadmin fixed server role can write job step output to an operating system file.

另请参阅See Also

SQL Server PowerShellSQL Server PowerShell