Sql Server CLR file system access from UDF

Question

I wrote a simple UDF that should plot a graphic and save it on disk. Actually, I am using an UDF as a proxy between SQL SERVER and R, so UDF only passes the R script to the R engine from SQL SERVER via DCOM. Everything works fine until I try to plot a graphic or save it to the disk. I created the assembly with UNSAFE permissions.

So, it goes like this: SQL Engine -> UDF -> (D)COM SERVER -> R -> (D)COM SERVER -> UDF -> SQL Engine.

So, my first problem is, can I create GUI from an UDF? I guess not, but it is worth asking.

The Second problem is, why an assembly with UNSAFE permission cannot access the filesystem. I am not receiving any error, just nothing happens.

The R environment is in the different address space so I don't see any reasons why permissions from SQL Engine for CLRs would affect it.

Thanks

Edit:

I tried to do the same thing with procedures. Now an empty file is created. This is my R test code:

jpeg("C:\\test1.jpg"); x <- rnorm(100); hist(x); dev.off()

Any idea what is happening here?

Answer 1

1. You cannot instantiate a GUI from server-side code
2. UNSAFE is dangerous, EXTERNAL_ACCESS would be better as it still allows filesystem access
3. If there is no error, there is a good chance that your code is running correctly but it's doing something different from what you expect it to do; can you add some debugging code or attach a debugger?
4. A procedure is more appropriate here than a UDF because they are much more flexible

But it's not clear why you're doing things this way. It would probably be much easier to write a small (?) program outside SQL Server to get the data from the database, call your R program, and save the image. Server-side code in SQL Server is great for processing data, but very awkward for interacting with filesystems and external resources in general, even when you use CLR code.

Is there any specific reason why you need to do this from within SQL Server?

Answer 2

To access filesystem it is better to use SSIS. You can edit and test package at any time, make logging when you need. Also you can easily add GUI in VisualStudio to this package. Access filesystem from DatabaseEngine is not best practise due possible security issues.