How to write to table with date column with DBI

Question

I'm trying to append a dataframe to a sql server table using:

DBI::dbWriteTable(con_poc, "DEP_EVENTS", data_up, overwrite=FALSE, append = TRUE, verbose = TRUE, rownames = FALSE)

But I am getting an error on a column that is 'date' type in the database.

    Error in result_insert_dataframe(rs@ptr, values) : 
  nanodbc.cpp:1587: 22003: [Microsoft][ODBC SQL Server Driver]Numeric value out of range 

I previously formatted the column using as.POSIXct(example_date) but this only seems to work for 'datetime' columns

Can anyone help?

Adding info:

DEP_EVENTS:
DATA_REGION (varchar(50), not null)
EVENT_ID(PK, bigint, not null)
EVENT_NAME(varchar(200), not null)
FORECAST_STATUS(varchar(50), not null)
FORECAST_CYCLE(date, not null)

data_up <- data.frame(DATA_REGION = "America",
                      EVENT_NAME = "shiny deal",
                      FORECAST_STATUS = "Plan of Record",
                      FORECAST_CYCLE = as.Date("2017-07-07"))

DBI::dbWriteTable(con_poc, "DEP_EVENTS", data_up, overwrite=FALSE, append = TRUE, verbose = TRUE, rownames = FALSE)

Error in result_insert_dataframe(rs@ptr, values) : 
  nanodbc.cpp:1587: 22003: [Microsoft][ODBC SQL Server Driver]Numeric value out of range 

I'm not inserting the primary key as I get the following error when I try that

Error in result_insert_dataframe(rs@ptr, values) : 
  nanodbc.cpp:1587: 23000: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'DEP_EVENTS' when IDENTITY_INSERT is set to OFF. 

Also as requested:

str(data_up)
'data.frame':   1 obs. of  4 variables:
 $ DATA_REGION    : Factor w/ 1 level "America": 1
 $ EVENT_NAME     : Factor w/ 1 level "shiny deal": 1
 $ FORECAST_STATUS: Factor w/ 1 level "Plan of Record": 1
 $ FORECAST_CYCLE : Date, format: "2017-07-07"

I also tried changing the factor columns to character but no change in the error.

Ignacia

could you try formatting your date object with format(example_date, format = "%Y-%m-%d")? – Heinrick

Tried that but it is giving the same error, it converted the column to a character type? – Ignacia

I usually use RODBCext instead of DBI, and converting dates and datetimes to strings was required up until a recent release. I didn't know if it was similar in DBI. – Heinrick

Did you try as.Date()? – Obscuration

I see you opened a bounty. Had you provided a reproducible example including a few rows of your R dataframe with datatypes (str()) and the SQL Server table's schema we would have gladly helped two days ago. Please provide those now. – Serrell

@Serrell Apologies, I have added more information – Ignacia

@DirkEddelbuettel I have tried both as.POSIXct() and as.Date() but no luck with either yet – Ignacia

Are you 100% sure the error is about the date column? I would expect an error like Invalid date format if the problem is within the date column. Guessing around: Your columns get mixed up, and it fails inserting a string value into the EVENT_ID column. I would try: Remove the auto-generated primary key column, make it (for the test) a normal bigint column, try to insert all values of the table. Or the other way round: Remove all columns except the date column to verify the problem is really within the date column. As said: guessing around. – Surmise

I have tried sqlSave from the RODBC package and also encountered problems. It was trying to insert into the primary key column. Is there a way using either sqlSave or dbWriteTable to skip the autoincrement primary key column? – Ignacia

In your data.frame() try adding the arg, stringsAsFactors=False as factors may be treated as numeric during database insert. Also, is Event_ID an auto-generated number, specifically Identity(1,1) field? – Serrell

I've just tried the posted code using both the odbc and RSQLServer packages to connect to SQL Server, and it works fine in both cases. Which package are you using? Which operating system? Which version of SQL Server? – Millford

Answer 1

Using RODBC you can insert into the primary key column by prefacing your sqlSave() command with a SET IDENTITY_INSERT = ON statement. For example:

con = odbcConnect(MY_DSN)
sqlQuery(con,'SET IDENTITY_INSERT DEP_EVENTS ON')
sqlSave(con, DEP_EVENTS, rownames = FALSE, append = 
        TRUE, verbose = FALSE, fast = FALSE)        
sqlQuery(con,'SET IDENTITY_INSERT DEP_EVENTS OFF')
close(con)

Cini