Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Question

Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Phil Factor (2/5/2009)
use of CLR makes it harder for a SQL Server administrator to see what's going on with their SQL Server.
Yes, Agreed! However, I've recently started using NET Reflector with an add-in that allows you to see the source of all the CLR routines currently in the database.
http://www.denisbauer.com/NETTools/SQL2005Browser.aspx
It is a joy to use and it means that the D**ned developers can't hide their code from you, particularly as you can decompile it into VB or C#. and save the source to a file. Power to the DBA!

Depending on how you deploy your Assembly you don't really even need Reflector. For brevities sake I didn't include all the files in the script I provided here, but if you do, then you can get the code from the sys.assembly_files DMV:

declare @content varchar(max)

select @content= cast(content as varchar(max))

from sys.assembly_files

where name = 'os_directory_info.cs'

print @content

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Attached is a new installer that will includes the source file so you can view the code natively in SQL Server, and handles the exception being raised if the path does not exist.

Jedak SSCertifiable Points: 5589 More actions · Answer 3

Thanks for the article. The last time I was developing a CLR function/procedure I could not find a good explanation of how to handle the signing and install of the assembly for external access. So, we had to use the trustworthy setting. Now I have a better idea of the key install.

Thanks

eschudy SSC Rookie Points: 33 More actions · Answer 4

The reason's that most DBA's continue to use xp_cmdshell is simple - it's easier. There are numerous examples on the web of how to get a directory listing using xm_cmdshell. I cut and paste, and voila! I have my routine. Using SQLCLR requires someone to know a different language, and all it's data access routines (Fill, etc.). So most DBA's will stick with what they know and since everyone's overworked, will not use something new.

BTW, I write and use SQLCLR for special mathematical functions that we write, so I have nothing against it. But I've been writing C/C++/Vb code since the 80's, and because I'm familiar with the algol-68 derivative languages, it's easy for me to switch back and forth. I don't think you can expect that of most people.

Phil Factor SSC-Insane Points: 20244 More actions · Answer 5

Oooh. This is getting very useful. I'd missed out on the advantages of passing string parameters as SQLStrings and the trick of passing back a NULL. It makes perfect sense. Now it has the behaviour that one would want.

Best wishes,
Phil Factor

don_goodman Say Hey Kid Points: 670 More actions · Answer 6

I did not read past the first paragraph. Instead I scanned the code samples. I saw a lot of code. If I was a DBA that wrote lots and lots of code to do simple tasks, I might be impressed. As I am a DBA that writes very simple code to do simple tasks, I am not.

I'll continue to use cmdexec because it is simple and fast. It has a small memory footprint where the CLR is bigger.

If you start thinking this way, it will help.

Small code good

Big code bad

simple good

complex bad

Just keep repeating those and you will be cured of you love of the CLR.

Richard M. SSCertifiable Points: 7401 More actions · Answer 7

Don, here is a VERY good example on why CLR's sometimes ARE the way to go:

SQL Server CLR function to improve performance of validating email addresses [/url]

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

don_goodman (2/5/2009)
I did not read past the first paragraph. Instead I scanned the code samples. I saw a lot of code. If I was a DBA that wrote lots and lots of code to do simple tasks, I might be impressed. As I am a DBA that writes very simple code to do simple tasks, I am not.
I'll continue to use cmdexec because it is simple and fast. It has a small memory footprint where the CLR is bigger.
If you start thinking this way, it will help.
Small code good
Big code bad
simple good
complex bad
Just keep repeating those and you will be cured of you love of the CLR.

Security is my #1 concern, and you can't get the same security from xp_cmdshell as you do with SQLCLR. I can crash a server with xp_cmdshell. You can't with SQLCLR that is SAFE / EXTERNAL_ACCESS only.

Simple code is often some of the worst performing code I find when I do consulting. For example:

Create a temp table.

Insert some information into it.

Then use nested selects to build the rest of the information into the table and return the results out.

Pretty simple, it is TSQL 101 type code, but I can write a single statement that does the same thing in 1/100 of the time and is more complex, so it is bad?

Since it is already published in the BOL that support for XP's will be removed in a future version of SQL Server, I wonder what you will do when that day comes?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

don_goodman (2/5/2009)
Just keep repeating those and you will be cured of you love of the CLR.

