June 15, 2012

xp_repl_convert_encrypt_sysadmin_wrapper (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.xp_repl_convert_encrypt_sysadmin_wrapper()

MetaData:

 --   
-- Name: xp_repl_convert_encrypt_sysadmin_wrapper
--
-- Descriptions:
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security:
-- Requires Certificate signature for catalog access
--
create procedure sys.xp_repl_convert_encrypt_sysadmin_wrapper
(
@password nvarchar(524) output
)
as
begin
set nocount on
declare @retcode int
--
-- Security Check: require sysadmin
--
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
begin
raiserror(21089,16,-1)
return (1)
end

-- if we have sphinx/shiloh encryption go ahead and convert
if sys.fn_replencryptversion(@password) in (1, 2, 3)
begin
exec @retcode = sys.xp_repl_convert_encrypt @password output
return @retcode
end

return 0
end

xp_revokelogin (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.xp_revokelogin(nvarchar @loginame)

MetaData:

 create procedure sys.xp_revokelogin  
@loginame sysname
AS
set nocount on

-- IF NAME NOT 'DOMAIN\USER', ADD DEFAULT DOMAIN --
if (charindex('\', @loginame) = 0)
begin
select @loginame = default_domain() + '\' + @loginame
end

Declare @ret int -- return value of sp call
execute @ret = sys.sp_revokelogin @loginame
return (@ret)

xp_logininfo (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.xp_logininfo(nvarchar @acctname
, varchar @option)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - xp_logininfo -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create procedure sys.xp_logininfo
@acctname sysname = null, -- IN: NT login name
@option varchar(10) = null, -- IN: 'all' | 'members' | null
@privilege varchar(10) = 'Not wanted' OUTPUT -- OUT: 'admin' | 'user' | null
as
set nocount on

declare @ret int

-- VALIDATE PARAMETERS --
if (@acctname is null AND (@option is not null OR (@privilege is null OR @privilege <> 'Not wanted')))
OR ((@option is null OR @option <> 'all') AND (@privilege is null OR @privilege <> 'Not wanted'))
OR (@option is not null and @option not in ('all', 'members'))
begin
raiserror(15600,-1,-1,'sys.xp_logininfo')
return 1
end


-- HANDLE case where NO @acctname GIVEN --
if (@acctname is null)
begin
select 'account name' = loginname,
'type' = convert(varchar(8), case when isntuser = 1 then 'user' else 'group' end),
'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
'mapped login name' = loginname,
'permission path' = convert(sysname, null)
from master..syslogins where isntname = 1 and hasaccess = 1
order by 3, 1
return @@error
end

-- HANDLE 'members' QUERY --
if (@option = 'members')
begin
declare @priv varchar(8)
select @priv = case when sysadmin = 1 then 'admin' else 'user' end
from master..syslogins where isntname = 1 and loginname = @acctname and hasaccess = 1
if @priv is not null
select 'account name' = domain+N'\'+name,
'type' = convert(varchar(8), case when sidtype = 1 then 'user' else 'group' end),
'privilege' = @priv,
'mapped login name' = domain+N'\'+name,
'permission path' = @acctname
from OpenRowset(TABLE NETGROUPGETMEMBERS, @acctname) order by 3, 1
else
select 'account name' = convert(sysname, null),
'type' = convert(varchar(8), null),
'privilege' = @priv,
'mapped login name' = convert(sysname, null),
'permission path' = convert(sysname, null)
where 0=1 -- empty result set
return @@error
end


-- CREATE TEMP TABLE AND POPULATE WITH THE REQUIRED DATA --
CREATE TABLE #nt (name sysname collate catalog_default, sid varbinary(85), sidtype int)
insert #nt select loginname, sid, isntgroup + 1 from master..syslogins
where isntname = 1 and loginname = @acctname
insert #nt select distinct domain+N'\'+name, sid, sidtype
from OpenRowset(TABLE NETUSERGETGROUPS, @acctname)
select @ret = @@error
if @ret <> 0
return @ret
-- IF ANY DENY, THEN NO ACCESS --
if exists (select * from master..syslogins where sid in (select #nt.sid from #nt) and denylogin = 1)
delete #nt


-- HANDLE case where OUTPUT REQUESTED --
if (@privilege is null OR @privilege <> 'Not wanted')
begin
select @privilege = case max(sysadmin)
when 1 then 'admin'
when 0 then 'user'
else NULL end
from master..syslogins where isntname = 1 and hasaccess = 1
AND sid in (select sid from #nt)
return @@error
end


-- GET NT TYPE FOR NEXT OPTIONS --
declare @type varchar(8)
select @type = case when get_sid('\U'+@acctname) is null then 'group' else 'user' end

-- HANDLE 'all' QUERY --
if (@option = 'all')
begin
select 'account name' = @acctname,
'type' = @type,
'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
'mapped login name' = @acctname,
'permission path' = case when l.loginname = @acctname then NULL else l.loginname end
from master..syslogins l join #nt n on l.isntname = 1 and l.sid = n.sid
where l.loginname = n.name and hasaccess = 1
order by 3, 5
return @@error
end


-- HANDLE DEFAULT QUERY --
select TOP 1
'account name' = @acctname,
'type' = @type,
'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
'mapped login name' = @acctname,
'permission path' = case when l.loginname = @acctname then NULL else l.loginname end
from master..syslogins l join #nt n on l.isntname = 1 and l.sid = n.sid
where l.loginname = n.name and hasaccess = 1
order by 3, 5
return @@error

sp_who2 (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_who2(nvarchar @loginame)

MetaData:

 create procedure sys.sp_who2  -- - 1995/11/03 10:16  
@loginame sysname = NULL
as

set nocount on

declare
@retcode int

declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)

-- defaults

select @retcode = 0 -- 0=good ,1=bad.
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
@spidlow = 0
,@spidhigh = 32767

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
IF (@loginame IS NULL) -- Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from sys.syslogins where loginname = @loginame)
select @sid1 = sid from sys.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL) -- Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end

-- -- -- --

IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active')) -- Special action, not sleeping.
begin
select @loginame = lower(@loginame collate Latin1_General_CI_AS)
GOTO LABEL_17PARM1EDITED
end

-- -- -- --

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) -- Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end

-- -- -- --

raiserror(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


-- -- -- -- -- -- -- -- -- -- Capture consistent sysprocesses. -- -- -- -- -- -- -- -- -- -

select

spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
,request_id

into #tb1_sysprocesses
from sys.sysprocesses_ex with (nolock)

if @@error <> 0
begin
select @retcode = @@error
GOTO LABEL_86RETURN
end

-- -- -- -- Screen out any rows?

if (@loginame in ('active'))
delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) in (
'AWAITING COMMAND'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
)

and blocked = 0



-- -- -- -- Prepare to dynamically optimize column widths.


select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)



select
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)

,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)

,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)

,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)

,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)

,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)

,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)

,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
spid >= @spidlow
and spid <= @spidhigh



-- -- -- -- Output the report.


