Exec Stored Proc (C#) - the Size property has an invalid size of 0

Hi All
I am trying to execute a stored procedure that does a very simple
lookup and returns a text field. However, when I try to execute it, I
am getting a rather strange error that I can't seem to fix!
There is defiantely information coming back as I have tested this in
Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
String[1]: the Size property has an invalid size of 0.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: String[1]: the
Size property has an invalid size of 0.
Many thanks in advance for your help
Darren
STORED PROC CODE
=================
ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
-- Add the parameters for the stored procedure here
@.iID int,
@.tXML text = null output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
pRINT @.tXML
END
C# CODE
=======
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = m_sConnectionString;
oConn.Open();
SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
oConn);
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.StoredProcedure;
SqlParameter spID = oCmd.Parameters.Add("@.iID",
SqlDbType.Int);
spID.Direction = ParameterDirection.Input;
spID.Value = iSQLCacheID;
SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
SqlDbType.Text);
spXML.Direction = ParameterDirection.Output;
oCmd.ExecuteNonQuery();
oConn.Close();
XmlDocument xdDBCache = new XmlDocument();
xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
return xdDBCache;
}Just a guess, but since the sp is going to return data, I don't think you
should use ExecuteNonQuery. ExecuteNonQuery is used for executing statements
that don't return a result set (like UPDATE or DELETE).
"daz_oldham" wrote:

> Hi All
> I am trying to execute a stored procedure that does a very simple
> lookup and returns a text field. However, when I try to execute it, I
> am getting a rather strange error that I can't seem to fix!
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.InvalidOperationException: String[1]: the
> Size property has an invalid size of 0.
> Many thanks in advance for your help
> Darren
> STORED PROC CODE
> =================
> ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
> -- Add the parameters for the stored procedure here
> @.iID int,
> @.tXML text = null output
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
> pRINT @.tXML
> END
>
> C# CODE
> =======
> SqlConnection oConn = new SqlConnection();
> oConn.ConnectionString = m_sConnectionString;
> oConn.Open();
> SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
> oConn);
> oCmd.Connection = oConn;
> oCmd.CommandType = CommandType.StoredProcedure;
> SqlParameter spID = oCmd.Parameters.Add("@.iID",
> SqlDbType.Int);
> spID.Direction = ParameterDirection.Input;
> spID.Value = iSQLCacheID;
> SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
> SqlDbType.Text);
> spXML.Direction = ParameterDirection.Output;
> oCmd.ExecuteNonQuery();
> oConn.Close();
> XmlDocument xdDBCache = new XmlDocument();
> xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
> return xdDBCache;
> }
>|||daz_oldham (Darren.Ratcliffe@.gmail.com) writes:
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
The error message is unknown to me, and I can't say where it's coming
from. However, I do spot an error:

> @.tXML text = null output
This won't fly. text for output parameters is bound to fail. You
cannot assign to variables of the type text.
If you are on SQL 2005, use varchar(MAX) instead. Or even better the
xml data type.
If you are on SQL 2000, return the XML column as a result set instead.
(In which case you must use something different than ExecuteNonQuery
to retrieve the data.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> Just a guess, but since the sp is going to return data, I don't think
> you should use ExecuteNonQuery. ExecuteNonQuery is used for executing
> statements that don't return a result set (like UPDATE or DELETE).
But Daz's procedure does not return any result set, but returns data in
an OUTPUT parameter (or would have returned, had he chosen a data type
that is eligible for output parameters). The procedure also includes a
PRINT statement. Both of these are fine with ExecuteNonQuery. (To get
the data from the PRINT statement you need an InfoMessage event handler.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland
I have changed this to get the value out via a data reader, and it is
spot on.
I have never been aware in the past about having a text value as an
output parameter, but I know now!
I am currently using SQL 2000 so can't take advantage of the added XML
benefits in 2005 which is a shame really.
Many thanks for your help - and everyone else too.
Regards
Darren

ExcelMDX assembly source property is blank

Hi, We're using SQL Server 2005 SP5. Excel 2005 was recently installed on the server so we could use Excel functions in MDX. However, we still get the function does not exist. For example: Query (1, 20) The '[Excel].[Ceiling]' function does not exist. VBA functions work fine.

When looking at the properties of the ExcelMDX Assembly (via SQL Server Mgmt Studio) the Source property is blank.

It just appears that Excel is not registered with Analysis Services. I've searched everywhere for an answer and found a few others describing the same problem but no suggested solutions. Any help would be appreciated.

Thanks,

Ed

I don't have any documentation on this, but I believe that for COM based assemblies that the source property is meant to be blank. The way COM activation works, it does not make sense to load COM assembly source into SSAS. Where as .Net source can be re-assembled into a byte stream and executed directly.

The source property is blank on my laptop and the Excel functions work fine.

Does Excel run OK on the server? It sounds like it might be an issue with the COM registration.

Another option might be that, if you have someone with .Net skills, you might find that implementing your own .Net stored proc might be a faster solution. Generally speaking there is less marshalling overhead with COM assemblies than .Net ones, but the Excel calls have to go cross process which can have a significant performance impact.