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

Execute Script From C# Application Rather Than From QA

How would I go about telling SQL Server to execute a script from a C#
application? Would I simply use ADO.NET's SqlCommand object - and have the
command text be a string that is the script (i.e., read the text file
containing the script into a string and set a SqlCommand object's
CommandType to .Text and set the CommandText to the string read from the
text file)?
Here's the "big picture" FWIW:
I have a non trivial script that I've been executing via QA. Rather than
executing it via QA, I want to include it as part of a larger installation
routine that is incorporated into a C# application.
Thanks.Hi
If you script has go statements then it will fail. The safest way to execute
the scirpt would be to break it down into into separate statements and then
excute those individually.
John
"Jeffrey Todd" wrote:

> How would I go about telling SQL Server to execute a script from a C#
> application? Would I simply use ADO.NET's SqlCommand object - and have the
> command text be a string that is the script (i.e., read the text file
> containing the script into a string and set a SqlCommand object's
> CommandType to .Text and set the CommandText to the string read from the
> text file)?
> Here's the "big picture" FWIW:
> I have a non trivial script that I've been executing via QA. Rather than
> executing it via QA, I want to include it as part of a larger installation
> routine that is incorporated into a C# application.
> Thanks.
>
>|||It has no GO statements. It does a few SELECTS and then just over 100
INSERTS. So, would you suggest 100 separate EXECUTE statements - perhaps
wrapped in an ADO.NET transaction?
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:468F9060-ADA5-4330-B4E9-E23DE4BF5C49@.microsoft.com...
> Hi
> If you script has go statements then it will fail. The safest way to
> execute
> the scirpt would be to break it down into into separate statements and
> then
> excute those individually.
> John
> "Jeffrey Todd" wrote:
>|||Hi
If the inserts are all the same, then you may want to look at bulk loading
(possibly XML) or using a parameterised query and just setting data values.
If the size of your current statements do not exceeed the batch size then yo
u
may be able to run it in one execution.
Having everything wrapped in a transaction would be useful if you are adding
or modifying existing data, and you need the capability of rolling back to
where you started.
Because the requirements for each release may vary you may find that you are
constantly spending time twing your installation program to copy with the
different issues each release brings.
John
"Jeffrey Todd" wrote:

> It has no GO statements. It does a few SELECTS and then just over 100
> INSERTS. So, would you suggest 100 separate EXECUTE statements - perhaps
> wrapped in an ADO.NET transaction?
>
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:468F9060-ADA5-4330-B4E9-E23DE4BF5C49@.microsoft.com...
>
>