EXEC(
'
SET nocount off

SELECT
SPID = convert(char(5),spid)

,Status =
CASE lower(status)
When '
'sleeping'' Then lower(status)
Else upper(status)
END

,Login = substring(loginname,1,'
+ @charMaxLenLoginName + ')

,HostName =
CASE hostname
When Null Then '
' .''
When '
' '' Then '' .''
Else substring(hostname,1,'
+ @charMaxLenHostName + ')
END

,BlkBy =
CASE isnull(convert(char(5),blocked),'
'0'')
When '
'0'' Then '' .''
Else isnull(convert(char(5),blocked),'
'0'')
END

,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,'
+ @charMaxLenDBName + ')
,Command = substring(cmd,1,'
+ @charMaxLenCommand + ')

,CPUTime = substring(convert(varchar,cpu),1,'
+ @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,'
+ @charMaxLenDiskIO + ')

,LastBatch = substring(last_batch_char,1,'
+ @charMaxLenLastBatch + ')

,ProgramName = substring(program_name,1,'
+ @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) -- Handy extra for right-scrolling users.
,REQUESTID = convert(char(5),request_id)
from
#tb1_sysprocesses -- Usually DB qualification is needed in exec().
where
spid >= '
+ @charspidlow + '
and spid <= '
+ @charspidhigh + '

order by spid_sort

SET nocount on
'

)


LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses

return @retcode -- sp_who2

sp_xml_schema_rowset2 (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_xml_schema_rowset2(nvarchar @schema_name
, nvarchar @target_namespace)

MetaData:

   
create procedure sys.sp_xml_schema_rowset2
(
@schema_name sysname = null,
@target_namespace sysname = null
)
as
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- copy & pasted from version 1 of the SProc and removed checks for 1st parameter !
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
select
SCHEMACOLLECTION_CATALOGNAME = xsv.SCHEMACOLLECTION_CATALOGNAME,
SCHEMACOLLECTION_SCHEMANAME = xsv.SCHEMACOLLECTION_SCHEMANAME,
SCHEMACOLLECTIONNAME = xsv.SCHEMACOLLECTIONNAME,
TARGETNAMESPACEURI = xsv.TARGETNAMESPACEURI,
SCHEMACONTENT = xsv.SCHEMACONTENT

from
sys.spt_xml_schema_view xsv

where
xsv.xml_collection_id > 1 and -- skip 'sys' entry
(@schema_name is null or xsv.schema_id = schema_id(@schema_name)) and
(@target_namespace is null or @target_namespace = xsv.targetnamespaceuri)

order by 1,2,3,4

xp_grantlogin (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.xp_grantlogin(nvarchar @loginame
, varchar @logintype)

MetaData:

 create procedure sys.xp_grantlogin  
@loginame sysname,
@logintype varchar(5) = Null -- ignored unless 'admin'
AS
set nocount on

-- IF NAME NOT 'DOMAIN\USER', ADD DEFAULT DOMAIN --
if (charindex('\', @loginame) = 0)
begin
select @loginame = default_domain() + '\' + @loginame
end

Declare @ret int -- return value of sp call
execute @ret = sys.sp_grantlogin @loginame
if (@ret = 0 and @logintype = 'admin')
execute @ret = sys.sp_addsrvrolemember @loginame, 'sysadmin'
return (@ret)

sp_xml_schema_rowset (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_xml_schema_rowset(nvarchar @collection_name
, nvarchar @schema_name
, nvarchar @target_namespace)

MetaData:

   
create procedure sys.sp_xml_schema_rowset
(
@collection_name sysname,
@schema_name sysname = null,
@target_namespace sysname = null
)
as
select
SCHEMACOLLECTION_CATALOGNAME = xsv.SCHEMACOLLECTION_CATALOGNAME,
SCHEMACOLLECTION_SCHEMANAME = xsv.SCHEMACOLLECTION_SCHEMANAME,
SCHEMACOLLECTIONNAME = xsv.SCHEMACOLLECTIONNAME,
TARGETNAMESPACEURI = xsv.TARGETNAMESPACEURI,
SCHEMACONTENT = xsv.SCHEMACONTENT

from
sys.spt_xml_schema_view xsv

where
xsv.schemacollectionname = @collection_name and
(@schema_name is null or xsv.schema_id = schema_id(@schema_name)) and
(@target_namespace is null or @target_namespace = xsv.targetnamespaceuri)

order by 1,2,3,4

sp_who (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_who(nvarchar @loginame)

MetaData:

 create procedure sys.sp_who  -- - 1995/11/28 15:48  
@loginame sysname = NULL -- or 'active'
as

declare @spidlow int,
@spidhigh int,
@spid int,
@sid varbinary(85)

select @spidlow = 0
,@spidhigh = 32767


if ( @loginame is not NULL
AND upper(@loginame collate Latin1_General_CI_AS) = 'ACTIVE'
)
begin

select spid , ecid, status
,loginame=rtrim(loginame)
,hostname ,blk=convert(char(5),blocked)
,dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where spid >= @spidlow and spid <= @spidhigh AND
upper(cmd) <> 'AWAITING COMMAND'

return (0)
end

if (@loginame is not NULL
AND upper(@loginame collate Latin1_General_CI_AS) <> 'ACTIVE'
)
begin
if (@loginame like '[0-9]%') -- is a spid.
begin
select @spid = convert(int, @loginame)
select spid, ecid, status,
loginame=rtrim(loginame),
hostname,blk = convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where spid = @spid
end
else
begin
select @sid = suser_sid(@loginame)
if (@sid is null)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
select spid, ecid, status,
loginame=rtrim(loginame),
hostname ,blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where sid = @sid
end
return (0)
end


-- loginame arg is null
select spid,
ecid,
status,
loginame=rtrim(loginame),
hostname,
blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from sys.sysprocesses_ex
where spid >= @spidlow and spid <= @spidhigh

return (0) -- sp_who

sp_vupgrade_mergetables (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_vupgrade_mergetables(bit @remove_repl)

MetaData:

 create procedure sys.sp_vupgrade_mergetables( @remove_repl bit = 0 )  
as
begin

set nocount on

declare @artnick int
declare @objid int
declare @goodlen int
declare @col_track int
declare @article sysname
declare @pubname sysname
declare @artid uniqueidentifier
declare @pubid uniqueidentifier
declare @qualified_name nvarchar(257)
declare @source_owner sysname
declare @source_object sysname
declare @table_name sysname
declare @cmd nvarchar(1000)
declare @default_name nvarchar(258)
declare @retcode integer
declare @snapshot_ready int
declare @constraintname nvarchar(258)
declare @dbname sysname

declare @additive_resolver_clsid nvarchar(60)
declare @average_resolver_clsid nvarchar(60)
declare @download_resolver_clsid nvarchar(60)
declare @max_resolver_clsid nvarchar(60)
declare @mergetxt_resolver_clsid nvarchar(60)
declare @min_resolver_clsid nvarchar(60)
declare @subwins_resolver_clsid nvarchar(60)
declare @upload_resolver_clsid nvarchar(60)
declare @sp_resolver_clsid nvarchar(60)

declare @additive_resolver nvarchar(80)
declare @average_resolver nvarchar(80)
declare @download_resolver nvarchar(80)
declare @max_resolver nvarchar(80)
declare @mergetxt_resolver nvarchar(80)
declare @min_resolver nvarchar(80)
declare @subwins_resolver nvarchar(80)
declare @upload_resolver nvarchar(80)
declare @sp_resolver nvarchar(80)
declare @priority_resolver nvarchar(80)
declare @earlierwins_resolver nvarchar(80)
declare @laterwins_resolver nvarchar(80)

declare @column_name nvarchar(128)
declare @column_type nvarchar(128)
declare @alter_cmd nvarchar(max)

DECLARE @cnt int, @idx int -- Loop counter, index --
DECLARE @columnid smallint -- Columnid-1 = bit to set --
DECLARE @columns binary(128) -- Temporary storage for the converted column --


select @additive_resolver_clsid = '{8FE7FF34-7C5D-4BE7-8056-ADB6D6F692DC}'
select @average_resolver_clsid = '{376F678E-4691-43E8-8AE7-DAD8CAA644EF}'
select @download_resolver_clsid = '{3BB9F418-3407-4F5B-8DB3-9E9147C3A710}'
select @max_resolver_clsid = '{7365BF95-62E8-4B72-A0F7-E238FE413DB7}'
select @mergetxt_resolver_clsid = '{9DCD5250-86BB-433D-8C1F-561460105CF0}'
select @min_resolver_clsid = '{93277AB4-C338-48B8-9A4A-CA5A32587AB7}'
select @subwins_resolver_clsid = '{77E52C5E-0016-4EDF-9391-8C07BFB668CE}'
select @upload_resolver_clsid = '{3D43EBE7-063C-4447-91E7-DE7A264C8441}'
select @sp_resolver_clsid = '{3BB074FA-0836-4A63-BE0C-AF49DDD42A1C}'

select @additive_resolver = 'Microsoft SQL Server Additive Conflict Resolver'
select @average_resolver = 'Microsoft SQL Server Averaging Conflict Resolver'
select @download_resolver = 'Microsoft SQL Server Download Only Conflict Resolver'
select @max_resolver = 'Microsoft SQL Server Maximum Conflict Resolver'
select @mergetxt_resolver = 'Microsoft SQL Server Merge Text Columns Conflict Resolver'
select @min_resolver = 'Microsoft SQL Server Minimum Conflict Resolver'
select @subwins_resolver = 'Microsoft SQL Server Subscriber Always Wins Conflict Resolver'
select @upload_resolver = 'Microsoft SQL Server Upload Only Conflict Resolver'
select @sp_resolver = 'Microsoft SQLServer Stored Procedure Resolver'
select @priority_resolver = 'Microsoft SQL Server Priority Column Resolver'
select @earlierwins_resolver = 'Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver'
select @laterwins_resolver = 'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver'

select @dbname = db_name()

exec @retcode = sys.sp_MSreplcheck_publish
if (@retcode <> 0 or @@error <> 0)
return 1


if object_id('sysmergearticles') is not NULL
begin
-- Update to 110 resolver clsids
update dbo.sysmergearticles set resolver_clsid =
case article_resolver
when @additive_resolver then @additive_resolver_clsid
when @average_resolver then @average_resolver_clsid
when @download_resolver then @download_resolver_clsid
when @max_resolver then @max_resolver_clsid
when @mergetxt_resolver then @mergetxt_resolver_clsid
when @min_resolver then @min_resolver_clsid
when @subwins_resolver then @subwins_resolver_clsid
when @upload_resolver then @upload_resolver_clsid
when @sp_resolver then @sp_resolver_clsid
when @priority_resolver then @max_resolver_clsid
when @earlierwins_resolver then @min_resolver_clsid
when @laterwins_resolver then @max_resolver_clsid
else resolver_clsid
end
end

-- Check if upgrade is needed. If the database is 90 then dispatch to sp_MSmerge_upgrade_from_90rtm
if object_id('sysmergepublications') is not NULL
begin
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'use_partition_groups')
begin
exec @retcode = sys.sp_MSmerge_upgrade_from_90rtm
return @retcode
end
end

begin tran
save tran vupgrade_mergetables

--
* dbo.sysmergepublications
--
if object_id('sysmergepublications') is not NULL
begin
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'backward_comp_level')
begin
alter table dbo.sysmergepublications add backward_comp_level int not NULL default 10 -- defaulted to 70 RTM
if @@error<>0 goto error
end
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = N'allow_partition_realignment' and is_nullable = 0)
alter table dbo.sysmergepublications alter column allow_partition_realignment bit null

end

if @remove_repl=0 and object_id('sysmergepublications') is not NULL
begin
--
* No direct select on sysmergepublications for public
--
-- default_access default no longer used
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and
name = 'default_access')
begin
-- Get the name of the default associated with default_access and drop it
select @default_name = QUOTENAME(object_name(constid))
from sysconstraints
where id = object_id('dbo.sysmergepublications')
and col_name(id, colid) = N'default_access'
if @default_name is not null
begin
-- Drop the default
exec (N'alter table dbo.sysmergepublications drop constraint ' + @default_name)
if @@error<>0 goto error
end

alter table dbo.sysmergepublications drop column default_access
if @@error<>0 goto error
end

--
* Since the ftp_address is now required to enable a publication for internet, publications
* that were enabled for internet can not be upgraded automatically. So the enabled for
* internet option is reset in the upgrade process. New FTP columns added later in script. (Shiloh)
--
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and
name = 'enabled_for_internet')
begin
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'ftp_address')
begin
exec(N'update dbo.sysmergepublications set enabled_for_internet = 0')
end
else
begin
exec(N'update dbo.sysmergepublications set enabled_for_internet = 0 where ftp_address is null')
end
if @@error<>0 goto error
end

declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'snapshot_in_defaultfolder', col_type = 'bit default 0 not null'-- Portable snapshot (Shiloh)
union all
select col_name = 'alt_snapshot_folder', col_type = 'nvarchar(255) null'
union all
select col_name = 'pre_snapshot_script', col_type = 'nvarchar(255) null'-- Snapshot pre/post scripts (Shiloh)
union all
select col_name = 'post_snapshot_script', col_type = 'nvarchar(255) null'
union all
select col_name = 'compress_snapshot', col_type = 'bit default 0 not null'-- Snapshot compression (Shiloh)
union all
select col_name = 'ftp_address', col_type = 'sysname null'-- Ftp support at publication level (Shiloh)
union all
select col_name = 'ftp_port', col_type = 'int not null default 21'
union all
select col_name = 'ftp_subdirectory', col_type = 'nvarchar(255) null'
union all
select col_name = 'ftp_login', col_type = 'sysname null default N''anonymous'''
union all
select col_name = 'ftp_password', col_type = 'nvarchar(524) null'
union all
select col_name = 'conflict_retention', col_type = 'int null' -- Conflict retention (Shiloh)
union all
select col_name = 'keep_before_values', col_type = 'int null'-- Keep partition changes (SQL7.0 SP2 )
union all
select col_name = 'allow_subscription_copy', col_type = 'bit null default 0'-- Attach & Go (Shiloh)
union all
select col_name = 'allow_synctoalternate', col_type = 'bit null default 0'-- Sync to any hub (Shiloh)
union all
select col_name = 'web_synchronization_url', col_type = 'nvarchar(500) null'-- WebSync URL (Yukon)
union all
select col_name = 'retention_period_unit', col_type = 'tinyint default 0 not null'-- 0=day, 1=week, 2=month, 3=year, 4=hour, 5=minute
union all
select col_name = 'validate_subscriber_info', col_type = 'nvarchar(500) NULL'-- Dynamic partition rvalue validation (Shiloh)
union all
select col_name = 'ad_guidname', col_type = 'sysname NULL'-- Active directory registration for publications (Shiloh)
union all
select col_name = 'max_concurrent_merge', col_type = 'int not NULL default 0'-- max_concurrent_merge control the max # of concurrent merge process at publisher side (Shiloh)
union all
select col_name = 'max_concurrent_dynamic_snapshots', col_type = 'int not NULL default 0'-- Maximum number of current dynamic snapshot sessions
union all
select col_name = 'use_partition_groups', col_type = 'smallint NULL'
union all
select col_name = 'dynamic_filters_function_list', col_type = 'nvarchar(500) NULL'-- Semi-colon delimited list of functions used in all dynamic filters used in this publication
union all
select col_name = 'replicate_ddl', col_type = 'int not NULL default 0'-- Bitmask on how this publication accepts new objects
union all
select col_name = 'partition_id_eval_proc', col_type = 'sysname NULL'-- Partition id evaluation proc for this publication
union all
select col_name = 'publication_number', col_type = 'smallint identity NOT NULL' -- publication_number for this publication (just a mapped value to be used locally instead of the 16-byte guid)
union all
select col_name = 'allow_subscriber_initiated_snapshot', col_type = 'bit not NULL default 0'-- allow_subscriber_initiated_snapshot column
union all
select col_name = 'allow_partition_realignment', col_type = 'bit not NULL default 1' -- allow_partition_realignment column
union all
select col_name = 'generation_leveling_threshold', col_type = 'int null default 1000' -- generation leveling threshold
union all
select col_name = 'automatic_reinitialization_policy', col_type = 'bit not null default 0'-- whether or not to upload first on reinits that are triggered by certain publication/article property changes
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergepublications', 'U'))
where t2.name is null) -- This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.sysmergepublications add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 goto error
end
close colcurs
deallocate colcurs

-- allow web sync (Yukon)
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'allow_web_synchronization')
begin
alter table dbo.sysmergepublications add allow_web_synchronization bit null default 0
if @@error<>0 goto error

-- we want to set allow_web_synchronization for ssce subscribers. So if we find a
-- character mode snapshot type we will set this property.
exec('update dbo.sysmergepublications set allow_web_synchronization = 1 where sync_mode = 1')
if @@error<>0 goto error
end

-- insure that index nc2sysmergepublications exists on status
if exists( select * from sys.indexes where name = 'nc2sysmergepublications' AND
object_id = object_id('dbo.sysmergepublications') )
begin
drop index nc2sysmergepublications on dbo.sysmergepublications
end
if exists(select * from syscolumns where id = object_id('sysmergepublications') and name = 'status')
begin
create index nc2sysmergepublications on dbo.sysmergepublications(status)
end


-- insure that default on generation_leveling_threshold is 1000 instead of 0 (or anything else)
declare @defaultname sysname
select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc
on sysdc.parent_object_id = sysc.object_id
and sysdc.parent_column_id = sysc.column_id
where sysdc.parent_object_id = object_id('sysmergepublications')
and sysc.name = 'generation_leveling_threshold'
if @defaultname is not null
begin
select @alter_cmd = 'alter table dbo.sysmergepublications drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd) -- drop old default
end
if exists(select * from syscolumns where id = object_id('sysmergepublications') and name = 'generation_leveling_threshold')
begin
select @alter_cmd = 'alter table dbo.sysmergepublications add default 1000 for generation_leveling_threshold'
exec (@alter_cmd) -- add new default
end

-- conflict logging on both publisher and subscriber
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'decentralized_conflicts')
begin
alter table dbo.sysmergepublications add decentralized_conflicts int null
if @@error<>0 goto error

-- before upgrade, centralized_conflicts==1 means centralized logging,
-- centralized_conflicts==0 means decentralized logging.
-- We now map this to the two explicit columns.
exec ('update dbo.sysmergepublications set decentralized_conflicts=1 where centralized_conflicts=0')
if @@error<>0 goto error
exec ('update dbo.sysmergepublications set decentralized_conflicts=0 where centralized_conflicts=1')
if @@error<>0 goto error
end

--
* sysmergepublications ftp_password
* no need to upgrade passwords since this column is new in 8.0.
--
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'ftp_password' and max_length = '1048')
begin
declare @cmptlevel tinyint
select @cmptlevel = cmptlevel from master.dbo.sysdatabases where name = @dbname collate database_default
if @cmptlevel < 70
begin
raiserror (15048, -1, -1, 70, 70, 70, 80)
end
else
begin
exec( 'alter table dbo.sysmergepublications alter column ftp_password nvarchar(524)' )
if @@error <> 0
goto error
end
end

-- in Yukon snapshot_jobid column has been moved from MSmerge_replinfo to sysmergepublications
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'snapshot_jobid')
begin
-- we cannot do alter table and update in the same batch so make this a dynamic sql
alter table dbo.sysmergepublications add snapshot_jobid binary(16) NULL
if @@error <> 0 goto error
-- get the values of snapshot_jobid from MSmerge_replinfo
if exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'snapshot_jobid')
begin
-- need to exec update in diff process space to avoid syntax error on deferred name resolution at time of proc exec
exec ('update dbo.sysmergepublications
set snapshot_jobid = r.snapshot_jobid
from dbo.sysmergepublications p, dbo.MSmerge_replinfo r
where r.repid = p.pubid'
)
if @@error <> 0
goto error
end
end

-- in Yukon distributor column has been moved from sysmergesubscriptions to sysmergepublications
-- the following is only useful in shiloh to yukon upgrade. In 70 the column distributor did not even exist
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'distributor')
begin
-- we will move the value from sysmergesubscriptions to sysmergepublications
alter table dbo.sysmergepublications add distributor sysname NULL
if @@error<>0 goto error
-- get the values of distributor from MSmerge_replinfo
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'distributor')
and exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'publication')
begin
-- need to exec update in diff process space to avoid syntax error on deferred name resolution at time of proc exec
-- add the colums for republisher's publications into sysmergepublications
exec ('insert into dbo.sysmergepublications (pubid, name, publisher_db, publisher, distributor)
select distinct s.pubid, s.publication, s.db_name, s.subscriber_server, s.distributor
from dbo.sysmergesubscriptions s, dbo.sysmergepublications p
where s.subid = s.pubid and s.pubid not in (select pubid from dbo.sysmergepublications)'
)
if @@error <> 0
goto error
exec ('update dbo.sysmergepublications
set distributor = s.distributor
from dbo.sysmergesubscriptions s, dbo.sysmergepublications p
where s.subid = s.pubid and p.pubid = s.pubid'
)
if @@error <> 0
goto error
end
else
begin
-- this is probably a 70 upgrade
exec ('update dbo.sysmergepublications
set distributor = publisher'
)
if @@error <> 0
goto error
end
end

