Executing Oracle Stored Procedure with output parameters using ADO.NET connection

I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DB\Microsoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:

[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.

Any help on this would be most appriciated.

hi Jason,

I got the same error. What driver are you using? I use .NET provider Oracle client data provider.

Can you also tell me how did you successfully invoke the stored proc using OLEDB for Oracle?

I have an sp with 1 input parameter of type Number and 14 output parameters some of type Number and some Varchar2.

Can you tell me the sql statement syntax to use and the parameter mapping to use. I know that the parameter name starts with 0,1,2...

Please help

thanks.

sandeep

sql

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection type

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection ty

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.