问题
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'