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]]
steve, i think you can use R to link to sql server directly with RODBC. but it is not wise to dump the whole table from db into R and then do manipulation, which will slow the speed of R. On 2/12/07, Steve Friedman <friedman.steve at gmail.com> wrote:> 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 at 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. >-- WenSui Liu A lousy statistician who happens to know a little programming (http://spaces.msn.com/statcompute/blog)
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 op 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 op stat.math.ethz.ch [mailto:r-help-bounces op stat.math.ethz.ch] Namens Steve Friedman Verzonden: maandag 12 februari 2007 15:15 Aan: r-help op 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 op 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.
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 at stat.math.ethz.ch
[mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of ONKELINX, Thierry
Sent: Monday, February 12, 2007 9:36 AM
To: Steve Friedman; r-help at 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 at 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 at stat.math.ethz.ch
[mailto:r-help-bounces at stat.math.ethz.ch] Namens Steve Friedman
Verzonden: maandag 12 februari 2007 15:15
Aan: r-help at 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 at 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 at 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.