Setting Up the Maintenance Plan

Since we’re going to be working within the design surface and not the wizard interface, setting up this plan is different from what you have seen. The essence of the task is the same, in that we are setting up a job to be run and defining the characteristics of that job within the interface presented to us. The simple fact is that we can’t run a T-SQL statement in a regular maintenance plan that we have already set up without a lot of extra work that really wouldn’t have anything to do with the original maintenance plan. It would need to be run as an addition to an existing plan, in other words, which makes no sense since we want to keep these maintenance tasks logically separated. Working this task up in the design surface, and not the wizard, gives us the opportunity to specifically create a T-SQL task.

Right-click Maintenance Plans and choose New Maintenance Plan… and enter T-SQL Plan as the name, as shown in Figure 11-1.

Figure 11-1. New Maintenance Plan

Click OK. You should see the interface shown in Figure 11-2.

Figure 11-2. Design surface

At this point, you can enter a description in the large box at the top of the screen. When you’re done with that, double-click the subplan that was created (Subplan_1) and fill it in like you see in Figure 11-3.

Figure 11-3. Subplan Properties

Looks sort of familiar, doesn’t it? This is the other way to enter a task. Change the schedule the way that we did in the other chapters and leave the “Run as option” to the default.

Like I said before, this doesn’t mean that it is any more or less powerful, or that it does anything different, it is just a different way of doing basically the same thing. In the Maintenance Plan Wizard, we didn’t have the option to just run an SQL query without going into the SQL Server Agent Job and running an SQL script from there.

Click OK when you are done with this screen. You are then shown the interface you were at before, with the values you entered in place. You should be looking at something similar to Figure 11-4.

Figure 11-4. Design surface

I know what you’re thinking. How do we add the SQL to this? There doesn’t seem to be any place to add anything. That’s because we haven’t shown the Toolbox yet. Remember way back when we discussed the differences between setting up the maintenance plan using the wizard, and by using the design surface? This is one of those big differences.

On the left side of your screen, you should see a menu that says Toolbox. If you don’t, press Ctrl+Alt+X to show it. Figure 11-5 shows what the Toolbox should look like.

Figure 11-5. Toolbox

That’s the Toolbox. Note that the majority of things in there are what we’ve already been through.

What you want to do here is the old “click and drag” technique. Click Execute T-SQL Statement Task and drag it into the gray area underneath the subplan information. What you will see is shown in Figure 11-6.

Figure 11-6. Execute T-SQL Statement Task (initial)

This is the first step, so don’t worry that the red X is there. That’s just because we haven’t finished setting it up yet. Speaking of that, go ahead and double-click anywhere on the Execute T-SQL Statement Task box to go to the next step, as shown in Figure 11-7.

Figure 11-7. Execute T-SQL Statement Task detail

This is what you see when you first get there. This screen lets you define the connection, if you happen to have more than 1, and enter the T-SQL statement. You can also define the execution time out here as well. This value is in seconds, with 0 meaning that it will run until it finishes execution.

The T-SQL statement we are going to enter is going to be something short and sweet:

SELECT firstname + ' ' + lastname FROM users ORDER BY userid;

This gives us the concatenated user names from the Users table in our database.

Click OK when you are ready to move on. Notice that in Figure 11-8, the red X went away and the screen looks more complete.

Figure 11-8. Execute T-SQL Statement Task Detail (completed)

Notice here that you can either keep the generic text of Execute T-SQL Statement Task in there, or you can enter a new value. To do this, just long-click the text and wait for it to become editable, enter the new value, and press Enter. I did this and changed it to Run Some SQL, as shown in Figure 11-9.

Figure 11-9. Execute T-SQL Statement Task Detail (updated)

Now that that’s set up, let’s look at some of the settings. Right-click and choose Properties. Holy cow, that’s a lot of properties. To define them would take another whole chapter, so we’re just going to assume that everything is okay in here and move along to one interesting bit.

Look for the property that says SqlStatementSourceType, as shown in Figure 11-10.

Figure 11-10. SqlStatementSourceType

That property is the key to this. The SqlStatementSourceType property is set to DirectInput by default. The other values are FileConnection and Variable. Let’s look at these separately in Figure 11-11 before we go on.

Figure 11-11. Source types overview

Make sense? Those are the three ways that you can define the SQL that needs to be run. If you can’t do it with one of these three methods, then I don’t know what to tell you. I can’t think of any other way that it can be run, honestly; except if it were a Stored Procedure, which can be set to a Boolean value by changing the IsStoredProcedure value to True from False (the default).

For this exercise, we are going to stick with DirectInput, but I strongly encourage you to get into the other two methods of doing this step, if you haven’t already.

Save the plan and close it, and then go back to the Maintenance Plans section and refresh the Maintenance Plans folder to see the plan.

Open the Jobs folder. The plan is right near the bottom. Figure 11-12 shows this new job with the rest of the jobs.

Figure 11-12. SQL Server Agent Jobs

Interesting! So now, we have a job… but did it work? Is it a failure or a success? We didn’t do any of those things this way. Remember setting all those options before? What happened to them?

Well, think of it like this. Creating the task this way gave you the chance to operate strictly from the task side and not the schedule or reporting side. This way allowed us to focus in on what, specifically, we wanted to do. Implementing it will be another story, but it isn’t as hard as you may think.

Implementing the Maintenance Plan

Now that the task is set up, let’s put it to work. How do we do that? Just double-click the job name under SQL Server Agent (mine is T-SQL Plan.T-SQL Task) and there you go, as shown in Figure 11-13.

Figure 11-13. Job Properties, General tab

