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 https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Server 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
MSSQLLocalDB
ProjectsV13

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"

References:

Also see:

ODBC Connectivity: https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf
The R Project for Statistical Computing: https://www.r-project.org/
Visual Studio Downloads: https://www.visualstudio.com/downloads/

Leave a Reply

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