This project is read-only.

Inside SQLCLR

Apr 8, 2011 at 1:26 PM

I'm trying to embed R.NET into SQLCLR (the .net framework inside SQL Server 2008).

SQLCLR only hosts .NET 2... is there a version of this library for version 2 of the framework?

Also, inside SQLCLR there are no such things as directories... is there an alternative to setting the path for R.dll? (may be embed it somehow).

Apr 16, 2011 at 3:40 PM
Edited Apr 16, 2011 at 3:40 PM

R.NET is developed for .NET Framework 4, and there is no version for .NET Framework 2. I haven't tried but it's probably not so hard to port to .NET 2. Please try it by yourself.

I don't know about SQLCLR, so I cannot help you about it.

Jun 6, 2011 at 1:11 AM
nsulikow wrote:

I'm trying to embed R.NET into SQLCLR (the .net framework inside SQL Server 2008).

SQLCLR only hosts .NET 2... is there a version of this library for version 2 of the framework?

Also, inside SQLCLR there are no such things as directories... is there an alternative to setting the path for R.dll? (may be embed it somehow).

Check this forum out - http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/e1af7c75-b43f-472f-80b8-b98be270b594

Read down a little and you'll see where someone references .NET 4.0 frameworks in the SQLCLR.

(SQL2008R2)

Aug 21, 2015 at 4:23 AM
Did anyone come up with anything on this front? I'm attempting to use R.Net in SQL CLR and I'm not getting very far. I don't have the .Net version issue but when I try to run the following code, my query in Sql Server just hangs....
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorldFromR()
    {
        REngine.SetEnvironmentVariables();

        // There are several options to initialize the engine, but by default the following suffice:
        REngine engine = REngine.GetInstance();

        // Direct parsing from R script.
        GenericVector S = engine.Evaluate("S <- cat('Hello World!')").AsList();
        string s = S.AsCharacter().First().ToString();

        SqlContext.Pipe.Send(s);

        // you should always dispose of the REngine properly.
        // After disposing of the engine, you cannot reinitialize nor reuse it
        engine.Dispose();

    }
Aug 23, 2015 at 12:21 AM
You are sending either a null or an empty string to your Sql Pipe. Not sure whether this in intended, but just look at what R is doing even without R.NET with:
str(as.list(cat('Hello World!')))
str(as.list(s <- cat('Hello World!')))
str(s <- cat('Hello World!'))
as.character(s <- cat('Hello World!'))
If you call HelloWorldFromR several times, you should not call engine.Dispose more than once. As I recall, there should be an exception that state pretty clearly that the engine has been reinitialized/disposed more than once in the same process lifetime, something R cannot do.

Not enough information to figure out whether this explains why things "hang".
Aug 27, 2015 at 2:07 AM
I've implemented the HellowWorldFromR() method as well as several others but after I execute one from SQL Server one time and attempt a second execution, I get this error, presumably because I call Dispose():

Msg 6522, Level 16, State 2, Line 32
A .NET Framework error occurred during execution of user-defined routine or aggregate "OneSidedHypothesis":
System.InvalidOperationException: The single REngine instance has already been disposed of (i.e. shut down). Multiple engine restart is not possible.
System.InvalidOperationException:
at RDotNet.REngine.GetInstance(String dll, Boolean initialize, StartupParameter parameter, ICharacterDevice device)
at ProviderAnalysisCLR.OneSidedHypothesis(SqlDecimal PriorAverage, SqlDecimal Tau, SqlDecimal ProvMean, SqlDecimal ProvStdDev, SqlDecimal N)

How am I supposed to get around this? Clearly, the engine should be disposed and when I run this as a standard function from a Console app it works fine with repeated executions.
Aug 27, 2015 at 2:42 PM
gksmithlcw wrote:
I've implemented the HellowWorldFromR() method as well as several others but after I execute one from SQL Server one time and attempt a second execution, I get this error, presumably because I call Dispose():

Msg 6522, Level 16, State 2, Line 32
A .NET Framework error occurred during execution of user-defined routine or aggregate "OneSidedHypothesis":
System.InvalidOperationException: The single REngine instance has already been disposed of (i.e. shut down). Multiple engine restart is not possible.
System.InvalidOperationException:
at RDotNet.REngine.GetInstance(String dll, Boolean initialize, StartupParameter parameter, ICharacterDevice device)
at ProviderAnalysisCLR.OneSidedHypothesis(SqlDecimal PriorAverage, SqlDecimal Tau, SqlDecimal ProvMean, SqlDecimal ProvStdDev, SqlDecimal N)

How am I supposed to get around this? Clearly, the engine should be disposed and when I run this as a standard function from a Console app it works fine with repeated executions.
Update:

I got it to run properly in SQL Server by omitting the call to Dispose(). However, now I'm running up against the library apparently crashing after running for several minutes. Any thoughts?

Thanks!
Aug 29, 2015 at 4:17 AM
You'll need to provide more information, if possible of course.

Why do you state "apparently crashing"?. If you have a stack trace or error message to report, please do so.