Getting SQL stored procedure results in data.frame format using RODBC

Question

I use the RODBC package to query the results on my SQL server. I have a specific stored procedure written when executed in my SQL Server Mgmt. studio (for example), returns a table. However, when I run the request through R, it returns character(0)

 # Execute command... sqlQuery(production,"exec port.tdp_RISK2_ModelRunCompare @ModelRunId1 = 399") 

It's strange when I do something like ...

 sqlQuery(production,"exec sp_who") 

I get a table of results ...

reference

Answer 1

I had the same problem.

You can try:

 set nocount on 

in the MS SQL Server stored procedure so that it returns only a dataset.

Hello,

Answer 2

Try the following:

 sqlQuery(production,"exec port.tdp_RISK2_ModelRunCompare @ModelRunId1 = 399", errors=FALSE) 

Answer 3

Also try writing to a new data frame. I kept getting the character (0) when I tried to overwrite an existing data frame using the sqlQuery function.