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

Question

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

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

Weird thing is... when I do something like...

sqlQuery(production,"exec sp_who")

I get a table of results...

Help?

Answer 1

I had the same problem.

You can try using:

set nocount on

in the MS SQL Server stored procedure so it will return just a dataset.

Regards,

Answer 2

Try this:

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