T-SQL Temp Tables and Stored Procedures from R using RevoScaleR

Question

In the example below, I was able to get the query to work with one exception. When I use q in place of source.query during the RxSqlServerData step, I get the error rxCompleteClusterJob Execution halted.

The first goal is to use a stored procedure in place of a longer query. Is this possible?

The second goal would be to create and call upon a #TEMPORARY table within the stored procedure. I'm wondering if that is possible, as well?

library (RODBC)
library (RevoScaleR)
sqlConnString <- "Driver=SQL Server;Server=SAMPLE_SERVER; Database=SAMPLE_DATABASE;Trusted_Connection=True"
sqlWait <- TRUE   
sqlConsoleOutput <- FALSE  
sql_share_directory <- paste("D:\\RWork\\AllShare\\", Sys.getenv("USERNAME"), sep = "")
sqlCompute <- RxInSqlServer(connectionString = sqlConnString, wait = sqlWait, consoleOutput = sqlConsoleOutput)  
rxSetComputeContext(sqlCompute) 


#This Sample Query Works
source.query <- paste("SELECT CASE WHEN [Order Date Key] = [Picked Date Key]",
                              "THEN 1 ELSE 0 END AS SameDayFulfillment,",
                              "[City Key] AS city, [STOCK ITEM KEY] AS item,",
                              "[PICKER KEY] AS picker, [QUANTITY] AS quantity",
                              "FROM [WideWorldImportersDW].[FACT].[ORDER]",
                              "WHERE [WWI ORDER ID] >= 63968")
#This Query Does Not
q <- paste("EXEC [dbo].[SAMPLE_STORED_PROCEDURE]")
inDataSource <- RxSqlServerData(sqlQuery=q, connectionString=sqlConnString, rowsPerRead=500)
order.logit.rx <- rxLogit(SameDayFulfillment ~ city + item + picker  + quantity, data = inDataSource)
order.logit.rx

You can catch with SQL SERVER PROFILER which exactly error occurs in SQL. Perhaps this user may have no permissions to run this sp. — Ivan Starostin, Dec 11 '16 at 10:40
@IvanStarostin, after going back through my code line by line, I found the solution/problem. Currently, only T-SQL SELECT statements are allowed as input data-set, not stored procedures. Here's an interesting blog post on the topic [Passing T-SQL select statement to sp_execute_external_script](https://tomaztsql.wordpress.com/2016/06/20/passing-t-sql-select-statement-to-sp_execute_external_script/). — Andrew, Dec 12 '16 at 17:08

score 0 · Accepted Answer · answered Dec 14 '16 at 21:31

0

Currently, only T-SQL SELECT statements are allowed as input data-set, not stored procedures.

answered Dec 14 '16 at 21:31

Andrew

373
2
8

T-SQL Temp Tables and Stored Procedures from R using RevoScaleR

1 Answers1