Question
Daniel Castro · Jun 15

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,

Product version: IRIS 2022.1
$ZV: IRIS for Windows (x86-64) 2021.1 (Build 215.3U)
0
0 156
Discussion (8)2
Log in or sign up to continue

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

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)")