Fast and Free; SQL Scripts Manager’s Script Generator

When William produced his second article on the free tool 'SQL Scripts Manager', revealing that it worked just as well with PowerShell and Python scripts as it does with TSQL, he thought that would be the end of the series. Oh no; in response to feedback, comes a small add-in called 'Script Generator' that makes a big difference to the speed of developing and producing new scripts.

SQL Scripts Manager, Simple-Talk’s Christmas present to its readers,  is a free tool by Redgate. It now has an ‘add-in’ called Script Generator.

SQL Scripts Manager is a utility that allows you to run TSQL, PowerShell or Python scripts in a simple wrapper that provides the menu of available scripts, the User Interface for supplying parameters, and a separate GUI for displaying the output. I give a general introduction to it here in ‘SQL Scripts Manager: An Appreciation‘ and go on to show how it is equally effective for PowerShell scripting in ‘SQL Scripts Manager with PowerShell‘. It would be boring for you to repeat anything I covered in those two articles so if you’re unfamiliar with the tool, then please dip in to them.

After  it was first released, it became obvious from feedback from the users that SQL Scripts Manager wasn’t yet ideal for end-users who wished to create their own scripts. Whereas an XML file seemed a trivial barrier to the developers and testers of the product, it proved to be tedious for the end-user who wished to use the tool to manage a large number of scripts. The problem was that it was originally conceived as a way of packaging a whole series of industrial-strength ‘issued’ scripts such as Ola Hallengren‘s amazing Database Maintenance package, reviewed here, whereas, experience has shown that it is currently also popular for deploying a collection of customised scripts for particular requirements.

The tool owes its existence to Red-Gate’s occasional ‘down-tools’ weeks, where the development teams take a quick break from the tasks they are engaged in, and organize themselves into different teams to work on whatever projects they want. Quite a lot can be achieved in these weeks and SQL Search, SQL Scripts Manager and MySQL Compare all came about through this work. It is great for FORGs (Ed: Friends Of RedGate) because this is the time we can  give some input into what the Red-Gaters do without nervous interventions from the product managers. It isn’t exactly a free-for-all, since the participants have to present on what they are planning to do, and have to afterwards show what they’ve achieved.

The team who designed SQL Scripts Manager haven’t finished thinking of ways to improve the tool (if you want to get involved in Beta Testing the next version, please contact the team on their forum). The most pressing problem was designing a quicker and easier way of creating and submitting a script. This is where the Script Generator, which is an add-in, comes into play. At the moment, it is designed for SQL Scripts rather than PowerShell or Python, but if it proves popular, Hey, there will be more down-tools weeks and the team can be tempted to dust out the source from SubVersion.

Running SQL Scripts Generator with a SQL Script.

  The sequence of events is this: Firstly, Copy  the  Script Generator from this zip file to the install directory of SQL Scripts Manager (there is no installer as yet). Test your script first in SSMS. If it runs in SSMS, it is quite likely to run in SQL Scripts Manager. (You have to be more careful with implicit casting for displaying strings, I found).

 Then fire up Script Generator from the SQL Scripts Manager directory.

1306-NewScript-630x588.jpg

Paste your script into the text editor panel, or load it via the tool bar or menu and fill the fields in the left hand pane.

1306-Script%20Entered-630x588.jpg

Click ‘Save’ to build your .rgtool file. This file is compatible with SQL Scripts Manager, and it will appear in the list along with the scripts that are supplied.  The reason the title is showing in red in the screenshot below isn’t because it is still glowing hot from the furnace, but is simply that the script hasn’t been signed by Red Gate.  This doesn’t stop the script from running in any way at all. It just means it isn’t part of the distribution.

1306-ScriptInPlace-630x407.jpg
You can tweak the parameters in the XML file that is produced. Click on the OPEN button to execute the script and this dialog box pops up

1306-IWonderWhatThisDoes.PNG

Phew. It has taken on board the way I changed the ‘script target’ from ‘database’ to ‘server’, so that, the first time I run it I will need to supply it. (you can get it to remember all your servers, but only on a script-by-script basis). Now hit the ‘Run’ button.

1306-ScriptExecuted-630x632.jpg

  Once you’re happy with the wrapped script, share it with the SQL Server Central community  at http://www.sqlservercentral.com/Scripts/rgtool/   if you think others can benefit from it.

Scripts take hardly any time at all to generate. It is so much quicker than the original way of creating  scripts from a template. Suddenly, I have no excuses for delaying a grand tidy-up of the heap of scripts lying around for occasional use. It is as easy as filling in a brief form. When you hit a problem (I stumbled over a minor problem with the output window of SQL Scripts Manager) it is lightening fast to modify a script in this add-in. This utility isn’t currently supporting parameterised scripts; but you can edit the XML .rgtool file created by the script, to include parameters.  This tool is intended more for the SQL scripts that are executed within a server or database, and SQL Script Manager does all that for you by default.

Under the Kimono

The tool has produced a simple XML file as follows. Without too much difficulty, you’ll be able to see how you could alter it now it has been made in order to improve it. (I tweaked the size of the output window before taking the screenshot above!) The meaning of the XML that we’ve just generated can be looked up in detail here in The SQL Scripts Manager XML schema . You’ll see that only the ‘server’ and ‘database’ controls have been catered for in the Generator, but that is probably all that is needed for the average SQL Script and it is easy to add more in a text editor once the initial file has been generated. They are all listed here.

Conclusion

For me, it isn’t hard to be keen on SQL Scripts Manager because it is a quick way to put a wrapper on a script so as to allow anyone to use it. This ‘anyone’ includes myself in the future, who, I can predict from past experience, is slightly forgetful. With all my routine scripts all tidy, documented and consistent, I actually save myself the brief pause where I try to remember the complexities of how to use my own scripts. At the time I write this, there is a version of SQL Scripts Manager being tested (we need more volunteer beta testers- apply in the forum) that allows the user to change the scripts folder location so that a networked team can then share scripts. With that in place, SQL Scripts Manager becomes a lot more generally useful in the typical DBA ‘shop’.