SQL Server - Error in result_fetch(res@ptr, n) : wstring_convert::to_bytes

[gitnirajky]

Issue Description and Expected Result

dbGetQuery fails translating an Unicode special character stored in NVARCHAR(MAX) column in SQL Server with error message: Error in result_fetch(res@ptr, n) : wstring_convert::to_bytes

Database

SQL Server 2016, All ODBC drivers

Reproducible Example

Create a SQL server table with NVARCHAR column, add data as below with special character: ( similar to <U+FFFD> but not exactly, please see image) . I can't paste the script here as special characters changes.

Issue appears with any versions of R below: R 4.1.0 (May, 2021) R 4.0.5 (March, 2021) R 4.0.4 (February, 2021) R 4.0.3 (October, 2020) R 4.0.2 (June, 2020) R 4.0.1 (June, 2020) R 4.0.0 (April, 2020)

and any of SQL server ODBC drivers: Tested with SQL Server below drivers and same issue ODBC Driver 17 for SQL Server ODBC Driver 13 for SQL Server SQL Server

R 4.0.5, odbc package version 1.3.2, RStudio version, RStudio-1.4.1106, Windows 10 Pro machine

install.packages("DBI")
install.packages("odbc")

require(odbc)

packageVersion("odbc")
#sort(unique(odbcListDrivers()[[1]]))
#con@info$driver.version

con <- dbConnect(odbc(), "SQLServerDSN")
#dbGetInfo(con)
qry<- "SELECT * FROM [dbo].[tempt_test]"

frm <- dbGetQuery(con,qry)
dbDisconnect(con)

[nickv9]

I am also getting this when querying data on a Microsoft SQL Server database where the column contains non ANSI characters. > sessionInfo() R version 4.1.1 (2021-08-10) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 19042)

Matrix products: default

locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252 [4] LC_NUMERIC=C LC_TIME=English_United States.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] dbplyr_2.1.1 forcats_0.5.1 stringr_1.4.0 dplyr_1.0.7 purrr_0.3.4 readr_2.0.2 tidyr_1.1.4 tibble_3.1.5
[9] ggplot2_3.3.5 tidyverse_1.3.1

loaded via a namespace (and not attached): [1] tidyselect_1.1.1 xfun_0.26 haven_2.4.3 colorspace_2.0-2 vctrs_0.3.8 generics_0.1.0 htmltools_0.5.2
[8] yaml_2.2.1 utf8_1.2.2 blob_1.2.2 rlang_0.4.11 pillar_1.6.3 glue_1.4.2 withr_2.4.2
[15] DBI_1.1.1 bit64_4.0.5 sessioninfo_1.1.1 modelr_0.1.8 readxl_1.3.1 lifecycle_1.0.1 munsell_0.5.0
[22] gtable_0.3.0 cellranger_1.1.0 rvest_1.0.1 evaluate_0.14 knitr_1.36 fastmap_1.1.0 tzdb_0.1.2
[29] fansi_0.5.0 broom_0.7.9 Rcpp_1.0.7 scales_1.1.1 backports_1.2.1 jsonlite_1.7.2 fs_1.5.0
[36] bit_4.0.4 digest_0.6.28 hms_1.1.1 stringi_1.7.5 grid_4.1.1 cli_3.0.1 tools_4.1.1
[43] odbc_1.3.2 magrittr_2.0.1 crayon_1.4.1 pkgconfig_2.0.3 ellipsis_0.3.2 ctxsdb_0.1.0 xml2_1.3.2
[50] reprex_2.0.1 lubridate_1.8.0 assertthat_0.2.1 rmarkdown_2.11 httr_1.4.2 rstudioapi_0.13 R6_2.5.1
[57] compiler_4.1.1

[simonpcouch]

I'm not able to reproduce at the moment, though I'm using <U+FFFD>:

library(DBI)
library(odbc)
con <- dbConnect(odbc(), dsn = "MicrosoftSQLServer", uid = "SA", 
                 pwd = Sys.getenv("sqlServerPass"))

packageVersion("DBI")
#> [1] '1.2.2'
packageVersion("odbc")
#> [1] '1.4.2'
con
#> <OdbcConnection> dbo@sql2
#>   Database: master
#>   Microsoft SQL Server Version: 16.00.4095

dbExecute(con, SQL("SET ANSI_NULLS ON"))
#> [1] 0
dbExecute(con, SQL("SET QUOTED_IDENTIFIER ON"))
#> [1] 0
dbExecute(con, SQL("CREATE TABLE [dbo.temp_test]([Answer] [nvarchar](100) NULL);"))
#> [1] 0
dbExecute(con, "INSERT [dbo.temp_test] ([Answer]) VALUES (N'Tricky character � test');")
#> [1] 1

dbReadTable(con, "dbo.temp_test")
#>                    Answer
#> 1 Tricky character � test

dbExecute(con, SQL("DROP TABLE [dbo.temp_test]"))
#> [1] 0

^{Created on 2024-03-12 with reprex v2.1.0}

I'm on MacOS aarch64, driver is Microsoft (rather than FreeTDS or Posit's Professional Drivers).

[simonpcouch]

The changes in https://github.com/r-dbi/odbc/pull/553 went out in 1.3.5, which was released after this issue was filed. Is it possible that PR resolved this issue, @detule?

[detule]

Hey Simon,

Was either fixed by that PR or alternatively, may have been related to a dated tool chain. Either way, I think we can close in absence of recent reports.

Thanks!