Error :Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library

Question

I'm trying to fetch data from MS SQL server in a shiny app. I'm using the odbc package version 1.2.2. When I run the app locally I am able to fetch the data and everything is fine. But on the server I have the following error :

Listening on http://127.0.0.1:5823
Warning in connection_release(conn@ptr) :
  There is a result object still in use.
The connection will be automatically released when it is closed
Warning: Error in : <SQL> 'select Parameter.name,ParamValue.value
                                    from Parameter
                                    inner join ParamValue on Parameter.id = ParamValue.parameterId
                                    where Parameter.name = 'airDensity'order by Parameter.name'
  nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. 
  81: <Anonymous>

The following is a short version of my app.

library(shiny)

ui <- fluidPage(
  withMathJax(),
  tabsetPanel(
    tabPanel("Help Page",
             h2(HTML("<ul> <b>How to use the tool</b></ul>")) 
    ),
    tabPanel("LCA",
      dashboardPage(skin = "green",
                    dashboardHeader(title = "LCA Database"),
                    dashboardSidebar(
                      tags$a(href = "https://www.xxxxxxxxx.com/en-int", tags$img(src='SG_logo_neg_rgb.png', height = 70, width = 230)),
                      tags$hr(),
                      selectInput("t1","Select Parameter", choices = c("Param")),
                      selectInput("t2","Select Parameter", choices = c("-"))

                    ),
                    dashboardBody(

                    )

      )
    )
  )
)

and the server :

 library(shiny)
    library(odbc)
    library(dbplyr)

    server <- function(input, output, session){



      main <- reactive({
        con <- dbConnect(odbc(),
                         Driver = "libtdsodbc.so",
                         Server = "xxxxx.xxxxxx.xxxxx",
                         Database = "LCA",
                         UID = "LCA-User",
                         PWD = "xxxxxxxxxxxxxx",
                         encoding = "windows-1252",
                         Port = 1433)

        on.exit(odbc::dbDisconnect(con))

        squery = dbSendQuery(con,"select name from Parameter order by name",stringsAsFactors = FALSE)
        parameterNmaes = dbFetch(squery )

        result= list(parameterNmaes = parameterNmaes)
        return(result)
      })

    # updating the first dropdown 
      observeEvent(main(),{

        updateSelectInput(session, "t1",
                          choices = main()$parameterNmaes)

      })

    # second dropdown based on user selection
      subselect1 <- reactive({
        con <- dbConnect(odbc(),
                         Driver = "libtdsodbc.so",
                         Server = "xxxx.xxxxx.xxxx",
                         Database = "LCA",
                         UID = "LCA-User",
                         PWD = "xxxxxxxxxxxxxx",
                         encoding = "windows-1252",
                         Port = 1433)

        on.exit(odbc::dbDisconnect(con))

        squery = dbSendQuery(con,paste0("select Parameter.name,ParamValue.value
                                        from Parameter
                                        inner join ParamValue on Parameter.id = ParamValue.parameterId
                                        where Parameter.name = '",input$t1,"'order by Parameter.name"),stringsAsFactors = FALSE)
        p = dbFetch(squery )
        p1 = p %>%
          mutate(deserial = lapply(p[,2], mType))%>%
          mutate(numericValue = sapply(lapply(deserial, deserialize),`[`, "v"))

        res = sort(as.character(unique(p1$numericValue)))
        result= list(res = res, p1 = p1)
        return(result)
      })

      observeEvent(subselect1(),{

        updateSelectInput(session, "t2",
                          choices = subselect1()$res)

      })

    }

Answer 1

You could try odbc::odbcListDrivers() , I my case I have :

odbc::odbcListDrivers()
         name   attribute                                                   value
1  PostgreSQL Description                                     ODBC for PostgreSQL
2  PostgreSQL      Driver                                   /usr/lib/psqlodbcw.so
3  PostgreSQL       Setup                                /usr/lib/libodbcpsqlS.so
4  PostgreSQL    Driver64                                 /usr/lib64/psqlodbcw.so
5  PostgreSQL     Setup64                              /usr/lib64/libodbcpsqlS.so
6  PostgreSQL   FileUsage                                                       1
7       MySQL Description                                          ODBC for MySQL
8       MySQL      Driver                                  /usr/lib/libmyodbc5.so
9       MySQL       Setup                                  /usr/lib/libodbcmyS.so
10      MySQL    Driver64                                /usr/lib64/libmyodbc5.so
11      MySQL     Setup64                                /usr/lib64/libodbcmyS.so
12      MySQL   FileUsage                                                       1
13    FreeTDS Description                                       FreeTDS for MSSQL
14    FreeTDS      Driver                                /usr/lib64/libtdsodbc.so
15    FreeTDS       Setup                                   /usr/lib64/libtdsS.so
16    FreeTDS    Driver64                                /usr/lib64/libtdsodbc.so
17    FreeTDS     Setup64                                   /usr/lib64/libtdsS.so
18    FreeTDS   FileUsage                                                       1
19    FreeTDS  UsageCount                                                       1
20     MSODBC Description                 Microsoft ODBC Driver 13 for SQL Server
21     MSODBC      Driver /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1
22     MSODBC  UsageCount 

So then setting driver = "MSODBC " should solve the issue !