August 20, 2020 at 12:00 am
Comments posted to this topic are about the item Calling Procs from PowerShell with Parameters
August 20, 2020 at 1:54 pm
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"
August 23, 2020 at 2:56 pm
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
August 23, 2020 at 7:03 pm
My question would be... "This is good but how could you do this from a job in SQL Server"?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2020 at 12:16 am
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
August 24, 2020 at 7:28 am
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
Change is inevitable... Change for the better is not.
August 24, 2020 at 6:25 pm
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.
August 24, 2020 at 7:28 pm
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
August 24, 2020 at 10:40 pm
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
Change is inevitable... Change for the better is not.
August 24, 2020 at 10:53 pm
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
Change is inevitable... Change for the better is not.
August 25, 2020 at 3:19 pm
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.
August 25, 2020 at 4:23 pm
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
August 25, 2020 at 10:45 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply