Problem solved:

I don´t know why sometimes the SQL Server odbc driver gives an error when the "describe parameter" function it´s called. The solution is simple. Just change the ExecuteParmArray parameters as the following: Set tSC = ..Adapter.ExecuteProcedureParmArray(.respostaQuery,.tOutParms, tQuery, "*" ,.parametros). -> just change de pIO paramter to "*". 

My code:

set respostaQuery = ##class(EnsLib.SQL.Snapshot).%New()
set pResp = ##class(User.LancamentoResp).%New()
set tOutParms = ##class(%ListOfDataTypes).%New()
#Dim parametros = 3
//
Set parametros(1) = "2022-01-01 00:00:00.000"
Set parametros(2) = "2022-01-05 00:00:00.000"
Set parametros(3) = 1

set tQuery = " EXEC [dbo].[SPCALL] ?, ?, ?"
Set tSC = ..Adapter.ExecuteProcedureParmArray(.respostaQuery,.tOutParms, tQuery, "*" ,.parametros)
if $system.Status.IsError(tSC) {
$$$LOGSTATUS(tSC)
return tSC
} #Dim snapshot As EnsLib.SQL.Snapshot = respostaQuery.GetAt(1)
While snapshot.Next() {
$$$LOGINFO(snapshot.Get("dia"))
}
return tSC

Hope this can help others !

Thanks everybody for the help !

Thank you very much for your help Scott... but... what is WRC?

lol ... I am new in this intersystems environment...

Hi Scott,

Thank you for your attention.

This is my code:

Method LeDados(pReq As User.ParamRequest, Output pResp As User.LancamentoResp) As %Status
{
set tSC = $$$OK
set parms = 3
set parms(1) = ##class(Ens.Rule.FunctionSet).ConvertDateTime("2022-01-01","%Y%m%d","%q(1)")
set parms(2) = ##class(Ens.Rule.FunctionSet).ConvertDateTime("2022-01-05","%Y%m%d","%q(1)")
set parms(3) = 1

set tQuery = "{ ?= call dbo.spr(?,?,?) }"

 set tSC = ..Adapter.ExecuteProcedureParmArray(.respostaQuery,.tOutParms,tQuery, "oiii", .parms)

if $system.Status.IsError(tSC) {
$$$LOGSTATUS(tSC)
return tSC
}
set pResp = ##class(User.LancamentoResp).%New()
While respostaQuery.Next() {
 set pResp.Dia = respostaQuery.Get("dia")
 set pResp.LanTipo = respostaQuery.Get("lan_tipo")
 set pResp.Documento = respostaQuery.Get("documento")
 set pResp.Descricao = respostaQuery.Get("descricao")
 set pResp.CorDescricao = respostaQuery.Get("cor_descricao")
 set pResp.Ordem = respostaQuery.Get("ordem")
 set pResp.DataLanc = respostaQuery.Get("data")
 set pResp.Conta = respostaQuery.Get("conta")
 set pResp.ValorCredito = respostaQuery.Get("valor_credito")
 set pResp.ValorDebito = respostaQuery.Get("valor_debito")
} return tSC
}

The SP definition of parameters:

CREATE PROCEDURE [dbo].[spr] (
	@dtInicio SMALLDATETIME, 
	@dtFim SMALLDATETIME,
	@Conta INT
)

Now i am getting the error:

ERRO #6022: Gateway: failed MoreResults.
+
ERRO <Ens>ErrGeneral: SQLState: (01000) NativeError: [0] Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Debug: 01

Hi Scott,

I am trying to execute the stored procedure inside a Business Operation usin EnsLib.SQL.OutboundAdapter.

I am getting the error "ERROR #6022: Gateway: failed PrepareW." all the time. I suspect that is happening because I am calling the stored procedure in the wrong way.

I am using the following syntax:

set tQuery "EXECUTE [dbo].[ProcedureCount] '?' , '?' , ? "

set tSC = ..Adapter.ExecuteProcedure(.respostaQuery,.tOutParms,tQuery"iii"pamIniciopamFim, conta)

where parInicio, pamFim and conta are the input parameters, and pamInicio = "2022-01-01 00:00:00" and pamFim = "2022-01-05 00:00:00" and conta = 1.

Do I have to use any data conversion command ?

Thanks !

Hi Martin !

I am having the same problem with ODBC and SQL Server. Can you please show me an example of your ExecuteProcedure syntax?

For example :

Do you use ..Adapter.ExecuteProcedure ( PROCEDURE_NAME '2022-01-01 00:00:00', '2022-01-10 23:59:00') syntax ?  I mean, do you ' as separators? 

Thanks in advance...

Hi Vitaly,

I am having a problem in calling the SP. I am receiving a error : SQLState: (07002) NativeError:.

I think it is probably because I am not using the correct sql syntax calling the SP with smalldatetime parameters.

set pamInicio = "2022-01-01 00:00:00.000"
set pamFim = "2022-01-05 23:59:00.000"
set conta = 1set conta = 1
set tQuery = "EXECUTE [dbo].[ProcedureCount] '?' , '?' , ? "

set tSC = ..Adapter.ExecuteProcedure(.respostaQuery,.tOutParms,tQuery, "iii", pamInicio, pamFim, conta)
if $system.Status.IsError(tSC) {
$$$LOGSTATUS(tSC)
return tSC
}

When I execute the query in a WINSQL client, which uses ODBC, it works:

EXECUTE [dbo].[ProcedureCount] '2022-01-01 00:00:00.000', '2022-01-05 23:59:00.000', 1
 

Can you help me please? I noticed that you have date data types in your records. Are them parameters?
If so, how you make your call to the SP?

Thank you very much !