It's not that I love SQLCLR. In fact I more or less hate how it gets bastardized like User Defined Functions, Cursors, OLE Automation, XML, and other of the features that are included in SQL Server to solve problems, but are most often abused by people who have no idea what they are doing.

That is one of the purposes behind this article series, to show where proper application of SQLCLR can solve bigger problems at hand, like the security risks imposed by having xp_cmdshell open for use. If you can't see how much safer SQLCLR is in this scenario, you aren't giving it a fair impartial review.

Jedak SSCertifiable Points: 5589 More actions · Answer 10

don_goodman (2/5/2009)
I did not read past the first paragraph. Instead I scanned the code samples. I saw a lot of code. If I was a DBA that wrote lots and lots of code to do simple tasks, I might be impressed. As I am a DBA that writes very simple code to do simple tasks, I am not.
I'll continue to use cmdexec because it is simple and fast. It has a small memory footprint where the CLR is bigger.
If you start thinking this way, it will help.
Small code good
Big code bad
simple good
complex bad
Just keep repeating those and you will be cured of you love of the CLR.

Elegant code is good. What I mean by elegant is, code that is as small, simple, reusable, and efficient as it can be.

Simple code that is inefficient is not good.

Phil Factor SSC-Insane Points: 20244 More actions · Answer 11

Actually, I think I've found a very good use for CLRs, and that is to handle JSON and YAML within a database. you can create a JSON data type and all the functions for creating, querying and shredding it. The C# libraries to do this already exist.

Best wishes,
Phil Factor

James Layman SSC Enthusiast Points: 105 More actions · Answer 12

Don,

CLR is another tool for me to use. It is up to the individual to decide what tool to use or recommend for a given situation. At my last position, developer, I wore many hats. SQL Server DBA, data modeler, web app developer and programmed in several languages. I resisted learning asp.net since asp was a good fit for the site's limited number users. asp.net seems to come into its own under higher user loads. Well the site closed a year ago and as I looked for jobs, I quickly decided that I erred in not having a working knowledge of asp.net.

After a month out of work, I landed my current position as a DBA. My past experience with unix, ASP, vbscript, and other programming has helped me more than once as a DBA. I need to build my knowledge of CLR. This will benefit me now and when I move on.

Jonathon,

Thank you for an interesting article. CLR is something that I need to wrap my mind around. The security aspect certainly caught my attention.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 13

I guess I'm still an old fart. I like having my SQL and applications separate. Then if Phil is getting on the bandwagon ...

Jonathan, if you want to really freak folks out it's possible to get the size of a directory. Well the sum of it's contents anyway. Your fill row routine only needs to call a recursive function that gets the sum of all the file sizes. This is something that you can't do with the DIR command itself.

SPEAKING OF ACCOUNTS! Drive letters can mess you up bad. Each account can have it's own drive letters. So Y: for me can be different from Y: for you on the same machine. Thankfully MS has seen fit to NOT map drive letters if you are not on a GUI. Still (and I'll skip the long, drawn out, tirade) different accounts can see the file system world differently. Consult your system policy very carefully.

Using Dot Net code it's a bit more difficult to do:

DEL C:\*.* /S < y.txt

But not much more difficult. Sure everybody has a file with the letter y followed by carriage return and line feed.

Are CLR procs better than writing extended stored procedures. Well you can do the table valued thing and the UDF thing. So that's better. More safe, stable and secure. Forget your SQL server service. I can crash your whole OS using nothing but managed code.

It's a tool ladies and gentlemen. Same abuse vs safety arguments with all of them.

ATBCharles Kincaid

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

Great article Jonathan, should wet peoples appetites for CLR a little

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Ralph Hightower SSCrazy Points: 2843 More actions · Answer 15

Jonathan,

Great article! I am a C# .Net programmer and not a SQL Server DBA in my real job, although I am the DBA for my home system. I read SQL ServerCentral for the ideas, and articles to improve my SQL skills.

Your article of using SQLCLR for .Net programming shows that I have an opportunity to contribute to the SQL ServerCentral community, if not in a DBA capacity.

Thanks,

Ralph

http://www.linkedin.com/in/ralphhightower

http://www.codeplex.com/NasaStsTvSchedule

http://www.codeproject.com/KB/office/NasaTvSchedule.aspx