Connection Pane - SQL Server connection only shows dbo and system schemas. All other schemas are not displayed.

[abstrarie]

Hello. My company recently hired a data scientist and he was complaining that when he connects to our SQL Server environment that he can't see any tables in the connection pane that are in schemas besides dbo, INFORMATION, or sys. After installing the latest version of RStudio on my own machine, I am having the exact same problem. This is a major issue for us as I use different schema names than dbo for almost all of the tables I make/he needs to access. Here is the connection string I used:

con <- dbConnect(odbc::odbc(), .connection_string = "Driver={SQL Server};server={myservername};trusted_connection={true}", timeout = 10)

And it connects just fine, but the Connection Pane just shows this:

That database has 6 other schemas in it. To make matters worse, if I try to run SQL queries through R Studio on schemas besides dbo, I get this error: "missing value where TRUE/FALSE needed". That is from a simple select top 10 *. If I run the same query on a different table in the dbo schema, it works fine.

We are using SQL Server 2017 Standard Edition and permissions aren't an issue as I am the sysadmin on the db.

I feel like I am missing something obvious and that this question has been answered before, but all my googling has gotten me nowhere. Can anyone give me some direction on how to fix this?

[abstrarie]

I figured out the issue. The problem was with the ODBC connection itself, nothing with RStudio. I had to install the newest ODBC driver MS had available (2017) and when specifying my connection in the ODBC Connection Manager I had to choose a default database. Once I did those 2 things all the schemas started showing up. If you don't choose a default database, then only the system schemas show up.

I don't know if you want to leave this topic up for future reference, or if I should just delete it.

[kevinushey]

Glad to hear you were able to find out what was going on!

I think it's worth leaving this up, just in case anybody else in the future happens to have the same problem -- they might be able to stumble upon your solution.