Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2008-08-11 : 16:22:28
|
| I have done quite a bit of C# in the past, and I like the idea of using CLR integration.But is anyone actually using CLR Intergration on production servers?Greg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2008-08-11 : 17:58:32
|
| Hmmm...intesting - I am glad to hear that. I think it has great potential.What kind of DBA tasks are you addressing? I hadn't thought of it in that compacity.Greg |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
benjaminmoskovits
Starting Member
1 Post |
Posted - 2008-08-11 : 18:23:38
|
| Hi,The reason its very rarely used is because programmers don't know how easy it is to create a CLR function and DBA's don't want to be responsible for something they are not comfortable doing. If its Tsql or a proc most dbas can (or should be able to) figure it out and will accept a package given to them that contains the proc. If its CLR code they believe (with some justification) that they will have to maintain something they have not been trained in or are familiar with.Its a shame because CLR routines are often (when doing calculations not selects) faster by a factor of ten to 100 over Tsql. By the way to get MS SQL Server DBA, or developer, certification you need to know something about CLRs. Microsoft has really thought it out well (security and creating a CLR routine) but the data types are not equivalent. One of the nice things about the .net is that a type is the same thing in any .net language but the correspondence between SQL server types and .net types are not exact (see this article for more on this (http://msdn.microsoft.com/en-us/library/ms131092.aspx). Again there are very good examples as to how to deal with this but it requires a bit of work to this out.BenjaminMoskovits@gmail.com |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2008-10-02 : 10:42:01
|
| It's some time since the original post, but here are my two cents.CLR is da bomb. One of the things I do (on production) is using templates and replace Tags in these templates with db values. This can be done in the GUI, but emails and letters are send at night as well, so it had te be done without and active GUI. A windows Service could do the same, but using CLR is a lot easier. Say I have a text "Hello ##FullName##, Your order id = ##OrderID## and your sales person is called ##SalesPersonFullName##"I have a tables with orders and customers.SELECT * FROM Orders o JOIN Customers C ON c.CustomerId = o.CustomerId WHERE OrderId = @OrderIdThe output of the select statement will replace the columnname tags in the template with the column value like:[code]For Each _dtc As DataColumn In _dt.Columns Content = Replace(Content "##" & _dtc.ColumnName.ToString & "##", _dt.Rows(0).Item(_dtc.ColumnName.ToString).ToString)Next[\code]This is hard to do in T-SQL, but easy in CLR.Another example is sending mail.I have a table: EmailsThe CLR procudure does a select on all emails where send is null. Then for each table records it prepares an email (and does a replacemt as decripted abouve) and send it. Easy does it!Henri~~~~There's no place like 127.0.0.1 |
|
|
|
mct
Starting Member
4 Posts |
Posted - 2008-10-31 : 19:20:31
|
| Gregie,I have used CLR integration for string manipulation and regex find / replace quite extensively. Native T-SQL does not have RegEx functions as far as I know. Plus string manipulation in T-SQL would not be as efficient as if done in .Net code.-Shiva[url]mycodetrip.com[/url] |
|
|
|
|
|
|
|
|
Does anyone actually use CLR integration?