User bio
404 bio not found
Member since Jul 31
Replies:

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.

Certifications & Credly badges:
alon has no Certifications & Credly badges yet.
Global Masters badges:
alon has no Global Masters badges yet.
Followers:
alon has no followers yet.
Following:
alon has not followed anybody yet.