Hello, I am trying to execute an Oracle stored procedure using an Execute SQL task. It has 1 input and 1 output parameter. I have the parameters mapped to variables, the ResultSet property set to "single row" with the result set parameter mapped to a variable, and the connection is OLE DB. When I go to run, I get the following error: failed with the following error: "Exception from HRESULT: 0x80040E21". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I have tried to lookup various threads but can not find one that explains this error. I have other procedures with no parameters working fine in an Execute SQL task. It is this one particular case that is getting an error. Thank you

Check in Execute SQL task if you added OUTPUT in sql statement for output parameter. In your case the sql statement should be EXEC yourStoredProcedure ?, ? OUTPUT (the first ? is for input and ? OUTPUT is for output) or EXEC ?=yourstoredprocedure ?, ? OUTPUT if your stored procedure return something. Check the Direction in Parameter Mapping section. For output parameter the direction should be Output, for input should be Input and for result set should be ReturnValue.

Depends on what driver you are using - but sometimes oracle parameters need to be defined like this using a semicolon and the parameter name EXEC yourStoredProcedure( :ParmName1, :ParmName2) Or it might be CALL yourStoredProcedure( :ParmName1, :ParmName2) Chuck

Hi Andrew DV, Map Query Parameters to Variables in an Execute SQL Task, please note these points as below: 1. The data type of the parameter must be compatible with the data type of the variable. 2. The order of parameter mappings must be the same as the order in which the parameters appear in the SQL statement. For more information about it, please refer to: http://technet.microsoft.com/en-us/library/ms140355.aspx Thanks, Eileen

Hi Eileen, Would you happen to have a document/link that lists the Oracle data types to Microsoft ones? Thank you

Hi Andrew DV, Oracle data types and Microsoft SQL Server data types do not always match exactly. Where possible, the matching data type is selected automatically when publishing an Oracle table. In cases that a single data type mapping is not clear, alternative data type mappings are provided. For information about it, please see the following: Data Type Mapping for Oracle Publishers: http://msdn.microsoft.com/en-us/library/ms151817(v=sql.100).aspx Thanks, Eileen

Hi Andrew DV, Oracle data types and Microsoft SQL Server data types do not always match exactly. Where possible, the matching data type is selected automatically when publishing an Oracle table. In cases that a single data type mapping is not clear, alternative data type mappings are provided. For information about it, please see the following: Data Type Mapping for Oracle Publishers: http://msdn.microsoft.com/en-us/library/ms151817(v=sql.100).aspx Thanks, Eileen

Hi Andrew DV, Oracle data types and Microsoft SQL Server data types do not always match exactly. Where possible, the matching data type is selected automatically when publishing an Oracle table. In cases that a single data type mapping is not clear, alternative data type mappings are provided. For information about it, please see the following: Data Type Mapping for Oracle Publishers: http://msdn.microsoft.com/en-us/library/ms151817(v=sql.100).aspx Thanks, Eileen