SQL Task - Output Parameter fails using strings.

Scenario:
Using Execute SQL Task in SSIS.
Connection Type: Ado.Net
IsQueryStoredProcedure: True
SqlStatement: [spAdoNetParamTest]

I trying to return a output string from a stored procedure and place the value in a package level variable. I am able to do this with other data types. See sample SP below. However, the task fails when trying to return a string with an error. Does anyone know how to set the size property for a string parameter

Here is the error:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "[spAdoNetParamTest]" failed with the following error: "String[0]: the Size property has an invalid size of 0.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Additional Info --
SQL Trace (works) w/o @DTString as output:
-- RPC: Starting
declare @p2 int
set @p2=NULL
declare @p3 datetime
set @p3=NULL
declare @p4 uniqueidentifier
set @p4=NULL
exec [spAdoNetParamTest] @DTString=N'',@DTInteger=@p2 output,@DTDateTime=@p3 output,@DTGuid=@p4 output
select @p2, @p3, @p4

(RPC:Completed)
declare @p2 int
set @p2=50
declare @p3 datetime
set @p3='Oct 3 2005 1:52:57:563PM'
declare @p4 uniqueidentifier
set @p4='4A64A927-B1A3-42B4-AE98-A9E57556EEAC'
exec [spAdoNetParamTest] @DTString=N'',@DTInteger=@p2 output,@DTDateTime=@p3 output,@DTGuid=@p4 output
select @p2, @p3, @p4

Stored Proc:

CREATE PROCEDURE [dbo].[spAdoNetParamTest]
@DTInteger INT OUTPUT ,

@DTString varchar(50) OUTPUT,

@DTDateTime datetime OUTPUT,

@DTGuid uniqueidentifier OUTPUT

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT @DTInteger = 50

SELECT @DTString = 'My test string'

SELECT @DTDateTime = GETDATE()

SELECT @DTGuid = newid()

END

Answer this question

SQL Task - Output Parameter fails using strings.

Hal Kilmer
The problem with using OLE DB to work around this bug in ADO.NET is that when a null is returned in an output parameter and stored in a variable, the IsDBNull function does not recognize it as null. In fact, if you MsgBox the variable you will see that not only is it not null, it isn't even blank! Some random set of characters get returned instead.

Ron Rice
Andri

Hi Duane ... nope I dint try that y'all.

I tried liftin ma left leg while typin and shoa nuff it seemed to work jest dandy!

Well done Microsoft ... rest assured t'aint no BUG !!!!
shp21
Donald Farmer, it is not a bug. Basically we need to pass a empty string value of DbNull value to the SqlParameter.value property before making a call to the ExecuteNonQuery. It works fine.

PCarrier

Ron Rice wrote:

The problem with using OLE DB to work around this bug in ADO.NET is that when a null is returned in an output parameter and stored in a variable, the IsDBNull function does not recognize it as null. In fact, if you MsgBox the variable you will see that not only is it not null, it isn't even blank! Some random set of characters get returned instead.

did you try using the ISNULL function instead of the ISDBNULL function

jimmimacc
I am running the final release of SQL Server 2005 and I am still having the problem described above. Is it a known issue that is waiting for its resolution Did you use the OLE DB provider instead and did that resolve your problems The documentation in Books Online on how to execute stored procedures using an Execute SQL Task leaves a lot to be desired.

Edit: I can confirm that switching to OLE DB seems to resolve the problem.
Nandar
Thanks. I was aware of the OLEDB provider as an alternative. I prefer not to use the OLEDB provider precisely due to the syntax for the parameters. Exec = spSomeProc , , , , .... can be visually quite cumbersome when developing compared to the information obtained on the parameter mapping screen using Ado.net. Also, the sequence of the parameters has to be in order with no means of resequencing them on the UI. Perhaps, an up/down control would help on parameter mapping screen.

Hopefully, the fix for the Ado.net output parameter bug will be included in the final release of SQL 2005. Thanks again.
Tomas Scott

Hi Raj ... Hello ... it's a bug because it don't work !!!!!!!!!

... It is now April 2007 and it is still a BUG! I'll use a workaround as most of my code in SSIS currently is.

OLE DB syntax is rubbish so don't suggest that route. For a start what is the data type for returning a varchar(128) string parameter in OLE DB speak ... or a bit VARIANT_BOOLEAN I also can't be bothered reading Microsoft help any more ... sometimes it is really good but most of it is so obvious it wasn't worth saying. E.g. Procedure Name ... That would be the name of the procedure.

Really I would never have thought that!

Bored now, have to get back to tearing my hair out !!!!!
didier_g

Thanks - I'll look into that. Sounds like we may have a bug there. Could you report the issue throught the formal channel

Donald
wmmihaa
Did anyone identify a resolution to this within Integration Services I am experiencing the same issue where I want to have an Execute SQL Task which sets a variable based on the execution of a stored proc.

Is the suggestion above referring to the SqlParameter.value property and to ExecuteNonQuery done in code rather than in Integration Services
Stortik
We have experienced the exact same problem with the ADO.NET provider on an execute SQL task. To work around it (and still use ADO.NET provider with better parameter handling) we are returning value to a result set instead of an output parameter. Output parameters work fine with integers, but apparently not strings.

Was this bug ever entered using proper channels as requested by Donald It is still very much alive and well on SQL build 2153 (post SP1 hotfix build). . .

Josh
Tzu-Yie
This issue is currently being fixed. We are hoping to release the fix in one of the upcoming releases.
Salimgbelim

We have seen this recently in another case.

You can avoid this by using an OLEDB provider with the Execute SQL Task.

Note that the syntax for output parameters with the OLEDB provider is a little different.

See the excellent article at http://www.sqlis.com/default.aspx 58 for a very full tutorial on this task that you may find useful.
william123
I've also experienced this issue in SQL2005 with SP2 installed.

An easy workaround (without going OLE DB) is to set the output parameter's .Value property to any non-zero-length string before calling any of the SqlCommand's .Execute methods. This clears up the "Size property has an invalid size" errors for me, anyway:

Code Snippet

if ((parm.Direction == ParameterDirection.InputOutput ||
parm.Direction == ParameterDirection.Output) &&
parm.SqlDbType = SqlDbType.VarChar)
{ parm.Value = " "; }

I don't know if the same bug applies to Char() parameters; that might also be worth a try, if fixed lengths don't mess things up in your situation.

Hope this helps...
-Paul
SQL Task - Output Parameter fails using strings.

Answer this question