ExecuteXMLReader fails with valid xml from sql server. Why?

(From a .net class using sqlcommand)
A stored proc that takes 2 input params is firing on the server (i've
checked using sql profiler). The sp in question produces valid xml (i've
checked by creating a sqlxml virutal dir and allowing url queries - the
result is valid in IE and therefore well formed xml)
The stored proc in question is not a single "select X for xml ..." query. It
is made up of fragments
example of results
<root>
<subnode>
select x ... for xml ...
</subnode>
<subnode>
select x ... for xml ...
</subnode>
...
</root>
Why won't ExecuteXmlReader work with the results?
XR = CMD.ExecuteXmlReader()
(where xr is an xml reader)
this line comes back with the error
"Invalid command sent to ExecuteXmlReader. The command must return an Xml
result."
The command is not invalid! I can see it executing on sql. The result is
valid xml.
I have also tried using the sqlxmlcommand but it claimed I was not passing
parameters in (which I was) so I gave up on that.
I am beginning to hate .net
The concept is great but there is too much pain
trying to connect things together...
Any ideas...?
CODE--
Dim StartDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
Dim EndDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
CMD = New SqlCommand
CMD.Connection = CN
Select Case SubType
Case Nothing
CMD.CommandText = "XXXXX"
CMD.CommandType = CommandType.StoredProcedure
Dim Param As New SqlParameter
CN.Open()
Try
Param.ParameterName = "@.start_date" : Param.Value =
StartDate : CMD.Parameters.Add(Param)
Param = New SqlParameter
Param.ParameterName = "@.end_date" : Param.Value =
EndDate : CMD.Parameters.Add(Param)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
Case Else
'do something
End Select
'--
Try
XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
* * * *
ResultsDoc = New XmlDocument
ResultsDoc.Load(XR)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
END CODE--
In your description, you create a command like this:

> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
Where did you do such construction? What type of variable or command are you
using?
One easy solution on Yukon is that you can try is store the result in a
"xml" type variable, and then select from it.
For the ExecuteXmlReader() expecting an "XML" typed data back, instead of
any string that looks like "XML".
Thanks.
Xin
"adolf garlic" wrote:

> (From a .net class using sqlcommand)
> A stored proc that takes 2 input params is firing on the server (i've
> checked using sql profiler). The sp in question produces valid xml (i've
> checked by creating a sqlxml virutal dir and allowing url queries - the
> result is valid in IE and therefore well formed xml)
> The stored proc in question is not a single "select X for xml ..." query. It
> is made up of fragments
> example of results
> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
> Why won't ExecuteXmlReader work with the results?
> XR = CMD.ExecuteXmlReader()
> (where xr is an xml reader)
> this line comes back with the error
> "Invalid command sent to ExecuteXmlReader. The command must return an Xml
> result."
> The command is not invalid! I can see it executing on sql. The result is
> valid xml.
> I have also tried using the sqlxmlcommand but it claimed I was not passing
> parameters in (which I was) so I gave up on that.
> I am beginning to hate .net
> The concept is great but there is too much pain
> trying to connect things together...
> Any ideas...?
>
> CODE--
>
> Dim StartDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
> Dim EndDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
> CMD = New SqlCommand
> CMD.Connection = CN
> Select Case SubType
> Case Nothing
> CMD.CommandText = "XXXXX"
> CMD.CommandType = CommandType.StoredProcedure
> Dim Param As New SqlParameter
> CN.Open()
> Try
> Param.ParameterName = "@.start_date" : Param.Value =
> StartDate : CMD.Parameters.Add(Param)
> Param = New SqlParameter
> Param.ParameterName = "@.end_date" : Param.Value =
> EndDate : CMD.Parameters.Add(Param)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
> Case Else
> 'do something
> End Select
> '--
> Try
> XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
> * * * *
> ResultsDoc = New XmlDocument
> ResultsDoc.Load(XR)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
>
> END CODE--

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
..net framework - 1.0.3705
Thanks,
Scott
Hi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about the
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the[vbcol=seagreen]
|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode-532459699int
StackTrace" at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManaged Interface.ExecuteToOutputStream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(S tream strm)"string
Scott Walters wrote:[vbcol=seagreen]
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fake
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote:
sql

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
.net framework - 1.0.3705
Thanks,
ScottHi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about t
he
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode -532459699 int
StackTrace " at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManagedInterface.ExecuteToOutputS
tream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(Stream strm)" string
Scott Walters wrote:
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
>|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fa
ke
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote: