0

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.

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

  • Hi, I try to use Ola's maint solution. But other solutions, don't seem for my activity. – Joe Taras Feb 6 '17 at 7:26
  • Can you be more specific .. what does not fit ? – Kin Shah Feb 6 '17 at 12:03
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.