I am using version 9.00.2047.00 SP1 of Visual Studio 2005. Using ADO.NET, I have been unable to get the Execute SQL task to successfully return the value of an output parameter defined as varchar or nvarchar when the value is null. No other data types seem to have this problem, including the sql_variant data type. Here is the stored procedure I am calling: create proc spx @in int = null output,@vc nvarchar(10) = null output,@dt datetime = null outputasselect @in = null,@vc = null,@dt = nullreturn The variables to which the three output parameters return their valueshave a data type ofObject. The task runs fine when the integer or datetime parameters are used, and the variables can be identified as null using IsDBNull.But as soon as the nvarchar (or varchar) parameter is included,the task fails withthis message: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@vc"): Data type 0xE7 has an invalid data length or metadata length." I have seen a couple of postings that sound similar to this problem, but so far I have found no resolution. Any advice would be much appreciated. Thanks, Ron Rice

This is a known issue. We are considering to fix it in the next release. The workaround is to use OLE DB connection type. When you use OLE DB connection type, you may have to trim the blankspaces appended to the actual value.

Does anyone has any idea about the time duration when MS will stop releasing buggy software? I have been hearing since the releas of .Net that we should use SQLClient to connect to SQL Server and now due to a bug, am I supposed to change the existing code to use Oledb? I dont see any sense in this.

I met the same problem. Does anyone know whether MS realease patch to fix it?

Anand Prakash wrote: Does anyone has any idea about the time duration when MS will stop releasing buggy software? I have been hearing since the releas of .Net that we should use SQLClient to connect to SQL Server and now due to a bug, am I supposed to change the existing code to use Oledb? I dont see any sense in this. the "OLE DB"in this thread referrs to the ssis OLE DB connection manager, not an OLE DB provider. the sql native client provider can be used with the ssis OLE DB connection manager.

After looking into the documentation and the actual connection configuration again after your reply, I found that the SSIS OLE DB connection manager uses SQL Server Oledb provider and not the managed provider. Please try to create both type of connection managers. The SSIS connection configuration dialog displays the provider name in the right hand pane. I hope this will help in making it clear what I was trying to say.

Anand Prakash wrote: After looking into the documentation and the actual connection configuration again after your reply, I found that the SSIS OLE DB connection manager uses SQL Server Oledb provider and not the managed provider. Pleasetry to create both typeofconnection managers. The SSIS connection configuration dialog displays the provider name in the right hand pane. I hope this will help in making it clear what I was trying to say. ok. i must've misunderstood you. have you considered using the ado.net connection manager instead of the ole db connection manager? http://msdn2.microsoft.com/en-us/library/ms141676.aspx the ado.net connection manager uses managed providers.

Ron, can you drop me a note at thorntonmark@hotmail.com for information regarding this issue. thanks

I have a similar problem with output parameters. I use an OLE DB connection and when I run the package on my local machine it works correctly, but when I run the same package on server i need to trim. Do you know why it works in one environement and not the other? Connection strings are the same. I have included the trace generated from the server and the work station. --FROM SSIS package running on server declare @p4 nchar(511) set @p4=N'data/bbs/bwmicb/pub/ ' declare @p5 nchar(511) set @p5=N'CptyLastActivity_SOPHIS_ENOVARA_ ' declare @p6 nchar(511) set @p6=N'csv ' declare @p7 nchar(511) set @p7=N'us0227 ' declare @p8 nchar(511) set @p8=N'xxxxx ' declare @p9 nchar(511) set @p9=N'xxxxx ' exec sp_executesql N'EXEC pGetFTPDetails @P1, @P2 OUTPUT, @P3 OUTPUT, @P4 OUTPUT, @P5 OUTPUT, @P6 OUTPUT, @P7 OUTPUT',N'@P1 int,@P2 nchar(511) OUTPUT,@P3 nchar(511) OUTPUT,@P4 nchar(511) OUTPUT,@P5 nchar(511) OUTPUT,@P6 nchar(511) OUTPUT,@P7 nchar(511) OUTPUT',1,@p4 output,@p5 output,@p6 output,@p7 output,@p8 output,@p9 output select @p4, @p5, @p6, @p7, @p8, @p9 --FROM SSIS package running on my work station declare @p4 nvarchar(max) set @p4=N'data/bbs/bwmicb/pub/' declare @p5 nvarchar(max) set @p5=N'CptyLastActivity_SOPHIS_ENOVARA_' declare @p6 nvarchar(max) set @p6=N'csv' declare @p7 nvarchar(max) set @p7=N'us0227' declare @p8 nvarchar(max) set @p8=N'xxxxx' declare @p9 nvarchar(max) set @p9=N'xxxxx' exec sp_executesql N'EXEC pGetFTPDetails @P1, @P2 OUTPUT, @P3 OUTPUT, @P4 OUTPUT, @P5 OUTPUT, @P6 OUTPUT, @P7 OUTPUT',N'@P1 int,@P2 nvarchar(max) OUTPUT,@P3 nvarchar(max) OUTPUT,@P4 nvarchar(max) OUTPUT,@P5 nvarchar(max) OUTPUT,@P6 nvarchar(max) OUTPUT,@P7 nvarchar(max) OUTPUT',1,@p4 output,@p5 output,@p6 output,@p7 output,@p8 output,@p9 output select @p4, @p5, @p6, @p7, @p8, @p9

hi, I am sorry i may be late in replying to this mail. I have also tried to get varchar(max) output from a stored procedure in to ssis using adodotnet connection. I had a package developed on evaluation enterprise version of sql 2k5. I did get the same error when i first tried it, but later on when i applied the SQL 2k5 sp2 patch. The error got solved. Hope this helps anyone looking for a solution for this error mentioned by RON. Thanks

Hi Kaarthik, Is there a solution to be applied into the database and not in code? In my case, the problem happens with a 3d Party application. I know that it was developed in Visual Studio 2003 - Framework 1.1, but I can't change the code, so I need a solution to be applied into the data base, it's the only location that I can change. Any idea? Many thanks!