Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreatelightweightupdateproc

  No additional text.


Syntax
create procedure sys.sp_MScreatelightweightupdateproc
	@pubid		uniqueidentifier,
	@artid		uniqueidentifier
as
	set nocount on

	declare @argname            nvarchar(10)
	declare @tablenick			int
	declare @destination_object	sysname
	declare @destination_owner	sysname
	declare @qualified_name     nvarchar(270)
	declare @cmdpiece 			nvarchar(4000)
	declare @postfix			nchar(32)
	declare @id                 int
	declare @colid 				int
	declare @previouscolid		int
	declare @columnordinal		smallint
	declare @colname 			nvarchar(200)
	declare @typename			sysname
	declare @len				smallint
	declare @prec				int
	declare @scale				int
	declare @isrowguidcol		tinyint
	declare @comma				nvarchar(2)
	declare @retcode            int
	declare @maintainsmetadata	bit
	declare @coltracked			bit
	declare @enumtype_str		nvarchar(100)
	declare @cmdtable			table (phase int not null,
									   step int identity not null,
									   cmdtext nvarchar(4000) collate database_default null)

	-- Security check
	exec @retcode= sys.sp_MSreplcheck_subscribe
	if @@error <> 0 or @retcode <> 0 return (1)

	select @tablenick= nickname,
		   @destination_object= destination_object,
		   @postfix= procname_postfix,
		   @destination_owner= destination_owner,
		   @coltracked= column_tracking
		from dbo.sysmergearticles
		where pubid = @pubid and artid = @artid

	if 1=@coltracked
	begin
		set @enumtype_str= '@COLUMNS_ENUMERATED_ChangedOnly'
	end
	else
	begin
		set @enumtype_str= '@COLUMNS_ENUMERATED_AllOnOtherReason'
	end

	set @maintainsmetadata= sys.fn_MSarticle_allows_DML_at_this_replica(@artid, default)

	if @destination_owner is null or @destination_owner = ''
		select @qualified_name = quotename(@destination_object)
	else
		select @qualified_name = quotename(@destination_owner) + '.' + quotename(@destination_object)

	-- Phase 0: Create procedure and fixed part of argument list.
	set @cmdpiece= 'create procedure dbo.' + quotename('MSmerge_lws_sp_upd_'  + @postfix) + '
	@rowguid	uniqueidentifier,
	@setnullbm	varbinary(128)'

	if 1 = @maintainsmetadata
	begin
		set @cmdpiece= @cmdpiece + ',
		@rowvector		varbinary(11)'
	end

	insert into @cmdtable (phase, cmdtext) values (0, @cmdpiece)

	-- Phase 1: Column arguments list. This will be done later.

	-- Phase 2: Open procedure body.
	set @cmdpiece='
	as
	set nocount on

	-- Security check
	if (is_member(''db_owner'') <> 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

	declare @tablenick	int
	set @tablenick= ' + cast(@tablenick as nvarchar(20)) + '
	'

	insert into @cmdtable (phase, cmdtext) values (2, @cmdpiece)

	-- Phase 3: Add all code up to column list in update statement.
	if 1=@maintainsmetadata
	begin
		set @cmdpiece=
		'
		declare @errcode	int
		declare @METADATA_TYPE_UpsertLightweightProcessed tinyint'

		if 1=@coltracked
		begin
			set @cmdpiece=@cmdpiece + '
			declare @COLUMNS_ENUMERATED_ChangedOnly tinyint

			set @COLUMNS_ENUMERATED_ChangedOnly= 0'
		end
		else
		begin
			set @cmdpiece=@cmdpiece + '
			declare @COLUMNS_ENUMERATED_AllOnOtherReason tinyint

			set @COLUMNS_ENUMERATED_AllOnOtherReason= 2'
		end

		set @cmdpiece=@cmdpiece + '
		set @METADATA_TYPE_UpsertLightweightProcessed= 11

		begin tran
		save tran tran_lws_updproc
		
		update dbo.MSmerge_rowtrack
			set	
				changetype= @METADATA_TYPE_UpsertLightweightProcessed,
				rowvector= @rowvector,
				changedcolumns= null,
				columns_enumeration= ' + @enumtype_str + ',
				changed= sys.fn_MSdayasnumber(getdate()),
				sync_cookie= null
			where tablenick = @tablenick and rowguid = @rowguid

		if @@rowcount = 0
		begin
			insert into dbo.MSmerge_rowtrack
			(
				tablenick,
				rowguid,
				changetype,
				rowvector,
				changedcolumns,
				columns_enumeration,
				changed,
				sync_cookie
			)
			values
			(
				@tablenick,
				@rowguid,
				@METADATA_TYPE_UpsertLightweightProcessed,
				@rowvector,
				null,
				' + @enumtype_str + ',
				sys.fn_MSdayasnumber(getdate()),
				null
			)

			if @@rowcount <> 1
			begin
				set @errcode= 3
				goto Failure
			end
		end

		update ' + @qualified_name + '
			set'
	end -- 1=@maintainsmetadata
	else
	begin
		set @cmdpiece=
		'
		declare @errcode	int

		update ' + @qualified_name + '
			set'
	end
	
	insert into @cmdtable (phase, cmdtext) values (3, @cmdpiece)

	-- Phase 4: Add sequence of  in update statement. Will be done later

	-- Phase 5: Close update statement, and complete the stored proc.
	set @cmdpiece= '
		where rowguidcol = @rowguid
		
	if (@@rowcount <> 1)
	begin
		set @errcode= 3
		goto Failure
	end
	else
	begin
		set @errcode= 1
		delete from dbo.MSmerge_metadataaction_request
			where tablenick=@tablenick and rowguid=@rowguid
				
		goto Done
	end'

	if 1 = @maintainsmetadata
	begin
		set @cmdpiece= @cmdpiece + '
		Failure:
			rollback tran tran_lws_updproc

		Done:
			commit tran
			return @errcode
			'
	end
	else
	begin
		set @cmdpiece= @cmdpiece + '
		Failure:
		Done:
			return @errcode
			'
	end

	insert into @cmdtable (phase, cmdtext) values (5, @cmdpiece)

	-- Now we loop over the columns to complete the command.
	-- We omit computed, identity, and timestamp columns.

	select @id= object_id(@qualified_name)
	if @id is null return (1)
	
	set @columnordinal= 0
	set @previouscolid= -1
	set @comma= ''
	while (1=1)
	begin
		set @columnordinal= @columnordinal + 1
		
		set @colid= (select min(column_id) from sys.columns
						where object_id = @id and
							  column_id > @previouscolid and
							  is_computed = 0 and
							  is_identity = 0 and	-- Identity column
							  system_type_id <> type_id('timestamp'))

		if @colid is null
			break

		set @previouscolid= @colid

		select
			@colname= quotename(name),
			@typename= type_name(system_type_id),
		    @len= max_length,
		    @prec= precision,
		    @scale= scale,
			@isrowguidcol= is_rowguidcol
		    from sys.columns
		    where object_id = @id and column_id = @colid

		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)

		-- Phase 1: Add column to procedure arguments list.
		--			The list also contains the rowguidcol.
	    select @argname= '@p' + convert(nvarchar, @columnordinal)
	    set @cmdpiece= ',
	    ' + @argname + ' ' + @typename + '=null'

	    insert into @cmdtable (phase, cmdtext) values (1, @cmdpiece)

		-- Phase 4: Add sequence of  in update statement.
		--			Unlike in the argument list of phase 1, we now omit the rowguidcol.
		if @isrowguidcol = 1
			continue

		set @cmdpiece= @comma + '
			' + @colname + '= case' + '
				when ' + @argname + ' is not null then ' + @argname + '
				when @setnullbm =0x00 then ' + @colname + '
				when 1 = sys.fn_MStestbit(@setnullbm, ' + convert(nvarchar, @columnordinal) + ') then null
				else ' + @colname + '
			end'

	    insert into @cmdtable (phase, cmdtext) values (4, @cmdpiece)

	    set @comma= ', '
	end

	-- Now we select out the command text pieces in proper order so that our caller,
	-- xp_execresultset, will execute the command that creates the stored procedure.
	select cmdtext from @cmdtable order by phase, step

	return 0

 
Last revision 2008RTM
See also

  sp_MScreatelightweightarticleprocs (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