Automation basics in Toad for SQL Server (Part 1)

Hello, my name is Robert Pound, and I'm a Quest technologist. SQL Recall automation code libraries. These are just a few of the Toad features that enhance productivity. In this video, we'll cover the basics of automation. It is assumed for this video that you have a SQL Server Management Studio plug-in installed, you have a database connection established in both SSMS and Toad, and you have a SQL statement that you want to automate. So starting from the Management Studio, if I'm ready to start automating, I can right-click, choose Toad manage, and automation. This will open up Toad and the Automation tab. It will also establish the connection, if not already done. If working in Toad, we have a couple of options. We can simply come to tools and choose automation, or in the editor itself, we can come to send to automation. This will open up a wizard that will walk you through a few of the common scenarios. I will not get into that here, but it's available. So moving back to the Automation tab, there are three main components in the automation tab itself. There is the tool box, there is the script design pane, and there's the details pane. So on the tool box, you have three main groupings. There is the database activities, there is the file activities, and then the system activities, and we can scroll through with these arrows if we want to see more, or we can minimize certain groups so that we can focus on a single set of activities. You also have your save and run and schedule icons at the top of the toolbar. Currently, and since there's no activities in the script designer itself, we have settings selected. And from here, you can choose different settings for your script. For this script, ensure that stop on errors is selected, if it's not. That will ensure that if there are any errors in your script, it will not continue on with the next activity. You also have the ability to establish both a test and production connection. If you want to run the same automation in both those environments, you can set up the connections and then simply choose the drop down for the desired connection. But back to the activity at hand. We wanted to run our statement, and then output it to a file. So I will choose select to file. It has added an icon to the designer with a red exclamation point, letting us know that we have a few things to fill out. And in the details pane, we could simply start creating our statement. And we will get the intelligent feedback that we're used to in the editor, but, again, since we've already created it in both Toad and in the Management Studio, I will simply choose the ellipses, navigate to the folder, and click open. This will load in the content of the file itself, but if we make any changes to the underlying file, they won't be reflected in the automation script. So for that to happen, we first have to choose link SQL file, and choose the file, and it will again be loaded into the pane, but now we've been linked to the file. We also need to give the exported file a name. I'm going to choose CSV in this case, and choose demo 123, and if I wanted to change the location, I could do it here, but I'm going to simply choose the default for now. You also have the option to either overwrite the file or not overwrite the file. And you also have the ability to add suffixes to your file name, so date and date time. This will allow you to have multiple files in a given directory. There are also more advanced options, but I'm not going to get into that here, but just know that they are available. So our script is actually complete and ready to run. I will switch over to activity information. I like to name each one of my activities. It may not be important now, because we have a single activity, but when you have larger scripts it becomes much harder to read. So I'm going to name this demo file and call this gizmo, because why not. And you can enter in a description as well, so that way when you're looking at this a few months from now, you'll still understand what this is supposed to be doing. You also have the ability, if you have a larger script, to enable and disable certain activities, so that way you can test and debug on a single activity or multiple activities. I can either enable and disable it here in the Info, or I can right-click on something that's disabled and click enabled, and it will go back to being tested. And finally, you have the log tab to get any log information for this given connection. And with that, I'm going to go ahead and save the script. I'm going to come up here to the top, click save, and we'll call this demo automation 2. I'm going to go ahead and save it, and that's it. In the next video, we will talk about testing and scheduling our automation scripts. I hope this video helped you get started with automation. For more information on this product, visit www.quest.com/products/toad-for-sql-server. Or you can check out the community-driven innovation going on at www.toadworld.com. Thank you, and have a great day.