R and SQL Server 2008

Is 开发者_C百科there a way to use R power in SQL Server 2008?, I mean use in console or communicate both of them.

If it is What would be the procedure?

SQL Server Data Within R:

An example using SQL Server data within R is here. This blog post deals with plotting data from a SQL server table using K-Means Clustering.

Calling R within SQL Server:

You can use the extended Stored Procedure XP_CmdShell to call R (or any command line tool as Nick points out) within SQL Server.

Enable Functionality In Server Level:

XP_CmdShell exposes the cmd shell within SQL Server. For obvious reasons, this is not enabled by default. A SysAdmin (or someone with Control Server permission) can enable this functionality in server level.

Enable a specific User to Access this functionality:

 exec ON xp_cmdshell TO *YourLoginHere*

Refer this URL for more info on best practices (such as using a proxy account for non-SysAdmin users) and example usage.

You want to use the RODBC package: http://cran.r-project.org/web/packages/RODBC/.

Vignette is here.

I think RODBC does the reverse of what you ask (I think you want to call R from within SQL Server). However, from stored procedures in SQL Server, and with the correct security settings (you probably want to talk to a highly skilled sysadmin here), you can run any command line tool, including R. I remember there being a system stored procedure for that (but forgot its name since it's been a while).

You could pass a script (maybe even a scripted script) to R, or pass it the right information to connect to the SQL Server again via RODBC, where it could find a table with statements to execute or data to use for analysis.

That should give you enough power to communicate both ways...