Error running execute xp_cmdshell

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  January 5, 2018 at 12:45 pm

  #330782

  Hi All
  I can run this from the command prompt
  C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Unrestricted -File C:\DailyEtlData\EtlScripts\Powershell\MoveFileDirtoDir.ps1 -SourceServer C:\inetpub\ftproot\ -DestinationServer C:\DailyEtlData\Connect\PreLoadFilesFromFtp\ -Filename 20171202.dump.gz

  if I try to run it from SQL
  execute xp_cmdshell N'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Unrestricted -File C:\DailyEtlData\EtlScripts\Powershell\MoveFileDirtoDir.ps1 -SourceServer C:\inetpub\ftproot\ -DestinationServer C:\DailyEtlData\Connect\PreLoadFilesFromFtp\ -Filename 20171202.dump.gz'

  I get this error 
  Copy-Item : Cannot find path 'C:\inetpub\ftproot\20171202.dump.gz' because it does not exist.
  At C:\DailyEtlData\EtlScripts\Powershell\MoveFileDirtoDir.ps1:9 char:1
  + Copy-Item -Path $Source -Destination $Destination;
  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo    : ObjectNotFound: (C:\inetpub\ftproot\20171202.dump.gz:String) [Copy-Item], ItemNotFoundEx
   ception
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.CopyItemCommand

  any help would be good thanks and Happy New Year

• RandomStream

  Hall of Fame

  Points: 3735

  More actions

  January 5, 2018 at 12:55 pm

  #1974754

  When you run from command shell, I think it assumes the account used by SQL Server, which may not have permission to access the source directory if it is using local system account.

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  January 5, 2018 at 1:17 pm

  #1974760

  RandomStream - Friday, January 5, 2018 12:55 PM

  When you run from command shell, I think it assumes the account used by SQL Server, which may not have permission to access the source directory if it is using local system account.

  Thanks Random
  I will take a look on Monday when I'm back online Many thanks

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  January 9, 2018 at 3:55 am

  #1975044

  Thanks all that did the trick