SQL Outbound Adapter - Execute Procedure call returns null/undefined for Output value.
We have a stored procedure with one input parameter and one input/output parameter.
declare @hl7message nvarchar(max) = 'Pass the hl7 message here',
@output nvarchar(max);
EXEC sp_InsertHl7 @hl7message, @outputresult = @output OUTPUT;
print @output;
@output value will be "Pass" or "Fail"
Below code is inserting the HL7 successfully into the target DB, but not returning the output value as expected.
set arrParam(1,"IOType") = 1
set arrParam(1,"SqlType") = $$$SqlLongVarchar
set arrParam(1,"CType") = $$$SqlWChar
set arrParam(1,"Prec") = 8000
set arrParam(1,"LOB") = 1
set arrParam(1,"UseLOBLen")= 1
set arrParam(2) = ""
set arrParam(2,"IOType") = 2
set arrParam(2,"SqlType") = 12
Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutput,tQuery,"ib",.arrParam)
Set sOut = tOutput.GetAt(1)
sOut is always null. I tried to change the second parameter to output (IO type - 4) instead input/output (IO type - 2). But no luck. sOut returns as 'undefined',
When I assigned a wrong data type to the first parameter other than $$$SqlLongVarchar, then the HL7 message was not parsed properly - in this case .tOutput returned the correct output value "Fail'.
But if I assign the second parameter values as above code (Which is correctly inserting HL7 into the DB) then .tOutput returns null.