CODEWITHSUNDEEP
sqlrsql-server-2008connection-stringrodbc
user1963962
user1963962

Reputation:

getting the connection string for MS SQL Server 2008

I am new to SQL and not too sure how to connect R to an SQL database.

I would like to use R to connect to the database, but am unsure what the string is to be able to do this.

The database is not local and is on a server.

I am open to all CRAN libraries but am currently using RODBC and have got as far as knowing I will probably need to use the following command(s).

library(RODBC)
channel <- odbcDriverConnect("")

OR 

library(RODBC)
channel <- odbcDriverConnect("some connection string i need")

OR

library(RODBC)
channel <- odbcDriverConnect('driver=#not sure where to find this;server=#not sure where to find this either;database=#i think this would be the path to the database#;trusted_connection=true')

The first method opens up a user interface when using it an R Gui, but cannot find the specific table as the databases are not stored locally and are on a server...

I have access to the databases currently using Microsoft SQL Server Management Studio (2008), and feel that I probably will be able to find out the require connection string to be able to access all the databases I need, so I guess my question is where in Microsoft SQL Server Management Studio do I need to go in order to create/build/write the correct connection string to access the relevant databases.

Any help would be greatly appreciated, and sorry if this is such a basic question.

Thanks in advance.

EDIT

For some additional information this is the component name and version information

Microsoft SQL Server Management Studio                      10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)                     2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML                     2.6 3.0 4.0 5.0 6.0 
Microsoft Internet Explorer                     8.0.6001.18702
Microsoft .NET Framework                        2.0.50727.3623
Operating System                        5.1.2600

EDIT 2

I also get the following output when typing the following command

> odbcDataSources()
                                      MS Access Database                                              Excel Files 
          "Microsoft Access Driver (*.mdb, *.accdb)" "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" 
                                         dBASE Files 
   "Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)"

Upvotes: 3

Views: 4337

Answers (1)

Dieter Menne
Dieter Menne

Reputation: 10215

Here is my general method (because I also always forget how to do this)

  • Create an empty file, e.g. with Notepad, with extension .udl, e.g SQLS.udl.
  • Double-click on it; a dialog should open, where you can select provider etc, and can test the connection.
  • After Ok, open the file and use the provider line; it mostly works.

Upvotes: 2

Related Questions