Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSAdjustConflictTable

  No additional text.


Syntax
create procedure sys.sp_MSAdjustConflictTable(
@qual_source_object nvarchar(540),
@pubid uniqueidentifier,
@columnName sysname,
@column_change_type int = 1 --addcolumn 1, dropcolumn 2, alter column 3
)
AS
declare @artname sysname
declare @retcode int
declare @objid int
declare @con_id int
declare @conflict_table sysname
declare @ins_conflict_proc sysname
declare @qual_column        nvarchar(258)
declare @len            int
declare @prec            int
declare @scale int
declare @typename nvarchar(270)
declare @command nvarchar(4000)
declare @create_unq nvarchar(500)
declare @is_filestream int
declare @rowguidcol sysname

select @qual_column=QUOTENAME(@columnName)

select @objid = object_id(@qual_source_object)
select @conflict_table=conflict_table from dbo.sysmergearticles where pubid=@pubid and objid=@objid

if @conflict_table is null
    return 0

select @con_id = object_id(quotename(@conflict_table))
if @con_id is NULL
begin
    raiserror(21286, 16, -1, @conflict_table)
    return (1)
end

-- add/alter/drop column in conflict table
set @command = NULL
if (@column_change_type = 1 or @column_change_type = 3) and @conflict_table is not NULL
begin

    --figure out the type of newly added column and add it to the conflict table

    select @typename = NULL
    select @typename = case system_type_id when 240 then type_name(user_type_id) else type_name(system_type_id) end,
    @len = max_length, @prec = precision, @scale = scale, @is_filestream = is_filestream
            from sys.columns where name=@columnName and object_id=@objid and is_computed<>1 and system_type_id <> type_id('timestamp')
    if @typename is NULL
        return (0)
    if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes
        select @len = @len/2
    exec @retcode = sys.sp_MSmaptype @typename out, @len, @prec, @scale
    if @@error<>0 OR @retcode <>0
        return (1)
    if @column_change_type = 1 and not exists (select * from sys.columns where object_id = @con_id and name=@columnName)
        select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' add ' + @qual_column + ' ' + @typename
    else if @column_change_type = 3 and exists (select * from sys.columns where object_id = @con_id and name=@columnName)
        select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' alter column ' + @qual_column + ' ' + @typename
     -- add filestream keyword for Filestream columns	
     if @is_filestream = 1
     begin
     		
     		-- check if NON-NULL, unique rowguid col exists on conflict table

		-- is rowguidcol nullable?
		if not exists( select * from sys.columns where object_id= @con_id and is_rowguidcol = 1 and is_nullable = 0 )
		begin
			-- cannot alter rowguid col to be non-nullable
			return (1)
		end
		-- is rowguidcol unique?

		if not exists (
		select i.index_id from sys.indexes i
		join sys.index_columns icol on icol.object_id = i.object_id and icol.index_id = i.index_id
		where i.object_id = @con_id and i.is_unique_constraint = 1
		and columnproperty(i.object_id, col_name(i.object_id, icol.column_id), 'isrowguidcol')=1
		and indexproperty(i.object_id, i.name, 'userkeycount') = 1
		)
		begin
			-- unique index on ROWGUIDCOL does not exist. create one.
			select @rowguidcol = name from sys.columns where object_id= @con_id and is_rowguidcol = 1
			select @create_unq = 'alter table  ' + QUOTENAME(@conflict_table) + ' add unique (' + quotename(@rowguidcol) + ')'
			exec(@create_unq)
			if @@ERROR<>0
				return (1)
		end
        	select @command = @command + ' FILESTREAM '
     end
    select @command = @command + ' NULL '
    if @command is not NULL
    begin
        exec(@command)
        if @@ERROR<>0
            return (1)
    end
end
else if @column_change_type = 2 and @conflict_table is not NULL and exists (select * from sys.columns where object_id = @con_id and name=@columnName)
begin
    select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' drop column '  + @qual_column
    exec(@command)
    if @@ERROR<>0
        return (1)
end
return (0)

 
Last revision 2008RTM
See also

  sp_MSmerge_altertable (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash