How to Execute a Stored Procedure in a remote SQL Server Database ?
Hi,
I want to access a Stored Procedure in a remote SQL Server Database. I am quite new to Object script.
The stored procedure has 3 parameters: 2 datetime parameters and 1 integer.
Which is the best way to do that? I'm wondering if I can use the link stored procedure feature inside object script or some SQL statement in Object Script.
This SP returns a set of rows which I want to read, transform and save in a persistent class.
Thanks in advance,
Comments
Hi Daniel,
We had done this at one of our clients. We have Stored Procedures that are sitting on a SQL Server and are activated by a custom Business Operation (with a SQL.OutboundAdapter) which receives a trigger message with the needed parameters from a Trigger Business Service. The Stored Procedure can return SQL Results as XML which is what we had done... it is then easy to read that XML and extract data from it.
This was a large project (which I inherited) so it is possible that you may not need something this complex. And there could be other ways to do this. I can provide more details if you are interested.
Hi !
Thank you for your answer! Sure I am very interested in how you made it.
I would really appreciate it if you send me more details...
Hi Daniel,
I had put together a simple diagram that describes the process.
There is a lot of custom code and also several globals on the back end of which I have yet to learn. We are pulling a lot of data and from what I understand, a lot of the work was performance-related. I don;t have a lot of documentation. Hopefully, your use case is a bit simpler than ours.
Thahk you very, very much for your answer!
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 pamFim = "2022-01-05 23:59:00.000"
set conta = 1
set tQuery = "EXECUTE [dbo].[ProcedureCount] '?' , '?' , ? "
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 !
Daniel, I always used the ConvertDateTime function to convert string to sql date/time. I’m at Global Summit but can send you examples when I get back in the office.
Hi Scott !
I would appreciate if you do that ! thank you very much !
I am not sure which context you are using to populate the Stored Procedure, but if you are using a DTL, I used ..ConvertDateTime((field),"%Y%m%d","%q(1)")
Hi Scott,
I am trying to execute the stored procedure inside a Business Operation usin
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 =
Do I have to use any data conversion command ?
Thanks !
How is the date columns defined in your Stored Procedure class file?
For example I have a InsertProviderSp class file that represents all of the Parameters of the stored procedure, and within it I have a Property DOB As %TimeStamp.
.png)
My method within my Outbound adapter looks like this....
.png)
If you are "hardcoding" the dates within the EnsLib.SQL.OutboundAdapter, you could be able to call the above ConvertDateTime by adding lines to for ##class(Ens.Rule.FunctionSet).ConvertDateTime....
I have many cases where I am inserting, update, or querying data from external MS SQL sources to pull into Ensemble, so let me know if you still need help.
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. |
An issue I see with the code is with the value you are specifying with ConvertDateTime. If you are sending YYYY-mm-dd your code needs to account for that "%Y-%m-%d"
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)")
I fixed it like you posted but I am still getting the same error...
I am not sure then, maybe a call to WRC would help further as I have not experienced this issue.
Thank you very much for your help Scott... but... what is WRC?
lol ... I am new in this intersystems environment...
WRC is the support desk for InterSystems.
https://login.intersystems.com/login/SSO.UI.Login.cls?referrer=https%25…
Ahhh ok ! thank you again for all !
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:
My code:
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 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 !