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

Exchange-SQL server connectivity

I am fairly new to both the technologies.
Scenario:
I have an Exchange server on one machine, with Outlook. I
am looking for a way to get the information from Exchange
servers (mainly public folders) to SQL-server that is
located on a different machine.
My problem is as follows:
a. I need to get data from Exchange to SQL-server, real
time.
I am going through a round-about way of getting this
information. I have created Linked tables in an Access
Database that exports the data to the SQL-server (on the
same machine). It is not real time.
I can not seem to see the Linked tables from Access
when I use DTS.
There has to be a better way to do this.
Please advise.
Deven
for real time, open the outlook to the mailbox, using Outlook VBA NewMessage
write code to the sql server. Just leave the outlook open.
"deven" <anonymous@.discussions.microsoft.com> wrote in message
news:7cfa01c43149$c84894b0$a001280a@.phx.gbl...
> I am fairly new to both the technologies.
> Scenario:
> I have an Exchange server on one machine, with Outlook. I
> am looking for a way to get the information from Exchange
> servers (mainly public folders) to SQL-server that is
> located on a different machine.
> My problem is as follows:
> a. I need to get data from Exchange to SQL-server, real
> time.
> I am going through a round-about way of getting this
> information. I have created Linked tables in an Access
> Database that exports the data to the SQL-server (on the
> same machine). It is not real time.
> I can not seem to see the Linked tables from Access
> when I use DTS.
> There has to be a better way to do this.
> Please advise.
> Deven
>

Exchange-SQL server connectivity

I am fairly new to both the technologies.
Scenario:
I have an Exchange server on one machine, with Outlook. I
am looking for a way to get the information from Exchange
servers (mainly public folders) to SQL-server that is
located on a different machine.
My problem is as follows:
a. I need to get data from Exchange to SQL-server, real
time.
I am going through a round-about way of getting this
information. I have created Linked tables in an Access
Database that exports the data to the SQL-server (on the
same machine). It is not real time.
I can not seem to see the Linked tables from Access
when I use DTS.
There has to be a better way to do this.
Please advise.
Devenfor real time, open the outlook to the mailbox, using Outlook VBA NewMessage
write code to the sql server. Just leave the outlook open.
"deven" <anonymous@.discussions.microsoft.com> wrote in message
news:7cfa01c43149$c84894b0$a001280a@.phx.gbl...
> I am fairly new to both the technologies.
> Scenario:
> I have an Exchange server on one machine, with Outlook. I
> am looking for a way to get the information from Exchange
> servers (mainly public folders) to SQL-server that is
> located on a different machine.
> My problem is as follows:
> a. I need to get data from Exchange to SQL-server, real
> time.
> I am going through a round-about way of getting this
> information. I have created Linked tables in an Access
> Database that exports the data to the SQL-server (on the
> same machine). It is not real time.
> I can not seem to see the Linked tables from Access
> when I use DTS.
> There has to be a better way to do this.
> Please advise.
> Deven
>

Exchange Server as a LINKED SERVER

I have successfuly link Exchange to my sql server...what i
am trying to do is fetch all the data of the Exchange and
archive it to an SQL database... my question is it
possible when i got all the information from exchange and
archive it... i can use that archive to restore my
exchange mailboxes or something similar? any suggestions
is highly appreciated...
I don't think so as it's not in the same format needed by Exchange.
You'd want to look at just backing up Exchange. For individual
mailboxes, you could look at brick level backup/restore.
-Sue
On Thu, 9 Sep 2004 01:51:13 -0700, "perry" <kenzai_chuz@.yahoo.com>
wrote:

>I have successfuly link Exchange to my sql server...what i
>am trying to do is fetch all the data of the Exchange and
>archive it to an SQL database... my question is it
>possible when i got all the information from exchange and
>archive it... i can use that archive to restore my
>exchange mailboxes or something similar? any suggestions
>is highly appreciated...
|||Yeah, I think you're right. But my problem is this. If i
use the ExMerge Utility of microsoft, it only permits me
to backup not more that 2gb of file? I am using E2K
server...any suggestions? Because our mails are larger
than 2GB because of the attachments and one more thing...
will ExMerge backup or archive the attachments?
|||No suggestions on the ExMerge issues. You'd probably be
better off posting questions on ExMerge in one of the
Exchange newsgroups. Try:
microsoft.public.exchange2000.admin
-Sue
On Sun, 12 Sep 2004 17:55:14 -0700,
<anonymous@.discussions.microsoft.com> wrote:

>Yeah, I think you're right. But my problem is this. If i
>use the ExMerge Utility of microsoft, it only permits me
>to backup not more that 2gb of file? I am using E2K
>server...any suggestions? Because our mails are larger
>than 2GB because of the attachments and one more thing...
>will ExMerge backup or archive the attachments?