how to make a prediction from Microsoft sql server management studio.

Getting the following error in Step 5 of the \SQLR-Fraud-Detection.ps1 walkthrough script.

Invoke-Sqlcmd : A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
An external script error occurred:
The response variable 'Label' has only one level.
It must have at least 2 levels to fit a classification tree.
Error in doTryCatch(return(expr), name, parentenv, handler) :
The response variable 'Label' has only one level.
It must have at least 2 levels to fit a classification tree.
Calls: source ... tryCatch -> tryCatchList -> tryCatchOne -> doTryCatch -> .Call
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
At \SQLR-Fraud-Detection.ps1:63 char:5

• Invoke-Sqlcmd -ServerInstance $ServerName  -Database $DBName -Que ...
  

• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
  • FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Hi,

I just have few questions regarding this problem (time to failure prediction). I have few thoughts and I need your feedback about them (https://github.com/Microsoft/SQL-Server-R-Services-Samples/blob/master/PredictiveMaintenance/R/02a-regression-modeling.R)

Do we have right censoring in this problem? or did you assume that all the observations died and you know the exact time of the failure? In the case of right censoring, I was thinking classic ML models such as boosted tree and decision forest might not be appropriate and we need to focus on survival models. In case of considering only the devices that did die, there might be a bias in the model as we didn't consider those who are still alive. I am just trying to understand the problem better. I appreciate if you could possibly explain a little bit more about the problem definition and the model adjustment.

Thanks,
Mahsa

"Forecasting" Table Creation Script is missing

Don't know if this is the right forum for this but in the Quickstart: Predict and plot from model using R in SQL Server there is either a typo or just a bad example.

After creating the model and the new data to predict I tried to run the code:

DECLARE @speedmodel varbinary(max) = (select model from [dbo].[stopping_distance_models] where model_name = 'default 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);
            OutputDataSet <- cbind(new, ceiling(predicted.distance));
            '
    , @input_data_1 = N' SELECT [speed] FROM [dbo].[HugeTableofCarSpeeds] '
    , @input_data_1_name = N'NewCarData'
    , @parallel = 1
    , @params = N'@speedmodel varbinary(max)'
    , @speedmodel = @speedmodel
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))

As it said in the tutorial. However, I got an error message saying:

Msg 39004, Level 16, State 20, Line 68
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 68
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 69
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.

I had some trouble interpreting this error message, however, when I looked at the code I noticed DECLARE @speedmodel varbinary(max) = (select model from [dbo].[stopping_distance_models] where model_name = 'default model'); statement in the beginning.

This code assumes you have many models serialized, but if you (like me) just followed the tutorial you don't.

This Error is displayed when running code on line 298 of 01-data-preparation.R

ERROR: The sample data set for the analysis has no variables.
Caught exception in file: CxAnalysis.cpp, line: 3756. ThreadID: 12080 Rethrowing.
Caught exception in file: CxAnalysis.cpp, line: 5249. ThreadID: 12080 Rethrowing.
Error in doTryCatch(return(expr), name, parentenv, handler) :
ERROR: The sample data set for the analysis has no variables.

I suppose this error occurs due to the NULL value on the sd features for id=1 . An i really don't know how to remove this NULL values. I kindly need your help/assistance in removing this NULL values if that is the problem leading to this Error. Else what could be the reason for it to lead to an Error while everything works out for the normalization of test_features? And also in the test_features after being Normalized the columns setting3, s1, a1 and sd1 are missing.

Hi I have tried to walk through the PS steps but keeps erroring on 3a... any ideas? Thanks.

Invoke-Sqlcmd : A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
An external script error occurred:
Error in contrasts<-(*tmp*, value = contr.funs[1 + isOF[nn]]) :
contrasts can be applied only to factors with 2 or more levels
Calls: source ... model.matrix -> model.matrix.default -> contrasts<-
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
At C:\Users\USER\Downloads\Churn\SQLR\SQLR-Customer-Churn.ps1:53 char:5

• Invoke-Sqlcmd -ServerInstance $ServerName  -Database $DBName -Use ...
  

• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
  • FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

This repository misses the license file.
The severity of that varies from project to project, but my goal here is to attract some attention.

If it's not a problem, this issue can be closed.

I'm leaving it here just in case: https://help.github.com/articles/adding-a-license-to-a-repository/.

Thanks.

