New post
AllAccepted answers
Vitaliy Serdtsev · Aug 22, 2023

I also noticed three things from your screenshots:

  1. instead of the standard program %SYS.Task.RunLegacyTask you are using a non-standard CTools.Task.Legacy. Is this how it should be?
  2. you have disabled "Reschedule task after system restart?". Is this how it should be?
  3. your task is scheduled for 4:00:00 under Task2, but judging by the history, it was launched at 11:52 and 11:56 under Administrator. Something doesn't add up here.
Vitaliy Serdtsev · Aug 22, 2023

The problem can be solved in two ways:

  1. use OPTIONS="popup,sortbox"
  2. make a correction to the %CSP.PageLookup
    Write "&nbsp;<a href=""javascript:searchSort("_..QuoteJS(value)_");"" title=""Sort Results by "_alias_""">"
|
V
Write "&nbsp;<a href=""javascript:searchSort("_i_");"" title=""Sort Results by "_alias_""">"

In both case, the query will take the form "ORDER BY <the ordinal number of the field>", instead of "ORDER BY <field name>"

Vitaliy Serdtsev · Aug 14, 2023

In this case, it will be easier to temporarily dump the data into globals. Then you can access this data at any time, including through %ScrollableResultSet.

Here is a small example:

ClassMethod tt() [ Language = tsql, ReturnResultsets, SqlName = mycls, SqlProc ]
{
  drop table if exists mytemp1,mytemp2
  select name,dob,spouse into mytemp1 from Sample.Person where name like 'A%'
  select name,age,home_city,home_state into mytemp2 from Sample.Person where home_state = 'MA'
}

ClassMethod Test()
{
  d ##class(%SQL.Statement).%ExecDirect(,"call dc.mycls()")
  
  d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp1").%Display()
  d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp2").%Display()

  ; and even so
  d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp1 cross join mytemp2").%Display()
}
Vitaliy Serdtsev · Aug 11, 2023

One of the possible options:

ClassMethod odbcTest() As %Integer [ ReturnResultsets, SqlName = PersonSets2, SqlProc ]
{
  #dim %sqlcontext As %ProcedureContext
  if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() } 

  s tReturn = 0

  s conn=##class(%SQLGatewayConnection).%New()
  s sc=conn.Connect("TEST Samples","_system","SYS") //datasource
  if $$$ISOK(sc) {
    d conn.AllocateStatement(.h1)
    d conn.Prepare(h1,"select name,dob,spouse from sample.person where name %STARTSWITH 'A'")
    d conn.Execute(h1)
    d %sqlcontext.AddResultSet(conn.getResultSet(h1))
    d conn.AllocateStatement(.h2)
    d conn.Prepare(h2,"select name,age,home_city,home_state from sample.person where home_state = 'MA'")
    d conn.Execute(h2)
    d %sqlcontext.AddResultSet(conn.getResultSet(h2))
    s tReturn = 1
  }else{
    s sqlcode=$system.Status.StatusToSQLCODE(sc,.msg)
    s %sqlcontext.%SQLCODE = sqlcode, %sqlcontext.%Message = msg
  }
  q tReturn
}

Output:

SAMPLES>d ##class(%SQL.Statement).%ExecDirect(,"call Sample.PersonSets2()").%Display()
...

Surely there is a way to make it even easier.

Vitaliy Serdtsev · Aug 11, 2023

It's clearer now, this is the tricks of Excel, which convert the string 01022000 to the number 1022000.
In your case, try changing the cell type from General to Text.

PS: when generating Excel reports, I use the Date cell type (short format DD.MM.YYYY), and from Caché/IRIS I return the %Date type.
The components that I use already know how to translate the internal representation of the date 01.02.2000 from the Caché/IRIS format (58105) to Excel (36557) and vice versa.