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?
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.
Dieter Menne wrote
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?
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.