Alex Cashell · Apr 22, 2022

Linked Stored Procedure returning empty status and result Ensemble 2012.2.5

I have a linked procedure class and SQL gateway setup and I can't seem to get any response, status or error from using the stored procedure, I think it must be something big I'm missing to not even get a status.

Can someone see what my problem is please, any help would be very much appreciated.

Class generated by linked procedure wizard:

Include %occInclude /// 
Class dbo.MrnLookup [ Not ProcedureBlock ]
{ ClassMethod MrnLookup(pp01 As %String(MAXLEN=8), ByRef mrn As %String(MAXLEN=20)) As %Integer [ ProcedureBlock = 1, ReturnResultsets, SqlName = MrnLookup, SqlProc ]
    if '$isobject($g(%sqlcontext)) s %sqlcontext=##class(%Library.ProcedureContext).%New() s RETURNVALUE=$g(RETURNVALUE)
 s pp01=$g(pp01)
 s mrn=$g(mrn)
 s hdbc=$$GetConnection^%apiGTW("MRNLookup")
 i hdbc="" s %msg=$$$ERROR($$$GTWCConnectionError) s %msg=$$GetErrorText^%apiOBJ(%msg) g setError
 s QHandle=$zf(-5,%SQLGateway,5,hdbc)
 i QHandle="" s %msg=$$$ERROR($$$GTWCAllocationError) s %msg=$$GetErrorText^%apiOBJ(%msg) g setError
 s err=$zf(-5,%SQLGateway,$s(%SQLGateway("-uv"):37,1:3),QHandle,"{?=call dbo.""MrnLookup;1""(?,?)}")
 i err g getError
 s err=$zf(-5,%SQLGateway,62,QHandle,1,4,1,4,10,0,10)
 s err=$zf(-5,%SQLGateway,62,QHandle,2,1,1,12,8,0,8)
 s err=$zf(-5,%SQLGateway,9,QHandle,$lb(pp01),2)
 i err g getError
 s err=$zf(-5,%SQLGateway,62,QHandle,3,2,1,12,20,0,20)
 s err=$zf(-5,%SQLGateway,9,QHandle,$lb(mrn),3)
 i err g getError
 s err=$zf(-5,%SQLGateway,4,QHandle)
 i err g getError
 s %ROWCOUNT=$zf(-5,%SQLGateway,31,QHandle)
    set tConnection = ##class(%Library.SQLGatewayConnection).%New() set tConnection.DLLHandle = %SQLGateway, tConnection.ConnectionHandle = hdbc
    set rrs = ##class(%SQL.Gateway.ODBCResultSet).%New(tConnection,QHandle)
    while $Isobject(rrs) {
        if rrs.%GetMetadata().columnCount do %sqlcontext.AddResultSet(##class(%ResultSet.Static).%New(rrs))
        set rrs = tConnection.getNextResultSet(QHandle)
 s RETURNVALUE=$li($zf(-5,%SQLGateway,22,QHandle,1))
 s mrn=$li($zf(-5,%SQLGateway,22,QHandle,3))
setError ;
 If ($g(%sqlcontext)'=$$$NULLOREF) {
 Set %sqlcontext.%SQLCODE = $g(err)
 Set %sqlcontext.%Message = $g(%msg)
 Set %sqlcontext.%ROWCOUNT = $g(%ROWCOUNT)
  s err=$zf(-5,%SQLGateway,16,QHandle)
getError ;
 s %msg=$li($zf(-5,%SQLGateway,12,hdbc,QHandle)) g setError
} }

My output from the terminal:

set sc = ##class(dbo.MrnLookup)."MrnLookup"("2000614",.output)
zw output

zw sc
Product version: Ensemble 2012.1
$ZV: 2012.2.5.962.1
0 129
Discussion (1)1
Log in or sign up to continue

Solved it, using wireshark we can see MSSQL is responding that it can't find the stored procedure, the solution is to remove the ";1" from the stored procedure name and it works perfectly.