-- dynamic_snapshot_queue_timeout column this was in Yukon beta2 but has been removed since
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_queue_timeout')
begin
-- now drop the default constraint
select @constraintname = quotename(name) from sys.default_constraints where parent_object_id = object_id('dbo.sysmergepublications') and parent_column_id =
(select column_id from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_queue_timeout')

exec ('alter table dbo.sysmergepublications drop constraint ' + @constraintname)
if @@error<>0 goto error

alter table dbo.sysmergepublications drop column dynamic_snapshot_queue_timeout
if @@error<>0 goto error
end

-- dynamic_snapshot_ready_timeout column this was in Yukon beta2 but has been removed since
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_ready_timeout')
begin
-- now drop the default constraint
select @constraintname = quotename(name) from sys.default_constraints where parent_object_id = object_id('dbo.sysmergepublications') and parent_column_id =
(select column_id from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'dynamic_snapshot_ready_timeout')

exec ('alter table dbo.sysmergepublications drop constraint ' + @constraintname)
if @@error<>0 goto error

alter table dbo.sysmergepublications drop column dynamic_snapshot_ready_timeout
if @@error<>0 goto error
end
end -- end sysmergepublications modifications

-- dbo.MSmerge_contents
if object_id('dbo.MSmerge_contents') is not null and @remove_repl=0
begin
-- insure that index nc4Msmerge_contents exists on rowguid
if exists( select * from sys.indexes where name = 'nc4MSmerge_contents' and
object_id = object_id('MSmerge_contents') )
begin
drop index nc4MSmerge_contents on dbo.MSmerge_contents
end
if exists(select * from syscolumns where id = object_id('MSmerge_contents') and name = 'rowguid')
begin
create index nc4MSmerge_contents on dbo.MSmerge_contents(rowguid)
end
end

-- dbo.MSmerge_genhistory
if object_id('dbo.MSmerge_genhistory') is not null and @remove_repl=0
begin
-- insure that index nc2Msmerge_genhistory exists on rowguid
if exists( select * from sys.indexes where name = 'nc2MSmerge_genhistory' and
object_id = object_id('MSmerge_genhistory') )
begin
drop index nc2MSmerge_genhistory on dbo.MSmerge_genhistory
end
if exists(select * from syscolumns where id = object_id('MSmerge_genhistory') and name = 'genstatus')
and exists(select * from syscolumns where id = object_id('MSmerge_genhistory') and name = 'art_nick')
and exists(select * from syscolumns where id = object_id('MSmerge_genhistory') and name = 'changecount')
begin
create index nc2MSmerge_genhistory on MSmerge_genhistory(genstatus, art_nick,changecount)
end
end

--
* MSmerge_history
* Add new unique idx for correctness iff there are no uniqueness violations. Drop old
* index in favor of new column order in this index. Add new non-clustered index as needed.
--
if exists( select * from sys.indexes where name = 'nc1MSmerge_history' AND
object_id = OBJECT_ID('MSmerge_history'))
begin
drop index dbo.MSmerge_history.nc1MSmerge_history
end
if exists(select * from sys.columns where object_id = object_id('MSmerge_history') and name = 'session_id')
and exists (select * from sys.columns where object_id = object_id('MSmerge_history') and name = 'timestamp')
begin
create nonclustered index nc1MSmerge_history on MSmerge_history(session_id, timestamp)
end

-- dbo.MSmerge_replinfo
if @remove_repl=0 and (object_id('MSmerge_replinfo') is not NULL and
exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'replnickname'))
begin
-- recgen is int in Shiloh and before, bigint in Yukon and after
if 56 = (select system_type_id from sys.columns where
object_id = object_id('MSmerge_replinfo') and name = 'recgen')
begin
alter table dbo.MSmerge_replinfo alter column recgen bigint null
if @@error<>0 goto error
end

-- sentgen is int in Shiloh and before, bigint in Yukon and after
if 56 = (select system_type_id from sys.columns where
object_id = object_id('MSmerge_replinfo') and name = 'sentgen')
begin
alter table dbo.MSmerge_replinfo alter column sentgen bigint null
if @@error<>0 goto error
end

-- replnickname is int in Shiloh and before, binary(6) in Yukon and after
if 56 = (select system_type_id from sys.columns where
object_id = object_id('MSmerge_replinfo') and name = 'replnickname')
begin
begin tran
save tran tran_replinfonick80to90
alter table dbo.MSmerge_replinfo alter column replnickname binary(6) not null
if @@error<>0 goto err_replinfonick80to90
exec ('update dbo.MSmerge_replinfo set replnickname= substring(replnickname, 6, 1) + substring(replnickname, 5, 1) + substring(replnickname, 4, 1) + substring(replnickname, 3, 1) + substring(replnickname, 2, 1) + substring(replnickname, 1, 1)')
if @@error<>0 goto err_replinfonick80to90
commit tran
goto after_replinfonick80to90

err_replinfonick80to90:
rollback tran tran_replinfonick80to90
commit tran
goto error
end

after_replinfonick80to90:

-- this column and its values been added to sysmergepublications in the sysmergepublications if block
if exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'snapshot_jobid')
begin
alter table dbo.MSmerge_replinfo drop column snapshot_jobid
if @@error <> 0 goto error
end

-- hostname column added for Yukon
if not exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'hostname')
begin
alter table dbo.MSmerge_replinfo add hostname sysname NULL
if @@error <> 0 goto error
end
end -- dbo.MSmerge_replinfo

--
* dbo.sysmergesubscriptions
--
-- the following modifications to sysmergesubscriptions have to be done even if replication is being removed
if (object_id('sysmergesubscriptions') is not NULL)
begin
-- subscriber_server (Shiloh)
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
name = 'subscriber_server')
begin
alter table dbo.sysmergesubscriptions add subscriber_server sysname null
if @@error <> 0 goto error

-- need to exec update in diff process space to avoid syntax error on deferred name resolution at time of proc exec
exec( N'update dbo.sysmergesubscriptions set subscriber_server =
(select srvname from master.dbo.sysservers where srvid = dbo.sysmergesubscriptions.srvid)'
)
if @@error <> 0 goto error
end

