With MS SQL it's even easier as you can directly run ClassMethods/Procedures  written in TSQL

I didn't mention SOAP WebServices that you can generate directly in Studio.

with error trapping

/// Set value to %session.Data 
ClassMethod setValue(params...) As %Status
{
 try {
   set var=$name(%session.Data)
   for i=1:1:params-1 set var=$name(@var@(params(i)))
   set @var=$g(params(params))
   set status=$$$OK
  }
  catch { set status=$system.Status.Error(710)
  }
  quit status 
 }

it doesn' trap this cases yet

do ..setValue("key1", "key2", , "key4", "val")
do ..setValue("key1", "key2", "", "key4", "val")

did you mean that ?

/// Set value to %session.Data 
ClassMethod setValue(params...) As %Status
{
quit:params<2 0
set var="%session.Data("
for i=1:1:params-1 set var=var_"params("_i_"),"
set $e(var,*)=")"
set @var=params(params)
quit $$$OK
}

Very generally speaking: There is nothing in Caché that you can't do with IRIS.
The only thing you might miss eventually, are some ancient compatibility hooks back to the previous millennium. 

Incredible!   I really missed something important!
@Dan Pasco    Thanks for the pointer. I never stop learning.

Another unknown feature that isn't mentioned anywhere else.

Ah! csp.pagename.CLS

THX

No legal that I know of

What do you mean by csp.INT  ?? 

StreamPrefetch looks related but it is totally related to the .NET end (googled SQL prefetch in .NET), 
while <INVALID OREF> is clearly an issue @Ensemble end.
I'm not too deep in the external ODBC  world but I assume that collecting the resultset and
transmitting it to .NET  [ %SYS.SQLStreamSRV ] could happen asynchronously in different processes.
So I doubted %Stream.TempCharacter which is bound to a process private Global  vs. a standard Global visible also to other processes. And only the final %Save nails it down.
I wonder if you experience the same problem if you run your query from
terminal prompt: do $system.SQL.Shell()  
This can signal if the ODBC connection plays a role at all.

Jeff,
if this is still happening I suggest contacting WRC. 
They have access to the sources and more diagnostic tools.

%SYS.SQLStreamSRV.obj  is a deployed routine / class so we can't look into its details.
<INVALID OREF> expects a (stream) Object, but it doesn't exist.
So an additional check might be required.  Or you force some content for testing.
 

ClassMethod GetHL7Msg(pId As %String) As %Stream.TmpCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.GlobalCharacter).%New()
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)

     ;;  Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" failed.") 
         IF 'tMsg.Size DO tMsg.Write("*** no message for ID:"-pId_" ***")
         SET tSC=tMsg.%Save()

    Do tHl7.%Close()
    Return tMsg
}

So you should have always a Stream Object

So my next try would be to add %Save  of the Stream Object before closing  tHl7

ClassMethod GetHL7Msg(pId As %String) As %Stream.TmpCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.GlobalCharacter).%New()
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)

        IF tSC SET tSC=tMsg.%Save()          

    Do tHl7.%Close()
    Return tMsg
}