[R] RODBC results from stored procedure
tradenet
nodecorum at yahoo.com
Wed Jul 15 20:09:14 CEST 2009
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.
More information about the R-help
mailing list