Boring or Scripting

Heh... I look at the recommendations for "scripting" languages with a bit of disgust. There are so many things that SQL Server does very well through T-SQL but people would rather learn a scripting language and be burdened by scripts instead of creating a relatively simple stored procedure and scheduling it.

Yes, scripting is indeed useful, but it's not a panacea and too many people try to make it one because many people have proclaimed it to be a "shinny object", particularly PoSH. Hence the most recent addition to signature line below that starts out as "Just because you can..."

I use Python for my automation and data pipeline with the many Python based API's that feed our database. I rarely rely on VB Script or Powershell to get those jobs done. I think in time, I may have too on the SQL Server side, but for now, I can solve most problems with Stored Procedures or SSIS packages on the SQL Server side of things.

The reasons I use Python so heavily is because the many API's I use are Python driven. It only makes sense that continuing to use Python to leverage those API's continues. The other reason is because Python integrates so well into applications that can be used on any system that aims to bridge systems together seamlessly (i.e.: Linux -> Windows).

The big reason however is that I work in a lot of data science projects with SQL Server believe it or not (not NoSQL). Python and R are already being leveraged by the data science guys on the client end. Supporting and using Python on the back end means we are a match made in heaven. We are compatible and we integrate nicely together as one big happy family of data and storage.

Then lastly, Python works on Windows and integrates into SQL Server. So, I can still tie everything above directly into SQL Server to pretty much automate and control everything. It has insanely good flexibility that I unfortunately cannot solve with just SQL Server. Or maybe, I might have not discovered it just yet. :w00t:

I was amused to see "VBScript works fine". It's a statement I disagree with, totally and utterly.

1) If you want an MS only scripting language, learn PoSH, not VBScript.

2) If you want a widely supported scripting language which is supported by SQL Server Agent and is fully compatible with .NET and so on as well as being an international standard, learn JavaScript.

3) If you want your ability to understand programming concepts permanently impaired, learn a BASIC-based language like VBScript. Actually I think Dijkstra was a bit OTT when he suggeted that teaching people anything BASIC-based should be a capital offence - or whatever it as that he did say, it's so long ago I can't remember the exact phrasing - but only a little bit OTT.

Suggesting PERL is a good deal less unreasonable that suggesting VBScript, so I won't complain about that.

I'm afraid I am guilty of using VBScripts which I use for backups of odd important files.

I have also used XBasic in batch files to do similar.

On the more front end developer side - I also use something called Auto Hot Key which is an open source program that allows you to create scripts that can do pretty much anything. I can write an AHK script that can be compiled if necessary and then be called by another application. This is useful in applications where I have control of one front end but not the other and absolutely no direct access to the target back end and users are very frequently navigating to individual records but the web application will not allow direct access to the web record by placing the parameter in the url.

So the air script would be something like

Set pkid to field 1

Go to search web url http://www.searchscreen.com

Move cursor to element 1

Place pkid in element 1

Click button 2

Wait

Its a small thing but it really makes a difference. Ideally you would go straight to the back end and drag the information from there but even internally within organisations I frequently can't get this access maybe because its a vendor specific app and unfortunately the web application front end is poorly designed and has no facility to go direct to records relying on people endlessley having to drop into search screens and search for a record.

Crazy but you are in effect getting staff to manually do their own joins between forms!!!!!!:-D

Calling someone boring just because they can't script is a bit harsh!

I agree that scripting is no panacea, but I think it's not just useful - there are times when its essential.

I'm almost tempted to give myself a signature line:-

"Just because you CAN'T do something in T-SQL, doesn't mean you SHOULDN'T do it at all."

One of the things I did a lot in the bad old days when I had to worry about keeping customers happy (retirement is wonderful, now I can worry about keeping my wife and descendents happy instead) was write software that could crawl around all the COM/COM+ stuff on the system, work out where we had been slugged by either an undocumented and unfixed Microsoft problem, an undocumented and unfixed Cisco problem, or an undocumented and unfixed problem in our one of our own C++ components and decide what objects to modify or destroy to get things up again with a minimum of disruption and end-users not seeing any problem (or maybe one of them would have to be asked to repeat an input). I wanted to do this when the database indicated that imaginable way to do that in T-SQL, and there was no imaginable way I was going to permit C++ (a really awful language) to be used for something that sensitive, so I wrote the original version in Javascript and people took it forwards in Javascript. It was easy to invoke this stuff from T-SQL (either with XPs or by using SQL Agent, when using Agent either not requiring response or polling for it or taking response in new connection).

