Microsoft added the ability to use the .Net CLR for stored procedures in SQL Server 2005. Is anyone using the CLR for this? If so, why? If not, why not?
4 Answers
You'll probably get better answers over on StackOverflow.com. Here is a link to a SO search that will bring up a number of interesting questions related to SQL and the CLR.
That said, we're using it. There are quite a few gotchas that you need to be aware of before deciding to a) enable the CLR and b) choose what you code. From the dba perspective, here is a technet article speaking about CLR Integration security that discusses a lot. Specific topics in the article address:
- CLR Integration Code Access Security
- Host Protection Attributes and CLR Integration Programming
- Links in CLR Integration Security
- Impersonation and CLR Integration Security
- Allowing Partially Trusted Callers
- Application Domains and CLR Integration Security
answered Jun 15, 2009 at 15:52
-
-
Thanks squillman. I was not sure if this would fall under stackoverflow or serverfault. I thought it was more systems, but I can see where it would be better suited for SO. I will look there.– dp.Jun 15, 2009 at 18:51
I used it to create a report which called a stored procedure and wrote out the results to a text file automatically. It basically replaced the 'save as text file' export in sql reporting services...we like to breed lazy users here.
Academically, it sounds better than it is. In a production environment--at least in our case--it quickly became a nightmare. We use a DBGhost-based DB upgrading process, and the CLR was constantly in our way. We now avoid it like the plague.
Don't know if you're still interested in this question, but... Yeah, we've built an entire library of finance, stats, math, engineering and string functions specifically for SQL Server using CLR - a total of 450 individual functions and counting. Our primary motivator is to move the logic from the app tier into the DBMS: reduce network overhead, improve processing speed and work with better data. And of course, to reduce those (in)famous Excel cut/paste catastrophes. There are some cases where you might do the job in T-SQL but the debugging would probably drive you insane. Then there are some other cases where the functions simply are not possible without CLR.
