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

[taras]

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

[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

[jdlong]

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.

[taras]

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

[jdlong]

@taras

We'd be bored out of our minds if we didn't have these struggles.

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

[taras]

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.

[taras]

@jdlong

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.

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

[nathan]

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

[taras]

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

[mara]

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.

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …

Thanks

[taras]

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!