sp_scriptPublicationCustomProcs output can not be executed without lots of edits

Thorsten Ahrens 0 Reputation points
2024-01-10T08:49:54.9+00:00

I have same problem with the output from sp_scriptPublicationCustomProcs.

You see charakter ( ' ) and 'end' are missed in the output. The '0' and the comment at the bottom is not helpfull too. If I want to fill the output in a variable, i cant execute this output on a linked server. Please help and give solution with correct proc.

I have commented the positions in this sample:

if object_id(N'[dbo].[sp_MSins_dboarc_Testtable]', 'P') > 0
    drop proc [dbo].[sp_MSins_dboarc_Testtable]
go
if object_id(N'dbo.MSreplication_objects') is not null
    delete from dbo.MSreplication_objects where object_name = N'sp_MSins_dboarc_Testtable'
go
create procedure [dbo].[sp_MSins_dboarc_Testtable]
    @c1 int,
    @c2 nchar(10),
    @c3 bit
as
begin  
	insert into [dbo].[arc_Testtable] (
		[C1],
		[C2],
		[C3]
	) values (
		@c1,
		@c2,
		@c3	) 
end  
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null
exec sp_executesql 
    @statement = 
        N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type)' --<-------<-------<-----Hochkomma (')
go
if object_id(N'[dbo].[sp_MSupd_dboarc_Testtable]', 'P') > 0
    drop proc [dbo].[sp_MSupd_dboarc_Testtable]
go
if object_id(N'dbo.MSreplication_objects') is not null
    delete from dbo.MSreplication_objects where object_name = N'sp_MSupd_dboarc_Testtable'
go
create procedure [dbo].[sp_MSupd_dboarc_Testtable]
		@c1 int = NULL,
		@c2 nchar(10) = NULL,
		@c3 bit = NULL,
		@pkc1 int = NULL,
		@bitmap binary(1)
as
begin  
	declare @primarykey_text nvarchar(100) = ''

update [dbo].[arc_Testtable] set
		[C2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [C2] end,
		[C3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [C3] end
	where [C1] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
		Begin
			if exists (Select * from sys.all_parameters where object_id = OBJECT_ID('sp_MSreplraiserror') and [name] = '@param3')
			Begin
				
				set @primarykey_text = @primarykey_text + '[C1]' --<-------<-------<-----Hochkomma (')
			end --<-------<-------<-----end
		end --<-------<-------<-----end
end 
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null
exec sp_executesql 
    @statement = 
        N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type)' --<-------<-------<-----Hochkomma (')

go

0

Completion time: 2024-01-10T09:12:21.9367589+01:00


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,435 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.