PowerShell invoke-sqlcmd Get-ChildItem:无法调用方法.提供者不支持使用过滤器 [英] PowerShell invoke-sqlcmd Get-ChildItem : Cannot call method. The provider does not support the use of filters

问题描述

我在使用 invoke-sqlcmd 向表中插入行时发生了一个奇怪的错误.如果执行一次，整个脚本可以完美运行，但是如果我第二次运行它，它会失败并显示错误消息:Get-ChildItem:无法调用方法.提供程序不支持使用过滤器.我测试了代码并注释掉了 invoke-sqlcmd 行，并且可以多次运行它而没有任何错误.我将 invoke-sqlcmd 放在一个函数中，但它仍然出错，并且在第一次成功运行后，它也会在 PS 命令行中失败.

I have a strange error occurring when using the invoke-sqlcmd to insert rows into a table. The entire script works perfectly if executed one time, but if I run it a second time, it fails with the error message: Get-ChildItem : Cannot call method. The provider does not support the use of filters. I tested the code and commented out the invoke-sqlcmd line, and can run it multiple times without any errors. I put the invoke-sqlcmd in a function and it still errors, and after the first successful run it will fail at the PS command line too.

Clear-Host
$ErrorActionPreference = 'Stop'

function Update-SQL
{
    invoke-sqlcmd -query $strSQLInsert -server SXLSV-LEAPDBD1 -database DTCS_DV
}

$files = Get-ChildItem '\\pcslog1011\dtcs\ContractEmailNotes\' -Filter '*.txt' | Where-Object {$_.LastWriteTime -ge '08/22/2016 00:00:00' -and $_.LastWriteTime -le '08/22/2016 23:59:59'} | sort-object -Descending

for ($i=0; $i -lt $files.Count; $i++) 
{
    $SNAC_CNTR_ID = $files[$i].BaseName.Substring(0,6)
    $SNAC_MODIFIED = '{0:yyyy-MM-dd HH:mm:ss}' -f ($files[$i].LastWriteTime)

    $reader = [System.IO.File]::OpenText($files[$i].FullName)
    $lineCnt = 0
    $SNAC_ACTION = ''
    for() 
    {
        $lineCnt += 1 
        $line = $reader.ReadLine()
        if ($line -eq $null) {break}
        if ($lineCnt -eq 4)
        {
            if ($line.Contains('AMENDED') -eq $True)
            {
                $SNAC_ACTION = 'AMENDED'
            }
            elseif ($line.Contains('DEAL CHANGED') -eq $True)
            {
                $SNAC_ACTION = 'CHANGED'
            }
            else
            {
                $SNAC_ACTION = 'NEW'
            }
        }
    }
    $reader.Close()

    write-host $SNAC_CNTR_ID $SNAC_MODIFIED $SNAC_ACTION

    $strSQLInsert = "INSERT INTO CQT_CONTRACT_SOX_NAC_LIST (SNAC_CNTR_ID, SNAC_MODIFIED, SNAC_ACTION, SNAC_UPDATED_BY, SNAC_UPDATED_ON) VALUES ('" + $SNAC_CNTR_ID + "', '" + $SNAC_MODIFIED + "', '" + $SNAC_ACTION + "', Default, Default)"

    Update-SQL $strSQLInsert
}

exit

推荐答案

我知道这是一个相当老的线程，但我遇到了一个类似的问题，导致我进入了这个页面.以下是为我解释和解决问题的链接.

I know this is a rather old thread, but I ran into a similar issue which led me to this page. Here are the links that explained and resolved the issue for me.

PowerShell Invoke-Sqlcmd 切换到 sqlps 会话
卡在 Powershell sqlserver

SQLPS 提供程序默认不识别 UNC 路径.您可以使用特定的文件系统提供程序作为 UNC 路径的前缀:

The SQLPS provider does not recognize UNC paths by default. You can either prefix the UNC path with a specific file system provider:

