Add rows or update on a blob/varbinary in SQL server gives "[ODBC Driver 17 for SQL Server]String data, right truncation" error

[martedv]

When updating or adding a row in a database with a "blob/varbinary" column in sql server, an error is thrown.

This fails:

con <- DBI::dbConnect(odbc::odbc(),
    driver = "ODBC Driver 17 for SQL Server",
    server = db.keys[["server"]],
    database = "BlobDB",
    uid = db.keys[["uid"]],
    pwd = db.keys[["pwd"]]
  )
# table with blob_id (int) and blob (varbinary)
x <- con %>% tbl("table_with_blob")

y<- data.frame(blob_id=2, blob = blob::as_blob(base::serialize("test string in blob", NULL)))

rows_update(x, y, by = "blob_id", copy = TRUE, in_place = TRUE, unmatched = "ignore")
# gives: [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation
# same thing for rows_append

While it works with DBI:

y<- data.frame(blob = blob::as_blob(base::serialize("test string in blob", NULL)))
# this works
DBI::dbAppendTable(con, "table_with_blob", y)

[ejneer]

What is the definition of your blob column? It may not be large enough to fit your data.

rows_update and rows_append work in my tests, but dbAppendTable reports your right truncation error when attempting to insert data wider than the field being inserted to.

con <- DBI::dbConnect(
  odbc::odbc(),
  driver = "ODBC Driver 18 for SQL Server",
  server = "localhost",
  port = 1433,
  uid = "sa",
  pwd = "P@ssw0rd",
  TrustServerCertificate = "yes"
)

DBI::dbExecute(con, "CREATE TABLE my_table (
    blob_id INT,
    blob VARBINARY(MAX)
);")
#> [1] 0

DBI::dbExecute(con, "CREATE TABLE my_table2 (
    blob_id INT,
    blob VARBINARY(10)
);")
#> [1] 0

y <- data.frame(
  blob_id = 2,
  blob = blob::as_blob(base::serialize("longer than 10 bytes", NULL))
)

dplyr::rows_append(dplyr::tbl(con, "my_table"), y, copy = TRUE, in_place = TRUE)
#> Created a temporary table named #dbplyr_nxtuAFZkQt
dplyr::tbl(con, "my_table")
#> # Source:   table<"my_table"> [?? x 2]
#> # Database: Microsoft SQL Server 17.00.0925[dbo@14cb06ae4281/master]
#>   blob_id       blob
#>     <int>     <blob>
#> 1       2 <raw 59 B>


DBI::dbAppendTable(con, "my_table", y)
dplyr::tbl(con, "my_table")
#> # Source:   table<"my_table"> [?? x 2]
#> # Database: Microsoft SQL Server 17.00.0925[dbo@14cb06ae4281/master]
#>   blob_id       blob
#>     <int>     <blob>
#> 1       2 <raw 59 B>
#> 2       2 <raw 59 B>

dplyr::rows_append(dplyr::tbl(con, "my_table2"), y, copy = TRUE, in_place = TRUE)
#> Created a temporary table named #dbplyr_GkkXcWpNvO
#> Error in `dplyr::rows_append()`:
#> ! Can't modify database table "my_table2".
#> ℹ Using SQL: INSERT INTO "my_table2" ("blob_id", "blob") SELECT * FROM ( SELECT
#>   * FROM "#dbplyr_GkkXcWpNvO" ) "...y"
#> Caused by error in `dplyr::rows_append()`:
#> ! ODBC failed with error 00000 from [Microsoft][ODBC Driver 18 for SQL
#>   Server][SQL Server].
#> ✖ String or binary data would be truncated in table 'master.dbo.my_table2',
#>   column 'blob'. Truncated value: ''.
#> • The statement has been terminated.
#> • <SQL> 'INSERT INTO "my_table2" ("blob_id", "blob")
#> • SELECT *
#> • FROM (
#> •  SELECT *
#> •  FROM "#dbplyr_GkkXcWpNvO"
#> • ) "...y"'
#> ℹ From 'nanodbc/nanodbc.cpp:1802'.
DBI::dbAppendTable(con, "my_table2", y)
#> Error: nanodbc/nanodbc.cpp:1867: 00000
#> [Microsoft][ODBC Driver 18 for SQL Server]String data, right truncation


y2 <- data.frame(
  blob_id = 2,
  blob = blob::as_blob(base::serialize("even longer than the last string", NULL))
)

dplyr::rows_update(dplyr::tbl(con, "my_table"), y2, copy = TRUE, in_place = TRUE, unmatched = "ignore")
#> Created a temporary table named #dbplyr_KWLM5oQn9B
#> Matching, by = "blob_id"
dplyr::tbl(con, "my_table")
#> # Source:   table<"my_table"> [?? x 2]
#> # Database: Microsoft SQL Server 17.00.0925[dbo@14cb06ae4281/master]
#>   blob_id       blob
#>     <int>     <blob>
#> 1       2 <raw 71 B>
#> 2       2 <raw 71 B>

^{Created on 2025-10-18 with reprex v2.1.1}