2 for 1 replication sproc
-
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"
 
values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9
 
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
-
I could be mistaken, but aren't these incarnations of the proc as regards changes to replication / service pack upgrades?
-
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?
-
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.
-
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply