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:
When she runs sql queries within an Rmarkdown file, they execute fine.
What could be the problem?
cc @edgararuiz
Hey @taras, 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
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 @taras 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.
we'd probably play sports or interact with our families or something ridiculous like that...
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.
Could you explain the reason behind removing odbc and DBI packages?
I'm still fighting my setup 
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:
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:
odbc_config –-cflags .DSQL_WCHART_CONVERT ” flag, then set DriverManagerEncoding to UTF-32 .DriverManagerEncoding to UTF-16
Thank you @nathan! It solved the problem!
I missed this solution in the troubleshoot FAQ, but I'm thankful you showed it to me!
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
Hm... I was pretty sure I did that right away!... (Both liked and marked as solution) Oh well, I guess I'm tripping.
It's fixed now!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.