March 21, 2011 at 12:28 pm
What, if any, CLR procs and procedures do you all have and actually use?
in my limited experience, I've really only used CLR for three things:
played with Regular Expressions.
Converting RTF to raw text.
Downloading web site html for further data mining.
Are there any in your bank of snippets you think are CLR "Must Haves"?
Lowell
March 21, 2011 at 1:11 pm
Two that I use alot around here:
convert a string based IP address into a 64 bit integer.
encryption & decryption & key management functions.
The probability of survival is inversely proportional to the angle of arrival.
March 21, 2011 at 2:17 pm
Actually used in production:
- proc: access web services
- proc: accessing third-party address standardization software
- scalar function: use assemblies installed in local GAC containing proprietary encryption methods
- data type: IP address
Toying around with:
- proc or table-valued function: process XML document containing inline schema to produce resultsets where shape is dynamic
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2011 at 2:29 pm
I used CLR stored procedure to get Mount points/Disk Space information.
March 22, 2011 at 2:46 pm
Calling third-party DLLs (address list validation and presorting)
Disk data (space available, used, etc.) on network resources
Disk operations (file archiving and compression, file encryption)
Running totals
SFTP upload/download (SSIS won't do SFTP)
- 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
March 22, 2011 at 2:58 pm
GSquared (3/22/2011)
Calling third-party DLLs (address list validation and presorting)Disk data (space available, used, etc.) on network resources
Disk operations (file archiving and compression, file encryption)
Running totals
SFTP upload/download (SSIS won't do SFTP)
Are any of those ones that you have built and can/are willing to share? (I am most interested in the SFTP one.)
March 22, 2011 at 7:43 pm
GSquared (3/22/2011)
Calling third-party DLLs (address list validation and presorting)Disk data (space available, used, etc.) on network resources
Disk operations (file archiving and compression, file encryption)
Running totals
SFTP upload/download (SSIS won't do SFTP)
SSIS will do SFTP, but not natively. I created a package that used a third-party SFTP command line utility and it works well.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 23, 2011 at 6:53 am
UMG Developer (3/22/2011)
GSquared (3/22/2011)
Calling third-party DLLs (address list validation and presorting)Disk data (space available, used, etc.) on network resources
Disk operations (file archiving and compression, file encryption)
Running totals
SFTP upload/download (SSIS won't do SFTP)
Are any of those ones that you have built and can/are willing to share? (I am most interested in the SFTP one.)
CozyRoc and others (Pragmatic Works??) have a tool for SSIS SFTP.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 23, 2011 at 6:54 am
heavy-duty string manipulations are a good example (especially delimited-string stuff)
heavy-duty or complex math
running totals
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 24, 2011 at 6:49 am
Jeffrey Williams-493691 (3/22/2011)
GSquared (3/22/2011)
Calling third-party DLLs (address list validation and presorting)Disk data (space available, used, etc.) on network resources
Disk operations (file archiving and compression, file encryption)
Running totals
SFTP upload/download (SSIS won't do SFTP)
SSIS will do SFTP, but not natively. I created a package that used a third-party SFTP command line utility and it works well.
Yes, calling a third-party SFTP solution works too. It was just easier to solve the particular production problem I was running into by using a CLR assembly in a proc and avoiding SSIS entirely.
- 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
March 24, 2011 at 6:52 am
UMG Developer (3/22/2011)
GSquared (3/22/2011)
Calling third-party DLLs (address list validation and presorting)Disk data (space available, used, etc.) on network resources
Disk operations (file archiving and compression, file encryption)
Running totals
SFTP upload/download (SSIS won't do SFTP)
Are any of those ones that you have built and can/are willing to share? (I am most interested in the SFTP one.)
I don't have that one any more (SFTP).
The address validation one isn't my IP per contract terms, but wouldn't do you any good if I shared it anyway.
I got the disk space one and the running totals one from other people, and they're available online. I think the running totals one was on Adam Mechanic's blog, if I remember correctly.
- 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
March 24, 2011 at 6:54 am
I'm using just this one:
Autonomous Transactions for SQL 2005
-- Gianluca Sartori
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply