5510 Invalid session mode for procedure execution

Problem:

CALL USERAdmin.Create_User ('C34576','Nazmul Huda','U_Administrators','')

Executed as Single statement. Failed [5510 : HY000] Invalid session mode for procedure execution.

Elapsed time = 00:00:00.031

STATEMENT 1: Unknown failed.

Cause:

The session mode within which the CALL SQL is submitted is different than the one in which the stored procedure was created. A stored procedure created in Teradata mode cannot be executed in ANSI mode and vice versa.

Solution:

Change the session mode and resubmit the request or recreate the procedure in the desired session mode and resubmit the request.

Example:

For the above error, its failing because the transaction mode of the stored procedure is different than the transaction mode of the session.

Here I am checking the Transaction Mode of the stored Procedure.

help procedure USERADMIN.Create_USER attributes;

We can see from the Transaction Sematics column the mode is TERADATA. Now, Lets check the Transaction mode of the current session.

SELECT transaction_mode from dbc.SessionInfoV WHERE SessionNO=SESSION;

We can see the Transaction mode of the current session is ANSI. So lets change the Transaction mode of the session from ANSI to TERA, then it should work. So the transaction mode of the stored procedure should be same as your session Transaction mode.

Right Click to the database connection and Click Properties.

Select the TMODE and click Remove.

Click ADD and from the drop down menu select JDBC Property TMODE and select property Value TERA. Click OK.

Now it should be look like this.

Now click OK and Click Yes in the pop up Dialog box.

Now executing the procedure working fine.

CALL USERAdmin.Create_User ('C34576','Nazmul Huda','U_Administrators','')

Executed as Single statement.

Elapsed time = 00:00:00.920

STATEMENT 1: Unknown completed.