Microsoft SQL Server

Driver Options

Package Options

There are several that either connect via ODBC but do not provide support for DBI, or offer DBI support but connect via JDBC. The odbc package, in combination with a driver, satisfies both requirements.

Connection Settings

There are five settings needed to make a connection:

  • Driver - See the Drivers section for more information
  • Server - A network path to the database server
  • Database - The name of the database
  • UID - The user’s network ID or server local account
  • PWD - The account’s password
  • Port - Should be set to 1433
con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = "[your driver's name]",
                      Server   = "[your server's path]",
                      Database = "[your database's name]",
                      UID      = rstudioapi::askForPassword("Database user"),
                      PWD      = rstudioapi::askForPassword("Database password"),
                      Port     = 1433)

Known Issues

This section will be updated as new issues are found, and when fixes are applied in the development version of the dbplyr package. The issue will be fully removed when the fix is part of of the package’s version in CRAN.

  • dbCompute() is not able to create a temporary table (Issue 2931)

  • is.na() does not work if used in a filer() command (Issue 2940)

  • Direct support for Temporary Tables is missing. If using copy_to(), the best workaround is to set the TEMPORARY argument to FALSE and prefix the name with pound signs: copy_to(my_table, "##my_table", TEMPORARY = FALSE).

Please refer to the Issues section in dplyr to find out the latest regarding bugs and resolutions.