Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created a database maintenance plan using wizard though that link for SQL Server 2005.

I have created a Maintenance Plan for SQL Server 2008 and it works fine.

Now I required to complete the same task using a stored procedure for my desktop application.

I have tried this stored procedure for that but it doesn't works for me.

Stored Procedure Execute Successfully and also i can see the entered values in the table sysdbmaintplans of msdb the system database but created maintenance plan doesn't shows Under tree(MANAGEMENT=>MAINTENANCE PLAN) of Object Explorer(SQL Server Management Studio).

Please Help...

Below is the code snippet I have tried.

//   sp_add_maintenance_plan [ @plan_name = ] 'plan_name' , 
//@plan_id = 'plan_id' OUTPUT

String planid = "";

string maintenancePlan = "MyMaintenance";

string SQLstr = "DECLARE @plan_id as Varchar(50)";
SQLstr += " EXEC sp_add_maintenance_plan ";
SQLstr += " @plan_name='" + maintenancePlan + "',";
SQLstr += " @plan_id= @plan_id OUTPUT";
SQLstr += " SELECT @plan_id";

SqlConnection cnn = new SqlConnection(MSDBconnStr);
SqlCommand cmd = new SqlCommand(SQLstr, cnn);

cnn.Open();
planid = (string)cmd.ExecuteScalar();
cnn.Close();

//   sp_add_maintenance_plan_db [ @plan_id = ] 'plan_id' , 
//[ @db_name = ] 'database_name'

string maintenancePlanDataBase = "MyDataBase";
string SQLstr1 = " EXEC sp_add_maintenance_plan_db ";
SQLstr1 += " @plan_id='" + planid + "',";
SQLstr1 += " @db_name='" + maintenancePlanDataBase + "'";

SqlConnection cnn1 = new SqlConnection(MSDBconnStr);
SqlCommand cmd1 = new SqlCommand(SQLstr1, cnn1);

cnn1.Open();
cmd1.ExecuteNonQuery();
cnn1.Close();
share|improve this question
    
This is really a question for the DBA site, but what exactly are you trying to do? Are you trying to create a maintenance plan from a desktop application? If so, why not just use SSMS? –  Pondlife Jun 29 '12 at 13:46
    
i have a task to provide a functionality to create maintanace plan of database for daily database backup from my desktop application....because as per my leaders instruction we required to bypass the requirment of SSMS at client side(user side) because user may not have the knowledge of SSMS.......THANK YOU SO MUCH.... –  Pritesh Jun 29 '12 at 15:43
    
A user that doesn't understand SSMS should not be creating database maintenance plans. That would likely include whoever asked you to do this work. –  Mark Storey-Smith Jul 2 '12 at 23:08
add comment

migrated from stackoverflow.com Jun 30 '12 at 3:02

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

Instead of using maintenance plans, check out Ola Hallengren's free maintenance stored procedures. They're way more powerful than maintenance plans and they don't require SSMS (per your requirements) to implement. You can set them up with any T-SQL connection.

share|improve this answer
    
ya exactly i have used this stored procedure......thank you so much for replying –  Pritesh Jul 3 '12 at 11:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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