DBI::dbWriteTable doesn't append beyond specific number of rows and throws exception on nvarchar(max)

Question

• Currently working with Professional Rstudio drivers

• I have set all varchar to max in Sqlserver table as well but it didnt help.

  diag_test <- diag[1:1025]
  system.time({
  DBI::dbWriteTable(con, "urban_prodbi_diag", diag_test, append = TRUE)
  })
  
  Error in result_insert_dataframe(rs@ptr, values) : 
  nanodbc/nanodbc.cpp:1587: HY000: The incoming tabular data stream (TDS) 
  remote procedure call (RPC) protocol stream is incorrect. Parameter 5 
  (""): The supplied length is not valid for data type nvarchar(max). 
  Check the source data for invalid lengths. An example of an invalid 
  length is data of nchar type with an odd length in bytes., Timing 
  stopped at: 0.049 0.002 0.135
  

• It works with fewer rows though.

• Few subset examples:

  diag_test <- diagcopy[1:1024]
  system.time({
  DBI::dbWriteTable(con, "urban_prodbi_diag", diag_test, append = TRUE)
  })
  user  system elapsed 
  0.042   0.000   0.109 
  
  
  diag_test <- diagcopy[1000:1025]
  system.time({
  odbc::dbWriteTable(con, "urban_prodbi_diag", diag_test, append = TRUE)
   })
  user  system elapsed 
  0.014   0.000   0.074 
  

• Insert didnt give any error through RODBC package on the same table schema and dataframe.