2 for 1 replication sproc

Question

2 for 1 replication sproc

JohnMarks

Ten Centuries

Points: 1253
More actions
March 8, 2004 at 9:37 am

#82236

I am running transactional replication between 2 SQL 2k Ent edition servers. When looking at the sproc that replication creates to insert records into the replicate tables at the subscriber I notices something funny. Here is a sample insert sproc for one of the articles. For some reason there is a create procedure statement within the body of the sproc. Does anyone know why that might occur and what would its use be or is this a bug?
Thanks
create procedure "sp_MSins_OrderStatus" @c1 int,@c2 varchar(50),@c3 datetime,@c4 datetime,@c5 varchar(50),@c6 varchar(255),@c7 bit,@c8 bit,@c9 int
AS
BEGIN
insert into "OrderStatus"(
"OrderStatus_ID", "OrderStatus", "Date_Created", "Date_Changed", "ChangedBy", "Comments", "OrderOpen", "SCR", "SLA"
&nbsp
values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9
&nbsp
END
GO
create procedure "sp_MSins_OrderStatus";2 @c1 int,@c2 varchar(50),@c3 datetime,@c4 datetime,@c5 varchar(50),@c6 varchar(255),@c7 bit,@c8 bit,@c9 int
as
if exists ( select * from "OrderStatus"
where "OrderStatus_ID" = @c1
)
begin
update "OrderStatus" set "OrderStatus" = @c2,"Date_Created" = @c3,"Date_Changed" = @c4,"ChangedBy" = @c5,"Comments" = @c6,"OrderOpen" = @c7,"SCR" = @c8,"SLA" = @c9
where "OrderStatus_ID" = @c1
end
else
begin
insert into "OrderStatus" ( "OrderStatus_ID","OrderStatus","Date_Created","Date_Changed","ChangedBy","Comments","OrderOpen","SCR","SLA" ) values ( @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9 )
end
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

I could be mistaken, but aren't these incarnations of the proc as regards changes to replication / service pack upgrades?

Shamless self promotion - read my blog http://sirsql.net

JohnMarks Ten Centuries Points: 1253 More actions · Answer 2

Interesting theory. You could be right. My main concern are the possible performance impacts of the insert sproc having a create procedure statement inside it. Does that mean that the sproc will get created each time the insert sproc is run?

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

It will make the call directly to the procedure with the ;#. So if it wanted to exec sp_msins_orderstatus;2 it would call that part. Apparently it's a versioning tool and a way to keep multiple related procs together....from BOL

;number

Is an optional integer used to group procedures of the same name so they can be dropped with a single DROP PROCEDURE statement. This parameter is not used for extended stored procedures.

Procedures used in the same application are often grouped this way. For example, the procedures used with the orders application may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. After the procedures have been grouped, individual procedures within the group cannot be dropped. For example, the statement DROP PROCEDURE orderproc;2 is not allowed. For more information about procedure groups, see CREATE PROCEDURE.

Shamless self promotion - read my blog http://sirsql.net

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 4

It is a different version of the same stored procedure created by the replication because when tried to replicate with the first sp, it failed.

Then it will continue using the first sp. It is not an error and yoy shoudn't have any problems.