0

I'm new to connecting to databases hosted in SQL Server.

The following code works:

library(DBI)
con <- dbConnect(odbc::odbc(), .connection_string = "Driver={SQL Server};", 
                 user = rstudioapi::askForPassword("Your computer usename e.g. SmithJo"), 
                 password = rstudioapi::askForPassword("Your computer password (e.g. to login to Windows)"), 
                 port = 1433, 
                server = "myserver", 
                dbname = "ABC")

However, the connections pane then shows a number of databases: enter image description here

When I use the command dbListTables(con), are the tables listed JUST from database ABC?

Nova
  • 5,423
  • 2
  • 42
  • 62
  • SQL Server returns a LOT of crap^Htables and views and meta-things and such when you list the tables. I wish I knew how to programmatically cut out the system objects. An approximation might be to use [this code](https://stackoverflow.com/a/18298685/3358272) (or related), as I find its output is tables (and columns) only. – r2evans Sep 06 '18 at 16:22
  • I'm pretty new to this so if you had any idea on how to translate that to R...? – Nova Sep 06 '18 at 16:24
  • @r2evans ... if you think SQL Server is bad, try Oracle! I use `options(connectionObserver = NULL)` at top of script to avoid those other items. – Parfait Sep 06 '18 at 16:25
  • No thanks, I'm having enough trouble dealing with MS's implementation of ODBC ... their own protocol, nonetheless, which [requires the user to know the underlying storage of some columns](https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/getting-long-data?view=sql-server-2017). The fact that [`nanodbc`](http://github.com/nanodbc/nanodbc/) and [`odbc`](https://github.com/r-dbi/odbc) punt on this flaw makes my deailng with sqlserver in one thread and postgres in another to be ... a nightmare. (RIP, [`RSQLServer`](https://github.com/imanuelcostigan/RSQLServer), you are missed.) – r2evans Sep 06 '18 at 16:44
  • 1
    @Nova ... err, `DBI::dbGetQuery(con, "select * from INFORMATION_SCHEMA.COLUMNS")`? Or if you need just table names (as you would get with `dbListTables`), then `DBI::dbGetQuery(con, "select table_name from INFORMATION_SCHEMA.COLUMNS")` – r2evans Sep 06 '18 at 16:46
  • Now we are getting somewhere... that is helpful. Still having trouble connecting to the lookup tables though... – Nova Sep 06 '18 at 16:53
  • Re-reading your post, what is your question? Connect across databases? Across schemas? *are the tables listed JUST from database ABC?* ... aren't you able to answer that question? – Parfait Sep 06 '18 at 18:31
  • Thanks Parfait, you're right - I should just ask one question per post. I've edited my post. I'm still not sure that the tables listed are from just the ABC table, it seems like yes? r2evans helped me access the "meaningful" tables with their comment, though. – Nova Sep 06 '18 at 18:57
  • The new question is at https://stackoverflow.com/questions/52209203/dbreadtable-error-in-r-invalid-object-name. @r2evans... any ideas? – Nova Sep 06 '18 at 20:16
  • To address *this* question, I believe `dbListTables(con)` is specific to the database connection provided (`con`); other databases, other schemas, etc, need to be queried specifically, I believe. (Caveat: I'm really only familiar with changing specific databases on the same server or changing servers completely, not much about other terminology/structures, clustered dbs, etc.) – r2evans Sep 06 '18 at 21:41
  • Thanks! I just wanted to make sure I was getting the right information. That's... that's a lot of tables. – Nova Sep 07 '18 at 02:42

0 Answers0