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]
@DTIntegerINTOUTPUT,@DTString
varchar(50)OUTPUT,@DTDateTime
datetimeOUTPUT,@DTGuid
uniqueidentifierOUTPUTAS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;SELECT @DTInteger= 50SELECT @DTString='My test string'SELECT @DTDateTime=GETDATE()SELECT @DTGuid=newid()END
[4046 byte] By [
R9] at [2007-12-17]
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.
R9 at 2007-9-9 >
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 !!!!!
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
Ron Rice wrote: