Calling Procs from PowerShell with Parameters

Question

Post reply

Calling Procs from PowerShell with Parameters

Frank Dolan

Say Hey Kid

Points: 661
More actions
August 20, 2020 at 12:00 am

#3780042

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

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

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

SQLShark SSCrazy Points: 2204 More actions · Answer 1

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: 1467 More actions · Answer 2

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: 1003863 More actions · Answer 3

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.

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

Frank Dolan Say Hey Kid Points: 661 More actions · Answer 4

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.

Frank Dolan Say Hey Kid Points: 661 More actions · Answer 5

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: 1003863 More actions · Answer 6

Frank Dolan 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.

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

Frank Dolan Say Hey Kid Points: 661 More actions · Answer 7

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 SSCoach Points: 16017 More actions · Answer 8

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: 1003863 More actions · Answer 9

Frank Dolan 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.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

Frank Dolan 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.

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

Frank Dolan Say Hey Kid Points: 661 More actions · Answer 11

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 SSCoach Points: 16017 More actions · Answer 12

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 SSCoach Points: 16017 More actions · Answer 13

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: 1003863 More actions · Answer 14

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.

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