Re: [R] RODBC results from stored procedure
I know this thread is from a while back, but hopefully I can still get some help on this. I also used RODBC to connect to a SQL Server, and my stored procedure returns a results set (that is not stored as a temp or permanent table). So I was wondering if there was a way to access this results set, or am I forced to store the results into a table before I can access it? Some options I have considered: a)modifying the stored procedures to insert the results into tables b)creating temp tables (trying to stay away from this as it is not as dynamic and would require defining of many tables if I wanted to run some ad hoc analysis) The reason I do not want to modify the queries/create temp or perm tables is because the SQL end is maintained by a separate team, and I am simply using R to connect/run analyses off the data. I am trying to keep my side as self contained and independent as possible. Any suggestions/advice would be greatly appreciated. Thanks a lot, Adrian -- View this message in context: http://r.789695.n4.nabble.com/RODBC-results-from-stored-procedure-tp897462p2996173.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC results from stored procedure
Thanks Dieter. The stored proc drops and recreates a result table that contains a copy of the same result set that is returned by the query. If I pause after line 1, line 2 returns a valid result set and line 4 displays a table of data. If I do not pause the script after line 1 or 2, line 3 fails, saying datatable does not exist (line 1 query has not completed the recreation of datatable) and line 4 displays no data. Also, the query in line one returns a single result set, a select statement from datatable, but R doesn't see this returned result set after invocation of only line 1. 1.) dbdata<-sqlQuery(conn,"sp_GetData) #returns a result set in query analyzer, not in R 2.) head(dbdata) #it's empty 3.) dbdata<-sqlQuery(conn,"select * from datatable order by Date asc") 4.) head(dbdata) #the data is in dbdata My workaround is to run line 1's query manually before running any R scripts. Not ideal, but I'm at a loss for what else to try for this prickly case. Warm regards, Andrew Dieter Menne wrote: > > > > tradenet wrote: >> >> Thanks Dieter. >> >> The date argument isn't a problem. When I invoke the stored proc >> execution with the date arguments the stored proc runs fine, but RODBC >> doesn't wait for the stored proc to finish and return results. >> >> > > Don't understand that one. How do you know that is did not wait to finish? > > Did the call I showed you work with the Northwind database? > > Dieter > > -- View this message in context: http://www.nabble.com/RODBC-results-from-stored-procedure-tp24503096p24597208.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC results from stored procedure
tradenet wrote: > > Thanks Dieter. > > The date argument isn't a problem. When I invoke the stored proc > execution with the date arguments the stored proc runs fine, but RODBC > doesn't wait for the stored proc to finish and return results. > > Don't understand that one. How do you know that is did not wait to finish? Did the call I showed you work with the Northwind database? Dieter -- View this message in context: http://www.nabble.com/RODBC-results-from-stored-procedure-tp24503096p24577372.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC results from stored procedure
Thanks Dieter.
The date argument isn't a problem. When I invoke the stored proc execution
with the date arguments the stored proc runs fine, but RODBC doesn't wait
for the stored proc to finish and return results.
Regards,
Andrew
Dieter Menne wrote:
>
>
>
> 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-tp24503096p24576806.html
Sent from the R help mailing list archive at Nabble.com.
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC results from stored procedure
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.
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[R] 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...] Date Asset1 Asset2 2009-07-10 0.010.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. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
