Notifications

15 views

Issue

Symptoms

Orchestration activities fails with below error messages for SQL Procedures

{
"output": "[]",
"errorMessages": "SQLException:\nIncorrect parameter count: procedure expecting 0, while we have 1\n",
"probeCompletedEccId": "2aaadec1db407b0065fde3334a9619b4",
"totalRows": 0,
"storedProcedureOutputs": "{}"
}

 

Release

All Versions

Cause

The error message could be misleading. Though you send the same number of parameters from Orchestration activity, which the procedure is expecting, you may still see these message. The underlying cause could be a different problem altogether.

Resolution

Modify the same activity to provide SQL Procedure Name without any parameter and see the exact error message.

Most of the times, the reason could be because of the "User" doesn't have appropriate permissions to access/run the stored procedure.


"output": "[]", 
"errorMessages": "SQLException:\nThe EXECUTE permission was denied on the object 'ABC, database 'XYZ', schema 'dbo'.\n", 
"probeCompletedEccId": "5ca81e81db407b0065fde3334a96196a", 
"totalRows": 0, 
"storedProcedureOutputs": "{}" 

Providing appropriate permissions to the user that is used by Orchestration would resolve the issue.

The below documentation from Microsoft might be useful:

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-2017

Article Information

Last Updated:2019-08-02 20:48:41
Published:2019-07-29