6

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
tonyk
  • 348
  • 5
  • 22
  • could you try formatting your date object with `format(example_date, format = "%Y-%m-%d")`? – Benjamin Jul 07 '17 at 13:16
  • Tried that but it is giving the same error, it converted the column to a character type? – tonyk Jul 07 '17 at 14:29
  • 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`. – Benjamin Jul 07 '17 at 14:32
  • Did you try `as.Date()`? – Dirk Eddelbuettel Jul 09 '17 at 22:20
  • I see you opened a bounty. Had you provided a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-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. – Parfait Jul 09 '17 at 23:12
  • @Parfait Apologies, I have added more information – tonyk Jul 10 '17 at 10:01
  • @DirkEddelbuettel I have tried both `as.POSIXct()` and `as.Date()` but no luck with either yet – tonyk Jul 10 '17 at 10:02
  • 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. – erg Jul 10 '17 at 10:57
  • 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? – tonyk Jul 10 '17 at 11:38
  • 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? – Parfait Jul 10 '17 at 15:02
  • 2
    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? – Nick Kennedy Jul 12 '17 at 23:19

1 Answers1

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)
arcstorm
  • 11
  • 1