"Connections" pane shows weird results when connecting to a server

database
odbc
sql
#1

So, a weird thing is happening to my colleague. We have a similar setup, but once she gets connected to one of our SQL Servers, she doesn't see our databases or tables. She sees this:

35
Screen Shot 2018-06-15 at 12.00.35.jpg1310x1338 134 KB

16
Screen Shot 2018-06-15 at 12.00.16.jpg1314x1290 127 KB

When she runs sql queries within an Rmarkdown file, they execute fine.
What could be the problem?

cc @edgararuiz

#2

Hey @taraas, I believe this is either an issue with encoding, or mismatched R package versions. Can you share with me the function call used to connect to the database, and also the sessionInfo()?

Thanks

#3

FWIW I had this issue yesterday on my Mac. I could connect but I got these same weird one letter tables. Looked exactly like what @taraas shows above.

My situation ended up being conflicting installs of unixODBC and some interplay between the database access packages (DBI, and odbc). I had to remove one of my conflicting unixODBC installs (Homebrew in my case). Which left me with a MacPorts install of unixODBC that seemed to be working. Then I had to remove DBI, and odbc form R.. and iir I blew up tidyverse too, although may not have needed to. Then I restarted my R session and rebuilt DBI and odbc packages. I was then, after a reboot, finally able to get stable access to my sqlserver.

I suspect, but cannot prove, that I had built different database access packages under differing configurations of unixODBC.

you mileage will vary...

I'm looking forward to when R Studio just ships with its own unixODBC stack.

#4

What a time to be alive!
We'd be bored out of our minds if we didn't have these struggles.

#5

we'd probably play sports or interact with our families or something ridiculous like that...

#6

I now suffer from the same problem and will probably have to follow JD's process to troubleshoot. Will let y'all know once I come out alive on the other side.

#7

Could you explain the reason behind removing odbc and DBI packages?
I'm still fighting my setup :frowning:

#8

As @edgararuiz mentioned, the encoding for your driver must match the encoding for your driver manager. For help, see the bottom of the page on troubleshooting connections:

Why does my database catalog only show the first letter of each table?

The character encoding in the driver manager (UnixODBC) likely does not match the character encoding in the driver. Change this file /etc/rstudio-drivers/amazonredshift/bin/lib/rstudio.amazonredshiftodbc.ini so that DriverManagerEncoding=UTF-32 . This assumes that the driver manager is using UTF-32 encoding. You can check the character encoding in the driver manager by with instructions from this page. Here are the detailed instructions.

  • If you are unsure which driver manager you are using, or where the driver manager is installed, contact your system administrator or see Driver Managers for more information.

  • If you are using the unixODBC ODBC driver manager, check which setting to use:

    • At a command prompt, type odbc_config –-cflags .
    • If you see the “ DSQL_WCHART_CONVERT ” flag, then set DriverManagerEncoding to UTF-32 .
    • Otherwise, set DriverManagerEncoding to UTF-16
#9

Thank you @nathan! It solved the problem!
I missed this solution in the troubleshoot FAQ, but I'm thankful you showed it to me!

#10

If your question's been answered (even if by you), would you mind choosing a solution? (See FAQ below for how).

Having questions checked as resolved makes it a bit easier to navigate the site visually and see which threads still need help.

Thanks