SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What's in your CLR?


What's in your CLR?

Author
Message
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6638 Visits: 25623
Just some string manipulation functions, such as Levenshtein and InitCap. All of which could be coded in TSQL but are easy to do in C# (InitCap is a one-liner).

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




ross.cecil
ross.cecil
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 91
I have used SQL CLR code in a specific case after a lot of testing of alternatives coded in VB6/ADO, VB.NET/ADO.NET, and TSQL. The objective in each case was to return a 1000 (or so) row recordset where the contents of each record were determined by a metadata table that specified the table, view, utf, or sproc and the column of the data. The metadata table is dynamic and can change frequently.

As each solution was developed it was optimized for performance within its own technology. All solutions returned the same recordset. The CLR coded solution was roughly 1000 times faster than the worst case (VB6/ADO) and 100 times faster than TSQL. Each solution was tested over 100 times on each of three databases and the results combined in order to determine the results.

I put a lot of work into the development, optimization, and testing of the solutions as it was very important to providing good performance to my customers. The CLR solution is used tens of thousands of times a day by hundreds of users.

Like any tool, SLQ CLR solutions can provide the best solution in the appropriate situation. They can be misused as can any tool.

Ross
Ian Massi
Ian Massi
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2873 Visits: 2197
1. Have not written CLR functions yet. This is because I haven't encountered a situation where T-SQL couldn't cut it so I haven't looked into them thus far.

2. N/A

3. N/A

4. If I had some kind of really intricate string manipulation or needed something in the .NET that I couldn't figure out how to do in T-SQL I'd look into it. So far I haven't come across anything like that.
StephenRay
StephenRay
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 87
I've written one clr. It was a work around for being able to call an application on a backend AS400 without having access to Microsoft's OLEDB provider for DB2. That provider is only available with the Enterprise Version of SQL Server and since SQL Server is secondary in our environment, Enterprise was not an option. My clr function uses ODBC to connect to the 400 and execute a single program. The biggest pain of the whole thing was getting the proper permissions set for the stored procedure to actually run. Thank goodness for all the blogs/documentation/help on here and Microsoft to try and solve the errors I received on every step of getting it to work!
kramaswamy
kramaswamy
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4036 Visits: 1832
The stuff that I've used CLRs for are things that, as far as I know, can't be done using the native SQL environment.

In particular, at times I require code which sends a HTTP web request and receives a response. This task needs to be executed on a periodic basis - once per hour to be exact.

In order to accomplish this task, I've found only three solutions. One is to have the web request source executing the task on the periodic basis, and updating the database. This is not an option, since the web request is from a third-party, and I can't easily have that third party modify their application to communicate with mine. Another option is to have a Windows scheduled task which executes the web request and then updates the database. The final option was to have a database job which would execute on a periodic basis. This one proved easiest to manage, and all it required was for me to write a .NET CLR which would execute a HTTPRequest and return back the HTTPResponse string.

The other instance that I've used CLRs is when I needed to move files between servers. I couldn't find a way to do this through the native SQL interface, so I wrote a CLR which would handle moving the files between servers. I have found it possible to do it in native SQL using the xp_cmdshell extended stored procedure, but the limitations for that are that it becomes *very* tricky to handle mapping network drives and giving permissions to the users such that the files can be copied between servers.
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59493 Visits: 9730
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
Jason Miller-476791
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 694
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
DannyS
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 57
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
alastors
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 40
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
Joshua M Perry
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 551
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search