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

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    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 | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jedak

    SSCarpal Tunnel

    Points: 4974

    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

    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

    SSCoach

    Points: 19959

    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
    Simple Talk

  • don_goodman

    Say Hey Kid

    Points: 670

    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: 7288

    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: 26672

    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 | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jedak

    SSCarpal Tunnel

    Points: 4974

    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

    SSCoach

    Points: 19959

    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
    Simple Talk

  • James Layman

    SSC Enthusiast

    Points: 105

    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

    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: 233804

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

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Ralph Hightower

    SSCrazy

    Points: 2787

    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

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

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