[R-sig-DB] RODBC: how to view multiple objects returned by a stored procedure?

[Maciej Ombach]

I am trying to use a stored procedure (MS SQL Server 2005) that requires an xml input (R 2.11.0, RODBC 1.3-2).  This stored procedure returns a header/metadata and a data table (which I verified in DBArtisan).
 
My R code looks like this:
 
library(RODBC)
query <- "
exec sp_GetIndexData @ApplicationName='XXXXX', @UserName='XXXXX',
@XMLInput=N'<getdata>
<header fromdate=\"12/31/2009\" todate=\"8/23/2010\" frequency=\"1\" download=\"0\" viewcurveorspread=\"1\"/>
<serieslist>
<series cusip=\"H0A0\" item=\"62\"/>
</serieslist>
</getdata>'
"
query1 <- gsub("\n", "", query)
dbSource <- odbcConnect("XXX", uid = "xxx", pwd = "xxx")
out <- sqlQuery(dbSource, query1)
odbcClose(dbSource)
 
This and other slightly modified calls return an empty character string (out).  I am able to run simple select queries against this database, so the connection is working.  The stored proc also works in Excel VBA, where it returns two recordsets: the first one is empty (header?), the second one has the data I need (in VBA, rs.NextRecordset allows to loop through recordsets).
 
At this point, my best guess is that something similar is happening in R.  I am getting back an empty header and need to figure out how to view the actual data table.  Any suggestions would be much appreciated.
 
Maciek
 
 
 		 	   		  
	[[alternative HTML version deleted]]