go to post Daniel Castro · Jul 18, 2022 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 !
go to post Daniel Castro · Jul 6, 2022 Thank you very much for your help Scott... but... what is WRC? lol ... I am new in this intersystems environment...
go to post Daniel Castro · Jul 6, 2022 I fixed it like you posted but I am still getting the same error...
go to post Daniel Castro · Jul 6, 2022 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 = $$$OKset parms = 3set 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) = 1set 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
go to post Daniel Castro · Jul 5, 2022 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", pamInicio, pamFim, 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 !
go to post Daniel Castro · Jul 5, 2022 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...
go to post Daniel Castro · Jun 22, 2022 Hi Scott ! I would appreciate if you do that ! thank you very much !
go to post Daniel Castro · Jun 22, 2022 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 = 1set 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 !