SQL Server FOR XML (Stream?)

[TheVillageIdiot27]

I a trying to create an ASP page which I will be using for AJAX requests.

The page needs to go to a SQL 2005 database execute a stored procedure (FOR XML) and return the results as XML -

at the moment I have:

'Response.ContentType = "text/xml"
Set objReportDBConn = Server.CreateObject("ADODB.Connection")
	objReportDBConn.Open "Provider=SQLOLEDB;Data Source=DBSVR;DATABASE=thedatabase;User ID=sa;Password=thepassword"
	Set objADOCmd = Server.CreateObject("ADODB.Command")
        Set objADOStream = Server.CreateObject("ADODB.Stream")
	objADOStream.Open
	objADOStream.Charset = "UTF-8"
	With objADOCmd
		.ActiveConnection = objReportDBConn
		.CommandText = "sp_thestoredprocedure"
		.CommandType = 4
		.Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4, 0)
		.Parameters.Append .CreateParameter("@myparam", 200, 1, 25 , "showall")
		.Properties("Output Stream").Value = objADOStream
	End With

	objADOCmd.Execute

This appears to be working thus far - the query certainly seems to be executing, but I can't seem to get the XML out of the query.

Any help here gratefully appreciated.

The parameter will eventually be taken from the querystring.

 

[tsuji]

'.CommandType = 4
Try either
[1] include adExecuteStream.
[tt] .CommandType = &h4 or &h400[/tt]
or
[2] comment it out and set the option of the ado command object's execute method
[tt] [red]'[/red]CommandType = 4
'etc etc
objADOCmd.Execute ,,&h4 or &h400[/tt]