Search code examples
sqlrsql-serverodbcdbi

Numeric value out of range using R, ODBC and DBI

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.

Solution

  • 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.