R ODBC nanodbc error when not using DSN

Question

Running into an issue between using the DSN versus non DSN with the R package ODBC.

Using the DSN, I am successfully able to make a connection to the server and away the code goes. However, using the non DSN odbc connection string, I am receiving:

Error: nanodbc/nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Data >source name not found and no default driver specified

The connection appears to be syntactically correct and all of the required fields are populated according to multiple function documentations, including: https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-

odbc_con <- dbConnect(odbc::odbc(),
                  Driver = "SQLServer",
                  Server = server,
                  Database = test, 
                  Uid = 'username',
                  Pwd = 'password',
                  Port = 1433)

Both server and test are defined earlier in the code. I have tried removing the odbc:: and just using odbc(). Using semi-colons, removing caps.

Bit stuck, any suggestions?

Solution

When I switched from a Linux to a Windows box for R (note nothing else changed) I was getting

"Error: nanodbc/nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" 

The ODBC connection issue was fixed by changing

Driver = "ODBC Driver 17 for SQL Server"

to

Driver = "SQL Server"

Hope this saves you some time.

Comments

[Parfait]

What is your OS? Linux/Windows? If Linux, check if odbc.ini for DSN and odbcinst.ini for driver are aligned correctly assuming unixODBC is used. In Windows, check DSN file (open in Notepad) usually saved in Documents folder as My Data Sources to see its driver/server/user credentials. Also check odbcad32.exe (type in search) for SQL Server driver info. Usually, there is space in driver name.

[Brighter side]

Has I understand your error: To connect to a database it is required to provide a DSN which will allow the Driver manager to know which driver it is supposed to use. Thus you should specify one and if not it will use the default driver (which is here not set).

[Jabberwockey]

Parfait, you were spot on. There was a space in the Driver name. Great catch!

[Tonio Liebrand]

Maybe it helps someone: I got a similar error and in my case the driver was deleted.