R saves invalid encoding into SQL Server nvarchar column

Question

I'm trying to save text into SQL Server using R and ODBC Driver 17 for SQL Server. I have this simple script:

test <- data.table(dbGetQuery(con, "select LOCATIONNAME from LOCATION where LOCATION = 'AT2331'"))

name <- test[, LOCATIONNAME]

sql <- paste0('INSERT INTO LOCATION_TEST (TEST) VALUES (\'', name, '\')')

dbGetQuery(con, sql)

The data in the original table is VÖSENDORF and it's shown properly in R, but the result in the database is VÖSENDORF. The column type is nvarchar and server collation is SQL_Latin1_General_CP1_CI_AS. How can I fix this?

I also tried

sql2 <- iconv(sql, from="UTF-8", to="UTF-16LE")

But that says:

embedded nul in string

Edit:

Using 'N' prefix doesn't change the result:

sql <- paste0("INSERT INTO LOCATION_TEST (TEST) VALUES (N'", name, "')")

If I use just a hard coded value, it works fine:

sql <- paste0("INSERT INTO LOCATION_TEST (TEST) VALUES (N'VÖSENDORF')")

This is RStudio in Windows. Output of Sys.getlocale():

"LC_COLLATE=English_United Kingdom.1252;LC_CTYPE=English_United Kingdom.1252;LC_MONETARY=English_United Kingdom.1252;LC_NUMERIC=C;LC_TIME=English_United Kingdom.1252"

Tested my code in Linux with following locale, and there it works fine:

"LC_CTYPE=en_US.UTF-8;LC_NUMERIC=C;LC_TIME=en_US.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=en_US.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=en_US.UTF-8;LC_IDENTIFICATION=C"

Tested with the latest R version, 4.2.0 (2022-04-22 ucrt) and the same issue still continues.