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

[daz_oldham]

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;
}

 

[Guest]

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).

 

[Erland Sommarskog]

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, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Erland Sommarskog]

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, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[daz_oldham]

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