`dbplyr` only returning the first character of every text column

[jdlong]

Y'all I've got the weirdest thing. I've got a database that's SQL Server and I connect to it all the time. Tonight, when doing one join that I know worked a year ago, I only get back the first digit from each character field. Other data types return the whole thing, but not character

I end up with a mess like this:

After a few hours of all the usual tests and checks (including rebooting), I am going to go through and try and produce a reproducible example. I can't quite figure out how to reproduce, tbh. But I'll work on it. Before I do, have any of you seen this type of hot mess? Googling for this is hard because of the search noise of people WANTING only the first character.

I'll come back later with reprex, if I can make one. But I thought I'd put out my feelers before I dumped more hours into this.

[technocrat]

Syntax of the statement? Pleeees?

[jdlong]

I was going to do this then a quick test showed me I getting truncated answers from one table even if I do simply:

my_tibble %>% head %>% collect

so for some reason this one table (and not others) is producing truncated results

[technocrat]

Could you dput the tribble?

[jdlong]

yeah it's a remote tibble on SQL Server... here's an edited version

structure(list(src = structure(list(con = new("Microsoft SQL Server", 
    ptr = <pointer: 0x7fa9f2d48d40>, quote = "\"", info = structure(list(
        dbname = "xxxx", dbms.name = "Microsoft SQL Server", 
        db.version = "13.00.5622", username = "xxx\xxxx", host = "", 
        port = "", sourcename = "", servername = "xxxxxx", 
        drivername = "Simba SQL Server ODBC Driver", odbc.version = "03.52", 
        driver.version = "1.3.14.1000", odbcdriver.version = "03.80", 
        supports.transactions = TRUE, getdata.extensions.any_column = TRUE, 
        getdata.extensions.any_order = TRUE), class = c("Microsoft SQL Server", 
    "driver_info", "list")), encoding = ""), disco = NULL), class = c("src_Microsoft SQL Server", 
"src_dbi", "src_sql", "src")), ops = structure(list(x = structure("Location.Property", class = c("ident_q", 
"ident", "character")), vars = c("PropertyId", "

...

"SourcePropertyId"
)), class = c("op_base_remote", "op_base", "op"))), class = c("tbl_Microsoft SQL Server", 
"tbl_dbi", "tbl_sql", "tbl_lazy", "tbl"))

[technocrat]

Désolé, I misunderstood, thought it was the my_tibble of the screenshot. I'm now officially over my head.

[nirgrahamuk]

Databases often have metadata tables that would show the column datatatypes etc. If a history of these exist, I would check if the column definitions changed at some point in history.
Ultimately this issue may require you being supported by a database admin, or some stakeholder who is responsible for the data contents to work with you to resolve.

[jdlong]

Big ups to @jimhester who helped me figure this out. I'm on a Mac and this is from a bug in the odbc package. The solution was simply to install odbc from source:

devtools::install_github("r-dbi/odbc")

[jimhester]

You actually don't need to install the development version, just recompiling the code from CRAN will work as well, e.g. install.packages("odbc", type = "source")