What's in your CLR?

Question

What's in your CLR?

Viewing 15 posts - 16 through 30 (of 54 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I've got one CLR procedure in use, which checks drive space available on a network share before running backups. Pretty much the same functionality as xp_fixeddrives, but works on network UNC paths.

In a prior job, there was a whole set of procedures that accessed an external DLL for mailing address validation and mail list sorting. They were written using the OLE automation extended procs, but would have been much more efficient if converted to CLR. If I still worked there, I'd have converted them by now.

I've got another that I'm working on where I would normally use SSIS, but the manager here doesn't like SSIS. It will access web services, so I'll do that part in CLR.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jason Miller-476791 SSCommitted Points: 1520 More actions · Answer 2

We have used 1 CLR total. It simply looks up user data in our AD for some custom permission stuff.

That being said, I'm moving to a new home/project/environment. It may very well require the use of CLR. This is an analytical environment that currently sits in MySQL, but there is a desire to investigate SQL2008 for performance and scalability... SQL2005 wasn't an option because time didn't resolve finely enough. It is the largest db I've ever encountered.

This is going to be very interesting.

Honor Super Omnia-
Jason Miller

DannyS SSC Veteran Points: 231 More actions · Answer 3

Newb here, but saw CLR poll.

I've used CLR a bit for a database backed website. Used lots of simple function usually for text processing for example,

a function to determine if user input is a 5 digit number (zip code) or a valid email address (one line of regex). these functions are used to prevent sql injection.

Also, custom output of results. We produce files in a number of formats and have found CLR convienient in meeting clients format requirements.

Also Comm functions FTP, Email and file operations DoesFileExist() DeleteFile() CompressFile()

alastors SSC Enthusiast Points: 157 More actions · Answer 4

1. What's in YOUR CLR?

A UDF that sends a UDP Packet to a phone (displayed by my J2me App), called by a normal SQL insert Trigger to take advantage of set based logic.

2. If you have written CLR's, why did you write them (or it)?

AFAIK you can't send UDP packets from T-SQL

it was more performant as a CLR_UDF + SQL_Trigger than polling, plus I dont like polling.

3. Looking back at it, was it an appropriate thing to do?

Absolutly, received good comments about the performance from the client, made me look good to the boss

4. what would you consider a CLR to be appropriate for?

The problem about repacing multipe spaces with just one space that was mailed recently in here, didn't like the solucion using set based logic, because it reads the string more than one time.

Joshua M Perry SSCrazy Points: 2675 More actions · Answer 5

I used it to replace xp_getfiledetails. This allowed several developers relying on the stored procedure to just do a find and replace on the stored procedure name in their code to be able to move from 2000 to 2005.

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

namespace Enterprise.SqlServer.Server

{

public partial class GetFileDetails

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void csp_getfiledetails(string filePath)

{

try

{

FileInfo fileProperties = new FileInfo(filePath);

SqlMetaData colAlternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);

SqlMetaData colSize = new SqlMetaData("Size", SqlDbType.BigInt);

SqlMetaData colCreationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);

SqlMetaData colCreationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);

SqlMetaData colLastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);

SqlMetaData colLastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);

SqlMetaData colLastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);

SqlMetaData colLastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);

SqlMetaData colAttributes = new SqlMetaData("Attributes", SqlDbType.Int);

SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {

colAlternateName,

colSize,

colCreationDate,

colCreationTime,

colLastWrittenDate,

colLastWrittenTime,

colLastAccessedDate,

colLastAccessedTime,

colAttributes});

record.SetInt64(1, fileProperties.Length);

record.SetString(2, fileProperties.CreationTime.ToString("yyyyMMdd"));

record.SetString(3, fileProperties.CreationTime.ToString("HHmmss"));

record.SetString(4, fileProperties.LastWriteTime.ToString("yyyyMMdd"));

record.SetString(5, fileProperties.LastWriteTime.ToString("HHmmss"));

record.SetString(6, fileProperties.LastAccessTime.ToString("yyyyMMdd"));

record.SetString(7, fileProperties.LastAccessTime.ToString("HHmmss"));

char[] splitter = { ',' };

string[] attributes = fileProperties.Attributes.ToString().Split(splitter);

int attributesInt = 0;

foreach (string attributesString in attributes)

{

FileAttributes fileAttributes = (FileAttributes)Enum.Parse(typeof(FileAttributes), attributesString);

attributesint += (int)fileAttributes;

}

record.SetInt32(8, attributesInt);

record.SetInt32(8, (int)fileProperties.Attributes);

SqlContext.Pipe.Send(record);

}

catch (Exception myexception)

{

throw (myexception);

}

};

}

Joshua Perry
http://www.greenarrow.net

r5d4 SSCrazy Points: 2519 More actions · Answer 6

I've successfully used VB.NET CLR functions to replace TSQL Scalar functions.

2 instances where it works well are >

1) Conversion of String IP Address data to numeric form (and back)

2) Extraction of Search Terms from Search Engine Http Referrer Urls.

I found the CLR string manipulation efforts for the latter outperformed the TSQL function 10 fold.

r

Miles Neale SSChampion Points: 13147 More actions · Answer 7

1. have used CLR once for a basic function I wanted on the database side.

2.Why I did it, probably more like a test drive than anything else.