When you use trusted_connection=true in the connection string, RxInSqlServerData and RxSqlServerData return the error message:
Error in .rxEnsureOdbcCredentials(connectionString, server, databaseName, :
The Trusted_Connection keyword is not allowed in connection string.

Please document a reliable way to use integrated security since it was announced as working since RC2 and put it clearly in your What's New documents since finding the connection string is virtually an exercise in google futility.

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

Hello,
I tried Galaxies sample,but i got error "C:/Galaxies/data/galaxiesTraining.csv"
after invoke stored procedure 'TrainGalaxiesNN'.
I think ,It need file galaxiesTraining.csv for tranining.
thanks

if I have to instruct a user on how to use the predictive maintenance application: I would need at least a text explanation of the input variables and how to construct the query. All I found (notebook 4) is the follwing that lacks detail. Any help would be appreciated.

az ml service run realtime -i amlworkbenchpdmwebservice -d "{"input_df": [{"rotate_rollingmean_36": 450.0384342542265, "age": 9, "rotate_rollingstd_12": 0.0, "volt_rollingstd_36": 0.0, "volt_rollingstd_12": 0.0, "volt_rollingstd_24": 0.0, "pressure_rollingstd_36": 0.0, "error1sum_rollingmean_24": 0.0, "rotate_rollingmean_12": 445.7130438343768, "machineID": 27, "vibration_rollingmean_24": 40.302192663278625, "comp4sum": 399.0, "error4sum_rollingmean_24": 0.0, "pressure_rollingmean_36": 99.1626730910439, "pressure_rollingstd_12": 0.0, "vibration_rollingmean_12": 39.69610732198209, "comp3sum": 444.0, "error2sum_rollingmean_24": 0.0, "error5sum_rollingmean_24": 0.0, "pressure_rollingmean_24": 100.42784289855126, "pressure_rollingmean_12": 103.46853199581041, "vibration_rollingstd_36": 0.0, "vibration_rollingstd_12": 0.0, "rotate_rollingstd_36": 0.0, "vibration_rollingstd_24": 0.0, "volt_rollingmean_36": 166.5072079613422, "vibration_rollingmean_36": 39.86004229336383, "rotate_rollingstd_24": 0.0, "comp2sum": 564.0, "pressure_rollingstd_24": 0.0, "volt_rollingmean_24": 166.69782028530955, "comp1sum": 504.0, "volt_rollingmean_12": 162.37456132546583, "rotate_rollingmean_24": 444.92430808877185, "error3sum_rollingmean_24": 0.0}, {"rotate_rollingmean_36": 452.58602482190344, "age": 9, "rotate_rollingstd_12": 7.358009183124642, "volt_rollingstd_36": 1.2113288898088435, "volt_rollingstd_12": 1.7162303092954838, "volt_rollingstd_24": 1.1327450423992658, "pressure_rollingstd_36": 0.360813923769749, "error1sum_rollingmean_24": 0.0, "rotate_rollingmean_12": 448.82482383859184, "machineID": 27, "vibration_rollingmean_24": 39.8762193116053, "comp4sum": 399.0, "error4sum_rollingmean_24": 0.0, "pressure_rollingmean_36": 99.18126302139088, "pressure_rollingstd_12": 1.3059590035299573, "vibration_rollingmean_12": 40.534215611846555, "comp3sum": 444.0, "error2sum_rollingmean_24": 0.0, "error5sum_rollingmean_24": 0.0, "pressure_rollingmean_24": 98.84197839575184, "pressure_rollingmean_12": 100.13428527324218, "vibration_rollingstd_36": 0.12802019423837702, "vibration_rollingstd_12": 0.5581845837178677, "rotate_rollingstd_36": 1.3063227195446807, "vibration_rollingstd_24": 0.26866456414969686, "volt_rollingmean_36": 166.8633264221902, "vibration_rollingmean_36": 39.83194043387068, "rotate_rollingstd_24": 6.2252625510326345, "comp2sum": 564.0, "pressure_rollingstd_24": 0.5506261833397947, "volt_rollingmean_24": 168.8315798036505, "comp1sum": 504.0, "volt_rollingmean_12": 169.6342364499553, "rotate_rollingmean_24": 455.68853459771736, "error3sum_rollingmean_24": 0.0}, {"rotate_rollingmean_36": 452.6366978657443, "age": 9, "rotate_rollingstd_12": 12.545522310840685, "volt_rollingstd_36": 0.4066137169118576, "volt_rollingstd_12": 1.9026812928919759, "volt_rollingstd_24": 1.388783538126311, "pressure_rollingstd_36": 0.40800640702349306, "error1sum_rollingmean_24": 0.0, "rotate_rollingmean_12": 462.5522453568429, "machineID": 27, "vibration_rollingmean_24": 39.48080284488274, "comp4sum": 398.0, "error4sum_rollingmean_24": 0.0, "pressure_rollingmean_36": 99.92595364177775, "pressure_rollingstd_12": 0.30398746497620055, "vibration_rollingmean_12": 39.21822301136402, "comp3sum": 443.0, "error2sum_rollingmean_24": 0.0, "error5sum_rollingmean_24": 0.0, "pressure_rollingmean_24": 98.70475189546528, "pressure_rollingmean_12": 97.5496715182615, "vibration_rollingstd_36": 0.06733738203927228, "vibration_rollingstd_12": 0.33150005427630586, "rotate_rollingstd_36": 0.726203655443797, "vibration_rollingstd_24": 0.2757178837764945, "volt_rollingmean_36": 164.9839282666808, "vibration_rollingmean_36": 39.16084871098736, "rotate_rollingstd_24": 2.2615583783043336, "comp2sum": 563.0, "pressure_rollingstd_24": 0.43573594568766316, "volt_rollingmean_24": 165.47787140830766, "comp1sum": 503.0, "volt_rollingmean_12": 168.0289231573457, "rotate_rollingmean_24": 454.4666253135592, "error3sum_rollingmean_24": 0.0}]}"

Doing the same test (for Boosted Decision Tree Regression) in AzureML and on SQLR, resulting predictions are very different. Here are the result in SQLR

Mean Absolute Error: 21.1772554      
Root Mean Squared Error: 27.6586547      
Relative Absolute Error: 0.5759823 
Relative Squared Error: 0.4429988 
Coefficient of Determination 0.5570012

and these are the one produced by AzureML

Mean Absolute Error: 0.123788                  
Root Mean Squared Error: 0.248766      
Relative Absolute Error: 0.003367 
Relative Squared Error: 0.000036
Coefficient of Determination 0.999964 

To be honest the AzureML results seems to be even too great :)

Do you have the same results that I have?

the script executes successfully but no changes are reflected in database.

ERROR

Msg 39004, Level 16, State 20, Line 103
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 103
An external script error occurred:

Attachement du package : 'dplyr'

The following objects are masked from 'package:stats':

filter, lag

The following objects are masked from 'package:base':

intersect, setdiff, setequal, union

Le chargement a nécessité le package : ggplot2
Le chargement a nécessité le package : grid
Le chargement a nécessité le package : mvtnorm
Le chargement a nécessité le package : modeltools
Le chargement a nécessité le package : stats4
Le chargement a nécessité le package : strucchange
Le chargement a nécessité le package : zoo

Attachement du package : 'zoo'

The following objects are masked from 'package:base':

as.Date, as.Date.numeric

Le chargement a nécessité le package : sandwich
Error in [.data.frame(clt, , catcol) : objet 'catcol' introuvable
Calls: source ... eval -> eval -> sapply -> lapply -> [ -> [.data.frame

Error in execution. Check the output for more information.
Error in eval(expr, envir, enclos) :
Msg 39019, Level 16, State 2, Line 103
An external script error occurred:
Error in execution. Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Exécution arrêtée

Hi zhixingr,

I tried out the Fraud Detection example but got the following error message when executing rxExec function in the script 01-generate-tagged-data.R.

#rxExec function allows distributed execution of a function in parallel across nodes (computers) or cores of a "compute context" such as a cluster

• rxExec(tag_fraud,

•    in_table1 = untagged_table,
  

•    in_table2 = fraud_table,
  

•    out_table = tagged_fraud_table,
  

•    tag_mode = "Acct")
  

====== DATASCISQL16DB ( process 1 ) has started run at 2017-01-05 02:42:45.93 ======

DATASCISQL16DB [6024]: Rows Processed: 50000
DATASCISQL16DB [6024]: Rows Processed: 100000
DATASCISQL16DB [6024]: Rows Processed: 150000
DATASCISQL16DB [6024]: Rows Processed: 200000

DATASCISQL16DB [6024]: Rows Processed: 8640

DATASCISQL16DB [6024]: Rows Processed: 10000
Failed to allocate 20480000 bytes.
Caught exception in file: CxAnalysis.cpp, line: 6488. ThreadID: 3036 Rethrowing.
Caught exception in file: CxAnalysis.cpp, line: 5682. ThreadID: 3036 Rethrowing.
Caught exception in file: CxAnalysis.cpp, line: 5249. ThreadID: 3036 Rethrowing.
Error in doTryCatch(return(expr), name, parentenv, handler) :
bad allocation
Calls: source ... tryCatch -> tryCatchList -> tryCatchOne -> doTryCatch -> .Call
In addition: Warning messages:
1: In as.POSIXlt.POSIXct(x, tz) : unable to identify current timezone 'C':
please set environment variable 'TZ'
2: In as.POSIXlt.POSIXct(x, tz) : unknown timezone 'localtime'
Execution halted
Error in rxCompleteClusterJob(hpcServerJob, consoleOutput, autoCleanup) :
No results available - final job state: failed

Could you please kindly advise? Thanks a lot!

Hi there,

the script 'generate_dataset.sql' in the example for RetailForecasting seems to be incomplete.
Could you please correct?

Thanks
Thomas

Dear Expert,

I'd tried to test but not successfully.
My environment:
OS: WS2012 R2 latest update.
SQL: 2016 ENT GA

The error was recorded the table runlogs:
An external script error occurred:
Error in unserialize(as.raw(model)) : 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.

Please help me to review.

Thanks,
Vu Pham

If the "plyr" and the "zoo" packages are not installed the PredictiveMaintenance sample fail giving a weird error message:

ERROR: The sample data set for the analysis has no variables.
Error in doTryCatch(return(expr), name, parentenv, handler) : 
  ERROR: The sample data set for the analysis has no variables.

Followed the installation instructions for RServices in SQL Server 2016, but am not able to test any of these due to this issue. I've tried searching for solutions every where and have lost a few days of time trying to find the solution with no luck to date.

Is there any additional information about this simulated dataset (https://github.com/Microsoft/SQL-Server-R-Services-Samples/tree/master/PredictiveMaintanenceModelingGuide/Data)?

1. What real machine is simulated here?
2. What are the units of measurement for these sensors?
3. What do the different machine models stand for?
4. What do the different machine components stand for?
5. What do the different errors stand for?

There in one file missing in Predictive Maintenance Directory "feature_engineering_scoring.sql".

DataProcessing\feature_engineering_scoring.sql

Please provide some solution.

Note to creators

In file:
https://github.com/Microsoft/SQL-Server-R-Services-Samples/blob/master/Churn/SQLR/PredictRx.sql

Line 24:

Scores<-rxPredict(modelObject = mod, data = InputDataSet, outData = NULL,
predVarNames = "Score", type = "response", writeModelVars = FALSE, overwrite = TRUE);

should be

Scores<-rxPredict(modelObject = mod, data = InputDataSet, outData = NULL,
predVarNames = "TagPred1", type = "response", writeModelVars = FALSE, overwrite = TRUE);

As in line 27 you refer back to TagPred1.
Or vice versa offcourse..

I've tried getting RServices installed and set up on my organization's SQL Server 2016 Standard instance several times now. Each time I follow the set up/installation instructions published on Microsoft's website and it has yet to succeed.

I have the Launchpad service started and all of the MSSQLSERVER users are created as is the SQLRUserGroup logon. I've granted permissions to execute external scripts and am one of the sys admins on the server itself. However, no matter what I do I always get the same uninformative error message:

exec sp_configure 'external scripts enabled', 1
reconfigure with override;

Exec sp_configure  'external scripts enabled';

exec sp_execute_external_script  @language =N'R',  
@script=N'OutputDataSet<-InputDataSet',    
@input_data_1 =N'select 1 as hello'  
with result sets (([hello] int not null));  
go  

Msg 39011, Level 16, State 1, Line 1
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.  
Msg 11536, Level 16, State 1, Line 1
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.

Is it asking too much to get installation instructions that will work as expected or for more complete installation/troubleshooting documentation to be published? For example, some users have mentioned something about a db_rrerole database role, but this isn't created when I work through the installation process and since there isn't any mention of this role in the official documentation I have no way of knowing if this was something only needed during the community testing release or if it is still required in the current release.

while implementing the project on my computer, there is an error in sql. the error says "Msg 208, Level 16, State 1, Procedure PredictChurnR, Line 9 [Batch Start Line 39]
Invalid object name 'ChurnModelR'." How can I cope with it?

Hi,
I am trying a scenario where I need to run R script on 1 M data. I am trying it with your method, I am using rxDTree instead of rxDForest because it is taking too much time to build model on 100000 rows also both algorithms are giving error when I try to execute it on 1M data. The error is-

A ‘R’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x80004004.

I read that comment where you said it could be memory issues but in my case memory went upto 39 GB max before giving this error.

I have 52 GB RAM on my machine. I know that RevoScaleR can handle very large data-set in SQL Server. I am very excited to see the performance of it.

I really want to go in more detail about this if you can help me, really appreciate it.

Thanks,
Akash