Numeric value out of range using R, ODBC and DBI

Question

I have a problem that you could help me to solve. I had been trying to insert some rows into a table. I show you the table definition:

CREATE TABLE Scc_OrdenSevicioFunerarioPagare(
[NumeroPagare] [int] NOT NULL,
[CodigoEstablecimiento] [tinyint] NOT NULL,
[NumeroOrden] [int] NOT NULL,
[CodigoClienteAvalista] [int] NOT NULL,
[ValorRecibido] [money] NOT NULL,
[ValorPagare] [money] NOT NULL,
[FechaPago] [date] NOT NULL,
[CantidadPago] [int] NOT NULL)

Using R, I had tried to insert the rows with the next code:

dsnDesarrollo <- "TESTSQL";
SCC_OrdenServicioFunerarioPagare <- "Scc_OrdenSevicioFunerarioPagare";
con <- dbConnect(odbc::odbc(), dsnDesarrollo, encoding = 'latin1');
dbWriteTable(con, SCC_OrdenServicioFunerarioPagare, dfPagareFuente, append = TRUE);
dbDisconnect(con);

My dataframe dfPagareFuente only has one row at moment (just for test), the data:

(0 <dbl>, 3 <dbl>, 2214 <dbl>, 56239 <dbl>, 2275 <dbl>, 2600 <dbl>, '2017-01-05' <dttm>, 3 <dbl>)

But when I tried to run my R code, I got the error:

Error in result_insert_dataframe(rs@ptr, values) : nanodbc/nanodbc.cpp:1587: 22003: [Microsoft][ODBC SQL Server Driver]Valor numérico fuera del intervalo (Numeric value out of range)

Anyone can give me a clue about what am I doing wrong or any solution to this?

Always thank you. By the way, I'm using the DBI and odbc library for R.

Test each column for min/max values in R. One of them is probably outside the range for it's SQL Server data type. The most likely one, in my opinion, is CodigoEstablecimiento. Tinyints can only be 0-255 in SQL Server. There is no similar data type in R. It's possible you have a negative number or a large number. — Steven Hibble

Yadder Aceituno Yadder Aceituno · Accepted Answer · 2018-08-13T16:53:47

Finally I solved it.

The problem was the FechaPago field. In the table it has date data type, but in R I was trying to insert a datetime value. So I had to cast the values with as.Date in R.

I was able to disccovered when I deleted column by column to identify the problem.

Thank's Steven for your answer.

Numeric value out of range using R, ODBC and DBI

2 Answers