3. It was fine.

4. Have been looking at it as a customization for running reports based on certain conditions that an update or insert may have caused, may use it for customized logging against an asycn web service, in one area I may use it to cause a copy of an image I am storing to be archived on Sharepoint. These are the type of things I see as advantageous but have not had time to research them to see how far I can get with CLR.

Not all gray hairs are Dinosaurs!

j.a.c SSCrazy Points: 2283 More actions · Answer 8

We use it to send syslog messages instead of database mail.

Bill Wunder Old Hand Points: 317 More actions · Answer 9

I use CLR to create a node type that models the SMO object model hierarchy. My application has been in use since before the vendor supplied assembly with the hierarchyid, actually since before the SQL 2005 RTM... I probably could have made the hierarchyid type work but with the one I coded I also get a tree representation. The advantages of the node as a CLR type are that I get a single compact, index-able numeric value that is a pkey and can also used to index based only on mid level nodes of the tree - if it helps my queries - yet never have to parse a string, recurse a parent-child hierarchy or mess with constructing/deconstructing a bundle of character columns into the node when I move the hierarchy between the database and the application tree views. I also get the ordering and DML operation advantages of the hiererchyid. My storage efficiency is not as good as the hierarchyid, in part because when I started the project I was stuck with the 8000 byte requirement that has since been improved considerably, but it kept my eye off the storage ball early on and have just not had a reason to revisit yet.

I have also used the CLR for proxied IPC communications between database instances to mange SQLTrace rather than having to resort to the command-line, OLEDB or IS. Makes it easy to tell if something actually worked on the other end or if not, what the error was when trying to manage many instances with out rolling a bunch of cookie cutter stored procedures out to every target and then trying to keep them in sync. In this usage only have to enable the CLR on the instance that the DBAs use to manage the environment. Thinking about developers using the CLR too much will cost me the other two hours of sleep so I try not to go there...

Bill Wunder

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 10

Custom aggregators...

bit-wise aggregation; OR, AND, XOR...

string aggregation; deduped concatenation, longest string, shortest string, longest left/right most match,...

intelligent date/time aggregation

XML aggregation

...

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 11

I think that the CLR is great for doing things not natively available in T-SQL, such as RegEx, File System, and Internet related functions (just to name a few). Also, sometimes it nice just to be able to encapsulate some algorithms that maybe can be done in straight T-SQL but T-SQL is more for set-based operations than doing some computations or string manipulation.

I have been working on a CLR project for 3 years now and it has gotten a much better response than one would expect given some of the negativity towards using CLR at all. The project -- SQL# (SQLsharp) -- is just a library of some common and some less common functions. Some were just interesting to produce even if there is little practical application and some have certainly made my life easier. Unfortunately with over 150 functions I am not sure which ones are used more than others by those who download it, but personally I have used the File System functions to export data, GZip files, copy files between servers, etc. There are plenty of back-end operations that are not set-based and are called by SQL Agent where being able to accomplish more within T-SQL makes us DB folks less reliant upon application developers who might not have time to help us with certain projects. Some will argue that some of these operations should still be done by business logic outside of the DB, but we all have jobs to do and we have to use what means are available to us. For those who are most comfortable with T-SQL, doing many of these functions simply allows people to get their job done in a language that is familiar, can be easily maintained, and doesn't require bringing on additional people (which can mean getting the job done now instead taking an additional 2 months).

Also, creating a User-Defined Aggregate to get a Median has helped.

Lastly, the most unexpected positive response has been the desire to access Twitter via SQL Server. Originally I incorporated that functionality because it seemed interesting (even if I was unsure as to how useful it was) but the number of downloads I am getting has increased dramatically since adding the Twitter functions.

So, I think that CLR functionality has been very helpful for many people, it is just that they are not being very vocal about it.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

-- An XOR Aggregate function

-- Several text parsers

-- some CLR functions for pipeline summation

-- CLR proc to send message/records from SQL Server Service Broker to Berkley databases

I thinks theres a couple more ...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

JacekO SSCertifiable Points: 6610 More actions · Answer 13

1. To send MSMQ messages (originally it was a SQL2000 extended SP written in C)

2. SQL Server could't do it natively.

3. Yes

4. Accessing resources not available directly to SQL Server. The problem with those - in most cases you have to access namespaces not 'secure' form SQL Server point of view.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Gift Peddie SSC Guru Points: 73570 More actions · Answer 14

I have not used CLR but if I am working with XML I think CLR will be better solution to working with XML because .NET makes working with XML very easy. I also think CLR makes some distributed transaction operations with Oracle redundant, if you are running Oracle 10g and 11g.

http://msdn.microsoft.com/en-us/library/ms403279.aspx

Kind regards,
Gift Peddie

michael.davies-650746 Newbie Points: 7 More actions · Answer 15

I read this every day even though I am not a DBA. It nearly always makes sense to me. However in this case I am at a loss as to what a CLR is. I'm sure it is obvious and I will slap my head when I eventually find out, but if you could get contributors just define the jargon once in the article (since it is used 8 or 9 times) that would make it just that little bit more relevant to people like me.

P.S. it is common language runtime for others like myself who don't already know it. (I am not slapping my head because it isn't obvious to me - never heard of it)

thanks,