Return value from batch file to powershell

Question

I have a batch file which calls a SQL script to send an email. This batch file is embedded in a powershell. How do I return a success or a failure return code from the batch file to the powershell. I have tried the following

In Batch file

exit /b %ERRORLEVEL%

In powershell

cmd.exe /c 'C:\scripts\send_email_sp_prd.bat F'
if($LastExitCode -eq -0){
write-host "Success"
}
else
{
write-host "Failure"
}

Even if the SQL script fails, the batch files is returning a value of 0 which is not what I want. Is there a better/correct way to do this?

Stop embedding batch files in your powershell and just do everything in powershell. — TheMadTechnician
– TheMadTechnician, Commented Jun 3, 2016 at 0:19

Tyler Helder · Accepted Answer · 2016-06-06 23:30:21Z

1

Personally, I would eliminate the batch file and do this all through PowerShell, this will be much easier in terms of manipulating or validating against command output. In the installer for SQL Management Studio on the feature selection install "Management Tools" to obtain the PowerShell module. Once the module in installed you could create a script to execute your job. Here is something I created quickly but you could probably use this or something similar:

Function Invoke-Sqljob {

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String[]] $ServerInstance,

    [Parameter(Mandatory=$true)]
    [String[]] $DatabaseName,

    [Parameter(Mandatory=$true)]
    [String[]] $Query
     )

Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance "$ServerInstance" -Database "$DatabaseName" -Query "$Query"

}

You would execute Invoke-Sqljob as follows:

Invoke-Sqljob -ServerInstance "DB_Server\SQL_Instance" -Database "DB_Name_Here" -Query "EXEC send_mail_sp_prd"

If you are unable to or would rather use sql authentication you could utilize the -Username and -Password parameters of the Invoke-Sqlcmd cmdlet.

Hope this helps solve you problem.

answered Jun 6, 2016 at 23:30

Tyler Helder

6444 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vibhav MS Over a year ago

Thanks Tyler and sorry for the delayed response. This was part of code written by someone else and I didn't want to change it way too much but in the end, I did change everything to Powershell from batch.

Tyler Helder Over a year ago

Not a problem Vibhav MS, glad you were able to get everything converted and working in PowerShell!

Brian · Accepted Answer · 2016-06-03 04:58:00Z

1

I am not sure if your post is the script, verbatim, but you have a - before the 0. Try changing the first line of the if block to $LastExitCode -eq 0

if($LastExitCode -eq 0){
   write-host "Success"
}
else
{
   write-host "Failure"
}

answered Jun 3, 2016 at 4:58

Brian

5,0693 gold badges22 silver badges30 bronze badges

1 Comment

Vibhav MS Over a year ago

Thanks for pointing out but that was type when I keyed it in here.

Collectives™ on Stack Overflow

Return value from batch file to powershell

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related