January 11, 2016 at 11:00 pm
Hi All,
I have a requirement to run 1 or more powershell scripts from via TSQL. Catch here is scripts should be run asynchronously in same Store proc.
Here is the Description:
Store Proc Starts here
BEGIN
Some validations and read statements.
kick off a Powershell Script 2
DO NOT WAIT for above Powershell Script to complete. The Store proc execution should end. once the powershell script is kicked off.
END
End of Store Proc.
January 11, 2016 at 11:08 pm
A couple options:
1. Setup a SQL Agent Job to run each of your PowerShell scripts and start the jobs from within your proc using msdb.dbo.sp_start_job which will start the job and immediately return control to the proc.
2. Setup a Service Broker queue to accept requests and kickoff the relevant PowerShell scripts asynchronously.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2016 at 11:10 pm
PS May I ask what it is these PowerShell scripts do and why you need to kick them off from a T-SQL context and who or what will be calling these stored procs?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2016 at 11:14 pm
I have a series of powershell scripts that i need to call on a server before I enable and start Windows Update service on a production server.Once the server is patched and rebooted, then the powershell script should identify that and run few more commands on the server and start then our application service.
January 11, 2016 at 11:33 pm
I thought of creating SQL Agent Jobs OR Scheduled Tasks and keeping them in disabled mode and call them on demand as you suggested. I have atleast 40 Powershell scripts that needs to be run. (Few Sequentially and few asynchronously). I though I would keep this as my last option coz it looks odd to create jobs then are used only once a month and also high risk of human mistakes as someone might accidentally kick off a job which would take down entire application.
I'll have to dig in more in service broker option or any other available option.
January 12, 2016 at 5:58 am
If you don't have to guarantee that each powershell script fires off the EXACT moment you start it, nor that everything be in some kind of huge distributed transaction, then SQL Service Broker is exactly what you need.
If they need to start RIGHT NOW when you kick off each script you could either use XP_CMDSHELL (not sure if or when this will be deprecated and there are some distinct security risks inherent with allowing it to run at all on your server) or have a series of SQL Agent jobs that you simply start with sp_job_start or whatever the appropriate system sproc is.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply