Re: [R] Linking R with Microsoft SQL Server / Client

From: Bos, Roger <roger.bos_at_us.rothschild.com>
Date: Mon 12 Feb 2007 - 15:58:14 GMT


Yes, you can use RODBC to connect to MS SQL. You can query, issue commands, and save to the db from R and it works very well (at least for me).

For example, with a database named xf, create an ODBC data source and then link open a connection in R:
library(RODBC)
xf <- odbcConnect("xf", username, password) Then you can issue commands such as those below:

go <- sqlQuery(xf, "alter table roger_test alter column name varchar(3) NOT NULL")
go <- sqlQuery(xf, "select * from roger_test") sqlSave(xf, val, tablename="roger_test", append=TRUE, rownames=FALSE)

HTH, Roger  

-----Original Message-----
From: r-help-bounces@stat.math.ethz.ch
[mailto:r-help-bounces@stat.math.ethz.ch] On Behalf Of ONKELINX, Thierry Sent: Monday, February 12, 2007 9:36 AM
To: Steve Friedman; r-help@stat.math.ethz.ch Subject: Re: [R] Linking R with Microsoft SQL Server / Client

Dear Steve,

Reading the data shouldn't be a problem with RODBC. I've been doing that plenty of times. I'm not sure about writing, but I suppose you probably will.

Cheers,

Thierry



ir. Thierry Onkelinx

Instituut voor natuur- en bosonderzoek / Reseach Institute for Nature and Forest

Cel biometrie, methodologie en kwaliteitszorg / Section biometrics, methodology and quality assurance

Gaverstraat 4

9500 Geraardsbergen

Belgium

tel. + 32 54/436 185

Thierry.Onkelinx@inbo.be

www.inbo.be  

Do not put your faith in what statistics say until you have carefully considered what they do not say. ~William W. Watt

A statistical analysis, properly conducted, is a delicate dissection of uncertainties, a surgery of suppositions. ~M.J.Moroney

-----Oorspronkelijk bericht-----
Van: r-help-bounces@stat.math.ethz.ch
[mailto:r-help-bounces@stat.math.ethz.ch] Namens Steve Friedman Verzonden: maandag 12 februari 2007 15:15 Aan: r-help@stat.math.ethz.ch
Onderwerp: [R] Linking R with Microsoft SQL Server / Client

Hello

My colleagues and I have recently established a large database (40 tables each with greater than 15 variables) in Microsoft's SQL Server 2000.
Currently we are accessing this database via SQL client running an Windows
XP. Our objectives are many fold including running SQL applications, outputting results to ARC/INFO IMS, production of summarizing tables - graphs and web interfaces for user accessibility.

The project is still very much in a design phase. I'm interested in knowing if we can link R directly to the database as it is either stored in SQL Server, or SQL Client, or if we are better off keeping it simple and extracting ascii (csv) files from SQL server prior to processing summarizing and model development.

Any insight provided will be greatly appreciated.

Steve

--
Steve Friedman
Computational Ecology and Visualization Laboratory Michigan State
University

Envisioning Ecosystem Decisions

	[[alternative HTML version deleted]]


______________________________________________
R-help@stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
______________________________________________
R-help@stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. ********************************************************************** * This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No right to confidential or privileged treatment of this message is waived or lost by any error in transmission. If you have received this message in error, please immediately notify the sender by e-mail, delete the message and all copies from your system and destroy any hard copies. You must not, directly or indirectly, use, disclose, distribute, print or copy any part of this message if you are not the intended recipient.
______________________________________________
R-help@stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Received on Tue Feb 13 03:05:48 2007

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Mon 12 Feb 2007 - 16:30:32 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.