SQLXML3.0 Question

[mysorian]

If the SQLXMLCommand.text property is set to a stored procedure(or template) and the ExecuteStream method is called an exception is thrown whether or not it is processed on the server or the client. The exception is posted here: Exception Details: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x80040E21. Source Error: Line 36: 'sxcmd.RootTag = "FullName" Line 37: Dim MemSt As MemoryStream Line 38: MemSt = sxcmd.ExecuteStream()--->**error occuring here** Line 39: Response.Write(MemSt.Length) Line 40: Dim strRdr As New StreamReader(MemSt After much searching, try to find what HRESULT:0X800040E21 is, and you will be surprised as to what I found. It says that an error occured:-O. How much more user friendly message do you want?:|

[DavidNohejl]

maybe a stupid question, bt is that stored procedure ok? I mean does it really return XML? (contains FOR XML modifier) If not, take a look at this[^] mysorian wrote: How much more user friendly message do you want? ah. It's COM. You can expect everything from COM but not being user friendly :) best regards, David 'DNH' Nohejl Never forget: "Stay kul and happy" (I.A.)

[mysorian]

Hi David: Nothing is stupid, everything needs an explanation. Thanks for the link to the article, but it is not using the ExecuteStream method. sincerely, mysorian ah! too many things under the hood:mad:

[DavidNohejl]

mysorian wrote: Nothing is stupid :laugh: So, how'd you call somebody who produced 53MB (!) ASPX web form due to buggy XSLT? (jup, I have my day!) :-D So, does it mean that SP doesn't return XML? I basically told you to look at that link only IF it isn't ok ;) mysorian wrote: but it is not using the ExecuteStream method. No it's not, bt it shows you how to make SP produce XML results. Btw I think you use ExecuteStream right way. You can use ad-hoc SQL statements or you can also execute stored procedures that return XML. Consider the following stored procedure: CREATE PROCEDURE dbo.GetEmployeesXml AS SELECT * FROM employees FOR XML AUTO RETURN This can be executed as follows: SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString); cmd.RootTag = "Employees"; cmd.CommandText = "EXEC GetEmployeesXML"; XmlReader xr = cmd.ExecuteXmlReader(); I suspect ExecuteStream() fails because SP return non-XML data (some table or something like that). If you can, try ExecuteReader() (and XmlReader instead of stream). That way you can be sure if problem is in SP or somewhere else. (If it fails than problem is probably in SP) mysorian wrote: ah! too many things under the hood :~ Is that your sig or do you say this to me? :-D David Never forget: "Stay kul and happy" (I.A.)

[mysorian]

David: There is more to it than meets the eye... ------------- PROCEDURE dbo.GetEmployeesXml AS SELECT * FROM employees FOR XML AUTO RETURN ------------------------------ Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not IsPostBack Then Dim sxcmd = New SqlXmlCommand("PROVIDER=SQLOLEDB.1;SERVER=XPHTEK;DATABASE=Northwind;INTEGRATED SECURITY=sspi;") sxcmd.RootTag = "Employees" sxcmd.CommandText = "EXEC GetEmployeesXml" Dim xrdr As System.Xml.XmlReader xrdr = sxcmd.ExecuteXmlReader() Response.Write(xrdr.HasValue) End If End Sub ---------------------------- Exception Details: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x80040E21. Source Error: Line 32: Dim xrdr As System.Xml.XmlReader Line 33: Line 34: xrdr = sxcmd.ExecuteXmlReader()--->error generted here Line 35: Response.Write(xrdr.HasValue) Line 36: While xrdr.Read ----------------------- Now I substitute the 'stored procedure' by the statement used in the procedure, voila! No exception thrown.