[R] RODBC results from stored procedure

[tradenet]

Short of uploading a SQL server database, I don't think I can make this
example reproducible, but I hope it's not so complicated as to require
reproducibility....

I'm using RODBC to get data from Microsoft SQL Server.
I can call a parametrized stored procedure without a problem and the proc
does indeed execute successfully.  However, even though the proc returns the
results I found that I had to modify the proc so that, in addition to
returning the results to the caller, it also saved the results to an actual
SQL Server table.  Then I was able to make second call to sqlQuery with a
simple select statement for the results table and retrieve the results back
into R.  My question is:  can I get stored proc results directly back to R
without having to populate and query a results table?


>dbdata<-sqlQuery(conn,"sp_GetReturns,'2009-07-10','2009-07-14'")
>head(dbdata)
character(0)  [....no data here....]
>dbdata<-sqlQuery(conn,"select * from returns order by Date asc") 
[...success...]

  Date          Asset1   Asset2....
2009-07-10  0.01        0.02
2009-07-13  0.007      -0.003
...

I'd appreciate any suggestions,

Andrew

W






rm(dbdata)


#run query in query analyzer and get date from rtnsUnderscored table
#dbdata<-sqlFetch(conn,sqtable="sp_GetModelRtns4OptimizeRangeVer4
5000,'2001-10-01','2009-07-14")
#dbdata<-sqlFetch(conn,sqtable="sp_GetModelRtns4OptimizeRangeVer4
5000,'2009-07-01','2009-07-14")

dbdata<-sqlQuery(conn,"select * from rtnsUnderscored order by Date asc")
-- 
View this message in context: http://www.nabble.com/RODBC-results-from-stored-procedure-tp24503096p24503096.html
Sent from the R help mailing list archive at Nabble.com.