Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

What's in your CLR? Expand / Collapse
Author
Message
Posted Friday, December 11, 2009 6:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550


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).


____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #832886
Posted Friday, December 11, 2009 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 7:48 AM
Points: 48, Visits: 88
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
Post #832918
Posted Friday, December 11, 2009 7:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:52 AM
Points: 1,568, Visits: 1,905
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.
Post #832927
Posted Friday, December 11, 2009 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 10:02 PM
Points: 152, Visits: 86
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!
Post #832936
Posted Friday, December 11, 2009 7:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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.
Post #832939
Posted Friday, December 11, 2009 7:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #832944
Posted Friday, December 11, 2009 8:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, 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
Post #832964
Posted Friday, December 11, 2009 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 23, 2010 9:31 AM
Points: 6, 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()
Post #832976
Posted Friday, December 11, 2009 8:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 12:15 PM
Points: 33, 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.

Post #832984
Posted Friday, December 11, 2009 8:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:39 AM
Points: 420, Visits: 487
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.usesage.com
Post #832987
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse