Powershell in SQLCLR?

Question

In the past I've been able to embed a sripting languate (like JScript) inside the SQLCLR, so scripts can be passed as parameters of functions, to perform certain calculations. Here is a simplistic example (the function ssScriptExecute returns a concatenation of all the print's in the script):

select dbo.ssScriptExecute( 'print("Calculation: "+(1+2/3) );' )

-- Calculation: 1.6666666666666665

I'd love to be able to embed a Powershell runtime in the same way. But I've had all sort of problems because the runtime tries to find assemblies by path, and there are no paths inside the SQlCLR. I'm happy to provide more information on the errors I get, but I was wondering if anybody has tried this!

Thanks!

Answer 1

I thought SQLCLR was restricted to just a certain set of assemblies and PS Automation is not one of them.

Answer 2

I think the only way to do this is to create a WCF service hosting powershell, and let SQLCLR send the request dbo.ssScriptExecute(...) to that service for execution. Besides from that, I've also successfully embedded paxScript.net in the SQLCLR (an interpreter that does not have the memory leak problems of the DLR languages).