Question
· Jul 31

Issue Sending Data to SQL Server from a Business Operation in IRIS Ensemble

Hi everyone,

I'm developing an Ensemble process that sends user data from a Business Process to a Business Operation using EnsLib.SQL.OutboundAdapter. The operation is supposed to call a stored procedure on a SQL Server.

The stored procedure expects 3 parameters:
@FirstName, @LastName, and @Email.

The procedure works fine when executed directly from SQL Server Management Studio.

However, when I trigger it via Ensemble, I get the following error:

[SQL Server] Incorrect syntax near 'email@domain.com'

hat is the proper way to use ExecuteProcedure or ExecuteUpdateParmArray to safely pass parameters to a SQL Server stored procedure using EnsLib.SQL.OutboundAdapter?

If anyone has a working example of a Business Operation calling a SP with parameters – that would be a huge help 🙏

Thanks in advance!

Product version: IRIS 2025.1
Discussion (4)3
Log in or sign up to continue

Hi Enrico,

  • Environment: InterSystems IRIS for Health
  • Production: Uses Business Process → Business Operation → SQL Server
  • SQL Server: Accessible via ODBC using EnsLib.SQL.OutboundAdapter

The stored procedure works in SSMS:

EXEC InsertUser 'John', 'Doe', 'john@example.com'

But when called from IRIS, I get:

ERROR #6022: Gateway failed: Execute.
ERROR <Ens>ErrGeneral: SQLState: (42000) NativeError: [102] 
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '11'.

ERROR <Ens>ErrGeneral: SQLState: (42000) NativeError: [8180] 
Message: Statement(s) could not be prepared.

There is no '11' anywhere in my code or data — yet that’s what the error refers to

IRIS Business Operation Code (UserSQLOperation):

Class MyApp.Operation.UserSQLOperation Extends Ens.BusinessOperation
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Method OnMessage(pRequest As MyApp.Messages.UserRequest, Output pResponse As MyApp.Messages.JSONOutResponse) As %Status
{
    Set tSC = $$$OK
    Set tSQL = "EXEC InsertUser ?, ?, ?"

    Try {
        Set tStatement = ..Adapter.Prepare(tSQL)
        Do tStatement.%Execute(pRequest.FirstName, pRequest.LastName, pRequest.Email)

        Set pResponse = ##class(MyApp.Messages.JSONOutResponse).%New()
        Set pResponse.status = "Success"
    }
    Catch ex {
        Set tSC = ex.AsStatus()
        Set pResponse = ##class(MyApp.Messages.JSONOutResponse).%New()
        Set pResponse.status = "Error"
        Set pResponse.error = $System.Status.GetErrorText(tSC)
    }

    Quit tSC
}
}

Stored Procedure definition:

CREATE PROCEDURE InsertUser
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Users (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email);
END
 

Why does the procedure fail only when called from IRIS?

What does "Incorrect syntax near '11'" refer to?

Is this related to ODBC cursor handling, parameter mapping, or concurrency?

Any help or insight would be greatly appreciated!

ty.

Please check the relevant documentation:

Executing Stored Procedures

I've asked the DC AI chat bot and the answer is worth a look:

https://community.intersystems.com/ask-dc-ai?chat_id=1920

Note that using SQL Server ODBC you probably do not need to specify "IOType" in the param array.

If you still have problems, get back with a sample code of what you are doing.