September 8, 2017 at 1:53 pm
Hi Everyone
Is there a way to convert this SQL Agent Job Step to Powershell?
it's being executed with Type OperatingSystem(CmdExec)
if master.dbo.fn_hadr_group_is_primary('WEB_AG') = 1
BEGIN
D:\ClientPortalApp\PROD\GCRPSendIntroEmailCNSLE.exe;
END;
ELSE
BEGIN
PRINT 'This is not primary replica ';
END;
I tried single and double quotes but nothing changes.
I get an error " = was unexpected at this time. Process Exit Code 1. The step failed."
Thank you
September 8, 2017 at 4:24 pm
Why on earth do you want to convert it to PowerShell, which is really not much more than a CLI to begin with?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2017 at 8:29 pm
Hi Jeff
Using exec xp_cmdshell works but the requirement is to be done in PS.
September 11, 2017 at 9:08 am
I'm with Jeff, why?
SQL Agent and PowerShell are orthogonal. They don't depend on each other. I can have an Agent Job using, or not using, PoSh. I can have a PoSh script run in, or outside of, Agent.
If you really want to do this , the PoSh I've seen just runs this T-SQL.
September 11, 2017 at 11:10 am
AlexSQLForums - Friday, September 8, 2017 8:29 PMHi Jeff
Using exec xp_cmdshell works but the requirement is to be done in PS.
Just to confirm the problem, you want this to run in powershell entirely OR do you want a powershell step of a SQL job?
If it is a powershell step of a SQL job, you could have it as a 2 step SQL job. First step does the check in TSQL to run
if master.dbo.fn_hadr_group_is_primary('WEB_AG') = 1
If it is true, then you succeed that step
If it is false, print "this is not a primary replica", then you fail the step
In the advanced job step options, you have your on success action to go to step 2 which runs the exe in question via powershell. And set your failure action to "quit the job reporting success".
If the entire thing needs to be in powershell (ie you do not wish to run this from the SQL Server Agent, but from windows task manager), I would build a stored procedure first for the query you have as something like:CREATE PROCEDURE [dbo].[ProcName]
AS
IF master.dbo.fn_hadr_group_is_primary('WEB_AG') = 1
RETURN 1
ELSE
RETURN 0
END
Then for your powershell, something like this:$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=machine1;Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.data.sqlclient.sqlcommand("EXEC [database].[dbo].[ProcName]", $cn2);
$cn2.Open();
if ($cmd.ExecuteNonQuery() -eq 0)
{
echo "This is not primary replica";
}
else
{
D:\ClientPortalApp\PROD\GCRPSendIntroEmailCNSLE.exe;
}
$cn2.Close();
NOTE - there are better options for SQL code such as Invoke-SQLCMD, but the above works if you don't have the powershell SQL tools installed and/or using an old version of powershell.
I did not test any of the above and it could be simplified, but I believe the above should work.
Now, the reason you got the " = was unexpected at this time. Process Exit Code 1. The step failed." error is because according to the command prompt, the "=" character was unexpected. Which makes sense as the whole master.dbo.fn_hadr_group_is_primary('WEB_AG') is not parseable from the command prompt (ie cmdexec). And "print" is not the command you want to run from the command line either; you want "ECHO". If you are trying to build up a CmdExec command to run, you should build that from the command prompt. The easiest way is to build a .bat file. If that runs successfully, then copy-pasting that into the job step that uses CmdExec should also run successfully. I say "should" because if you are relying on relative paths instead of absolute paths or running it with network share drive letters instead of UNC paths, you can sometimes get inconsistencies with running it from the command line and running it from a SQL job step..
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 11, 2017 at 9:19 pm
AlexSQLForums - Friday, September 8, 2017 8:29 PMHi Jeff
Using exec xp_cmdshell works but the requirement is to be done in PS.
Why? Is it because someone on the team believes that xp_CmdShell is a security risk? I have a 75 minute presentation that shows you it' not. 😉
But if it's got to be PoSh, I can't help there. I'll be learning along with you.
p.s. Simple CmdExec in the job would do it without either xp_CmdShell or PoSH. But, if you were to use xp_CmdShell, it's uber simple to capture the feedback so you can programmatically determine if the job ran fine or not and what type of email you might want to send either way. It's also pretty handy for keeping a trail of breadcrumbs for when the auditors descend upon you. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2017 at 9:20 am
Steve Jones - SSC Editor - Monday, September 11, 2017 9:08 AMI'm with Jeff, why?SQL Agent and PowerShell are orthogonal. They don't depend on each other. I can have an Agent Job using, or not using, PoSh. I can have a PoSh script run in, or outside of, Agent.
If you really want to do this , the PoSh I've seen just runs this T-SQL.
Hi Steve
The client wants this step in powershell and does not want xp_cmdshell due to auditing.
September 12, 2017 at 9:22 am
bmg002 - Monday, September 11, 2017 11:10 AMAlexSQLForums - Friday, September 8, 2017 8:29 PMHi Jeff
Using exec xp_cmdshell works but the requirement is to be done in PS.Just to confirm the problem, you want this to run in powershell entirely OR do you want a powershell step of a SQL job?
If it is a powershell step of a SQL job, you could have it as a 2 step SQL job. First step does the check in TSQL to run
if master.dbo.fn_hadr_group_is_primary('WEB_AG') = 1If it is true, then you succeed that step
If it is false, print "this is not a primary replica", then you fail the step
In the advanced job step options, you have your on success action to go to step 2 which runs the exe in question via powershell. And set your failure action to "quit the job reporting success".If the entire thing needs to be in powershell (ie you do not wish to run this from the SQL Server Agent, but from windows task manager), I would build a stored procedure first for the query you have as something like:
CREATE PROCEDURE [dbo].[ProcName]
AS
IF master.dbo.fn_hadr_group_is_primary('WEB_AG') = 1
RETURN 1
ELSE
RETURN 0
END
Then for your powershell, something like this:$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=machine1;Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.data.sqlclient.sqlcommand("EXEC [database].[dbo].[ProcName]", $cn2);
$cn2.Open();
if ($cmd.ExecuteNonQuery() -eq 0)
{
echo "This is not primary replica";
}
else
{
D:\ClientPortalApp\PROD\GCRPSendIntroEmailCNSLE.exe;
}
$cn2.Close();
NOTE - there are better options for SQL code such as Invoke-SQLCMD, but the above works if you don't have the powershell SQL tools installed and/or using an old version of powershell.
I did not test any of the above and it could be simplified, but I believe the above should work.Now, the reason you got the " = was unexpected at this time. Process Exit Code 1. The step failed." error is because according to the command prompt, the "=" character was unexpected. Which makes sense as the whole master.dbo.fn_hadr_group_is_primary('WEB_AG') is not parseable from the command prompt (ie cmdexec). And "print" is not the command you want to run from the command line either; you want "ECHO". If you are trying to build up a CmdExec command to run, you should build that from the command prompt. The easiest way is to build a .bat file. If that runs successfully, then copy-pasting that into the job step that uses CmdExec should also run successfully. I say "should" because if you are relying on relative paths instead of absolute paths or running it with network share drive letters instead of UNC paths, you can sometimes get inconsistencies with running it from the command line and running it from a SQL job step..
Thank you bmg002
September 12, 2017 at 10:38 am
AlexSQLForums - Tuesday, September 12, 2017 9:22 AMThank you bmg002
Not a problem. If you have any questions about that powershell, let me know. I found it from a search ages ago and did some changes to meet your needs. I've used it a few times (not very often), but use similar things more often in C# than powershell.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 12, 2017 at 11:44 am
If you need PoSh, Inboke-SQLCmd makes many things easier. Just drop your code in there.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply