Calling Procs from PowerShell with Parameters

  • FrankDolan77

    SSC Veteran

    Points: 279

    Comments posted to this topic are about the item Calling Procs from PowerShell with Parameters

  • SQLShark

    SSCrazy

    Points: 2204

    To clean the quote mess up this works fine, I left out the 2 file variables.

    get-content $file | foreach-object { $_ -replace """" ,""} | set-content $file2 -force

    Write-Host "Wiley Cyote at your service sir"

  • eliassal

    Default port

    Points: 1411

    Hi FRank, useful post, all works fine except when I try the best way by running

    $SqlSqlConn = New-Object System.Data.SqlClient.SqlSqlConnection("Server = $Server; Database = $Database; Integrated Security = True;")

    I get the following error

    New-Object : Cannot find type [System.Data.SqlClient.SqlSqlConnection]: verify that the assembly containing this type is loaded

     

    Which assembly I should load I have dbatools loaded

  • Jeff Moden

    SSC Guru

    Points: 997150

    My question would be... "This is good but how could you do this from a job in SQL Server"?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • FrankDolan77

    SSC Veteran

    Points: 279

    Jeff Moden wrote:

    My question would be... "This is good but how could you do this from a job in SQL Server"?

    That's a great question. I'll see if I can write an article on that.

  • FrankDolan77

    SSC Veteran

    Points: 279

    eliassal wrote:

    Hi FRank, useful post, all works fine except when I try the best way by running

    $SqlSqlConn = New-Object System.Data.SqlClient.SqlSqlConnection("Server = $Server; Database = $Database; Integrated Security = True;")

    I get the following error

    New-Object : Cannot find type [System.Data.SqlClient.SqlSqlConnection]: verify that the assembly containing this type is loaded

    Which assembly I should load I have dbatools loaded

     

    I think this is in the SMO object space when I search on Stack Overflow. Import the SQLPS module, or I'd assume, the SqlServer module.

    https://stackoverflow.com/questions/28506172/powershell-cannot-find-type-system-data-datatable

  • Jeff Moden

    SSC Guru

    Points: 997150

    FrankDolan77 wrote:

    Jeff Moden wrote:

    My question would be... "This is good but how could you do this from a job in SQL Server"?

    That's a great question. I'll see if I can write an article on that.

    In the meantime, is there a reasonably quick answer you could share?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • FrankDolan77

    SSC Veteran

    Points: 279

    I haven't done this myself. I've mostly been running PoSh interactively for administrative scripting items. All my Agent jobs are T-SQL based or they use some batch scripting.

    I was looking at this link: https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15

    as a basis for writing. I suspect that I will need to get the Agent service account to use a profile, or ensure that my PoSh script has some Import-Module items at the beginning to ensure the environment is set.

  • frederico_fonseca

    SSChampion

    Points: 14748

    although you could do it through a PS script directly on the step I prefer, and advise, using a command prompt instead and start an instance of powershell - more versatility and less prone to "errors" due to the PS instance not allowing all that the command line one does - And I do not remember now which issue i got when I used it before

  • Jeff Moden

    SSC Guru

    Points: 997150

    FrankDolan77 wrote:

    I haven't done this myself. I've mostly been running PoSh interactively for administrative scripting items. All my Agent jobs are T-SQL based or they use some batch scripting.

    I was looking at this link: https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15

    frederico_fonseca wrote:

    although you could do it through a PS script directly on the step I prefer, and advise, using a command prompt instead and start an instance of powershell - more versatility and less prone to "errors" due to the PS instance not allowing all that the command line one does - And I do not remember now which issue i got when I used it before

    as a basis for writing. I suspect that I will need to get the Agent service account to use a profile, or ensure that my PoSh script has some Import-Module items at the beginning to ensure the environment is set.

    To wit, I use xp_CmdShell for a whole lot.  Common to what appears to be general public opinion, it's not a security risk unless one is dumb enough to grant an unwise low-prived use the privs to call it directly.  The real risk of xp_CmdShell is letting an attacker get in with sysadmin or controlserver privs and then they don't need xp_CmdShell to cause a lot of damage or make off with a payload and do it in an invisible fashion.  In fact, under such circumstances, they could even turn on xp_CmdShell, use it, turn it off, and reset the logs to cover their tracs.  Having it turned off isn't even a speedbump for an attacker that makes it in with such privs.

    And it's truly an amazing tool that you can use to actually decrease the privs required even for some "super users".

    In other words, instead of calling stored procedures from Powershell, I use stored procedures to call Powershell... and WMIC... and (what people still refer to as) DOS... and anything else that I might need.  As strange as it may seem, I even use store procedures to call xp_CmdShell to call SQLCmd to call other procs to do some amazing things with the output returned from the OUTPUT of xp_CmdShell especially where BULK INSERT comes into play.

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden

    SSC Guru

    Points: 997150

    FrankDolan77 wrote:

    I haven't done this myself. I've mostly been running PoSh interactively for administrative scripting items. All my Agent jobs are T-SQL based or they use some batch scripting.

    I was looking at this link: https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15

    as a basis for writing. I suspect that I will need to get the Agent service account to use a profile, or ensure that my PoSh script has some Import-Module items at the beginning to ensure the environment is set.

    No problem.  Other that creating a parameter table the job reads from, I don't know anyone else that has been able to do it either.  When I read your article, I though "Perhaps he's the one that knows" and there was nothing to lose by asking.

    Anyway, thank you for the great article.  I appreciate any time someone steps up to share good information.

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • FrankDolan77

    SSC Veteran

    Points: 279

    Thanks for the complement, Jeff. I think you've written some great articles and I always look forward to reading them.

    I'll mess around with it and see. I think using the cmdexec or xp_cmdshell would be the same, so I'll mention them in an article.

  • frederico_fonseca

    SSChampion

    Points: 14748

    if you going for an article on this I suggest you highlight the differences between the Powershell version that SQL Agent uses vs the "windows installed" version - also the fact that with SQL 2019 you can disable the SQLPS version and use the installed one instead - see https://docs.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-ver15

  • frederico_fonseca

    SSChampion

    Points: 14748

    Jeff Moden wrote:

     Other that creating a parameter table the job reads from, I don't know anyone else that has been able to do it either.

    out of curiosity what did you mean with this?

  • Jeff Moden

    SSC Guru

    Points: 997150

    frederico_fonseca wrote:

    Jeff Moden wrote:

     Other that creating a parameter table the job reads from, I don't know anyone else that has been able to do it either.

    out of curiosity what did you mean with this?

    I'm just talking about a table in a database that you could have something populate with whatever "parameters" a job needs (even in job steps to "pass parameters" to the next step).  I've also not seen people pass parameters directly from one step in a job to another.  With the understanding that I'm definitely NOT an SSIS Ninja by any stretch of the imagination, I wouldn't mind learning how to do such a thing if it can be done without such a "parameter table".

    Do you know of a way to pass parameters forward from one job step to another?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 15 posts - 1 through 15 (of 16 total)

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