replication autogenerated procs - interesting ;2 notation

I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure "sp_MSins_dboMEETING" @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)

AS
BEGIN

insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)

values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, @.c13, @.c14, @.c15, @.c16, @.c17, @.c18, @.c19, @.c20, @.c21
)

END

GO

create procedure "sp_MSins_dboMEETING";2 @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @.c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @.c2,"MEETING_STATUS_ID" = @.c3,"TITLE" = @.c4,"START_DATE" = @.c5,"END_DATE" = @.c6,"PUBLISH_IND" = @.c7,"GROUP_IND" = @.c8,"PUBLISH_DATE" = @.c9,"MY_ADVISORS" = @.c10,"SUBMITTED_IND" = @.c11,"ACTIVE_IND" = @.c12,"CREATE_DATE" = @.c13,"CREATED_BY" = @.c14,"LAST_UPDATE_DATE" = @.c15,"LAST_UPDATED_BY" = @.c16,"DATE_INDEXED" = @.c17,"ON_DEMAND_IND" = @.c18,"NOT_REPORTED_IND" = @.c19,"MAJOR_PROJECT_IND" = @.c20,"MAJOR_PROJECT_COMMENT" = @.c21
where "MEETING_ID" = @.c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@.c12 ,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21 )
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?From Books online's Create Procedure reference;number
Is an optional integer used to group procedures of the same name so they can be
dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter around
procedure_name only.
I'll admit that I have neither used this functionality, nor ever seen it used. It may be that the application specifies which version of the procedure is executed by including the number in the call. And in that case, I wouldn't go dropping or commenting out the code, since it could break your app.

Replicating Stored Procedure

Hi:
I made a change to the stored proc in the publisher database but the change
did not get replicated. I should not have to run the snapshot agent again to
do that. Please let me know how can this be easily done.
Thanks
Mike,
this is better handled in SQL 2005, but in 2000 we can use sp_addscriptexec,
linked servers or manual addition of the new code. For this reason I
generally add the code parts of my publications (sps, views, functions) to a
separate publication, so if I decide to use replication to send them over, it
doesn't disturb my main publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use sp_addscriptexec to send the commands if your subscribers were deployed
through UNC(file copies). If there were deployed through ftp you will have
to connect to them individual and run the commands individually.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:15028C08-3FA7-4647-9C23-7A2698AF8C41@.microsoft.com...
> Hi:
> I made a change to the stored proc in the publisher database but the
> change
> did not get replicated. I should not have to run the snapshot agent again
> to
> do that. Please let me know how can this be easily done.
> Thanks
>

Replicating Stored Proc Execution

Hi Guys:
When we run the proc from Query Analyzer, we can see the replication of
store proc execution on subscriber/distributor. However when we run this
proc from Java application, we don't see the proc being executed on
subscriber even though we see it on distributor. Any reason why execution of
proc is not replicated properly when executing it from Java. Please let me
know.
Thanks
When you say you see it in the distributor do you mean it shows up in the
output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
that it is running there with the correct parameters. Then run it manually
on the subscriber to see if it works there. It is possible that the proc is
referencing non-existent tables or data which is not present on the
subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
> Hi Guys:
> When we run the proc from Query Analyzer, we can see the replication of
> store proc execution on subscriber/distributor. However when we run this
> proc from Java application, we don't see the proc being executed on
> subscriber even though we see it on distributor. Any reason why execution
> of
> proc is not replicated properly when executing it from Java. Please let
> me
> know.
> Thanks
|||that is what we did we used the profiler on the subscriber but we don't find
that proc when running from Java. You are saying that if the proc is
referencing non-existance table/data, it will not show up in profiler. We
are pretty sure that table is there. Any other thoughts.
Thanks
"Hilary Cotter" wrote:

> When you say you see it in the distributor do you mean it shows up in the
> output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
> that it is running there with the correct parameters. Then run it manually
> on the subscriber to see if it works there. It is possible that the proc is
> referencing non-existent tables or data which is not present on the
> subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
>
>
|||This is bewildering. There are some options for stored procedure execution -
like only running within a serializable transaction. Are you using any of
these?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...[vbcol=seagreen]
> that is what we did we used the profiler on the subscriber but we don't
> find
> that proc when running from Java. You are saying that if the proc is
> referencing non-existance table/data, it will not show up in profiler. We
> are pretty sure that table is there. Any other thoughts.
> Thanks
> "Hilary Cotter" wrote:
|||Thanks for your setting. That is what we thought because we were using that
option (serializable). Now we changed it to execute procedure every time but
still having this problem. Not sure whether Java is using some setting
which is causing this.
"Hilary Cotter" wrote:

> This is bewildering. There are some options for stored procedure execution -
> like only running within a serializable transaction. Are you using any of
> these?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...
>
>