April 18, 2006 at 9:37 am
Hi,
I have the following code:
declare @new_plan_id uniqueidentifier
declare @new_command nvarchar(1000)
exec msdb.dbo.sp_add_maintenance_plan 'System DB Maint',@plan_id=@new_plan_id output
exec msdb.dbo.sp_add_maintenance_plan_db @new_plan_id, 'All System Databases' --line 21
/* entry for max_history_rows field. */
IF EXISTS (SELECT *
FROM msdb.dbo.sysdbmaintplans
WHERE plan_name='System DB Maint')
BEGIN
update msdb.dbo.sysdbmaintplans
set max_history_rows =1000
where plan_id = @new_plan_id
END
/* followed by agent job creations */
When i execute the above - all code part goes through fine but i get this msg.:
Server: Msg 14262, Level 16, State 1, Procedure sp_add_maintenance_plan_db, Line 21
[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @db_name ('All System Databases') does not exist.
When using the data maintenance plan wzd - it shows name, databases, servers, actions. Through my code - i get all displayed except the databases - where i want it to show All System Databases
Using the wzd - the sysdbmaintplan_database, sysdbmaintplan_history table contains the database name as All System Databases (if this is selected thru the wzd).
In the stored proc - sp_add_maintenance_plan_db the 2nd parameter is sysname. But since 'all system databases' doesnt exists in sysdatabases - its breaking at line 21........
Any idea as to why i get the above err msg.
Thnx.
April 19, 2006 at 1:27 pm
This probably not a staright forward one using msdb sprocs as you think,so you would need to work directly with msdb system tables
code like this might help you to create maintenance plan for 'All System Databases', and you need to change some things like schedule time, frequency etc., based on your need..
DECLARE @PlanID nchar(36)
DECLARE @PlanName varchar(50)
SELECT @PlanID = NEWID()
SELECT @PlanName = 'System DB Maint'
INSERT msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
VALUES (@PlanID, @PlanName, 1000,'', 0)
SELECT @PlanID
BEGIN TRANSACTION
DECLARE @ReturnCode INT
DECLARE @JobID6 nchar(36)
DECLARE @Job_Name nvarchar(200)
DECLARE @command nvarchar(1000)
SELECT @Job_Name = 'DB Backup Job for DB Maintenance Plan ''' + @PlanName +''''
SELECT @command = 'EXECUTE master.dbo.xp_sqlmaint N''-PlanID '+ @PlanID + ' -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'''
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID6 OUTPUT, @job_name = @Job_Name, @enabled = 1, @category_id = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID6, @step_id = 1, @step_name = N'Step 1', @subsystem = N'TSQL', @on_success_action = 1, @on_fail_action = 2, @command = @command, @flags = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID6, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 0, @active_start_time = 20000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID6, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
SELECT @JobID6
COMMIT TRANSACTION
GOTO EndSave1
QuitWithRollback1: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave1:
BEGIN TRANSACTION
IF (SELECT COUNT(*) FROM msdb.dbo.sysdbmaintplan_databases WHERE plan_id = @PlanID AND database_name = 'All System Databases') < 1
INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) VALUES (@PlanID, 'All System Databases')
DELETE FROM msdb.dbo.sysdbmaintplan_jobs WHERE plan_id = @PlanID
INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, @JobID6)
COMMIT TRANSACTION
GOTO EndSave2
QuitWithRollback2: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave2:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply