locked
Issues running script as scheduled task RRS feed

  • Question

  • I've checked the basics.. the scheduled task is running under a system account with permissions to SQL Server, the local server (is in the administrators group), and has full permission to remote target (copy-item).  I tried using my domain account, which has permission to all of those things as well (the script runs just fine when I run it from the server logged on as my using remote desktop). 

    I've tinkered with a lot of other things too, like changing relative paths to absolute paths, and putting the scripts folder in the "start in" field of the scheduled task, etc., and I just cannot get this thing to work.  Any suggestions?

    Clear-Host
    
    
    # Writes entity to network, invoked by a scheduled task
    
    $vResultsFile1 = "C:\MyFolder\PROD\Metadata\" + (((Get-Date -Format o).Replace("T","-")).Replace(":","")).Substring(0,17) + "_entity_full.csv"
    $vResultsFile2 = "C:\MyFolder\PROD\Metadata\" + (((Get-Date -Format o).Replace("T","-")).Replace(":","")).Substring(0,17) + "_entity_NoShares.csv"
    
    
    # FULL ENTITY DIMENSION
    Write-host "Running query"
    $results = Invoke-Sqlcmd -Query "select * from MyView" -ConnectionString "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;" 
    
    Write-host "Writing Results"
    $results | convertto-csv -Delimiter "," -NoTypeInformation | out-file $vResultsFile1 -fo -en ascii
    
    Write-host "Copy file to network location"
    Copy-Item $vResultsFile1 -Destination "\\123.45.67.8\MyRemoteShare\" 
    
    Write-host "Delete file"
    Remove-Item $vResultsFile1
    
    
    
    
    # ENTITY DIMENSION NO SHARES
    Write-host "Running query"
    $results = Invoke-Sqlcmd -Query "select * from MyView where DataStorage <> 'shared'" -ConnectionString "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;" 
    
    Write-host "Writing Results"
    $results | convertto-csv -Delimiter "," -NoTypeInformation | out-file $vResultsFile2 -fo -en ascii
    
    Write-host "Copy file to network location"
    Copy-Item $vResultsFile2 -Destination "\\123.45.67.8\MyRemoteShare\" 
    
    Write-host "Delete file"
    Remove-Item $vResultsFile2
    
    

     
    Monday, July 2, 2018 2:03 PM

All replies

  • You fail to say what doesn't work.  What are the error messages?

    Also note that the SYSTEM account is restricted to remote systems. You will need to use either a service account or a user account.


    \_(ツ)_/

    Monday, July 2, 2018 2:09 PM
  • use a User Account (on the domain with privileges on everything, SQL, and  Domain), instead of a system account.

    Remember to give this user the required privileges and try again :)


    • Edited by j0rt3g4 Tuesday, July 3, 2018 5:01 AM
    Tuesday, July 3, 2018 5:00 AM