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?

So you a have schema in sql server named port? – jrara Dec 09 '11 at 20:07 — jrara, Dec 09 '11 at 20:07

score 9 · Answer 1 · answered Dec 22 '12 at 22:09

9

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,

answered Dec 22 '12 at 22:09

iba99_99

91
2
3

1

Also is heavily important that the stored procedure should not print any message via the "PRINT" command. – Galled Sep 29 '15 at 21:39
1

Is like this link http://codeworkaround.tumblr.com/post/38942730894/executingcalling-a-ms-sql-stored-procedure-from – Galled Sep 29 '15 at 21:45

score 1 · Answer 2 · answered Dec 09 '11 at 20:13

1

Try this:

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

answered Dec 09 '11 at 20:13

jrara

16,239
33
89
120

score 0 · Answer 3 · answered Apr 01 '16 at 15:14

0

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

answered Apr 01 '16 at 15:14

Matthew

41
1

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

3 Answers3

Linked