R help - Jul 2009 - RODBC results from stored procedure

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...]
>head(dbdata)
  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.

tradenet wrote:
> 
> 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 can call a parametrized stored procedure without a problem and the proc
> does indeed execute successfully. 
> 
This works for me with the popular Northwind database

channel = odbcConnect("northwind") # Assume this is configured
correctly
sqlQuery(channel,"EXEC CustOrderHist @CustomerID=ALFKI")

Try with a non-date query first, the switch to the tricky date format in the
parameter.

Dieter


-- 
View this message in context:
http://www.nabble.com/RODBC-results-from-stored-procedure-tp24503096p24503854.html
Sent from the R help mailing list archive at Nabble.com.