March 29, 2013 at 10:04 am
I use SQL server agent job to call a powershell script. I am using Type operating system(Cmdexec)
In the job command window, I type:
powershell.exe "D:\PowershellScripts\Write-VolToDb.ps1 'MySQLserver\v2012' dba"
OR
powershell.exe "& D:\PowershellScripts\Write-VolToDb.ps1 MySQLserver\v2012 dba"
It is not working. The scipt self is OK, but I just cannot figure out what is the correct syntax to call it.
The 'MySQLserver\v2012' is my server name\instanceName, dba is the database name. They are two parameter of the powershell.
What is correct syntax of it,
Thanks
--------------------------------------------------------------------------------
March 29, 2013 at 10:58 am
What is error message? I assume you considered the agent account and it's access on the target sql server.
March 29, 2013 at 11:31 am
Sorry, it works now.
I found out the path for the file is wrong.
Thanks much
April 1, 2013 at 4:12 am
Glad to hear it's fixed.
Just curious though, why not use a PowerShell type of step in SQL Agent for a PowerShell script?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2013 at 1:10 pm
Grant Fritchey (4/1/2013)
Glad to hear it's fixed.Just curious though, why not use a PowerShell type of step in SQL Agent for a PowerShell script?
Depending on what your script does sometimes you just can't. The SQL Agent PowerShell Step Type in 2008 R2 executes the code it is handed within a closed shell named sqlps.exe (referred to as mini-shell all over the net). There are tons of limitations but the most prominent one I could think of is that the PowerShell Step Type does not implement a default output host meaning that any CmdLet that uses Write-Host, analog to T-SQL PRINT, will throw an error like this:
Cannot invoke this function because the current host does not implement it.
Some of the in-built CmdLets (e.g. Remove-Item iirc) assume there will be an output host and in some cases will try to output information messages so it is simply not safe to use those in a PowerShell Step Type.
I would need to verify but in SQL 2012 I think PowerShell Steps were changed to use a full-blown PowerShell v2 shell, i.e. no more of the limitations imposed by 2008 R2's use of sqlps.exe.
This article explains that in 2008 R2 developing sqlps.exe solved some implementation concerns for the SQL Server team, namely locking us into a closed shell would allow them to lock down user scenarios and avoid problems with third-party snap-ins. This was great for the SQL Team in terms of being able to deliver a stable product but it created many other limitations for people like myself that like to forego T-SQL and use PowerShell for tasks that need to interact with more than just a single database engine, e.g. syncing the Server Logins from a primary instance to a DR-instance, and run them from a SQL Agent Job step.
In 2008 R2 I end up doing the same as the OP, namely executing my ps1 scripts using powershell.exe in a CmdExec step type. Once I get more into figuring out how the changes in SQL 2012 have changed the UX I may change my approach.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 1, 2013 at 2:23 pm
Grant Fritchey (4/1/2013)
Glad to hear it's fixed.Just curious though, why not use a PowerShell type of step in SQL Agent for a PowerShell script?
I found it runs much faster using cmdexe type vs powshell type to run the jobs.
I read an article telling what is the difference , it seems they are using different powershell module to run the job. I cannot find the article now by doing some search.
For my cases, I can use both, but it takes much shorter time for using cmdexec to trigger the job and finish the job.
April 1, 2013 at 3:48 pm
sqlfriends (4/1/2013)
Grant Fritchey (4/1/2013)
Glad to hear it's fixed.Just curious though, why not use a PowerShell type of step in SQL Agent for a PowerShell script?
I found it runs much faster using cmdexe type vs powshell type to run the jobs.
I read an article telling what is the difference , it seems they are using different powershell module to run the job. I cannot find the article now by doing some search.
For my cases, I can use both, but it takes much shorter time for using cmdexec to trigger the job and finish the job.
🙂
Shifting gears, do you have any auditing in place to audit your PowerShell runs at the OS Level? Has nothing to do with your current problem. I'm just curious. Thanks for any feedback on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2013 at 2:10 pm
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.
November 19, 2013 at 3:21 pm
Jeff Moden (4/1/2013)
sqlfriends (4/1/2013)
Grant Fritchey (4/1/2013)
Glad to hear it's fixed.Just curious though, why not use a PowerShell type of step in SQL Agent for a PowerShell script?
I found it runs much faster using cmdexe type vs powshell type to run the jobs.
I read an article telling what is the difference , it seems they are using different powershell module to run the job. I cannot find the article now by doing some search.
For my cases, I can use both, but it takes much shorter time for using cmdexec to trigger the job and finish the job.
🙂
Shifting gears, do you have any auditing in place to audit your PowerShell runs at the OS Level? Has nothing to do with your current problem. I'm just curious. Thanks for any feedback on this.
I guess I'll have to take the silence as a "No". Thanks anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2013 at 3:22 pm
No, we don't. Sorry for the late response.
November 19, 2013 at 3:23 pm
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
Change is inevitable... Change for the better is not.
November 20, 2013 at 7:28 am
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.
November 20, 2013 at 9:35 am
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
November 20, 2013 at 11:04 am
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.
May 12, 2015 at 9:52 pm
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply