Altering Maintenance Package XML in MSDB?
the dba team i'm working intern working on trying automate deployment of maintenance plans via msx agent.
the problem tasked solving changing reporting , logging file location each of them stored in own folder on target server. since there no way use variable here using ssms gui, solution found search package data in msdb , alter xml directly. (to clear, haven't used method of maintenance plan deployment yet, until test aren't sure if function planned)
one potential problem came don't want end in situation altering data causes our software no longer supported...so need find out whether or not going ok. (any available documentation if possible)
here code (slightly altered security) code:
please note: aware changing xml node rather casting varchar data type more robust...and that's i'm trying figure out. end result should same in regards question. won't offended suggestions improve code ;-)
---------------------------------
-- creates string machine/instance name
declare @instname varchar(50) = '\\storageserver\'+convert(varchar(15),serverproperty('machinename'))+'\'+isnull(convert(varchar(15),serverproperty('instancename')),'')
select @instname
----------------
--check existing #packagedata, drop if exists
if object_id('tempdb..#packagedata') not null
drop table #packagedata
--cast packagedata temp table nvarchar data type
select packagedata = cast(cast(cast(packagedata varbinary(max)) xml) nvarchar(max))
into #packagedata
from msdb.dbo.sysssispackages
where name = 'dbmaintenance'
--replace text file report location in temp
update #packagedata
set packagedata = replace(packagedata, n'c:\program files\microsoft sql server\mssql11.sql2', @instname)
--push updated data temp table msdb
update msdb.dbo.sysssispackages
set packagedata = (
select packagedata = cast(cast(cast(packagedata xml) varbinary(max)) image)
#packagedata
)
where name = 'dbmaintenance'
SQL Server > SQL Server XML
Comments
Post a Comment