如何将SQL存储过程调用的PowerShell脚本中的错误记录到SQL表中？

I have a SQL stored procedure that executes a powershell file, and I want to log any errors that occur executing the powershell file into a SQL table.

我有一个执行powershell文件的SQL存储过程，我想将执行powershell文件时发生的任何错误记录到SQL表中。

My SQL stored procedure:

我的SQL存储过程：

CREATE PROCEDURE [dbo].[sp_RemoveEmptyFiles]
    @filePath varchar(260)

AS 

DECLARE @sql as varchar(4000)
DECLARE @powershellFileLocation varchar(260)

SET @powershellFileLocation = '\\MyComputerName\Files\Powershell\cleandirectory.ps1'


SET @sql = 'powershell -c "& { . ' + @powershellFileLocation + '; clean-directory ' + @filePath + ' }"'

EXEC master..xp_cmdshell @sql

My powershell script:

我的powershell脚本：

function clean-directory {
    param ([string]$path)
    try 
    {
        if ($path.Length -le 0 -or -not (test-path -literalPath $path)) {
            throw [System.IO.FileNotFoundException] """$path"" not a valid file path."
        }

        #
        #
        # Clean directories here
        #
        #
    }
    catch 
    {
        write-host $error
    }
}

Right now, if the script is successful, it returns an output of NULL and a Return Value of 0. The goal is to replace that catch block with something that will save those errors to a SQL table.

现在，如果脚本成功，它将返回NULL输出和返回值0.目标是将那个catch块替换为将这些错误保存到SQL表的东西。

My first (inefficient) thought is to then invoke a SQL command in that catch block, something like:

我的第一个（效率低下）的想法是在那个catch块中调用一个SQL命令，例如：

$commandText = "INSERT INTO ErrorLogTable (TimeStamp, ErrorMessage) VALUES ($(Get-Date), $error)"
$command = $conn.CreateCommand()
$command.CommandText = $commandText
$command.ExecuteNonQuery()

But this hardly seems like the best way to do it-- connecting back to the SQL server the stored procedure was called from and creating a new command, etc. It should be noted that the powershell script, file path argument of the stored procedure, and SQL server are in different locations, so I do need to keep permission issues in mind (and hence why I am trying to avoid calling Invoke-Sqlcmd from my powershell script).

但这似乎不是最好的方法 - 连接回SQL服务器调用存储过程并创建新命令等。应该注意powershell脚本，存储过程的文件路径参数，和SQL服务器在不同的位置，所以我需要记住权限问题（因此我试图避免从我的powershell脚本调用Invoke-Sqlcmd）。

Is there a way to get the output of the powershell file in the stored procedure, and then save the error message into a table from there?

有没有办法在存储过程中获取powershell文件的输出，然后从那里将错误消息保存到表中？

1 个解决方案

#1

Since I'm using xp_cmdshell, I can capture that output by changing the SQL script as follows:

由于我使用的是xp_cmdshell，我可以通过更改SQL脚本来捕获该输出，如下所示：

SET @sql = 'powershell -c "& { . ' + @powershellFileLocation + '; clean-directory ' + @filePath + ' }"'

-- table to hold the output from the cmdshell
CREATE TABLE #PowershellOutput ([Output] varchar(1000))

-- execute actual powershell script
INSERT INTO #PowershellOutput ([Output]) EXEC master..xp_cmdshell @sql

This captures each line sent to the console as a separate row. It's not the best solution for capturing error messages from powershell, so I'm still looking for a better way to capture them. I'll either join these rows to a single output, or find a better way to capture only the powershell error (as opposed to the entire stacktrace).

这会将发送到控制台的每一行捕获为一个单独的行。它不是从PowerShell捕获错误消息的最佳解决方案，所以我仍然在寻找一种更好的方法来捕获它们。我要么将这些行连接到单个输出，要么找到一种更好的方法来捕获powershell错误（而不是整个堆栈跟踪）。