• SQL Agent 在Powershell中使用UNC路径错误


    问题

    SQL Agent 在Powershell中使用UNC路径错误,报找不到路径。但不在sql agent中跑一点问题没有

    原因

    SQL Agent 使用的是SQL Server PowerShell,而UNC路径是FileSystem类型的provider
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/719ede23-9f1a-43f5-8c4e-8188fea0b19f/sql-server-2012-importmodule-sqlps-breaks-the-quottestpathquot-powershell-cmdlet?forum=sqlsmoanddmo

    After importing the 'sqlps' module, your location is changed to the SQLSERVER drive, as indicated by the powershell prompt:
    
    PS SQLSERVER:>
    
    This drive is an instance of the SQLSERVER Powershell Provider.
    
    The core powershell cmdlets (Clear-Item, Convert-Path, Copy-Item, Get-Item, Get-Location, Invoke-Item, Join-Path, Move-Item, New-Item, Pop-Location, Push-Location, Remove-Item, Rename-Item, Resolve-Path, Set-Item, Set-Location, Split-Path, Test-Path, ...) will assume the item, path or location you specify as an argument of your cmdlet is relative to the Powershell Provider you are currently using.
    
    The UNC path is meaningful to the FileSystem Provider, but not to the SQLServer Provider (which you are currently using).
    
    To resolve your issue, you could (1) explicitly specify the FileSystem provider in your path or (2) switch to a drive of providertype FileSystem before invoking the Test-Path cmdlet for the second time:
    
    Solution 1:
    Test-Path -path "\serverdirectoryname"
    Import-Module 'sqlps' –DisableNameChecking
    test-path -path  Microsoft.PowerShell.CoreFileSystem::\serverdirectoryname
    
    Solution 2:
    
    Test-Path -path "\serverdirectoryname"
    Import-Module 'sqlps' –DisableNameChecking
    C:
    Test-Path -path "\serverdirectoryname"
    

    意思就是说,UNC路径的Provider是FileSystem类型,但在Agent 里是SQL Server Provider类型了。相当于安卓的充电头识别不了苹果的,需要一种方式转换。

    � LS@DESKTOP-GA9O2OV �� SQLSERVER: � Get-PSDrive
    Name           Used (GB)     Free (GB) Provider      Root                                                                                   CurrentLocation
    ----           ---------     --------- --------      ----                                                                                   ---------------
    Alias                                  Alias
    C                  86.56         30.83 FileSystem    C:                                                                                           UsersLS
    Cert                                   Certificate   
    D                 256.55         74.83 FileSystem    D:
    E                 338.52        261.48 FileSystem    E:
    Env                                    Environment
    F                   0.11        119.14 FileSystem    F:
    Function                               Function
    HKCU                                   Registry      HKEY_CURRENT_USER
    HKLM                                   Registry      HKEY_LOCAL_MACHINE
    I                  24.29         20.16 FileSystem    \192.168.31.89mssql_backup
    SQLSERVER                              SqlServer     SQLSERVER:
    

    解决方法

    1.在脚本里设置location(推荐)

    Set-Location C:
    

    2.在UNC Path里加‘FileSystem::’

    'FileSystem::'+'Path'
    
  • 相关阅读:
    主进程与渲染进程的异同
    Electron node integration enabled 设置
    JS-函数总结
    JS-变量、作用域、垃圾回收机制总结
    python进阶知识笔记
    高级抽象函数
    mac支持的文件系统
    生成器generator & 迭代器iterator
    磁盘如何做才能让系统识别
    winPE盘能做什么
  • 原文地址:https://www.cnblogs.com/ls11736/p/15254078.html
Copyright © 2020-2023  润新知