So my view of scripting is that it's not just useful, it's essential, and everyone should be able to do it. Maybe not everyone should be able to crawl around various structures and find holes, but that's the point at which, if you reach it, you realise that it's no longer at all possible to argue that you can avoid scripting. But scripting can save you a lot of trouble even before you reach that point.

You'll get no disagreement from me on that. I've built some huge ETL systems that would import from just about anywhere or anything and it couldn't have been done without quintessential scripting. Heh... I also love your proposed tagline. It smacks of what I've always said... "It's not that it can't be done... you've just not figured out how to do it, yet". 😀

I certainly don't object to scripting. I couldn't have made my enterprise-wide disk space report without it. It uses xp_CmdShell to call a PoSH script that makes WMI calls to each server. Same with the ETL stuff. It took scripts called from SQL server to login to the various sources and download the information.

But I've also run into scripts made by people that used the scripts in a painful attempt to make up for their serious lack of even some of the basics of SQL Server. For example, there was one place I worked at that downloaded information from Double-Click.net and the data was a bit horrendous. It was "Comedy Delimited" versions of spreadsheets with unknown and constantly changing numbers of columns not to mention column name changes and some seriously odd column titles that need to be interpreted and correctly loaded into normalized tables. The DTS package (it was back on SQLServer 2000) was riddled with scripts in 3 different "languages" (Active-X, (mostly) Perl, and VB script) and took 45 minutes to just to get on 30,000 line file with a couple hundred columns ready for import never mind actually doing the import and validating the data before pushing it to the final normalized tables. I rewrote it all in T-SQL and was doing the full monty for 8 files every 2 minutes.

I've also seen people write some fairly complicated PoSH scripts to do backups on a server. While I applaud their Posh prowess, it was much simpler for me to write a self-healing, self-realizing backup proc in T-SQL.

In the same vein as scripts and to show the ignorance of T-SQL that many people have, I had a Database "developer" submit an SQL CLR to me for promotion to production at the same company. He insisted that it was urgent that I promote it NOW so that he could get his job done. It was a CLR to calculate a modulus because he didn't know how to do it in T-SQL.

So, yeah... I absolutely agree. Scripting is essential and I couldn't actually do my job as a DBA/Database Developer without scripts. But, to make/emphasize the only points that I want to make about them, they should never be used just to make up for ignorance especially when the scripts constitute a performance problem or unnecessarily contribute to the proverbial "Tower of Babel" by reinventing things that SQL Server already does quite well. They also shouldn't be created just because they can be created using the latest shinny object just because the new shinny object has come into being and it's been deemed that you're suddenly cool or somehow intelligent if you use it. 😀

I agree with learning PowerShell, VBScript...not so much.

Coming from a *nix background I have done a LOT of scripting and have found that PowerShell is fairly useful. PowerShell is no where as powerful as the *nix suite of scripting tools, but for Windows (which for all intents and purposes has no scripting tools and relies almost exclusively on pointy-clicky things) simply having a tool as nice as PowerShell is a massive asset.

I agree with Jeff that scripting is no panacea. Like all tools, it should be used when it is the best tool for the job.

I use PowerShell for file processes (moving/renaming/(un)zipping) and setting up staging areas for SSIS/SSDT packages to pull from. I also do some file validation with a PowerShell script.

One caveat to PowerShell. The security features are a joke.

It is trivial to bypass the PowerShell security features on a machine if you have access (even as a non-system admin) to the machine. This is such a gaping security hole that I am frankly dumbfounded that PowerShell is allowed on any enterprise system.

The CmdShell calls is a good argument on why scripting is needed and why it should be kept separate. I can't do CmdShell calls. I rather a separate system handle everything I need and HAND it off to SQL Server as it should be. Separate, secure and completely safe without having to issue operating system commands directly in the command shell with T-SQL code.

I'd rather be harsh now than having them be unable to find a new job because their skills have languished and they expect a position where they don't need to think.

If you're working through tasks like checking logs, backups, manually clicking the GUI (slowly ) to while away the day, you're going to struggle. It seems not only are more and more people learning scripting, more and more of them get input on whether to hire the next person.

Learn scripting.

Certainly T-SQL can be a scripting language, and a useful one. how often do DBAs need to schedule things and can't access or run things from the Windows host?

However, Jeff, learning PoSh isn't a shiny new thing that's useful everywhere. Unlike some of my fellow advocates, I think most of your work as a SQL Server pro should always be in T-SQL. However where you need to deploy things across many/multiple systems, where you need to manipulate the environment outside of SQL Server, PoSh works really well.

Plus it's a skill that can help you work in Exchange, AD, Windows, etc if you get called to help out.

I don't view those repetitive tasks as boring.

Agreed. I know it's possible to search for PowerShell tutorials, etc., but it would be nice to see some recommendations.