New post
Question
· Jun 15, 2022

How to Execute a Stored Procedure in a remote SQL Server Database ?

#InterSystems IRIS

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,

Product version: IRIS 2022.1
$ZV: IRIS for Windows (x86-64) 2021.1 (Build 215.3U)
Discussion (18)0
Log in or sign up to continue
Vitaly Furman · Jun 15, 2022

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.

1 0
Vitaly Furman · Jun 17, 2022

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.

   

1 0
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 = 1 set 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 !

0 0
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 !

0 0
Scott Roth · Jul 6, 2022

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. 

My method within my Outbound adapter looks like this....

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.

0 0
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 = $$$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
0 0
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 !

1 0