Connecting to MSSQLLocalDB on the Local Machine with RGui

R version 3.4.3 uses a version of the .NET framework which is prior to 4.0.2, therefore the connection must be made using a named pipe.

This post uses SQL Server 2016 and may work with other versions.

To find the instance pipe name of a local database instance, open a CMD window to C:\Program Files\Microsoft SQL Server\130\Tools\Binn\.

The “130” in the path will be different for other versions of SQL Server. Refer to to associate version to server release name. For example, server name, “SQL Server 2016” is version 13.0, therefore the folder name will be ~\Microsoft SQL Server\130\.

Run the command, “sqllocaldb info” to list the local database instances.

C:\Program Files\Microsoft SQL Server\130\Tools\Binn>sqllocaldb info

Run SqlLocalDB again, appending a database name.

C:\Program Files\Microsoft SQL Server\130\Tools\Binn>sqllocaldb info MSSQLLocalDB
Name:               MSSQLLocalDB
Version:            13.1.4001.0
Shared name:
Owner:              DESKTOP-9RKFP0L\chris
Auto-create:        Yes
State:              Running
Last start time:    3/12/2018 6:51:22 PM
Instance pipe name: np:\\.\pipe\LOCALDB#1E089CA3\tsql\query

In RGui, install library RODBC. Make the connection to the database and retrieve data. Note that the backslashes in the connection string are double-backslash or escaped backslash.

> library(RODBC)
> dbconnection <- odbcDriverConnect("Driver=SQL Server;Server=np:\\\\.\\pipe\\LOCALDB#1E089CA3\\tsql\\query;Database=RDatabase;trusted_connection=yes")
> data <- sqlFetch(dbconnection, "dbo.Table1", colnames=FALSE, rows_at_time=1000)
> data
  Id  name value
1  1   One     1
2  2   Two     2
3  3 Three     3
> class(data)
[1] "data.frame"


Also see:

ODBC Connectivity:
The R Project for Statistical Computing:
Visual Studio Downloads:

Leave a Reply

Your email address will not be published. Required fields are marked *