0

When I ran a procedure from R, it stops in the middle of the execution. But if I ran it directly from SQL Server, it completes the execution.

Here is the code (there is not a lot to show):

connection<-odbcDriverConnect("SERVER=server_name;DRIVER={SQL Server};DATABASE=DB;UID=RUser;PWD=****")
stringEXEC<-"EXEC [dbo].[LongProcedure]"
data<-sqlQuery(channel = connection,query = stringEXEC,errors = TRUE)

Some remarks:

  • the procedure is calling for 12 another procedures. and each of the 12 creating a specific table (it's very long query to print it here in the question)

And there is no error.

Why is this happening?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dima Ha
  • 156
  • 1
  • 4
  • 20
  • What's the query, what's the error, is there one ? what's the data value if there's none ? does it happen with any query ? Please edit to describe your issue in further details. – moodymudskipper Nov 12 '17 at 13:47
  • @Moody_Mudskipper ok, Thanks! – Dima Ha Nov 12 '17 at 13:49
  • Carefully check where *middle of execution* occurs. Is there a semicolon placed there? Are you wrapping entire call in `BEGIN...END`? Do you have `SET NOCOUNT ON`? We need to actually see the TSQL to help. Try posting main stored proc (not other 12) here or with Github Gists or pastebin. – Parfait Nov 12 '17 at 16:25
  • Interestingly, a [Python poster](https://stackoverflow.com/q/47273543/1422451) had your same issue of partial stored proc execution via ODBC with SQL Server which appears to be resolved with a `BEGIN ... END` properly placed. – Parfait Nov 13 '17 at 22:37

1 Answers1

0

I ran into a similar issue. Currently, you can only execute SELECT statement from R, not stored procedures.

If you prefer working in R-Studio, I suggest executing the results of your stored procedure into a table in SQL Server first, then using that table in R. You'll still get the benefit of scalability with that compute context.

Passing T-SQL select statement to sp_execute_external_script

Andrew
  • 373
  • 2
  • 8