Oh, thank God. We’re finally back to familiar territory! From here, set it up like you would normally; give it a new name and update the description, if you would like. I changed mine to T-SQL Task. Notice that we are on the General tab on the left. Let’s step through the other tabs now.

We are now on the Steps tab. Initially, this interface appears similar to what you see in Figure 11-14.

Figure 11-14. Job Properties, Steps tab

Everything is all set up for us! There are a couple of little things that I would like to change, though, so click Edit and then Advanced. You should see the interface shown in Figure 11-15 at this point.

Figure 11-15. Job Properties, Steps tab, Advanced option

Just select the “Include step output in history” check box, and that’s it.

Click OK when you are done in this area.

Click Schedules on the left to continue. This screen shows the setting that was defined for the task, which was Daily at 12:00 AM. Remember setting this earlier? It can stay like this, unless you need to change it.

The Alerts tab is next, so go ahead and click that. We don’t really need to set any alerts on this, since we are just running a simple query. Again, if you need to run something here, feel free. This is your maintenance plan, after all!

Notifications is the next to last. It is the important bit. Make sure that you check the E-mail box, choose our operator, and set it to “When the job completes”. Figure 11-16 shows the recommended settings.

Figure 11-16. Job Properties, Notifications tab

On the Targets tab, just leave everything blank. We haven’t defined anything at all for this section.

When you are ready, go ahead and click OK to save the changes to this Job.

This setup routine should have been familiar to you if you read through Chapter 4. If you haven’t, then you should certainly read that so you get an understanding on what we’re doing and why.

Remember when I asked how would we know whether it had succeeded? Well, let’s find out now.

Executing the Maintenance Plan

Right-click the job and choose Start Job at Step… to run it. More than likely, it failed. Mine did! Figure 11-17 shows this failure.

Figure 11-17. Start Jobs

It’s time for some troubleshooting. Click the hyperlink on the right side of this box and you will see what is shown in Figure 11-18.

Figure 11-18. Start Jobs error

Wow. Thanks, Microsoft. That tells us nothing useful, except to read the history log. Okay, let’s do that then. Close the window that showed the failure, right-click the job, and choose View History. This opens the interface shown in Figure 11-19.

Figure 11-19. Log File Viewer

Interesting. Expanding this error message shows us “Invalid object name ‘users’.” Hmm. Close this window and open the T-SQL Plan, as shown in Figure 11-20. Double-click the Run Some SQL task, as also shown in in Figure 11-20.

Figure 11-20. T-SQL Plan

Since the error specifically said that ‘users’ was an invalid name, let’s change the query to the following, complete with a perfectly valid name:

SELECT firstname + ' ' + lastname FROM DEVTEST.dbo.users ORDER BY userid;

Click OK when you’re done and then save the maintenance plan. Go ahead and right-click the T-SQL Task job and select Start Job at Step… to fire it off. Did it work? It sure did for me. This is a great lesson in how SQL Server Agent isn’t looking at our specific database, but is instead looking at master as a default.

If your query is still failing, I’m going to give you a major piece of advice here. You can specify the account you want to execute the SQL instead of the SQL Server Agent, even though you cannot choose another option in Job Properties ä Steps ä Edit ä Run as. Want to know how?

Recall that our original query was as follows:

SELECT firstname + ' ' + lastname FROM users ORDER BY userid;

And then we updated that query as follows:

SELECT firstname + ' ' + lastname FROM DEVTEST.dbo.users ORDER BY userid;

That’s the extent of our query. And it is failing. So at this point, we have a choice to make: we can either chase down a system administrator and demand heightened permissions for the SQL Server Agent account on the server, or we can create another domain account for the SQL Server Agent to be running as (with heightened permissions as well). Perhaps there’s a third option, though? Tell me, can you successfully run this query by itself? Yes. So why can’t you be set as the “Run as” value? Oh, but you can!

Double-click the T-SQL Plan maintenance plan and then double-click the Run Some SQL task in the gray area shown in Figure 11-21.

Figure 11-21. Run Some SQL

This brings up our T-SQL statement box, with the query listed earlier. We’re going to change that around just slightly by entering the following SQL instead.

USE DEVTEST
GO

SELECT firstname + ' ' + lastname FROM users ORDER BY userid

EXEC AS LOGIN = '[DOMAIN][USERNAME]';

Take a look at the first bit… it’s essentially the same as how we defined the SQL earlier, isn’t it? That’s right. Choosing to use the USE keyword or defining the database in the SQL as we did earlier accomplishes the same goal.

An important note to this is that the package is still executed by SQL Server Agent. The task is executed under the context of my user account. Does that make sense?

Save this maintenance plan and close it. Right-click the job and select Start Job at Step… and watch what happens in Figure 11-22, just by changing that SQL query to include my login.

Figure 11-22. Success!

I love seeing that.

Summary

Let’s do a quick recap here…

• We set up another plan using the “other” way (design surface).

• We edited that plan using SQL Server Agent’s Job Properties.

• We discovered a possible failure in the query, so we rewrote the query and now it works.

Your Jobs folder should now look like Figure 11-23.

Figure 11-23. SQL Server Agent Jobs

Your Maintenance Plans folder should now look like Figure 11-24.

Figure 11-24. Maintenance Plans

Excellent job on this chapter. Reread what you didn’t understand, though, especially the part about how to run the query with your user account instead of SQL Server Agent. Next, we will look at the Notify Operator Task, and then wind everything down for the conclusion. If you’ve made it this far, excellent job! You are well on your way to having the tools and the knowledge to create or maintain your own maintenance plan.