May 14, 2012

sp_MSfix_6x_tasks (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSfix_6x_tasks(nvarchar @publisher
, int @publisher_engine_edition)

MetaData:

 create procedure sys.sp_MSfix_6x_tasks   
(
@publisher sysname = NULL
,@publisher_engine_edition int = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @distributor sysname
,@distproc nvarchar (255)
,@retcode int

-- Security Check: require sysadmin
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
begin
raiserror(21089,16,-1)
return (1)
end

-- If @publisher is null redirect the call to distributor
if @publisher is null
begin
--
-- Get distribution server information for remote RPC
-- agent verification.
--
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT
IF @@error <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
--
-- Get the engine edition of the publisher
--
select @publisher_engine_edition = sys.fn_MSrepl_editionid()
--
-- RPC distributor
--
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.master.sys.sp_MSfix_6x_tasks'
EXECUTE @retcode = @distproc
@publisher = @@SERVERNAME
,@publisher_engine_edition = @publisher_engine_edition
IF @@ERROR <> 0 or @retcode <> 0
return (1)
end
else
begin
--
-- We are on distributor
--
declare @category_id int
,@category_name sysname
,@server sysname
,@databasename sysname
,@name sysname
,@distdb sysname
,@job_id uniqueidentifier
,@sSubsystem sysname

-- Drop entry in systasks first.
-- qunguo: removing reference to systasks_view as this is going away in Yukon
-- should be safe to do since this looks to be used only by upgrade from 6.x

-- DECLARE hCtasks CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM msdb.dbo.systasks_view st WHERE

-- drop distribution agents.
(st.name LIKE @publisher + '_' + '%' + '_' + '%' AND
LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'distribution' AND
server = @@SERVERNAME) OR

(LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'logreader' AND
server = @publisher) OR

(LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'snapshot' AND
server = @publisher) OR

(st.name LIKE '%' + '_' + '%' + '_Cleanup' AND
st.command LIKE '%' + 'sp_replcleanup' + '%' AND
LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'tsql')
FOR READ ONLY

OPEN hCtasks
FETCH hCtasks INTO @name

WHILE (@@fetch_status <> -1)
BEGIN

EXEC @retcode = msdb.dbo.sp_droptask @name = @name
if @retcode <> 0 or @@error <> 0
return(1)
FETCH hCtasks INTO @name
END
-- looks like this code is missing close and deallocate of the cursor hCtasks anyway:)

--

-- Now spin through each old replication job and fixup categories names
declare hcJobsToFix CURSOR LOCAL FAST_FORWARD for
select distinct j.job_id, j.name, s.subsystem,
s.server, s.database_name
from msdb.dbo.sysjobs j left join msdb.dbo.sysjobsteps s
on j.job_id = s.job_id
where j.category_id = 0 and s.step_id = 1 and lower(s.subsystem collate SQL_Latin1_General_CP1_CS_AS) in ( 'snapshot', 'logreader', 'distribution' )
for read only

open hcJobsToFix
fetch hcJobsToFix into @job_id, @name, @sSubsystem,
@server,@databasename

while (@@fetch_status <> -1 )
begin
-- Note, have to make it a transaction, once the category_id is changed,
-- the task will never be picked up again.
begin tran

-- Get Distribution category name (assumes category_id = 10)
select @category_id =
case lower(@sSubsystem collate SQL_Latin1_General_CP1_CS_AS)
when 'snapshot' then 15
when 'distribution' then 10
when 'logreader' then 13
else 0
end

select @category_name = name FROM msdb.dbo.syscategories where category_id = @category_id

-- raiserror( 'Would update %s to category %d based on subsystem value %s', -1, 10, @nJobName, @iCategory, @sSubsystem )
exec @retcode = msdb.dbo.sp_update_job @job_id = @job_id,
@category_name = @category_name
if @retcode <> 0 or @@error <> 0
goto UNDO

-- Add the replication agent for monitoring
SELECT @distdb = distribution_db from msdb..MSdistpublishers where UPPER(name collate database_default) = UPPER(@server) collate database_default
IF (@category_id = 13) -- Logreader
BEGIN
SELECT @distproc = QUOTENAME(@distdb) + '.dbo.sp_MSadd_logreader_agent'
EXECUTE @retcode = @distproc
@name = @name,
@publisher = @server,
@publisher_db = @databasename,
@publication = '',
@local_job = 1,
@job_existing = 1,
@job_id = @job_id,
@internal = N'YUKON',
@publisher_engine_edition = @publisher_engine_edition

IF (@retcode <> 0 or @@error<>0)
goto UNDO
END
ELSE
IF (@category_id = 15) -- Snapshot
BEGIN
DECLARE @publication sysname

SELECT @publication = NULL
EXECUTE sys.sp_MSget_publication_from_taskname
@taskname = @name,
@publisher = @server,
@publisherdb = @databasename,
@publication = @publication OUTPUT

IF (@publication IS NOT NULL )
BEGIN
SELECT @distproc = QUOTENAME(@distdb) + '.dbo.sp_MSadd_publication'
EXECUTE @retcode = @distproc
@publisher = @server,
@publisher_db = @databasename,
@publication = @publication,
@publication_type = 0, -- Transactional
@publisher_engine_edition = @publisher_engine_edition
IF (@retcode <> 0 or @@error<>0)
goto UNDO

SELECT @distproc = QUOTENAME(@distdb) + '.dbo.sp_MSadd_snapshot_agent'
EXECUTE @retcode = @distproc
@name = @name,
@publisher = @server,
@publisher_db = @databasename,
@publication = @publication,
@local_job = 1,
@job_existing = 1,
@snapshot_jobid = @job_id,
@internal = N'YUKON'
IF (@retcode <> 0 or @@error<>0)
goto UNDO
END
END
commit tran
fetch hcJobsToFix into @job_id, @name, @sSubsystem,
@server,@databasename
end
close hcJobsToFix
deallocate hcJobsToFix
end
return (0)
UNDO:
if @@trancount <> 0
rollback tran
return (1)
END

No comments:

Post a Comment

Total Pageviews