Im trying to execute four practice scripts for a SQL fundamentals class
setup. The first three scripts run fine. The last script gives me the
following errors:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'Orders', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'Product', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'Customer', because it does not exist in the system
catalog.
Server: Msg 128, Level 15, State 1, Line 8
The name 'C001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'C002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'C003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'C004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'C005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 13
The name 'C006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 14
The name 'C007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 15
The name 'C008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 3
The name 'P010' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 4
The name 'P009' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 5
The name 'P008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 6
The name 'P007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 7
The name 'P006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 8
The name 'P005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'P004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'P003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'P002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'P001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 3
The name 'O001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 4
The name 'O002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 5
The name 'O003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 6
The name 'O004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 7
The name 'O005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 8
The name 'O006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'O007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'O008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'O009' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'O010' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
When I connect from the client, I can connect to the database but no
information is available in my select statement.www.aspfaq.com/5006
Based on the info you posted, it's doubtful anyone could tell you any more
than the error messages you already received...
"Buffalotech" <Buffalotech@.discussions.microsoft.com> wrote in message
news:354B7594-309E-4E1D-8C88-48288225D0DA@.microsoft.com...
> Im trying to execute four practice scripts for a SQL fundamentals class
> setup. The first three scripts run fine. The last script gives me the
> following errors:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'Orders', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Product', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Customer', because it does not exist in the system
> catalog.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'C001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'C002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'C003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'C004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'C005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 13
> The name 'C006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 14
> The name 'C007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 15
> The name 'C008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'P010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'P009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'P008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'P007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'P006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'P005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'P004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'P003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'P002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'P001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'O001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'O002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'O003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'O004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'O005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'O006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'O007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'O008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'O009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'O010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> When I connect from the client, I can connect to the database but no
> information is available in my select statement.|||Hi
At a guess there are a several possible reasons for this!
1. The script is wrong in that assumes the objects exist before trying to
drop them. This may imply that you have either not executed all the scripts,
or have not run them in the correct order.
2. You are running the script against the wrong database.
3. A combination of the above as the script uses the USE command to set the
database, but you have created a different database to the one documented as
there was already one that existed with the given name!
John
"Buffalotech" <Buffalotech@.discussions.microsoft.com> wrote in message
news:354B7594-309E-4E1D-8C88-48288225D0DA@.microsoft.com...
> Im trying to execute four practice scripts for a SQL fundamentals class
> setup. The first three scripts run fine. The last script gives me the
> following errors:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'Orders', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Product', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Customer', because it does not exist in the system
> catalog.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'C001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'C002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'C003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'C004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'C005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 13
> The name 'C006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 14
> The name 'C007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 15
> The name 'C008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'P010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'P009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'P008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'P007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'P006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'P005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'P004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'P003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'P002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'P001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'O001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'O002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'O003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'O004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'O005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'O006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'O007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'O008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'O009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'O010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> When I connect from the client, I can connect to the database but no
> information is available in my select statement.|||On Sun, 27 Mar 2005 18:55:02 -0800, Buffalotech wrote:
(snip)
>Server: Msg 128, Level 15, State 1, Line 8
>The name 'C001' is not permitted in this context. Only constants,
>expressions, or variables allowed here. Column names are not permitted.
(snip)
Another wild guess: this error is often caused because string constants
are enclosed in "double quotes" instead of 'single quotes'.
The good way to solve this is to open the script and replace all quotes.
The quikc and ugly kludge is to force non-standard interpretation of
quotes. Look up SET QUOTED_IDENTIFIER in BOL.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label class. Show all posts
Showing posts with label class. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
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--
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--
Monday, March 19, 2012
ExecuteNonQuery hangs in Timer event notification
If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?
.B ekiM
class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");
static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);
System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;
// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);
Console.ReadLine();
}
private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
SqlCommand cmd = new SqlCommand(
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @.When, @.AvailPhys);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @.When, @.AvailPageFile);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @.When, @.AvailVirtual);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @.When, @.AvailExtendedVirtual);\n");
DateTime dt = DateTime.Now;
cmd.Parameters.AddWithValue("AvailPhys", mem.ullAvailPhys);
cmd.Parameters.AddWithValue("AvailPageFile", mem.ullAvailPageFile);
cmd.Parameters.AddWithValue("AvailVirtual", mem.ullAvailVirtual);
cmd.Parameters.AddWithValue("AvailExtendedVirtual", mem.ullAvailExtendedVirtual);
cmd.Parameters.AddWithValue("When", dt);
cmd.ExecuteNonQuery();
Console.WriteLine("Inserted {0}", dt);
}
}
A-hah! It's not hanging; it's just throwing an exception that the runtime itself catches, then doesn't report.|||Is the problem solved then, or you you want to elaborate on the error message ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
The problem is that the runtime catches an exception. It shouldn't: it hasn't published a contract saying it will catch exceptions. It also offers very little indication that it did catch the exception.
These problems are certainly not solved.
ExecuteNonQuery hangs in Timer event notification
If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?
.B ekiM
class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");
static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);
System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;
// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);
Console.ReadLine();
}
private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
SqlCommand cmd = new SqlCommand(
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @.When, @.AvailPhys);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @.When, @.AvailPageFile);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @.When, @.AvailVirtual);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @.When, @.AvailExtendedVirtual);\n");
DateTime dt = DateTime.Now;
cmd.Parameters.AddWithValue("AvailPhys", mem.ullAvailPhys);
cmd.Parameters.AddWithValue("AvailPageFile", mem.ullAvailPageFile);
cmd.Parameters.AddWithValue("AvailVirtual", mem.ullAvailVirtual);
cmd.Parameters.AddWithValue("AvailExtendedVirtual", mem.ullAvailExtendedVirtual);
cmd.Parameters.AddWithValue("When", dt);
cmd.ExecuteNonQuery();
Console.WriteLine("Inserted {0}", dt);
}
}
A-hah! It's not hanging; it's just throwing an exception that the runtime itself catches, then doesn't report.|||Is the problem solved then, or you you want to elaborate on the error message ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
The problem is that the runtime catches an exception. It shouldn't: it hasn't published a contract saying it will catch exceptions. It also offers very little indication that it did catch the exception.
These problems are certainly not solved.
Execute() in class Microsoft.SqlServer.Dts.RunTime.Package has memory leak
The Execute method in Microsoft.SqlServer.Dts.RunTime.Package class has memory leak after each invokation. This following code demonstrates it.
Output from the program:
Allocated memory after 1 iteration(s) = 476316
Allocated memory after 2 iteration(s) = 546448
Allocated memory after 3 iteration(s) = 555008
Allocated memory after 4 iteration(s) = 563632
Allocated memory after 5 iteration(s) = 572232
Allocated memory after 6 iteration(s) = 580856
Allocated memory after 7 iteration(s) = 589480
Allocated memory after 8 iteration(s) = 598240
Allocated memory after 9 iteration(s) = 606816
Allocated memory after 10 iteration(s) = 615424
Allocated memory after 11 iteration(s) = 624000
Allocated memory after 12 iteration(s) = 632576
Allocated memory after 13 iteration(s) = 641152
Allocated memory after 14 iteration(s) = 649728
Allocated memory after 15 iteration(s) = 658352
Allocated memory after 16 iteration(s) = 666948
Allocated memory after 17 iteration(s) = 675760
Allocated memory after 18 iteration(s) = 684380
Allocated memory after 19 iteration(s) = 693008
Allocated memory after 20 iteration(s) = 701532
//--
// The Execute method in Microsoft.SqlServer.Dts.RunTime.Package has memory
// leak. This program demonstrates it. The package invoked by this program has
// only a single 'Script Task' that does nothing.
//
// To compile, add referece to Microsoft.SQLServer.ManagedDTS.dll.
//
// csc /r:"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll" ExecPackage.cs
//
//--
using System;
using System.Diagnostics;
using Microsoft.SqlServer.Dts.Runtime;
namespace Misc
{
/// <summary>
/// Programmatically executes SSIS package, then displays memeory usage
/// after each execution. The memeory usage goes up after each
/// Package.Execute() call, which indicates memory leak!
/// </summary>
static class ExecPackage
{
static void DisplayUsage()
{
Console.WriteLine(@."Usage: ExecPackage <pkgName>");
Console.WriteLine(@." Package <pkgName> resides in Package Store on localhost under \File System\");
}
static void Main(string[] args)
{
// Parse command line arguments.
if (args.Length != 1)
{
DisplayUsage();
return;
}
string pkgName = @."\File System\" + args[0];
// Programmatically execute the package several times.
Application app = new Application();
for (int i = 1; i <= 20; i++)
{
Package pkg = app.LoadFromDtsServer(pkgName, "localhost", null);
pkg.Execute(); // comment out this line, then allocated memory does not increase
// Process.Start("dtexec.exe", "/dts \"" + pkgName + "\"");
pkg.Dispose();
pkg = null;
// Do garbage collection, then display memory usage
GC.Collect();
Console.WriteLine("Allocated memory after {0} iteration(s) = {1}",
i, GC.GetTotalMemory(true));
}
}
}
}
Thank you for the detailed posting and your time. I have gathered the information and we will investigate.
|||Craig,
Are you able to confirm the issue? Is there a fix or workaround?
Thanks,
Bin
|||Bin, yes we are able to reproduce the issue and we are reviewing it for inclusion into the next service pack. However at this time there is not real work around other than restarting the appliction that calls the package. Thank you again for your time and patience.
|||Has there been any updates with this issue? I am currently dealing with the same problem.
Thanks,
Drew
|||the next service pack would be sp2, which has not yet been released.|||Thanks, but... is this really such a low priority it doesn't justify a hotfix? I'd say not being able to execut SSIS packages programmatically without having to write hacked code would be up on the top of their list... I know they had released a hotfix for the fuzzy lookup memory leak in the past... Is there anyone from MS who can confirm this is true?dferraro wrote:
Has there been any updates with this issue? I am currently dealing with the same problem.
Thanks,
Drew
Thanks again,
Drew|||Please advise...... we are looking to use this in a production environment, and I would like to know if/when this issue is fixed, so I can have better knowledge when deciding which options to pursue. How are other people dealing with this issue? Using dtexec? But what if you need to check return values, etc? Any other work-arounds besides building a composite app that runs the package and then dies off?
Thanks again,
Drew|||Looks like I'm having the same problem as well. I've got a .Net app that dynamically builds 120 tasks in a package and pulls in 3 million records running once an hour. I thought I had a memory leak with some other objects but it appears to happen whenever executing this package through the .Excecute() method in my app and still remains after destroying the object. Running this much data that often is going to be a real show stopper with a memory leak.|||The issue appears to be identified and we are working on the fix for a future Service pack.|||
Hello,
We have a production server which runs a nightly batch which is quite memory intensive. The batch contains a start package which is started by SQL Server Agent, and starts other packages using the "Execute Package Task".
The server crashes completely, without logging any error information at all, at random moments during the batch, usually with an interval of a few days. The vendor of the server is investigating the hardware configuration, but we weren't able to find a problem there as of yet.
Can the issue described in this topic have anything to do with the server crashes?
Kind regards, Jeroen
Subscribe to:
Posts (Atom)