-- last_makegeneration_datetime
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
name = 'last_makegeneration_datetime')
begin
alter table dbo.sysmergesubscriptions add last_makegeneration_datetime datetime null
if @@error <> 0 goto error
end



if exists (select * from sysconstraints where id = object_id('dbo.sysmergesubscriptions') and
object_name(constid) = 'unique_pubsrvdb')
begin
alter table dbo.sysmergesubscriptions drop constraint unique_pubsrvdb
if @@error <> 0 goto error
end

IF EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc2sysmergesubscriptions' AND
id = object_id('dbo.sysmergesubscriptions') )
begin
drop index nc2sysmergesubscriptions on dbo.sysmergesubscriptions
if @@error <> 0 goto error
end

if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
name = 'srvid')
begin

alter table dbo.sysmergesubscriptions drop column srvid
if @@error <> 0 goto error
end

if not exists (select * from sysconstraints where id = object_id('dbo.sysmergesubscriptions') and
object_name(constid) = 'unique_pubsrvdb')
begin
exec(N'alter table dbo.sysmergesubscriptions
add constraint unique_pubsrvdb
unique nonclustered (pubid, subscriber_server, db_name)'
)
if @@error <> 0 goto error
end
end

if @remove_repl=0 and (object_id('sysmergesubscriptions') is not NULL)
begin
-- rename partnerid to replicastate... since we will be copying the table
-- we will do this first so that the remaining changes will not need to be copied
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'partnerid')
begin
-- because sp_rename does not allow the renaming of tables marked as system objects
-- we have to take this round about way of doing things. This table should always be
-- relatively small so perf here is not a really big concern and should not be affected
-- exec sys.sp_rename @objname = 'sysmergesubscriptions.partnerid', @newname = 'replicastate', @objtype = 'COLUMN'
-- if @@error <> 0 goto error
if object_id(N'sysmergesubscriptions_tmp_name') is not null
begin
drop table sysmergesubscriptions_tmp_name
if @@error <> 0 goto error
end

select *
into sysmergesubscriptions_tmp_name
from dbo.sysmergesubscriptions
if @@error <> 0 goto error

exec sys.sp_rename @objname = 'sysmergesubscriptions_tmp_name.partnerid', @newname = 'replicastate', @objtype = 'COLUMN'
if @@error <> 0
begin
drop table sysmergesubscriptions_tmp_name
goto error
end

drop table sysmergesubscriptions
if @@error <> 0 goto error

exec sys.sp_rename @objname = 'sysmergesubscriptions_tmp_name', @newname = 'sysmergesubscriptions'
if @@error <> 0
begin
drop table sysmergesubscriptions_tmp_name
goto error
end


-- recreate indexes
create unique clustered index uc1sysmergesubscriptions on dbo.sysmergesubscriptions (subid)
if @@error <> 0 goto error

create index nc2sysmergesubscriptions on dbo.sysmergesubscriptions (subscriber_server, db_name)
if @@error <> 0 goto error

-- mark as system object
exec sp_MS_marksystemobject 'sysmergesubscriptions'
if @@error <> 0 goto error
end

declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'use_interactive_resolver', col_type = 'bit NOT NULL default 0'-- Interactive resolver support (Shiloh)
union all
select col_name = 'validation_level', col_type = 'int NOT NULL default 0'-- merge validation level (Shiloh)
union all
select col_name = 'resync_gen', col_type = 'bigint not NULL default -1'
union all
select col_name = 'attempted_validate', col_type = 'datetime NULL' -- date of the last attempted validate (Shiloh)
union all
select col_name = 'last_sync_status', col_type = 'int NULL'-- status of the last sync (Shiloh)
union all
select col_name = 'last_sync_date', col_type = 'datetime NULL'-- date of the last sync (Shiloh)
union all
select col_name = 'last_sync_summary', col_type = 'sysname NULL'-- summary message of the last sync (Shiloh)
union all
select col_name = 'metadatacleanuptime', col_type = 'datetime not NULL default getdate()'-- metadata cleanup time
union all
select col_name = 'cleanedup_unsent_changes', col_type = 'bit NOT NULL default 0'-- cleanedup_unsent_changes(Yukon)
union all
select col_name = 'replica_version', col_type = 'int NOT NULL default 60'-- replica_version (Yukon)
union all
select col_name = 'supportability_mode', col_type = 'int NOT NULL default 0'-- supportability_mode (Yukon)
union all
select col_name = 'application_name', col_type = 'sysname NULL'-- application_name and subscriber_number added in yukon
union all
select col_name = 'subscriber_number', col_type = 'int identity not NULL'
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergesubscriptions', 'U'))
where t2.name is null) -- This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.sysmergesubscriptions add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0
begin
goto error
end

end
close colcurs
deallocate colcurs

-- drop old views
declare @old_view_name sysname
declare drop_old_views_cursor cursor LOCAL FAST_FORWARD FOR
select name from sys.objects where (name like 'ctsv_%' or name like 'tsvw_%') AND type ='V' and ObjectProperty(object_id, 'IsMSShipped')=1
for read only
open drop_old_views_cursor
fetch drop_old_views_cursor into @old_view_name
while(@@fetch_status <> -1)
begin
declare @drop_view_cmd nvarchar(max)
select @drop_view_cmd = N'drop view ' + QUOTENAME(@old_view_name)
exec(@drop_view_cmd)
if @@error <> 0 goto error
fetch drop_old_views_cursor into @old_view_name
end
close drop_old_views_cursor
deallocate drop_old_views_cursor


IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc2sysmergesubscriptions' AND
id = object_id('dbo.sysmergesubscriptions') )
begin
create index nc2sysmergesubscriptions on dbo.sysmergesubscriptions (subscriber_server, db_name)
if @@error <> 0 goto error
end

-- Remove alternate_pubid column from sysmergesubscriptions (Shiloh)
-- This column is dropped in 8.0 Beta 2
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and
name = 'alternate_pubid')
begin
alter table dbo.sysmergesubscriptions drop column alternate_pubid
if @@error <> 0 goto error
end

-- this column and its values been added to sysmergepublications in the sysmergepublications if block (yukon)
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'distributor')
begin
alter table sysmergesubscriptions drop column distributor
if @@error <> 0 goto error
end

-- this column and its values been added to sysmergepublications in the sysmergepublications if block (yukon)
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergesubscriptions') and name = 'publication')
begin
alter table sysmergesubscriptions drop column publication
if @@error <> 0 goto error
end
end



-- move of columns from sysmergesubscriptions to MSmerge_replinfo and vice versa for the sake of
-- better consistency and subscriber tracking.
if @remove_repl=0 and object_id('sysmergesubscriptions') is not NULL and
(object_id('MSmerge_replinfo') is not NULL and
exists (select * from sys.columns where object_id = object_id('MSmerge_replinfo') and name = 'replnickname'))
begin
select *
into #sysmergesubscriptions
from sysmergesubscriptions
if @@error <> 0 goto error

select *
into #MSmerge_replinfo
from MSmerge_replinfo
if @@error <> 0 goto error
begin tran
save tran tran_upgrademergesubtables
drop table dbo.sysmergesubscriptions
if @@error<>0
goto err_upgrademergesubtables

drop table dbo.MSmerge_replinfo
if @@error<>0
goto err_upgrademergesubtables

-- this creates the sysmergesubscription and MSmerge_replinfo tables with new schema
exec @retcode = sys.sp_MSmerge_create_sub_table
if @retcode<>0 or @@error<>0
goto err_upgrademergesubtables
-- insert the values for the new set of columns
exec('insert into dbo.sysmergesubscriptions
(subscriber_server, db_name, pubid, datasource_type, subid, replnickname,
replicastate, status, subscriber_type, subscription_type, sync_type,
description, priority, recgen, recguid, sentgen, sentguid,
schemaversion, schemaguid, last_validated, attempted_validate,
last_sync_date, last_sync_status, last_sync_summary,
metadatacleanuptime, cleanedup_unsent_changes)
select sub.subscriber_server, sub.db_name, sub.pubid, sub.datasource_type, sub.subid, rep.replnickname,
sub.replicastate, sub.status, sub.subscriber_type, sub.subscription_type, sub.sync_type,
sub.description, sub.priority, rep.recgen, rep.recguid, rep.sentgen, rep.sentguid,
rep.schemaversion, rep.schemaguid, sub.last_validated, sub.attempted_validate,
sub.last_sync_date, sub.last_sync_status, sub.last_sync_summary,
sub.metadatacleanuptime, sub.cleanedup_unsent_changes
from #sysmergesubscriptions sub, #MSmerge_replinfo rep
where sub.subid = rep.repid'
)
if @retcode<>0 or @@error<>0
goto err_upgrademergesubtables
exec('insert into dbo.MSmerge_replinfo
(repid, use_interactive_resolver, validation_level, resync_gen,
login_name, merge_jobid)
select rep.repid, sub.use_interactive_resolver, sub.validation_level, sub.resync_gen,
sub.login_name, rep.merge_jobid
from #sysmergesubscriptions sub, #MSmerge_replinfo rep
where sub.subid = rep.repid'
)
if @retcode<>0 or @@error<>0
goto err_upgrademergesubtables

commit tran
goto after_upgrademergesubtables

err_upgrademergesubtables:
rollback tran tran_upgrademergesubtables
commit tran
goto error

after_upgrademergesubtables:
-- if we got here everything was successful.
drop table #sysmergesubscriptions
drop table #MSmerge_replinfo
end

if @remove_repl=0 and object_id('dbo.sysmergesubscriptions') is not NULL
begin
-- Dropping columns use_interactive_resolver, validation_level, resync_gen
if exists (select * from sys.columns where name = N'use_interactive_resolver' and object_id = object_id('dbo.sysmergesubscriptions'))
begin
-- drop constraint on this if it exists
select @defaultname = null -- temp stores constraint name to be removed
select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
(sysdc.parent_object_id = sysc.object_id and
sysdc.parent_column_id = sysc.column_id)
where sysc.object_id = object_id('dbo.sysmergesubscriptions') and
sysc.name = 'use_interactive_resolver'
if @defaultname is not null
begin
select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd)
end
-- drop column for Yukon
select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop column use_interactive_resolver'
exec (@alter_cmd)
end
if exists (select * from sys.columns where name = N'validation_level' and object_id = object_id('dbo.sysmergesubscriptions'))
begin
-- drop constraint on this if it exists
select @defaultname = null -- temp stores constraint name to be removed
select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
(sysdc.parent_object_id = sysc.object_id and
sysdc.parent_column_id = sysc.column_id)
where sysc.object_id = object_id('dbo.sysmergesubscriptions') and
sysc.name = 'validation_level'
if @defaultname is not null
begin
select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd)
end
-- drop column for Yukon
select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop column validation_level'
exec (@alter_cmd)
end
if exists (select * from sys.columns where name = N'resync_gen' and object_id = object_id('dbo.sysmergesubscriptions'))
begin
-- constraint (default) must be deleted before we can delete resync_gen
select @defaultname = null -- temp stores constraint name to be removed
select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
(sysdc.parent_object_id = sysc.object_id and
sysdc.parent_column_id = sysc.column_id)
where sysc.object_id = object_id('dbo.sysmergesubscriptions') and
sysc.name = 'resync_gen'
if @defaultname is not null
begin
select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd)
end
-- drop column for Yukon
select @alter_cmd = 'alter table dbo.sysmergesubscriptions drop column resync_gen'
exec (@alter_cmd)
end

-- insure index nc3sysmergesubscriptions exists on replnickname
if exists( select * from sys.indexes where name = 'nc3sysmergesubscriptions' and object_id = object_id('dbo.sysmergesubscriptions') )
begin
drop index dbo.sysmergesubscriptions.nc3sysmergesubscriptions
end
if exists(select * from syscolumns where id = object_id('dbo.sysmergesubscriptions') and name = 'replnickname')
begin -- if column replnickname exists create the index on it
create index nc3sysmergesubscriptions on dbo.sysmergesubscriptions(replnickname)
end
end


--
* dbo.sysmergearticles
--
-- the following have to be done for sp_MSremovedbreplication to work correctly
-- even we are going to remove replication
if object_id('sysmergearticles') is not NULL
begin
declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'lightweight', col_type = 'bit not null default 0'
union all
select col_name = 'before_upd_view_objid', col_type = 'int NULL'
union all
select col_name = 'metadata_select_proc', col_type = 'sysname NULL'
union all
select col_name = 'delete_proc', col_type = 'sysname NULL'
union all
select col_name = 'before_image_objid', col_type = 'int NULL'-- Keep partition changes (SQL7.0 SP2)
union all
select col_name = 'before_view_objid', col_type = 'int NULL'
union all
select col_name = 'preserve_rowguidcol', col_type = 'bit not null default 1'
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergearticles', 'U'))
where t2.name is null) -- This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.sysmergearticles add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 goto error
end
close colcurs
deallocate colcurs
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = N'delete_tracking' and is_nullable = 0)
alter table dbo.sysmergearticles alter column delete_tracking bit null
end

if @remove_repl=0 and object_id('sysmergearticles') is not NULL
begin
-- Set all invalid dbo.sysmergearticles.sync_objid to the corresponding
-- objid, this will allow regeneration of article procs to succeed
update dbo.sysmergearticles
set sync_objid = objid
where object_name(sync_objid) is null
if @@error <> 0 goto error

-- Make sure that article type in dbo.sysmergearticles is not null
-- for upgraded republisher
update dbo.sysmergearticles
set type = 0x0a
where type is null
if @@error <> 0 goto error

-- Turn on the trigger schema option, FK schema option, check
-- constraint schema option, and default schema option by default
-- so merge articles will retain the old scripting behaviour (Shiloh)
-- Also reset the 0x8000 option (PKUK as constraints) as it is
-- deprecated starting from yukon.
update dbo.sysmergearticles
set schema_option = (convert(bigint, schema_option) | convert(bigint, 0x00000F00)) & ~convert(bigint, 0x00008000)
from sys.objects so
inner join dbo.sysmergearticles sa
on so.object_id = sa.objid
where so.type = 'U' -- table articles only
if @@error <> 0 goto error

-- Add new non-clustered idx on nickname.
if not exists (select * from sys.indexes where name = 'nc1sysmergearticles')
begin
create nonclustered index nc1sysmergearticles on dbo.sysmergearticles(nickname)
if @@error <> 0 goto error
end

-- Resolver info column datatype change
if exists( select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = 'resolver_info' )
begin
alter table dbo.sysmergearticles alter column resolver_info nvarchar(517) NULL
if @@error <> 0 goto error
end

exec @retcode = sys.sp_MSUpgradeConflictTable
if @@ERROR<>0 or @retcode<>0
goto error

if object_id('MSmerge_delete_conflicts') is not NULL
begin
drop table dbo.MSmerge_delete_conflicts
if @@error <> 0 goto error
end

-- Update the columns column sysmergearticles by counting the number of columns int the
table. 70 did not have vertical partitioning so the columns column is NULL --
declare articlescurs cursor LOCAL FAST_FORWARD
for (select sma.name, sma.objid, sma.pubid from dbo.sysmergearticles sma
where sma.columns is NULL and sys.fn_MSmerge_islocalpubid(pubid) = 1)

for read only
open articlescurs
fetch articlescurs into @article, @objid, @pubid
while(@@fetch_status <> -1)
begin


SELECT @cnt = max(column_id), @idx = 1 FROM sys.columns WHERE object_id = @objid
SELECT @columns = NULL
WHILE @idx <= @cnt
BEGIN
-- to make sure column holes will not be included --
if exists (select * from sys.columns where column_id=@idx and object_id=@objid and
(is_computed<>1 and system_type_id <> type_id('timestamp')))
begin
exec sys.sp_MSsetbit @bm=@columns OUTPUT, @coltoadd=@idx, @toset = 1
if @@ERROR<>0 or @retcode<>0
begin
close articlescurs
deallocate articlescurs
goto error
end

end
SELECT @idx = @idx + 1
END
UPDATE dbo.sysmergearticles SET columns = @columns WHERE name = @article AND pubid = @pubid

fetch articlescurs into @article, @objid, @pubid
end

close articlescurs
deallocate articlescurs

if @@error <> 0 goto error

if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and
name = 'excluded_col_count')
begin
-- make sure 7.5's column bit map does not get messed up.
if exists (select * from dbo.sysmergearticles)
begin
-- enough to hold 246 columns in one table - after upgrade all columns are in partition
-- as in sphinx there is no way to kick any of them out.
-- in case a publication exists in downlevel subscriber without having ran snapshot.
-- missing_col_count would be NULL, which can cause problems when being upgraded to
-- latest version.
update dbo.sysmergearticles set missing_col_count=0,missing_cols=0x00 where missing_col_count is NULL
if @@error <> 0 goto error
end

alter table dbo.sysmergearticles add excluded_col_count int NULL
if @@error <> 0 goto error
end

declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'gen_cur', col_type = 'bigint NULL'-- Current generation for new generation assignment added in SQL7.0 SP1
union all
select col_name = 'excluded_cols', col_type = 'varbinary(128) NULL'-- Vertical Partitioning (Shiloh)
union all
select col_name = 'vertical_partition', col_type = 'int NULL'
union all
select col_name = 'identity_support', col_type = 'int default 0 NOT NULL '-- Auto identity management (Shiloh)
union all
select col_name = 'destination_owner', col_type = 'sysname default ''dbo'' not NULL'-- Destination owner support (Shiloh)
union all
select col_name = 'verify_resolver_signature', col_type = 'int NULL default 1'-- Resolver certificate support (Shiloh)
union all
select col_name = 'allow_interactive_resolver', col_type = 'bit NOT NULL default 0'-- Interactive resolver (Shiloh)
union all
select col_name = 'published_in_tran_pub', col_type = 'bit NOT NULL default 0'
union all
select col_name = 'fast_multicol_updateproc', col_type = 'bit NOT NULL default 0'-- Whether update proc should do one update per column or multiple columns in one update (Shiloh)
union all
select col_name = 'check_permissions', col_type = 'int NOT NULL default 0'
union all
select col_name = 'processing_order', col_type = 'int NOT NULL default 0'
union all
select col_name = 'maxversion_at_cleanup', col_type = 'int not null default 1'
union all
select col_name = 'upload_options', col_type = 'tinyint not null default 0'
union all
select col_name = 'procname_postfix', col_type = 'nchar(32) null'
union all
select col_name = 'well_partitioned_lightweight', col_type = 'bit null'
union all
select col_name = 'delete_tracking', col_type = 'bit not null default 1'
union all
select col_name = 'compensate_for_errors', col_type = 'bit not null default 0'
union all
select col_name = 'pub_range', col_type = 'bigint null'
union all
select col_name = 'range', col_type = 'bigint NULL'
union all
select col_name = 'threshold', col_type = 'int NULL'
union all
select col_name = 'stream_blob_columns', col_type = 'bit not NULL default 0'
union all
select col_name = 'deleted_cols', col_type = 'varbinary(128) NULL default 0x0'
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.sysmergearticles', 'U'))
where t2.name is null) -- This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.sysmergearticles add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 goto error
end
close colcurs
deallocate colcurs

-- add default constraint on verify_resolver_signature column
if not exists(
select *
from sysconstraints as con join sys.columns as col
on con.colid = col.column_id
and con.id = col.object_id
and OBJECTPROPERTY ( con.constid , 'IsDefaultCnst' ) = 1
and col.object_id = object_id('dbo.sysmergearticles')
and col.name = 'verify_resolver_signature')and exists
(select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = 'verify_resolver_signature')
begin
exec('alter table dbo.sysmergearticles add default 1 for verify_resolver_signature')
if @@error <> 0 goto error
end


-- change default constraint on compensate_for_errors column to be 0 and also update all values for this column to be 0
-- we think that compensate_for_errors=1 (sql2k default)is not very usefull and can cause more harm then good.
if exists(select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and name = 'compensate_for_errors')
begin
declare @default_compensate_for_errors_constraint_name nvarchar(258)
select @default_compensate_for_errors_constraint_name = obj.name
from sysconstraints as con join sys.columns as col
on con.colid = col.column_id
and con.id = col.object_id
and OBJECTPROPERTY ( con.constid , 'IsDefaultCnst' ) = 1
and col.object_id = object_id('dbo.sysmergearticles')
and col.name = 'compensate_for_errors'
join sys.objects as obj
on obj.object_id=con.constid

if(@default_compensate_for_errors_constraint_name is not null)
begin
select @default_compensate_for_errors_constraint_name = quotename(@default_compensate_for_errors_constraint_name)
exec ('alter table dbo.sysmergearticles drop constraint ' + @default_compensate_for_errors_constraint_name)
if @@error <> 0 goto error
end
exec('alter table dbo.sysmergearticles add default 0 for compensate_for_errors')
if @@error <> 0 goto error
exec('update dbo.sysmergearticles set compensate_for_errors = 0 ')
if @@error <> 0 goto error
end

-- gen_cur is int in SQL8 and earlier, bigint in SQL9
if 56 = (select system_type_id from sys.columns where
object_id = object_id('dbo.sysmergearticles') and name = 'gen_cur')
begin
alter table dbo.sysmergearticles alter column gen_cur bigint null
if @@error <> 0 goto error
end

-- Set default value of column destination_owner if NULL - could happen if first upgraded
-- from 7.0 to Beta 2, which does not have the default value and then to 80 RTM.

-- Destination owner support (Shiloh)
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergearticles') and
name = 'destination_owner')
begin
exec ('update dbo.sysmergearticles set destination_owner=''dbo'' where destination_owner is NULL')
if @@error <> 0 goto error
end

if not exists (select * from sys.indexes where name = 'nc2sysmergearticles')
begin
create nonclustered index nc2sysmergearticles on sysmergearticles(processing_order)
if @@error <> 0 goto error
end

