SQl Server 2016 with R

Question

I have the below Query

DECLARE @speedmodel varbinary(max) = (SELECT [model] FROM [dbo].[stopping_distance_models] WHERE model_name = 'latest model');
EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(speedmodel));
            new <- data.frame(NewCarData);
            predicted.distance <- rxPredict(current_model, new);
            str(predicted.distance);
            OutputDataSet <- cbind(new, ceiling(predicted.distance));
            '
    , @input_data_1 = N' SELECT speed FROM [dbo].[NewCarSpeed] '
    , @input_data_1_name = N'NewCarData'
    , @params = N'@speedmodel varbinary(max)'
    , @speedmodel = @speedmodel
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))

while running this code I got an error message

Msg 39004, Level 16, State 20, Line 1 A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 1, Line 1 An external script error occurred: Error in unserialize(as.raw(speedmodel)) : read error Calls: source -> withVisible -> eval -> eval -> unserialize

Error in ScaleR. Check the output for more information. Error in eval(expr, envir, enclos) : Error in ScaleR. Check the output for more information. Calls: source -> withVisible -> eval -> eval -> .Call Execution halted Msg 11536, Level 16, State 1, Line 2 EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Why I'm getting this error?

Based on the error, it looks like a read error in unserialize call. How was the model serialized and stored in the DB? Can you share this code? — Arun Gurunathan

ASH ASH · Accepted Answer · 2017-12-28T23:56:13

Did you even connect ro your SQL Server DB?

library("RODBC")
#library("XLConnect")

dbhandle <- odbcDriverConnect('driver={SQL Server};server=Name_Of_Server;database=Name_Of_DB;trusted_connection=true')
currTableSQL<-paste("SELECT * From Your_Table",sep="")
currTableDF<-sqlQuery(dbhandle,currTableSQL)

Uncomment the XLConnect if you want to utilize that library. I feel like a lot of times if you are using SQL Server, you are using Excel as well.

Or . . .

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=YourServerName; Database=YourDBName;Uid=; Pwd=; trusted_connection=yes")
initdata <- sqlQuery(dbconnection,paste("select * from MyTable;"))
odbcClose(channel)

Check out these links:

https://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-2012-and-14/

Finally, make sure SQL Server has all proper permissions applied.

SQl Server 2016 with R

1 Answers