T-SQL Statement Task choose specific database name on server

Question

I try to create a simple T-SQL Statement Task by Sql Server 2014 GUI.

My flow:

• Right click on Maintenance Plan
• Choose Create new Maintenance Plan
• Choose the type (T-SQL Statement Task)
• I add by toolbox an activity for SQL Statement (a simple SELECT * INTO mytable FROM anothertable)

Now I want to choose the databases where I want to execute my task.

By property I've changed the DatabaseSelectionType to SPECIFIC but I haven't any form to choose database.

If I try with another task type (Index maintenance) I can choose the databases.

Surely, I'm wrong in some steps but I don't understand where.

Answer 1

Do not rely on GUI .. use TSQL instead :-)

Many options you have to do your simple task :

• use 3 part naming SELECT * INTO dbname.schema.mytable FROM dbname.schema.anothertable
• explicitely use use dbname; SELECT * INTO mytable FROM anothertable;
• Use sqlcmd - either sqlcmd.exe or in SSMS query window
• Use powershell

BTW, dont use maintenance plans - instead use Ola's maint solution

Answer 2

My solution (but in the next days I will try Ola's maint solution).

I've created an empty database with a stored procedure.

This stored procedures call by other involved databases a local stored procedure with the logic.

In the maintenance plan I've added a step as Transact Sql where I call my general SP (on empty database defined before).

And then I've planned the execution for each day.