if not exists (select * from sys.indexes where name = 'nc3sysmergearticles')
begin
create unique nonclustered index nc3sysmergearticles on dbo.sysmergearticles(objid, pubid)
if @@ERROR <> 0 goto error
end
--
-- add default for compensate_for_errors
--
if not exists (select dc.name
from sys.default_constraints as dc
join sys.columns as c
on dc.parent_object_id = c.object_id
and dc.parent_column_id = c.column_id
where c.object_id = object_id(N'dbo.sysmergearticles')
and c.name = N'compensate_for_errors')
begin
alter table dbo.sysmergearticles add default 0 for compensate_for_errors
end
--
-- add default for excluded_col_count
--
if not exists (select dc.name
from sys.default_constraints as dc
join sys.columns as c
on dc.parent_object_id = c.object_id
and dc.parent_column_id = c.column_id
where c.object_id = object_id(N'dbo.sysmergearticles')
and c.name = N'excluded_col_count')
begin
alter table dbo.sysmergearticles add default 0 for excluded_col_count
end
--
-- add default for vertical_partition
--
if not exists (select dc.name
from sys.default_constraints as dc
join sys.columns as c
on dc.parent_object_id = c.object_id
and dc.parent_column_id = c.column_id
where c.object_id = object_id(N'dbo.sysmergearticles')
and c.name = N'vertical_partition')
begin
alter table dbo.sysmergearticles add default 0 for vertical_partition
end
--
-- remove the default for column destination_owner
--
select @column_name = NULL
select @column_name = dc.name
from sys.default_constraints as dc
join sys.columns as c
on dc.parent_object_id = c.object_id
and dc.parent_column_id = c.column_id
where c.object_id = object_id('dbo.sysmergearticles')
and c.name = N'destination_owner'
if (@column_name is not null)
begin
select @alter_cmd = N'alter table dbo.sysmergearticles drop constraint ' + quotename(@column_name)
exec(@alter_cmd)
if @@error <> 0
return 1
end

-- this table exists at publisher and subscriber dbs
if object_id('MSmerge_identity_range') is NULL
begin
create table dbo.MSmerge_identity_range (
subid uniqueidentifier not NULL,
artid uniqueidentifier not NULL,
range_begin numeric(38,0) NULL,
range_end numeric(38,0) NULL,
next_range_begin numeric(38,0) NULL,
next_range_end numeric(38,0) NULL,
is_pub_range bit not NULL,
max_used numeric(38,0) NULL
)
if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_identity_range
if @@error <> 0 goto error

create unique clustered index uclidrange on MSmerge_identity_range(subid, artid, is_pub_range)
if @@error <> 0 goto error
end

if object_id('MSmerge_settingshistory') is NULL
begin
-- raiserror('Creating table MSmerge_settingshistory',0,1)

-- This table records the history of when merge related settings
-- were changed. It can also bo used to record important events
-- that affect behavior of merge replication.

-- eventtype can have one of the following values
-- 1 Initial publication level property setting.
-- 2 Change in publication property.
-- 101 Initial article level property setting.
-- 102 Change in article property.
-- In future add publication related event below 100 and
-- article related events about 100 to make searching easier

create table dbo.MSmerge_settingshistory
(
eventtime datetime null default getdate(),
pubid uniqueidentifier NOT NULL,
artid uniqueidentifier NULL,
eventtype tinyint NOT NULL,
propertyname sysname NULL,
previousvalue sysname NULL,
newvalue sysname NULL,
eventtext nvarchar(2000) NULL
)

if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_settingshistory
if @@error <> 0 goto error

create clustered index c1MSmerge_settingshistory on MSmerge_settingshistory(pubid,eventtype)
if @@error <> 0 goto error

end
else
begin
if exists (select * from sys.columns where object_id = object_id('dbo.MSmerge_settingshistory') and name = N'eventtime' and is_nullable = 0)
alter table dbo.MSmerge_settingshistory alter column eventtime datetime null
end

if object_id('sysmergepartitioninfo') is NULL
begin
create table dbo.sysmergepartitioninfo
(
artid uniqueidentifier NOT NULL,
pubid uniqueidentifier NOT NULL,
partition_view_id int NULL,
repl_view_id int NULL,
partition_deleted_view_rule nvarchar(max) NULL,
partition_inserted_view_rule nvarchar(max) NULL,
membership_eval_proc_name sysname NULL,
column_list nvarchar(max) NULL,
column_list_blob nvarchar(max) NULL,
expand_proc sysname NULL,
logical_record_parent_nickname int NULL,
logical_record_view int NULL,
logical_record_deleted_view_rule nvarchar(max) NULL,
logical_record_level_conflict_detection bit null default 0,
logical_record_level_conflict_resolution bit null default 0,
partition_options tinyint null default 0
)
if @@error <> 0 goto error

create unique clustered index uc1sysmergepartitioninfo
on dbo.sysmergepartitioninfo(artid, pubid)
if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject sysmergepartitioninfo
if @@error <> 0 goto error

-- we need to insert a row for every article in sysmergearticles into sysmergepartitioninfo
insert dbo.sysmergepartitioninfo (artid, pubid)
select artid, pubid from dbo.sysmergearticles
if @@error <> 0 goto error

end
else
begin
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepartitioninfo') and name = N'logical_record_level_conflict_detection' and is_nullable = 0)
alter table dbo.sysmergepartitioninfo alter column logical_record_level_conflict_detection bit null
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepartitioninfo') and name = N'logical_record_level_conflict_resolution' and is_nullable = 0)
alter table dbo.sysmergepartitioninfo alter column logical_record_level_conflict_resolution bit null
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergepartitioninfo') and name = N'partition_options' and is_nullable = 0)
alter table dbo.sysmergepartitioninfo alter column partition_options tinyint null
end

if object_id('sysmergepartitioninfoview') is not NULL
begin
drop view dbo.sysmergepartitioninfoview
end

exec ('create view dbo.sysmergepartitioninfoview as
select sma.*, smaw.partition_view_id,
smaw.repl_view_id,
smaw.partition_deleted_view_rule,
smaw.partition_inserted_view_rule,
smaw.membership_eval_proc_name,
smaw.column_list,
smaw.column_list_blob,
smaw.expand_proc,
smaw.logical_record_parent_nickname,
smaw.logical_record_view,
smaw.logical_record_deleted_view_rule,
smaw.logical_record_level_conflict_detection,
smaw.logical_record_level_conflict_resolution,
smaw.partition_options
from dbo.sysmergearticles sma, dbo.sysmergepartitioninfo smaw
where sma.artid = smaw.artid and sma.pubid = smaw.pubid'
)
if @@error <> 0 goto error

exec dbo.sp_MS_marksystemobject sysmergepartitioninfoview
if @@error <> 0 goto error

-- Schema only articles (Shiloh)
if object_id('dbo.sysmergeschemaarticles', 'U') is null
begin
exec @retcode= sys.sp_MScreate_sysmergeschemaarticles_table
if @@error <> 0 or @retcode <> 0 goto error
end
else
begin
if not exists (select * from sys.columns where object_id = object_id('sysmergeschemaarticles') and
name = 'processing_order')
begin
alter table sysmergeschemaarticles add processing_order int NOT NULL default 0
if @@error <> 0 goto error
end
end

-- create view now that sysmergearticles is altered and sysmergeextendedarticles is created
if object_id('sysmergeextendedarticlesview') is not NULL
begin
drop view dbo.sysmergeextendedarticlesview
end

-- cannot create view directly in proc
exec @retcode= sys.sp_MScreate_sysmergeextendedarticlesview
if @@error<>0 or @retcode<>0 goto error

exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview

-- vertical partitioning requires a view based sync obj; SQL7.0 used zero as sync_objid
-- when a non-partitioned article was created; later versions must use the explicit base table
-- as sync_objid; fixup article sync_objid's prior to remaking the article procs (Shiloh)
update dbo.sysmergearticles set sync_objid = objid where sync_objid = 0

-- Do not regenerate views, procs if this is called from sp_restoredbreplication. Restore only
-- needs to update schema, then it can call existing system procs to remove db replication cleanly
if @remove_repl = 0
begin
-- when upgrading to yukon we have a huge amount of metadata upgrade to do. We do not want to
-- do this as part of the upgrade or restore process. We want the subsequent merge or snapshot to
-- take care of it. Hence here we will drop all triggers on publisher tables and create triggers
-- which do not allow the DML to happen. When the snapshot or merge has been run valid triggers will
-- be created and change tracking with work fine after the metadata has been upgraded.
select @artnick = min(nickname) from dbo.sysmergearticles
while @artnick is not null
begin
select @objid = NULL
select @source_object = NULL
select top 1 @objid = objid, @artid = artid from dbo.sysmergearticles where nickname = @artnick
select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid
if @objid is NULL or @source_object is NULL
goto error

exec @retcode = sys.sp_MSdroparticletriggers @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- generate the disable dml trigger
exec sys.sp_MScreatedisabledmltrigger @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- set the article status to inactive so that the subsequent snapshot prepares the article
update dbo.sysmergearticles set status = 1 where artid = @artid and status = 2
update dbo.sysmergearticles set status = 5 where artid = @artid and status = 6

-- find next article
select @artnick = min(nickname) from dbo.sysmergearticles where nickname > @artnick
end -- end article while
end -- end @remove_repl

-- MSmerge_errorlineage (Shiloh)
if object_id('MSmerge_errorlineage') is NULL
begin
create table dbo.MSmerge_errorlineage (
tablenick int NOT NULL,
rowguid uniqueidentifier NOT NULL,
lineage varbinary(311) null
)
exec dbo.sp_MS_marksystemobject MSmerge_errorlineage
if @@ERROR <> 0
goto error

create unique clustered index uc1errorlineage on MSmerge_errorlineage(tablenick, rowguid)
if @@ERROR <> 0
goto error
end
else
begin
if exists (select * from sys.columns where object_id = object_id('dbo.MSmerge_errorlineage') and name = N'lineage' and is_nullable = 0)
alter table dbo.MSmerge_errorlineage alter column lineage varbinary(311) null
end

-- lineage is varbinary(255) in Shiloh and before, varbinary(311) in Yukon and after
-- in addition, the format has changed from 4 to 6 byte nicknames, and there are new mergenicks
if 311 > col_length('MSmerge_errorlineage', 'lineage')
begin
begin tran
save tran tran_errlin80to90
alter table dbo.MSmerge_errorlineage alter column lineage varbinary(311) not null
if @@error<>0 goto err_errlin80to90
update dbo.MSmerge_errorlineage set lineage= {fn LINEAGE_80_TO_90(lineage)}
if @@error<>0 goto err_errlin80to90
commit tran
goto after_errlin80to90

err_errlin80to90:
rollback tran tran_errlin80to90
commit tran
goto error
end

after_errlin80to90:

-- MSmerge_altsyncpartners (Shiloh)
if object_id('MSmerge_altsyncpartners') is NULL
begin
create table dbo.MSmerge_altsyncpartners (
subid uniqueidentifier not null,
alternate_subid uniqueidentifier not null,
description nvarchar(255) NULL
)
if @@ERROR <> 0
goto error

exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners
if @@ERROR <> 0
goto error

create unique clustered index uciMSmerge_altsyncpartners on
dbo.MSmerge_altsyncpartners(subid, alternate_subid)
if @@ERROR <> 0
goto error
end

-- new tables added for the first time after SQL2000.
if object_id('MSmerge_partition_groups') is NULL
begin
create table dbo.MSmerge_partition_groups (partition_id int identity not null primary key clustered,
publication_number smallint not null, maxgen_whenadded bigint null,
using_partition_groups bit null default 0, is_partition_active bit default 1 not null)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_partition_groups
if @@ERROR <> 0 goto error

create nonclustered index nc1MSmerge_partition_groups on dbo.MSmerge_partition_groups (publication_number)
if @@ERROR <> 0 goto error

grant select on dbo.MSmerge_partition_groups to public
end
else
begin
-- Column is_partition_active was added after IDW15 for SQL 2005
if not exists (select * from sys.columns where object_id = object_id('MSmerge_partition_groups') and
name = 'is_partition_active')
begin
alter table MSmerge_partition_groups add is_partition_active bit default 1 not null
if @@error <> 0 goto error
end

