Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,
    I've got a batch file which used to execute a Powershell script.
    Code:
    powershell -command "& 'C:\JobsScripts\ScriptFiles\AgentJobStatusEmail.ps1' "
    The PoSh script queries multiple instances of SQL Server jobs on their last run time, etc., creates an Excel workbook (using COM object), formats and enters the job data into the worksheet. The workbook is then saved to a folder and from that location, attached to an email and sent to various recipients.
    The PoSh script executes successfully from within the PowerGUI script editor and the batch file executes the PoSh script successfully from a command prompt. (Both executions use my AdminAccount)
    However, when executed via the Task Scheduler, everything seems to be working, except for the creation (and hence the attachment) of the Excel file, as there is no .xlsx file in the saved to folder and an attachment-less email is received. The task itself, is set to use my AdminAccount with "run with highest priviledges" and "Run whether user is logged on or not." checked. All the files reside within their own folders, within the parent folder C:\JobsScripts -- .\BatFiles, .\ExcelFiles, .\ScriptFiles
    I have a NormalUser AD account that I use to log into my work station. Both my NormalUser and my AdminAccount are members of an AD group which is a member of the Administrators group on my work station. For opening the PowerGUI script editor or the command prompt, I use RunAs in a shortcut and use my AD AdminAccount, as it is this account which is a SQL Server sysadmin and member of the Administrators group on all the servers running an instance of SQL Server.
    Code:
    %windir%\system32\runas.exe /user:domain\AdminAccount "C:\WINDOWS\system32\cmd.exe"
    %windir%\system32\runas.exe /user:domain\AdminAccount "C:\Program Files (x86)\PowerGUI\ScriptEditor.exe"
    I can't get the Task Scheduler to open that way, so I have used the extended right click menu (<Shift><Right Click>) and "Run As a Different User" selection using my AdminAccount. This, however, does not grant the elevated Administrator credentials that, I think, are necessary for the creation and manipulation of the Excel worksheet/book. So I have also tried the right click menu to use the "Run As Administrator" and set the schedule I am creating to use my AdminAccount to "run with highest priviledges"....which, of course, it doesn't seem to. To me, it seems as if my AdminAccount credentials are not "filtering down" to the entire PoSh script: I can send an email, but can't build and/or populate an Excel wkbk.

    So....what are the permission(s)/method(s) needed or a work around so I can accomplish this task?

    Thank you.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    5 Star Lounger
    Join Date
    Jan 2010
    Location
    Los Angeles, CA
    Posts
    828
    Thanks
    4
    Thanked 38 Times in 34 Posts
    I have run into a similar problem with executing an application from a PowerShell script running from the Task Scheduler. The script works fine when I am logged in and run it from the PowerShell command line, but when run from the Task Scheduler the application gets access control errors on various files and directories, particularly with creating a file. And like you I have set the task to run under my account. There seems to be some problems will forwarding the security credentials to applications in this scenario.

    I eventually had to find some way arund this restriction. In your case, do you really have to create a XLS document, or would a CSV (comma-separated value) file do? You should be able to create the CSV directly from PowerShell (my scheduled script directly creates a number of files without any access control issues) and email that. Since CSV files are set to be opened my Excel by default, your users migth not even notice the difference. Of course this will only work if all you are doing is filling in tables and columns; if you are making use of other Excel features this might not work for you.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Peter,

    Thank you.
    I do format, highlight, etc. during the excel workbook creation, but a csv file is not totally out of the question....it's just not as pretty and harder to find the failures and/or did not runs. I also am bouncing about the idea of just gathering the data, shoving it into a table and using* Reporting Services for a scheduled, delivered report.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,592
    Thanks
    5
    Thanked 1,059 Times in 928 Posts
    Quote Originally Posted by gdrezek View Post
    I do format, highlight, etc. during the excel workbook creation, but a csv file is not totally out of the question....it's just not as pretty and harder to find the failures and/or did not runs. I also am bouncing about the idea of just gathering the data, shoving it into a table and using Reporting Services for a scheduled, delivered report.
    Have you used Set-ExecutionPolicy to change the default policy from Restricted to what you need?

    Joe
    Joe

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Joe,
    For this I set the execution policy to unrestricted.
    Thanks.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,592
    Thanks
    5
    Thanked 1,059 Times in 928 Posts
    Quote Originally Posted by gdrezek View Post
    For this I set the execution policy to unrestricted.
    Do you use the noprofile option on the pwoershell command. See Running PowerShell Scripts as a Service - Power Tips - PowerShell.com.

    It may also be worth looking at Windows PowerShell Tip: Getting Rid of a COM Object (Once and For All).

    Joe
    Joe

  7. #7
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Joe,
    Do you use the noprofile option on the pwoershell command. See Running PowerShell Scripts as a Service - Power Tips - PowerShell.com.
    No I hadn't. I've been trying it out...I've gotten rid of the batch file and setup a powershell only task in Task Scheduler, with no change in results.
    Code:
    powershell.exe -noprofile -command "& 'C:\JobsScripts\ScriptFiles\AgentJobStatusEmail.ps1' "
    Also, the first line in the article states:
    You should call powershell.exe with the options -noprofile -command like this to run a PowerShell script externally
    So I wonder, is this implying that since this script is acting locally this would not work?


    Thanks for this! I've actually noticed this, but had not paid any attention to it. I've found that this works
    Code:
    $x = New-Object -com Excel.Application
    $x.Visible = $True
    Start-Sleep 5
    $x.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
    Remove-Variable x
    but when I add
    Code:
    $Wkbk = $x.Workbooks.Add()
    $St = $Wkbk.Worksheets.Item(1)
    to make
    Code:
    $x = New-Object -com Excel.Application
    $Wkbk = $x.Workbooks.Add()
    $St = $Wkbk.Worksheets.Item(1)
    $x.Visible = $True
    Start-Sleep 5
    $x.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
    Remove-Variable x
    Excel still remains resident. I can still see the Excel process running using Task Manager. Further testing found that with only the line $St = $Wkbk.Worksheets.Item(1) commented out
    Code:
    $x = New-Object -com Excel.Application
    $Wkbk = $x.Workbooks.Add()
    #$St = $Wkbk.Worksheets.Item(1)
    $x.Visible = $True
    Start-Sleep 5
    $x.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
    Remove-Variable x
    The Excel object would be released and Excel closed. So adding a worksheet makes that code fail.
    I'm still plugging away.....

    Thank you very much.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Joe,
    I did find an answer to the "conundrum" I had with the following:

    The trick was to release each object in turn....as in the code below:

    Code:
    $xl = New-Object -ComObject Excel.Application
    $Wkbk = $xl.Workbooks.Add()
    $WkSh = $Wkbk.Worksheets.Item(1)
    $xl.Visible = $True
    $file = "C:\JobsScripts\ExcelFiles\Test.xlsx"
    $Wkbk.SaveAs($file)
    Start-Sleep 5
    $Wkbk.Close()
    $xl.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WkSh) | out-null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Wkbk) | out-null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | out-null
    [GC]::Collect()
    Remove-Variable WkSh
    Remove-Variable Wkbk
    Remove-Variable xl
    Thanks for your help. I'm still searching for the Task Scheduler solution though......

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,592
    Thanks
    5
    Thanked 1,059 Times in 928 Posts
    Quote Originally Posted by gdrezek View Post
    Hi Joe,
    I did find an answer to the "conundrum" I had with the following:



    The trick was to release each object in turn....as in the code below:

    Code:
    $xl = New-Object -ComObject Excel.Application
    $Wkbk = $xl.Workbooks.Add()
    $WkSh = $Wkbk.Worksheets.Item(1)
    $xl.Visible = $True
    $file = "C:\JobsScripts\ExcelFiles\Test.xlsx"
    $Wkbk.SaveAs($file)
    Start-Sleep 5
    $Wkbk.Close()
    $xl.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WkSh) | out-null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Wkbk) | out-null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | out-null
    [GC]::Collect()
    Remove-Variable WkSh
    Remove-Variable Wkbk
    Remove-Variable xl
    Thanks for your help. I'm still searching for the Task Scheduler solution though......
    Glad you got one thing fixed. I've seen a couple threads on the Task Scheduler but nothing worth while in them. I saw one thing about executing the powershell command from task scheduler directly instead of in a .bat file. Have you tried that?

    Joe
    Joe

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Joe,

    Yes I did try, and have switched to, using the powershell command (instead of calling powershell from a batch file). I mentioned it (it was hiding between a couple of quotes -- sorry I didn't make it clearer) a couple of threads back and was questioning the first line in that article you referred to me......wondering if it implied that since this script is acting locally, and not externally, that it would not work in this case?

    I'm still hoping I can find a workaround because I'd really like to have these scripts run via the Task Scheduler (rather than using SQL Server Agent jobs) on a machine that is not tasked with SQL Server*as well.

    Thank you again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •