I recently worked through a strange error (with help from a couple sharp cookies at Microsoft) and wanted to throw together a quick blog post in case anybody else sees it.

I have SQL Server R Services set up, and in the process of running a fairly complex stored procedure, got the following error message:

Msg 39004, Level 16, State 22, Line 0

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

There were two Stack Exchange posts, but not much else.  In one of them, Loretta Parks says to look at permissions.  I knew that wasn’t the issue because I could run this procedure sometimes, but not all the time, and when running the R code itself, everything worked as expected.  The other basically became “Well, I’ll do it outside of SQL Server R Services”

In my case, the issue was around arithmetic overflow.  Here’s a quick repro:

DECLARE
    @TestAccuracy DECIMAL(5, 2);

EXEC sp_execute_external_script
    @language = N'R',
    @script = N'TestAccuracy <- 15000.07',
    @params = N'@TestAccuracy DECIMAL(5,2) OUTPUT',
    @TestAccuracy = @TestAccuracy OUTPUT;

This script is quite simple:  it assigns a value of 15000.07 to a variable named TestAccuracy, and then tries to output the TestAccuracy variable in R to a SQL Server variable defined as DECIMAL(5,2).  The largest value a DECIMAL(5,2) can hold is 999.99, so this translation fails.

I’ve reported this to the right group and I’m hopeful that they will be able to provide a more meaningful error message in a future release.  But in the meantime, if you get error 0x80004005, this is a potential cause.

5 thoughts on “Error 0x80004005 In SQL Server R Services”

    1. Yeah, it wasn’t a particularly great time trying to figure out the issue, as the real code example was pretty large and the lack of surrounding information led me to look at the wrong thing first (and then another wrong thing second, and so on). The way I figured out the issue was to comment out every line of the script and re-enable in small sections until I stumbled upon the answer. In retrospect, I had a print statement which wrote out the decimal number and I should have realized a lot sooner that I can’t cram a four-digit or five-digit number into three digits.

      Reply
  2. Pingback: Error 0x80004005 In SQL Server R Services – Curated SQL

  3. Hi Kevin,

    Have you tried this:

    DECLARE @TestAccuracy DECIMAL(7,2)
    –SET @TestAccuracy = 15000.07

    EXEC sp_execute_external_script
    @language = N’R’
    ,@script = N’TestAccuracy <- 15000.07'
    ,@params = N'@TestAccuracy DECIMAL(7,2) OUTPUT'
    ,@TestAccuracy = @TestAccuracy OUTPUT;

    PRINT @TestAccuracy

    Or doing this without executing R script and with simple T-SQL:

    DECLARE @TestAccuracy DECIMAL(5,2)
    SET @TestAccuracy = 15000.07

    — Error message
    –Msg 8115, Level 16, State 8, Line 21
    –Arithmetic overflow error converting numeric to data type numeric.

    Is there any particular reason why you using DECIMAL(5,2); that is precision of 5 and scale of 2 instead of precision of 7 and scale of 2 (DECIMAL(7,2))?

    Best, Tomaž

    Reply

    1. Hey, Tomaz. I guess I should have been more clear: what I reported was that arithmetic overflow doesn’t give you a nice error but rather gives you a nasty, generic one which is hard to debug. The real R code we were using was, well, real R code, but the trivial example above got to the crux of the issue. You’re totally right about the answer, though: once I figured out what the problem was, changing the decimal size was the solution.

      Reply

Leave a comment