if exists (select * from sys.columns where object_id = object_id('dbo.MSmerge_partition_groups') and name = N'using_partition_groups' and is_nullable = 0)
alter table dbo.MSmerge_partition_groups alter column using_partition_groups bit null
end

if object_id('MSmerge_generation_partition_mappings') is NULL
begin
create table dbo.MSmerge_generation_partition_mappings
(
publication_number smallint not null,
generation bigint not null,
partition_id int not null,
changecount int NOT NULL default 0
)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_generation_partition_mappings
if @@ERROR <> 0 goto error

create clustered index cMSmerge_generation_partition_mappings on dbo.MSmerge_generation_partition_mappings (partition_id, publication_number)
if @@ERROR <> 0 goto error

create nonclustered index nc1MSmerge_generation_partition_mappings on dbo.MSmerge_generation_partition_mappings (generation) include (changecount)
if @@ERROR <> 0 goto error
end

if object_id('MSmerge_current_partition_mappings') is NULL
begin
create table dbo.MSmerge_current_partition_mappings (publication_number smallint not null, tablenick int not null, rowguid uniqueidentifier not null, partition_id int not null)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_current_partition_mappings
if @@ERROR <> 0 goto error

create clustered index cMSmerge_current_partition_mappings on dbo.MSmerge_current_partition_mappings (tablenick, rowguid)
if @@ERROR <> 0
goto error

create nonclustered index ncMSmerge_current_partition_mappings on dbo.MSmerge_current_partition_mappings (publication_number, partition_id)
if @@ERROR <> 0
goto error

end

if object_id('MSmerge_past_partition_mappings') is NULL
begin
create table dbo.MSmerge_past_partition_mappings (publication_number smallint not null, tablenick int not null, rowguid uniqueidentifier not null, partition_id int not null, generation bigint null, reason tinyint not null default(0))
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_past_partition_mappings
if @@ERROR <> 0 goto error

create clustered index cMSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (tablenick, rowguid)
if @@ERROR <> 0
goto error

create nonclustered index nc1MSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (publication_number, partition_id)
if @@ERROR <> 0
goto error

create nonclustered index nc2MSmerge_past_partition_mappings on dbo.MSmerge_past_partition_mappings (generation)
if @@ERROR <> 0
goto error
end

if object_id('MSmerge_dynamic_snapshots') is NULL
begin
create table dbo.MSmerge_dynamic_snapshots (
partition_id int not null primary key clustered foreign key references dbo.MSmerge_partition_groups(partition_id) on delete cascade,
dynamic_snapshot_location nvarchar(255) null,
last_updated datetime null,
last_started datetime null)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_dynamic_snapshots
if @@ERROR <> 0 goto error
end
else
begin
if not exists (select * from sys.columns where object_id = object_id('MSmerge_dynamic_snapshots') and
name = 'last_started')
begin
alter table MSmerge_dynamic_snapshots add last_started datetime NULL
if @@error <> 0 goto error
end
end

-- Added in Yukon
if object_id('MSmerge_supportability_settings') is NULL
begin
create table dbo.MSmerge_supportability_settings (
pubid uniqueidentifier NULL,
subid uniqueidentifier NULL,
web_server sysname NULL,
constraint unique_supportpubsrvdb unique nonclustered (pubid, subid, web_server),
support_options int NOT NULL default(0), -- check the SUPPORT_OPTIONS enum in agent code.
log_severity int NOT NULL default(2),
log_modules int NOT NULL default(0),
log_file_path nvarchar(255) NULL,
log_file_name sysname NULL,
log_file_size int NOT NULL default(10000000),
no_of_log_files int NOT NULL default(5),
upload_interval int NOT NULL default(0),
delete_after_upload int NOT NULL default(0),
custom_script nvarchar(2048) NULL,
message_pattern nvarchar(2000) NULL,
last_log_upload_time datetime NULL,
agent_xe varbinary(max) NULL,
agent_xe_ring_buffer varbinary(max) NULL,
sql_xe varbinary(max) NULL
)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_supportability_settings
end
else
begin
-- Column agent_xe was added for SQL 11
if not exists (select * from sys.columns where object_id = object_id('MSmerge_supportability_settings') and
name = 'agent_xe')
begin
alter table MSmerge_supportability_settings add agent_xe varbinary(max)
if @@ERROR <> 0 goto error
end

-- Column agent_xe_ring_buffer was added for SQL 11
if not exists (select * from sys.columns where object_id = object_id('MSmerge_supportability_settings') and
name = 'agent_xe_ring_buffer')
begin
alter table MSmerge_supportability_settings add agent_xe_ring_buffer varbinary(max)
if @@ERROR <> 0 goto error
end

-- Column sql_xe was added for SQL 11
if not exists (select * from sys.columns where object_id = object_id('MSmerge_supportability_settings') and
name = 'sql_xe')
begin
alter table MSmerge_supportability_settings add sql_xe varbinary(max)
if @@ERROR <> 0 goto error
end

end

-- Added in Yukon
if object_id('MSmerge_log_files') is NULL
begin
create table dbo.MSmerge_log_files (
id int identity(1,1),
pubid uniqueidentifier NULL,
subid uniqueidentifier NULL,
web_server sysname NULL,
file_name nvarchar(2000) NOT NULL,
upload_time datetime NOT NULL default getdate(),
log_file_type int NOT NULL, -- Check UPLOAD_LOG_FILE_TYPE enum in agent code.
log_file varbinary(max) NULL
)
if @@ERROR <> 0 goto error

create clustered index ucMSmerge_log_files on MSmerge_log_files(pubid, subid, id)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_log_files
end

-- Added in Yukon
if object_id('dbo.MSmerge_metadataaction_request', 'U') is null
begin
create table dbo.MSmerge_metadataaction_request
(
tablenick int not null,
rowguid uniqueidentifier not null,
action tinyint not null,
generation bigint null, -- for hws cleanup
changed int null -- for lws cleanup
)
if @@ERROR <> 0 goto error

create clustered index ucMSmerge_metadataaction_request on MSmerge_metadataaction_request(tablenick, rowguid)
if @@ERROR <> 0 goto error

exec dbo.sp_MS_marksystemobject MSmerge_metadataaction_request
if @@ERROR <> 0 goto error
end

-- Added in Yukon
if object_id('dbo.MSmerge_agent_parameters', 'U') is null
begin
-- raiserror('Creating table MSmerge_agent_parameters',0,1)

create table dbo.MSmerge_agent_parameters
(
profile_name sysname NOT NULL,
parameter_name sysname NOT NULL,
value nvarchar(255) NOT NULL
)

if @@ERROR <> 0
goto error

exec dbo.sp_MS_marksystemobject MSmerge_agent_parameters
if @@ERROR <> 0
goto error
end

-- we will now set the snapshot_ready status of all local publications. We invalidate the snapshot
-- so that the metadata upgrade can be run at snapshot time.
update dbo.sysmergepublications set snapshot_ready=2
where UPPER(publisher) collate database_default = UPPER(publishingservername()) collate database_default and publisher_db = db_name()

-- revoke select access to public on table which were previously granted to public
if object_id('dbo.sysmergepublications') is not NULL
revoke select on dbo.sysmergepublications from public

if object_id('dbo.MSmerge_errorlineage') is not NULL
revoke select on dbo.MSmerge_errorlineage from public

if object_id('dbo.sysmergearticles') is not NULL
begin
revoke select on dbo.sysmergearticles from public
grant select(nickname, maxversion_at_cleanup, objid) on dbo.sysmergearticles to public
end

if object_id('dbo.sysmergesubscriptions') is not NULL
revoke select on dbo.sysmergesubscriptions from public

if object_id('dbo.MSmerge_replinfo') is not NULL
revoke select on dbo.MSmerge_replinfo from public

if object_id('dbo.MSmerge_tombstone') is not NULL
revoke select on dbo.MSmerge_tombstone from public

if object_id('dbo.MSmerge_contents') is not NULL
revoke select on dbo.MSmerge_contents from public

if object_id('dbo.MSmerge_genhistory') is not NULL
revoke select on dbo.MSmerge_genhistory from public

if object_id('dbo.sysmergeschemachange') is not NULL
revoke select on dbo.sysmergeschemachange from public

if object_id('dbo.sysmergesubsetfilters') is not NULL
revoke select on dbo.sysmergesubsetfilters from public

end -- end dbo.sysmergearticles modifications

if object_id('sysmergearticles') is not NULL
begin
-- always drop down level triggers since in yukon the triggers are named differently
if exists (select * from dbo.sysmergearticles)
begin
declare @artidstr sysname
declare @instrigger nvarchar(517)
declare @updtrigger nvarchar(517)
declare @deltrigger nvarchar(517)

select @artnick = min(nickname) from dbo.sysmergearticles
while @artnick is not null
begin
select @objid = NULL
select @source_object = NULL
select top 1 @objid = objid, @artid = artid from dbo.sysmergearticles where nickname = @artnick
select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid
if @objid is NULL or @source_object is NULL
goto error

exec @retcode=sys.sp_MSguidtostr @artid, @artidstr out
if @retcode<>0 or @@ERROR<>0
goto error

-- the following are downlevel trigger names
select @instrigger = QUOTENAME(@source_owner) + '.ins_' + @artidstr
select @updtrigger = QUOTENAME(@source_owner) + '.upd_' + @artidstr
select @deltrigger = QUOTENAME(@source_owner) + '.del_' + @artidstr
if object_id(@instrigger) is not NULL
begin
exec ('drop trigger ' + @instrigger)
if @@ERROR<>0 return (1)
end
if object_id(@updtrigger) is not NULL
begin
exec ('drop trigger ' + @updtrigger)
if @@ERROR<>0 return (1)
end
if object_id(@deltrigger) IS NOT NULL
begin
exec ('drop trigger ' + @deltrigger)
if @@ERROR<>0 return (1)
end

-- find next article
select @artnick = min(nickname) from dbo.sysmergearticles where nickname > @artnick
end -- end article while
end
end




-- Merge dynamic snapshot --

-- Make sure that the database is enabled for merge replication before MSdynamicsnapshotviews is created --
if @remove_repl = 0 and object_id('sysmergepublications') is not NULL
begin

--
-- MSdynamicsnapshotviews -- Created from Shiloh Beta2 onwards
--

if object_id('MSdynamicsnapshotviews') is NULL
begin
create table dbo.MSdynamicsnapshotviews (
dynamic_snapshot_view_name sysname primary key,
)
if @@ERROR <> 0 goto error
end

exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews

--
-- MSdynamicsnapshotjobs -- Created from Shiloh Beta2 onwards
--
if object_id('MSdynamicsnapshotjobs') is NULL
begin
create table dbo.MSdynamicsnapshotjobs (
id int identity,
name sysname not null unique,
pubid uniqueidentifier not null,
job_id uniqueidentifier not null,
agent_id int not null default 0,
dynamic_filter_login sysname null,
dynamic_filter_hostname sysname null,
dynamic_snapshot_location nvarchar(255) not null,
partition_id int not NULL default -1,
computed_dynsnap_location bit not NULL default 0
)
if @@ERROR <> 0 goto error
end

-- Update MSdynamicsnapshotjobs so that it has:
-- agent_id default 1
-- partition_id default -1
-- We are know herer that dbo.MSdynamicsnapshotjobs is not null so no need to check.

-- Adding DEFAULT on column agent_id
if exists(select * from sys.columns where object_id = object_id('dbo.MSdynamicsnapshotjobs')
and name = 'agent_id')
begin
select @defaultname = null
select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
(sysdc.parent_object_id = sysc.object_id and
sysdc.parent_column_id = sysc.column_id)
where sysc.object_id = object_id('dbo.MSdynamicsnapshotjobs') and
sysc.name = 'agent_id'
if @defaultname is not null -- check if default exists
begin
-- delete default if one exists
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd)
end
-- add the default we want
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs add default 0 for agent_id'
exec (@alter_cmd)
end
if exists(select * from sys.columns where object_id = object_id('dbo.MSdynamicsnapshotjobs')
and name = 'partition_id')
begin
-- Adding DEFAULT on column partition_id
select @defaultname = null
select top 1 @defaultname = sysdc.name from sys.default_constraints sysdc join sys.columns sysc on
(sysdc.parent_object_id = sysc.object_id and
sysdc.parent_column_id = sysc.column_id)
where sysc.object_id = object_id('dbo.MSdynamicsnapshotjobs') and
sysc.name = 'partition_id'
if @defaultname is not null -- check if default exists
begin
-- delete default if one exists
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs drop constraint ' + QUOTENAME(@defaultname)
exec (@alter_cmd)
end
-- add the default we want
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs add default -1 for partition_id'
exec (@alter_cmd)
end

declare colcurs cursor LOCAL FAST_FORWARD
for (select col_name, col_type from (select col_name = 'partition_id', col_type = 'int not NULL default -1'
union all
select col_name = 'agent_id', col_type = 'int not NULL default 0'
union all
select col_name = 'computed_dynsnap_location', col_type = 'bit not NULL default 0'
) as t1
left outer join
sys.columns as t2
on (t1.col_name = t2.name and t2.object_id = object_id('dbo.MSdynamicsnapshotjobs', 'U'))
where t2.name is null) -- This query gives all the columns in t1 that are not in syscolums
for read only
open colcurs
fetch colcurs into @column_name, @column_type
if (@@fetch_status <> -1)
begin
select @alter_cmd = 'alter table dbo.MSdynamicsnapshotjobs add ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
while(@@fetch_status <> -1)
begin
select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
fetch colcurs into @column_name, @column_type
end
exec (@alter_cmd)
if @@error <> 0 goto error
end
close colcurs
deallocate colcurs

if not exists (select *
from sys.indexes
where object_id = object_id('MSdynamicsnapshotjobs')
and name = ('uciMSdynamicsnapshotjobs'))
begin
create unique clustered index uciMSdynamicsnapshotjobs on
dbo.MSdynamicsnapshotjobs(job_id, pubid)
if @@ERROR <> 0 goto error
end

if not exists (select *
from sys.indexes
where object_id = object_id('MSdynamicsnapshotjobs')
and name = ('nciMSdynamicsnapshotjobs'))
begin
create nonclustered index nciMSdynamicsnapshotjobs on
dbo.MSdynamicsnapshotjobs(partition_id)
if @@ERROR <> 0 goto error
end

exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs
if @@ERROR <> 0 goto error
end

-- Index updates (SQL7.0 SP1)
if @remove_repl = 0
begin
SELECT @table_name = N'sysmergepublications'
IF object_id('sysmergepublications') is not NULL
BEGIN
IF EXISTS ( SELECT pubid
FROM dbo.sysmergepublications
GROUP BY pubid
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 4, @table_name)
goto error
end
ELSE
IF NOT EXISTS ( SELECT * FROM sys.indexes WHERE name = 'nc1sysmergepublications' AND
object_id = object_id('dbo.sysmergepublications') )
CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications
ON dbo.sysmergepublications(pubid)
if @@ERROR <> 0 goto error

IF NOT EXISTS ( SELECT * FROM sys.indexes WHERE name = 'nc2sysmergepublications' AND
object_id = object_id('dbo.sysmergepublications') )
CREATE NONCLUSTERED INDEX nc2sysmergepublications
ON sysmergepublications(status)
if @@ERROR <> 0 goto error


-- add default constraint on allow_anonymous column
if not exists(
select *
from sysconstraints as con join sys.columns as col
on con.colid = col.column_id
and con.id = col.object_id
and OBJECTPROPERTY ( con.constid , 'IsDefaultCnst' ) = 1
and col.object_id = object_id('dbo.sysmergepublications')
and col.name = 'allow_anonymous')and exists
(select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'allow_anonymous')
begin
exec(N'alter table dbo.sysmergepublications add default 1 for allow_anonymous')
end


-- Changing default constraint on publisher from @@servername to publishingservername()
if exists(select * from sys.columns where object_id = object_id('dbo.sysmergepublications') and name = 'publisher')
begin
declare @default_publisher_c_name nvarchar(258)
select @default_publisher_c_name = obj.name
from sysconstraints as con join sys.columns as col
on con.colid = col.column_id
and con.id = col.object_id
and OBJECTPROPERTY ( con.constid , 'IsDefaultCnst' ) = 1
and col.object_id = object_id('dbo.sysmergepublications')
and col.name = 'publisher'
join sys.objects as obj
on obj.object_id=con.constid

if(@default_publisher_c_name is not null)
begin
select @default_publisher_c_name = quotename(@default_publisher_c_name)
exec (N'alter table dbo.sysmergepublications drop constraint ' + @default_publisher_c_name)
exec(N'alter table dbo.sysmergepublications add default publishingservername() for publisher')
end
end

END


-- MSmerge_conflicts_info
SELECT @table_name = N'MSmerge_conflicts_info'
IF object_id('MSmerge_conflicts_info') is not NULL
BEGIN
IF EXISTS ( SELECT tablenick, rowguid, origin_datasource, conflict_type
FROM MSmerge_conflicts_info
GROUP BY tablenick, rowguid, origin_datasource, conflict_type
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 6, @table_name)
goto error
end
ELSE
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1MSmerge_conflicts_info' AND
id = OBJECT_ID('MSmerge_conflicts_info') )
CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_conflicts_info
ON MSmerge_conflicts_info(tablenick, rowguid, origin_datasource, conflict_type)
if @@ERROR <> 0 goto error
END

-- sysmergeschemachange
SELECT @table_name = N'sysmergeschemachange'
IF object_id('sysmergeschemachange') is not NULL
BEGIN
IF EXISTS ( SELECT schemaversion, pubid
FROM dbo.sysmergeschemachange
GROUP BY schemaversion, pubid
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 7, @table_name)
goto error
end
ELSE
BEGIN
IF EXISTS ( SELECT * FROM sys.indexes WHERE name = 'schemachangeversion' AND
object_id = OBJECT_ID('sysmergeschemachange') )
DROP INDEX sysmergeschemachange.schemachangeversion
if @@ERROR <> 0 goto error

-- Recreate this index as unique clustered with one more field in index key.
CREATE UNIQUE CLUSTERED INDEX schemachangeversion ON sysmergeschemachange(schemaversion, pubid)
if @@ERROR <> 0 goto error
END

-- In Yukon we no longer have a schema version SCHEMA_TYPE_SYSTABLE which
-- indicates a system table schema script file. This type was deprecated because
-- it was used only by JET consumers. In Yukon Jet subscribers are not supported
-- Hence deleting any entries which have schema type SCHEMA_TYPE_SYSTABLE (20)
delete from dbo.sysmergeschemachange where schematype=20

-- Adding schemastatus column.
if not exists (select * from sys.columns where object_id = object_id('dbo.sysmergeschemachange') and
name = 'schemastatus')
begin
alter table dbo.sysmergeschemachange add schemastatus tinyint NOT NULL default(1)
if @@error <> 0 goto error
end

if not exists (select * from sys.columns where object_id = object_id('sysmergeschemachange') and
name = 'schemasubtype')
begin
alter table sysmergeschemachange add schemasubtype int NOT NULL default 0
if @@error <> 0 goto error
end

-- Modifying the type of the schematext from nvarchar(2000) to nvarchar(max)
if exists (select * from sys.columns where object_id = object_id('dbo.sysmergeschemachange') and
name = 'schematext')
begin
exec (' alter table dbo.sysmergeschemachange alter column schematext nvarchar(max) NOT NULL ')
if @@error <> 0 goto error
end

END


-- sysmergesubsetfilters
SELECT @table_name = N'sysmergesubsetfilters'
IF object_id('sysmergesubsetfilters') is not NULL
BEGIN
IF EXISTS ( SELECT join_filterid
FROM dbo.sysmergesubsetfilters
GROUP BY join_filterid
HAVING COUNT(*) > 1 )
begin
RAISERROR (21203, 10, 8, @table_name)
goto error
end
ELSE
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1sysmergesubsetfilters' AND
id = OBJECT_ID('sysmergesubsetfilters') )
begin
CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters ON dbo.sysmergesubsetfilters(join_filterid, pubid)
if @@ERROR <> 0 goto error
end

IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'uc2sysmergesubsetfilters' AND
id = OBJECT_ID('sysmergesubsetfilters') )
begin
CREATE UNIQUE CLUSTERED INDEX uc2sysmergesubsetfilters ON dbo.sysmergesubsetfilters(pubid, filtername)
if @@ERROR <> 0 goto error
end

if not exists (select * from sys.columns where object_id = object_id('sysmergesubsetfilters') and
name = 'filter_type')
begin
alter table dbo.sysmergesubsetfilters add filter_type tinyint NOT NULL default 1
if @@ERROR <> 0 goto error
end
END -- end index updates from SQL7.0 SP1


declare @binames table (biname sysname)
insert into @binames select name from sys.objects where type='U' and is_ms_shipped=1 and name like 'MS_bi%'
declare @biname sysname
set @biname= (select top 1 biname from @binames)
while @biname is not null
begin
set @cmd= 'drop index ' + quotename(@biname) + '.' + quotename(@biname + '_gen')
exec sys.sp_executesql @cmd
if @@ERROR <> 0 goto error
set @cmd= 'create clustered index ' + quotename(@biname + '_gen') + ' on ' + quotename(@biname) + '(generation)'
exec sys.sp_executesql @cmd
if @@ERROR <> 0 goto error
delete from @binames where biname=@biname
set @biname= (select top 1 biname from @binames)
end


if object_id('sysmergearticles') is not NULL
begin
exec sys.sp_MScreate_common_dist_tables @subside=1
if @@error <> 0
goto error
end

-- in Yukon we will create the merge ddl triggers, only for databases that have merge replication enabled.
if object_id('sysmergepublications', 'U') is NOT NULL
begin
if exists (select * from sys.triggers where name = 'MSmerge_tr_altertable' and type = 'TR')
begin
execute @retcode= sys.sp_MSrepl_ddl_triggers @type='merge', @mode='drop'
if @@ERROR <> 0 or @retcode <> 0
goto error
end
if not exists (select * from sys.triggers where name = 'MSmerge_tr_altertable' and type = 'TR')
begin
execute @retcode= sys.sp_MSrepl_ddl_triggers @type='merge', @mode='add'
if @@ERROR <> 0 or @retcode <> 0
goto error
end
end
-- we will also add a system table MSmerge_upgrade_in_progress which would indicate that
-- the metadata upgrade has not been completed yet.
if @remove_repl=0 and object_id('sysmergearticles') is not NULL
begin
if object_id('MSmerge_upgrade_in_progress', 'U') is NULL
begin
create table dbo.MSmerge_upgrade_in_progress
(
status tinyint not NULL
)
if @@ERROR <> 0 goto error
end
end
end
commit tran
return 0

error:
rollback tran vupgrade_mergetables
commit tran
return 1

end

Total Pageviews