Get-ChildItem Microsoft.PowerShell.Core\FileSystem::\\pcslog1011\dtcs\ContractEmailNotes\
-Filter '*.txt'

或者在 SQLPS 模块加载后通过将其添加到脚本顶部将您的工作目录切换回默认值:

or switch your working directory back to the default after the SQLPS module is loaded by adding this to the top of your script:

Push-Location
Import-Module SQLPS -DisableNameChecking
Pop-Location

长答案

问题似乎是由于 PowerShell 将工作目录从默认值切换到 SQLPS 提供程序的工作目录以及 SQLPS 提供程序无法识别 UNC 路径的组合(没有一些鼓励).

Long Answer

It seems the issue is due to a combination of PowerShell switching the working directory from the default to that of the SQLPS provider in addition to the fact that the SQLPS provider doesn't recognize UNC paths (without some encouragement).

那么正常的 PowerShell 提示符如下所示:

So where the normal PowerShell prompt looks something like this:

PS C:\Users\foobar>

当您在 SQLPS 提供程序的目录结构中工作时，它将看起来像下面的提示(更多关于这意味着什么这里):

It will instead look like the prompt below when you are working in the directory structure of the SQLPS provider (more on what this means here):

PS SQLSERVER:\>

无论如何，脚本第一次调用 Get-ChildItem 时，很可能是从默认 PowerShell 工作目录的上下文中调用的(其中 UNC 路径可以理解).但是，调用 Invoke-Sqlcmd 会加载 SQLPS 模块，该模块将工作目录更改为无法识别 UNC 路径的提供程序的工作目录.因此，下次调用 Get-ChildItem 时，它是从提供程序的目录结构中完成的，从而引发错误.该问题实际上与过滤器支持无关.

In any event, the first time the script calls Get-ChildItem it is likely done so from the context of the default PowerShell working directory (where UNC paths are understood). However, calling Invoke-Sqlcmd loads the SQLPS module which changes the working directory to that of the provider which does not recognize UNC paths. So the next time Get-ChildItem is called, it is done so from within the directory structure of the provider, raising an error. The issue doesn't actually have anything to do with filter support.

其他人发现的两种解决方法是在 UNC 路径前面加上文件系统提供程序，以便 SQLPS 可以解析它(source):

Two ways others have found around this are to either prefix the UNC path with a file system provider so that SQLPS can parse it (source):

Get-ChildItem Microsoft.PowerShell.Core\FileSystem::\\pcslog1011\dtcs\ContractEmailNotes\ -Filter '*.txt'

或者在 SQLPS 模块加载后切换回默认工作目录.这可能最好通过在脚本开头显式加载 SQLPS 提供程序并在前后添加 Push/Pop-Location cmdlet 来完成(无需对原始脚本进行其他更改).

Or switch back to the default working directory after the the SQLPS module loads. This is perhaps best done by explicitly loading the SQLPS provider at the beginning of your script and adding the Push/Pop-Location cmdlets before and after (no other changes required to the original script).

Push-Location
Import-Module SQLPS -DisableNameChecking
Pop-Location

最后要注意的两件事.首先，我在大约 100 台不同操作系统版本、补丁级别和 PowerShell 版本的服务器上运行我的代码.我只在一些运行 PowerShell 5.1 的服务器上看到过这个问题.其他 5.1 服务器没问题.

Two final things to note. First, I've run my code against about 100 servers on different OS versions, patch levels and PowerShell versions. I've only seen this issue on some servers running PowerShell 5.1. Other 5.1 servers were fine.

其次，这整个事件显然可以通过简单地使用应该替换 SQLPS 的较新的 SQLServer 提供程序来避免.

Second, this whole affair can apparently be avoided by simply by using the newer SQLServer provider that is supposed to replace SQLPS.

这篇关于PowerShell invoke-sqlcmd Get-ChildItem:无法调用方法.提供者不支持使用过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！