create procedure sys.sp_MSscript_insert_statement(
@objid int,
@artid int,
@identity_insert bit = 0,
@queued_pub bit = 0
)
as
BEGIN
declare @cmd nvarchar(4000)
,@qualname nvarchar(517)
,@tablename nvarchar(517)
,@colname sysname
,@ccoltype sysname
,@this_col int
,@rc int
,@num_col int
,@column_string nvarchar(4000)
,@var_string nvarchar(4000)
,@hfilterprocid int
,@sync_objid int
,@identityscriptedasbasedatatypeonsubscriber bit
-- Check if we have horizontal partition filter
select @hfilterprocid = filter, @sync_objid = sync_objid from dbo.sysarticles where artid = @artid
if (@hfilterprocid = 0)
begin
-- horizontal partional filter not defined, the qualified name should be the source object
exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
select @tablename = @qualname
end
else
begin
-- horizontal partional filter not defined, the qualified name should be the view object
exec sys.sp_MSget_qualified_name @sync_objid, @qualname OUTPUT
exec sys.sp_MSget_qualified_name @objid, @tablename OUTPUT
end
-- For identity column processing - see if base data is being scripted on subscriber
-- For queued or failover this will never be true, we need to really
-- check it only for pure immediate case
select @identityscriptedasbasedatatypeonsubscriber = 0
if (@queued_pub = 0) and (@identity_insert = 1)
begin
-- immediate updating only with an identity column
select @identityscriptedasbasedatatypeonsubscriber = case when (cast(schema_option as int) & 0x4 = 0) then 1 else 0 end
from sysarticles where artid = @artid
end
-- start scripting
select @cmd = N'
' + N'
' + N'-- detection/conflict resolution stage
' + N'--'
-- Queued specific
if (@queued_pub = 1)
begin
select @cmd = @cmd + N'
if (@execution_mode = @QPubWins)
save tran cftpass'
end
insert into #proctext(procedure_text) values( @cmd)
-- set identity_insert on if subscriber is also using it as identity
if (@identity_insert = 1 and @identityscriptedasbasedatatypeonsubscriber = 0)
begin
select @cmd = N'
set identity_insert ' + @tablename + ' on'
insert into #proctext(procedure_text) values( @cmd )
end
-- prepare the insert statement now
select @cmd = N'
insert into ' + @qualname + N'( '
insert into #proctext(procedure_text) values( @cmd )
-- Generate strings for col names and variables
select @num_col = 0
,@cmd = N''
DECLARE #hCColid CURSOR LOCAL FAST_FORWARD FOR
select column_id from sys.columns where object_id = @objid order by column_id asc
open #hCColid
fetch #hCColid into @this_col
while (@@fetch_status != -1)
begin
exec @rc = sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
if @rc = 0 and exists (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed<>1)
begin
-- skip the column if it is timestamp or it is an identity that is being scripted as base data on subscriber
if (@ccoltype != N'timestamp') and
not(columnproperty(@objid, @colname, 'IsIdentity') = 1 and @identityscriptedasbasedatatypeonsubscriber=1)
begin
select @num_col = @num_col + 1
if @num_col > 1
select @cmd = @cmd + N', '
select @cmd = @cmd + quotename(@colname)
exec sys.sp_MSflush_command @cmd output, 1
end
end
fetch #hCColid into @this_col
end
close #hCColid
-- Script end of colmn names
select @cmd = N' )'
insert into #proctext(procedure_text) values( @cmd )
-- Script column value string
if @num_col > 0
begin
select @cmd = N'
values ( '
insert into #proctext(procedure_text) values( @cmd )
-- Script column value string
select @num_col = 0
,@cmd = N''
open #hCColid
fetch #hCColid into @this_col
while (@@fetch_status != -1)
begin
exec @rc = sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
if @rc = 0 and exists (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed<>1)
begin
-- skip the column if it is timestamp or it is an identity that is being scripted as base data on subscriber
if (@ccoltype != N'timestamp') and
not(columnproperty(@objid, @colname, 'IsIdentity') = 1 and @identityscriptedasbasedatatypeonsubscriber=1)
begin
select @num_col = @num_col + 1
if @num_col > 1
select @cmd = @cmd + N', '
select @cmd = @cmd + N'@c' + cast(@this_col as nvarchar(4))
exec sys.sp_MSflush_command @cmd output, 1
end
end
fetch #hCColid into @this_col
end
close #hCColid
-- Script end of column value string
select @cmd = N' )
'
insert into #proctext(procedure_text) values( @cmd )
end
else
begin
-- This is to set @@rowcount.
insert into #proctext(procedure_text) values( N'
select @retcode = @retcode')
end
-- set the rowcount and error
select @cmd = N'
select @rowcount = @@ROWCOUNT, @error = @@ERROR
'
insert into #proctext(procedure_text) values( @cmd )
-- set indentity_insert off if subscriber is also using it as identity
if (@identity_insert = 1 and @identityscriptedasbasedatatypeonsubscriber = 0)
begin
select @cmd = N'
set identity_insert ' + @tablename + ' off'
insert into #proctext(procedure_text) values( @cmd )
end
-- Queued specific
if (@queued_pub = 1)
begin
select @cmd = N'
if (@execution_mode = @QPubWins)
rollback tran cftpass'
insert into #proctext(procedure_text) values( @cmd )
end
-- all done
deallocate #hCColid
return 0
END