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: Today @ 7:45 AM
Points: 37,832, Visits: 34,702
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: Wednesday, May 6, 2015 2:40 PM
Points: 17, Visits: 138
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: Today @ 11:02 AM
Points: 7,255, Visits: 13,039
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: Wednesday, May 6, 2015 2:40 PM
Points: 17, Visits: 138
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
Posted Tuesday, May 12, 2015 9:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 9, 2015 7:12 PM
Points: 3, Visits: 7
Hi

I am trying to schedule this powershell script but facing issues.

$cgs ="\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\3M ANDRG GROUPER v2013.1\cgs_console.exe"
$ip1 = "-input"
$ipt1 = "-input_template"
$ipt2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\templates\ar50in.dic"
$up1 = "-upload"
$upt1 = "-upload_template"
$upt2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\templates\chboiout.dic"
$err = "-error_log"
$usk1 = "-userkey1"
$usk2 = "8th Edn - 6.0x"
$usk3 = "8th Edn - 7.0"
$usk4 = "8th Edn - 5.1"
$usk5 = "8th Edn - 4.2"
$usk = $usk2,$usk3,$usk4,$usk5
$fileDirec = "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract"
$FilesArchive = "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract\Archive"
$files1 = get-childitem $fileDirec\*.txt
foreach($file1 in $files1)
{
#[System.Windows.Forms.MessageBox]::Show("We are proceeding with next step.")
foreach($bkey in $usk)
{
$upfile = [io.path]::GetFileNameWithoutExtension($file1)
#write-host $upfile
$up2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\Output\$upfile $bkey $(get-date -f yyyy-MM-dd-hhmmss).csv"
$err2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\Error\$upfile $bkey $(get-date -f yyyy-MM-dd-hhmmss).txt"
& $cgs $ip1 $file1 $ipt1 $ipt2 $up1 $up2 $upt1 $upt2 $err $err2 $usk1 $bkey
}
#[System.Windows.Forms.MessageBox]::Show($file1)
move-item $File1 $FilesArchive
}


This script runs fine from powershell or cmd. But the problem comes in sql server either i run from cmd of ps.

I am getting error this error through powershell on agent

Message
Unable to start execution of step 6 (reason: line(28): Syntax error). The step failed.


getting this message if from cmd on sql agent

The term '\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\3M ANDR
G GROUPER v2013.1\cgs_console.exe' is not recognized as the name of a cmdlet, f
unction, script file, or operable program. Check the spelling of the name, or i
f a path was included, verify that the path is correct and try again.
At \\SQL-DWH-DEV\STAGINGFILES\Casemix_New.ps1:30 char:2
+ & <<<< "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\3M AND
RG GROUPER v2013.1\cgs_console.exe" $ip1 $file1 $ipt1 $ipt2 $up1 $up2 $upt1 $up
t2 $err $err2 $usk1 $bkey
+ CategoryInfo : ObjectNotFound: (\\epwh.ad\EPWOR...cgs_console.e
xe:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
Post #1684947
Posted Wednesday, May 13, 2015 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 7,255, Visits: 13,039
When you run in a job it uses a service account to run the commands. Ensure your service account can access all the resources needed by your script.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1685045
Posted Wednesday, May 13, 2015 6:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 9, 2015 7:12 PM
Points: 3, Visits: 7
Thanks for your reply Orlando

I created proxies and yes the account can access the folders and files.
I created proxy for cmd and ran the job from that account and created proxy for powershell and run job from there.

When I run the cmd by it self its working good. the problem is when i run through agent.

Thanks
Post #1685315
Posted Wednesday, May 13, 2015 8:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 7,255, Visits: 13,039
Can you script the entire job out and post it here?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1685320
Posted Wednesday, May 13, 2015 8:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 9, 2015 7:12 PM
Points: 3, Visits: 7
Thanks for reply Orlando

Please find the script here

USE [msdb]
GO

/****** Object: Job [CaseMix_Automation] Script Date: 14/05/2015 12:15:10 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 14/05/2015 12:15:10 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CaseMix_Automation',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'EPWH\SatyaKo', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CaseMix Package] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaseMix Package',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/ISSERVER "\"\SSISDB\revenue\casemix\1_Generate Casemix_ExcelFiles.dtsx\"" /SERVER "\"SQL-DWH-DEV\DWH_DEV\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@database_name=N'master',
@flags=0,
@proxy_name=N'bi_etl_genuser_proxy'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [powersehll] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'powersehll',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'powershell.exe -ExecutionPolicy ByPass -File "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract\Powershell\Casemix_New.ps1"
',
@flags=32,
@proxy_name=N'bi_etl_genuse_proxy_cmd'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [powertest] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'powertest',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'# Create by Satya Konathala
# Purpose to Automate the process of running cgs_console.exe automatically

$cgs ="\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\3M ANDRG GROUPER v2013.1\cgs_console.exe"
$ip1 = "-input"
$ipt1 = "-input_template"
$ipt2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\templates\ar50in.dic"
$up1 = "-upload"
$upt1 = "-upload_template"
$upt2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\templates\chboiout.dic"
$err = "-error_log"
$usk1 = "-userkey1"
$usk2 = "8th Edn - 6.0x"
$usk3 = "8th Edn - 7.0"
$usk4 = "8th Edn - 5.1"
$usk5 = "8th Edn - 4.2"
$usk = $usk2,$usk3,$usk4,$usk5
$fileDirec = "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract"
$FilesArchive = "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract\Archive"
$files1 = get-childitem $fileDirec\*.txt
foreach($file1 in $files1)
{
#[System.Windows.Forms.MessageBox]::Show("We are proceeding with next step.")
foreach($bkey in $usk)
{
$upfile = [io.path]::GetFileNameWithoutExtension($file1)
#write-host $upfile
$up2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\Output\$upfile $bkey $(get-date -f yyyy-MM-dd-hhmmss).csv"
$err2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\Error\$upfile $bkey $(get-date -f yyyy-MM-dd-hhmmss).txt"
& "$cgs" $ip1 "$file1" $ipt1 "$ipt2" $up1 "$up2" $upt1 "$upt2" $err "$err2" $usk1 "$bkey"
}
#[System.Windows.Forms.MessageBox]::Show($file1)
move-item $File1 $FilesArchive
}
',
@database_name=N'master',
@flags=0,
@proxy_name=N'bi_etl_genuser_proxy_ps'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [cmdtest] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'cmdtest',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'cmd.exe /c "\\SQL-DWH-DEV\STAGINGFILES\calltesting.bat"',
@flags=0,
@proxy_name=N'bi_etl_genuse_proxy_cmd'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Post #1685323
Posted Wednesday, May 13, 2015 8:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 7,255, Visits: 13,039
If you setup Step 6 the same as Step 5 where it is a call to PowerShell.exe passing a script location on disk instead of a PowerShell step type with inline code you'll have a better chance. You'll need a proxy setup for the OS Exec step type.

The PowerShell step type was improved in SQL2012 but still does not offer the same user experience as running from a stand alone PowerShell session.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1685324
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse