I was wondering if anyone has a pointer to reading information about some of the weird fringe stuff that can be done with T-SQL. (I'm sorry that I'm not even clear on whether or not there is an all-encompassing term I should use to refer to this).
Example, working with files or folders, shelling out a program, sending an email, just oddball stuff other than DDL/DML.
Obviously this will be very specific to database version, but I have access to some extent to various versions (up through 2012), so any reading material would be nice. Thank You.
Any article that discusses those topics should start out with "these things should not be in T-SQL". There are security implications for most of that stuff. Why not use the proper tool instead?
I didn't know it was considered improper (although intuitively, the thought did cross my mind), I was just asking because it seemed like a nice idea to know what options were out there.
(Also, I have to assume that there are probably those who for whatever reason simply don't have access to SSIS (or whatever proper tools you may think of in their particular case) and because of that have gratefully made do with a t-sql technique for sending an email, creating a text file etc - although I admit this isn't my case).
I guess I just thought it would be prudent to understand my options in at least some of these items. I do sometimes feel like it's overkill to create an entire SSIS package for the purpose of sending a couple emails, if I could use SQL Job Agent's "transact sql" type in the Step dialogue as opposed to creating an entire DTSX file, complete with its own overhead, storage, documentation, etc. I don't really see the security implication in this example, a sql job agent which is running either an SSIS package or a T-SQL statement, probably running either/both of them using an SA.
Anyway - not arguing with you just explaining that I wasn't planning on transferring all my beautiful ETL packages over to these techniques or anything like that, I just wanted to know about some of them. There are a few cases where I schedule a stored procedure, and the only real reason to use an ETL or other programming type of tool is to send mail based on if there is some new data, etc. It seems a little overkill to do this in SSIS if T-SQL is perfectly capable of doing the exact same thing and it's only a couple lines of code for a brief email.
Who said SSIS?
If you'd like to send an email in T-SQL, you can setup Database Mail. We use Database Mail plenty, but that doesn't mean it's the right tool to use to send application users an email. We use it for backend emailing, such as output from Agent jobs as you mentioned.
But I would recommend getting experience with Powershell as it's what you are looking for instead of doing things in T-SQL that open security holes and bad practices. Powershell is where it's at for lots of these things.
I only said SSIS since your first response was a bit vague on what 'proper tools' you were referring to, so I was supplying a hypothetical for discussion. Curious, why do you suggest Powershell over SSIS? They both seem extremely popular from what I hear thrown around, we mostly use SSIS for the types of things I am referring to.
I'm still a little confused on your response. Your first reply came down pretty hard on doing things like sending email in T-SQL, but your latest response said you use it plenty and differentiates between sending application users an email, vs. 'backed emailing' which isn't clear to me. My use of SSIS to send emails is fairly mixed--some emails go to development team, some go to internal customers + development team, and many go externally as well.
My response is due to the security holes you have to open in order to do things in T-SQL. Read up the security implications for xp_cmdshell for instance. SSIS is fine to use, but like you said it's overkill for lots of things. This is where Powershell could come to the rescue. Sure T-SQL can come to the rescue too, but at times at the cost of security. I don't bend the security rules.
I will keep in mind Powershell and do some studying up on it to see where it can help me out. Thank you
There are a lot of things that I think PowerShell is over used and abused for. For example, there are some "wonderful" scripts out there that will do the backups for your entire enterprise. My question would be, if the box that script is on goes down, what do you think is going to happen on all the log files on all those boxes? 
I also think that there's a general paranoia about doing certain things in T-SQL based on old wives tales from years past especially when it comes to security. These are also the same people that will gleefully expand their surface area with SSIS, SSRS, and a whole host of other SQL Server 4 letter words and 3rd party apps (or unsupported free stuff). Those things are not necessarily bad and can actually be good. Just don't use them as a crutch for not knowing how to do things in SQL Server that actually can and should be done in SQL Server and maybe a few that most say shouldn't.
I agree with Tara that you probably shouldn't turn your money-maker server into an email engine or an external HTML generator or even a reporting server but I do recommend that you keep an open mind in your travels because the bottom line has always been as it is now... "It Depends".
Thanks for the comments and insight, Jeff. I have to confess I do still fail to see how using some of the file scripting type commands are any less secure in t-sql than ssis or another typical ETL/automation engine.
But I have enjoyed using SSIS for many of these types of things, I just didn't want to not know how to do a few of the other things in case for some reason at some point I don't have those things available (example, I just found out today I can't necessarily use Powershell on all of our servers and it's probably not something that's going to change).
I do totally understand what you mean about SSIS and such things being a crutch - ESPECIALLY for those of us who were stronger in vb type of background and now trying to master SQL, it's tempting to use SSIS loops to do stupid stuff that should have been done in SQL. So I try to be careful about that and I think I have been pretty good about it.
Also that there are those things that people just like to jump on the bandwagon and go crazy about saying they're evil in 100% of cases (like cursors), I've written some procedures that use cursors ONLY on the final "pared-down" recordset of a few hundred records, and the entire procedure still runs in 2 seconds...I mean, it all depends.
Anyway, thanks everyone for the comments and insight.