Is anyone making use of the CLR in SQL Server 2005/2008?
772
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?
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
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.
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:
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.