Powershell script works outside BuildMaster but not from BuildMaster

jstarbird_7831

I have a Powershell script the executes a stored procedure and the output to BuildMaster shows all the params it is passing and all the values are correct. However, it will fail complaining about a param of S and a Null value. There is no Null value being passed nor is the any param of S.
I can take the exact output that is shown in the BuildMaster log and execute that SP in the db and it works without issue.

I can even take the Powershell script and run it with the same values and it will run.

Does BuildMaster parse the scripts before running them? This is a script saved in the Script Assets area of BuildMaster.
Does it run Powershell in some different way? Just trying to figure out why this is happening when I can run it outside BuildMaster just fine.
This particular SP uses a combination of Strings and Ints for params.

I know it's not a permissions issue or an SP execution permission as there are some simpler ones that do work.

Here is what I am doing:

param([string]$dbServer,[string]$dbName,[string]$SerCustId,[string]$CurVersion,[string]$SerEventLogName,
			[string]$SerInboundIp,$SerInboundPort,$SerMarketId,[string]$SerOrderImageUrl,[string]$SerOutboundIp,
			$SerOutboundPort,[string]$SerRuntimeUser,[string]$SerRuntimeUserPwd,[string]$SerSMPatientId,[string]$InstallPath,
			[string]$SerLogsPath,[string]$SerInboundLogsPath,[string]$SerOutboundLogsPath)

import-module SQLCmd

try {
    $sqlQuery = "exec usp_SerCustomer_Search '"+ $SerCustId +"', NULL, NULL" 
    $dbChk = Invoke-SqlCmd2 -ServerInstance $dbServer -Database $dbName -Query $sqlQuery -ErrorAction Stop
} Catch {
    write-error "Error: Failed to execute usp_SerCustomer_Search stored procedure. Error Info: $_ "
}

if ($dbChk.Table.Rows.Count -gt 0)
{
    $conCurRec = $dbChk.ConcurrencyRecord
	$sqlQuery = @"
	exec usp_SerCustomer_Update 
        '$SerCustId',
		'$InstallPath',
		'$CurVersion',
		$conCurRec,
		'$SerRuntimeUser',
		'$SerRuntimeUserPwd',
		'$SerLogsPath',
		'$SerEventLogName',
		'$SerInboundIp',
		$SerInboundPort,
		'$SerInboundLogsPath',
		'$SerOutboundIp',
		$SerOutboundPort,
		'$SerOutboundLogsPath',
		'$SerOrderImageURL',
		'$SerSMPatientId'
"@
} elseif ($dbChk.Table.Rows.Count -eq 0)
{
    $sqlQuery = @"
	exec usp_SerCustomer_Insert 
		'$SerCustId',
		'$InstallPath',
		'$CurVersion',
		'$SerRuntimeUser',
		'$SerRuntimeUserPwd',
		'$SerLogsPath',
		'$SerEventLogName',
		'$SerInboundIp',
		$SerInboundPort,
		'$SerInboundLogsPath',
		'$SerOutboundIp',
		$SerOutboundPort,
		'$SerOutboundLogsPath',
		'$SerSMPatientId',
		$SerMarketId,
		'$SerOrderImageURL'
"@
}

try {
    Invoke-SqlCmd2 -ServerInstance $dbServer -Database $dbName -Query $sqlQuery -ErrorAction Stop
} Catch {
    write-error "Error: Failed to execute stored procedure to Update or Insert Customer settings. Error Info: $_ "
}

Product: BuildMaster
Version: 5.7.3

Is there a way to collect Verbose output using PSCall?
I have it enabled for the module call but I do not get any of the verbose output in the BuildMaster log.

Also, I tried changing the SqlQuery values into one long string and still have the same issue.
I do have other scripts with the same module being used and they all work fine but they are simple SP's with 1 or 2 string vars.
This is the only one I have currently that has mixed value types.

Probably one of the values is not coming through correctly for some reason. To enable verbose output, it should work to explicitly set VerbosePreference in your script like:

 $VerbosePreference = 'Continue'

That's all we're doing in the other PowerShell operations like PSExec.

Thanks I'll try setting it that way.

The script is actually printing out what it is running as, invoke-sqlcmd2 does it, and it shows all the params are fine.

jstarbird_7831

I believe we finally figured out the issue.
The Invoke-Sqlcmd does not pass thru plain return values and when run via BuildMaster this was causing the execution to fail. I say while in BuildMaster as I can execute the exact same script outside BuildMaster and not have an error. Even though the try/catch is in the powershelll script.
I know it shouldn't matter about BuildMaster, just explaining what is occurring.

In any event if we change the Stored Procedure to do a Return Select 0 rather than Return 0 I can then remove the try/catch and use an IF on the returned row value in the Powershell script.

Once I did that it is working.

jstarbird_7831

I should clarify my last post, it does a Return Select 0 only when it is successful.