trying to call powershell from TSQL statement

  • Hello,

    trying to call a powershell file I created, locally on the server, but also, made sure since this has an always on cluster, to first check if its PRIMARY, BUT, what i am having trouble with, is using the xp_cmdshell command... when i run the following:

    EXEC  xp_cmdshell 'powershell.exe -File E:\folder\RunToExport.ps1'

    it gives the following error message:

    The argument 'E:\folder\RunToExport.ps1' to the -File parameter does not exist. Provide the path to an existing '.ps1' file as an argument to the -File parameter.

    I KNOW KNOW for sure it works, i know it runs, and the file is there on that path... but why does it not see it? and their should be a -File parameter...

     

  • Run the following code from SSMS on the server that you're trying to run you call from...

    EXEC xp_cmdshell 'WHOAMI';

    ... then tell us who or what that login is (DON'T POST THE ACTUAL LOGIN!!!!  We just need to know if it's the server login or you or something else).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thanks Jeff, its the Nt service default login, should i grant that login to the path?

  • Siten0308 wrote:

    thanks Jeff, its the Nt service default login, should i grant that login to the path?

    Is that E: Drive on the server where you're running this or on your local machine?  And where are you running the code from?  If you're running it from SSMS on your desktop and the server is on a different machine, then you may be fighting the infamous "local machine v.s. server" problem.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am currently running the TSQL statement on the server directly.

    i gave the built in sql server user account full access to that path, sadly still same error message.

     

  • I am curious as to why you need to call out to PS directly from SQL Server.  If I need to call a PS script from SQL Server - I will put that in an agent job, using the command subsystem - calling powershell.

    With that said - most of the time I don't need to start from SQL Server, so my PS scripts are written to access SQL Server and pull the data they need.  This way, they just need to be run from task scheduler for automated processing.

    Can you elaborate on what this script is doing - and why it needs to be called from SQL?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ugh... so one thing, i realized... is always check the naming... of the file... really sorry, everything you mentioned is great troubleshooting... when i found the file.. it was 1 letter off lol... really really sorry... its friday... and i am ready for the weekend.

  • Siten0308 wrote:

    ugh... so one thing, i realized... is always check the naming... of the file... really sorry, everything you mentioned is great troubleshooting... when i found the file.. it was 1 letter off lol... really really sorry... its friday... and i am ready for the weekend.

    Man, thanks for owning up to that!  Now I don't feel so stupid when I do it. 😀  It happens!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeffrey Williams wrote:

    I am curious as to why you need to call out to PS directly from SQL Server.  If I need to call a PS script from SQL Server - I will put that in an agent job, using the command subsystem - calling powershell.

    With that said - most of the time I don't need to start from SQL Server, so my PS scripts are written to access SQL Server and pull the data they need.  This way, they just need to be run from task scheduler for automated processing.

    Can you elaborate on what this script is doing - and why it needs to be called from SQL?

    Understood but the problem is, if it doesn't work from the command prompt, there's a high probability it's not going to work using the tools you describe above.

    As for PowerShell calling SQL Server instead of the other way around, we'll have to have a fun discussion about that sometime in the future. 😀

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply