Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Call powershell in SQL agent job Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 3:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 37,056, Visits: 31,619
jobudzin (11/19/2013)
I have a similar problem. I am attempting to assign a value to a variable and then execute the powershell script using that variable. The SQL agent job step is CmdExec and it executes or completes successfully but does not update the underlying table like it does in using the powershell window alone. Here is the code from the job step:

powershell.exe "$dt = D:\PSscripts\Invoke-Sqlcmd2.ps1 -ServerInstance 'JAXSQLMON01' -Database DBACentral -Query 'Select server_name from server_instance' | foreach-object {D:\PSscripts\Invoke-Sqlcmd2.ps1 -ServerInstance $_.server_name -Database master -InputFile ./D:\PSscripts\get-dbspace.sql -As 'DataRow'}"

powershell.exe "D:\PSscripts\Write-DataTable.ps1 -ServerInstance 'JAXSQLMON01' -Database 'DBACentral' -TableName 'db_Space' -Data $dt"


Any help would be greatly appreciated. When I run this using regular powershell window it creates the variable and then populates the table with the file sizes which I am trying to collect.


I don't know much about PowerShell but those are two different calls to PowerShell and the variable loses scope between the calls. You need to write this as a single PowerShell script in order for that Second Part to recognize the $dt variable from the first part.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1515815
Posted Wednesday, November 20, 2013 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 6:27 AM
Points: 17, Visits: 128
That is what I was originally thinking, so then I am guessing I would need to create a brand new powershell script which encompasses the commands which I provided? I have been tinkering around with something like that to no avail. I would think this would be easier but since I am new to powershell it seems quite difficult. So from my understanding we cannot use the powershell type for a job step if it is going to execute powershell scripts, only lines of powershell code? That is the most frustrating part is that I have this working successfully using powershell but when it comes to implementing into a SQL Agent job it always becomes a bigger hassle and I end up spending most of my time with trial and error. I don't understand why we can just take the commands we ran in a powershell window, calling a script or not, and just paste into a SQL agent job step of type powershell and let it run. So I guess my biggest challenge, is how do i get my powershell which runs successfully into a SQL agent job? I have heard that the powershell type has limitations so I will use CmdExec, but it is still not working.
Post #1516063
Posted Wednesday, November 20, 2013 9:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:59 PM
Points: 7,124, Visits: 12,652
jobudzin (11/20/2013)
That is what I was originally thinking, so then I am guessing I would need to create a brand new powershell script which encompasses the commands which I provided? I have been tinkering around with something like that to no avail. I would think this would be easier but since I am new to powershell it seems quite difficult. So from my understanding we cannot use the powershell type for a job step if it is going to execute powershell scripts, only lines of powershell code? That is the most frustrating part is that I have this working successfully using powershell but when it comes to implementing into a SQL Agent job it always becomes a bigger hassle and I end up spending most of my time with trial and error. I don't understand why we can just take the commands we ran in a powershell window, calling a script or not, and just paste into a SQL agent job step of type powershell and let it run. So I guess my biggest challenge, is how do i get my powershell which runs successfully into a SQL agent job? I have heard that the powershell type has limitations so I will use CmdExec, but it is still not working.

In my earlier post I explain why, in SQL Server 2008, the PowerShell experience in SQL Server Agent is not what it could be.

It might be of little consolation at this point, but the experience has improved in SQL Server 2012.

For your situation, one option is to save all your commands to a single PowerShell script and execute that using powershell.exe from a CmdExec step. This way all commands will execute within the same PowerShell session and in your example $dt will have a value when being passed into Write-DataTable. That's pretty much what I have resorted to doing to schedule my PowerShell scripts. I would also recommend using SQL Agent Proxy account so you can get granular on what permissions each script runs with.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516112
Posted Wednesday, November 20, 2013 11:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 6:27 AM
Points: 17, Visits: 128
Thanks! Ill have to continue with creating a single powershell script which holds these commands and then include that into a job step. Appreciate the help.
Post #1516149
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse