Problem connecting to SQL Server

#1

con <- dbConnect(odbc::odbc(), "MSSQL2", UID = "uid", PWD = "pswd")

seems to connect (no error) but instead of showing tables from my database, it shows: mse, md, SI, tmd, and TsD. I have no idea what those are?!!? The DSN MSSQL2 is working from Python and other apps.

Also tried:
db <- odbcConnect("MSSQL2",uid="uid",pwd="pwd")
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=MSSQL2;UID=uid;PWD=pwd") : [RODBC] ERROR: state �Ӏ, code 20013, message x#|
2: In RODBC::odbcDriverConnect("DSN=MSSQL2;UID=uid;PWD=pwd") : [RODBC] ERROR: state �Ӏ, code 0, message x#|
3: In RODBC::odbcDriverConnect("DSN=MSSQL2;UID=uis;PWD=pwd") :
ODBC connection failed

What am I missing?

#2

What command are you using to look at tables in your database?

#3

dbListTables(con) which gives a list of 999 tables (and 51 more beyond the print limit). That is after issuing:
con <- dbConnect(odbc::odbc(), "MSSQL2", UID = "sa", PWD = "pwd",database="dbname")

If instead I use:

odbcConnect("MSSQL2",uid="sa",pwd="pwd") I get:

[1] -1
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=MSSQL2;UID=sa;PWD=pwd") : [RODBC] ERROR: state ��, code 20013, message
2: In RODBC::odbcDriverConnect("DSN=MSSQL2;UID=sa;PWD=pwd") : [RODBC] ERROR: state ��, code 0, message
3: In RODBC::odbcDriverConnect("DSN=MSSQL2;UID=sa;PWD=pwd") :
ODBC connection failed

And this:

library(RODBC)

SEIS <- odbcConnect('driver={SQL Server};server=tcp:192.168.3.10,1433;database=dbname;trusted_connection=true')

There were 50 or more warnings (use warnings() to see the first 50)

warnings()
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=driver={SQL Server};server=tcp:192.168.3.10,1433;database=SEIS;trusted_connection=true") :
[RODBC] ERROR: state IM002, code 26252160, message [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

Only the first attempted connection seems to returns without error but the tables it shows are not from my db.
MSSQL2 is the DSN that I use from other programs and it works fine.

#4

Try dbListTables(con, schema = "dbo")

I would guess you're reading tables from the master schema.

#5

The plot thickens. If I execute the command dbListTables(con, schema = "dbo"), I get a result with 517 table names which I believe are a proper subset of the 1050 names that result from dbListTables(con). However, none of these are the tables that are part of my db. So weird!

#6

I finally got R to connect using the freetds driver for MS SQL.