Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreatedupkeyupdatequery

  No additional text.


Syntax
create procedure sys.sp_MScreatedupkeyupdatequery
    @tablename nvarchar(270),
    @tablenickstr nvarchar(12),
    @phase int,
    @isconflictproc bit,
    @tablename2 nvarchar(270)
as
    declare @uniqueindexid       smallint
    declare @numofindex          smallint
    declare @cmdpiece            nvarchar(4000)
    declare @cmdpiecebegin       nvarchar(1000)
    declare @colid               int
    declare @tableid             int
    declare @colname             nvarchar(140)
    declare @paramname           nvarchar(10)
    declare @skipthisindex       bit

    set @numofindex= 0
    set @cmdpiece= ''
    set @cmdpiecebegin= ''
    set @tableid= object_id(@tablename)

    if 1 = @isconflictproc
    begin
        set @cmdpiecebegin= '
        declare @error    int
        set @error= @reason_code'
    end

    -- Create the part of the query that preceds the where-clause.
    set @cmdpiecebegin= @cmdpiecebegin + '

    declare @REPOLEExtErrorDupKey            int
    declare @REPOLEExtErrorDupUniqueIndex    int

    set @REPOLEExtErrorDupKey= 2627
    set @REPOLEExtErrorDupUniqueIndex= 2601

    if @error in (@REPOLEExtErrorDupUniqueIndex, @REPOLEExtErrorDupKey)
    begin
        update mc
            set mc.generation= 0
            from dbo.MSmerge_contents mc join ' + @tablename2 + ' t on mc.rowguid=t.rowguidcol
            where
                mc.tablenick = ' + @tablenickstr + ' and
                (
'

    -- Iterate over all unique indexes, and build up the column comparison part of the where-clause.
    -- The index on rowguidcol is skipped.
    -- Exit right away if there is no index at all, or only the merge-related rowguidcol-index.
    set @uniqueindexid= (select min(index_id) from sys.indexes
                            where object_id=@tableid and
                                  is_unique=1 and
                                  (
                                      1<>ColumnProperty(object_id, index_col(@tablename, index_id, 1), 'IsRowGuidCol') or
                                      index_col(@tablename, index_id, 2) is not null
                                  )
                        )

    while @uniqueindexid is not null
    begin
        set @skipthisindex= 0
        set @colid= 1

        set @numofindex= @numofindex + 1

        -- Iterate over all columns that belong to that index.
        set @cmdpiece= ''
        set @colname= index_col(@tablename, @uniqueindexid, @colid)
        while 0 = @skipthisindex and
              @colname is not null
        begin
            set @colname= quotename(@colname)
            set @paramname= (select paramname from #coltab where colname = @colname)

            -- @paramname can be null if we are in the update proc, and the column is
            -- an identity column. (Those columns are skipped for the update proc, and thus
            -- are not in #coltab, either.)
            -- As a consequence, this part of the where clause is skipped, too.
            if @paramname is null
            begin
                set @skipthisindex= 1
                set @numofindex= @numofindex - 1
            end
            else
            begin

                if 1 = @colid
                begin
                    if 1 < @numofindex
                        set @cmdpiece= @cmdpiece + ' or
'

                    set @cmdpiece= @cmdpiece + '                        (t.' + @colname + '=' + @paramname
                end
                else
                begin
                    set @cmdpiece= @cmdpiece + ' and t.' + @colname + '=' + @paramname
                end

                set @colid= @colid+1
                set @colname= index_col(@tablename, @uniqueindexid, @colid)
            end

        end

        if 0 = @skipthisindex
        begin
            -- Add the beginning of the command if we are at the first index
            if 1 = @numofindex
                insert into #tempcmd (phase, cmdtext) values (@phase, @cmdpiecebegin)
            -- Close this part of the where-clause, and add it to the generated query.
            set @cmdpiece= @cmdpiece + ')'
            insert into #tempcmd (phase, cmdtext) values (@phase, @cmdpiece)
        end

        set @uniqueindexid= (select min(index_id) from sys.indexes
                                where object_id=@tableid and
                                    is_unique=1 and
                                      (
                                          1<>ColumnProperty(object_id, index_col(@tablename, index_id, 1), 'IsRowGuidCol') or
                                          index_col(@tablename, index_id, 2) is not null
                                      ) and
                                    index_id>@uniqueindexid
                            )
    end

    -- Close the entire where-clause.
    if 0 < @numofindex
    begin
        set @cmdpiece= '
                        )
            end'

        insert into #tempcmd (phase, cmdtext) values (@phase, @cmdpiece)
    end

 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (Procedure)
sp_MSmakeconflictinsertproc (Procedure)
sp_MSmakeinsertproc (Procedure)
sp_MSmakeupdateproc (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