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.

Replication and Enterprise Manager

Hi
I have a very interesting situation with my replication. Replication works
great if I leave opened Enterprise Manager, but when I close it,
replication and SQL server does not work any more.
If I made a "mistake" and close EM, that the next morning SQL server does
not respond even if I reboot server. I must reinstall SQL service pack and
than it works. This was happened 5 time and now I am positivly shure that
this is the problem.
And yes ... this is SQL 2000 with SP3, Windows 2000 Server with Terminal
services installed.
Did anyone knows about this funny problem?
Thank you
what security context are your replication jobs running under? It sounds
like they are not running under a system administrator account.
To fix this right click on your agents and look at your agent properties.
Change the owner account to an account which is in the system administrator
role.
<dklancar@.nkbm.si> wrote in message
news:uxK3yvvFEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a very interesting situation with my replication. Replication works
> great if I leave opened Enterprise Manager, but when I close it,
> replication and SQL server does not work any more.
> If I made a "mistake" and close EM, that the next morning SQL server does
> not respond even if I reboot server. I must reinstall SQL service pack and
> than it works. This was happened 5 time and now I am positivly shure that
> this is the problem.
> And yes ... this is SQL 2000 with SP3, Windows 2000 Server with Terminal
> services installed.
> Did anyone knows about this funny problem?
> Thank you
|||Thank you for response, but I must tell you, that all agent are running as
user listed in the system admin role.
Thank you again.
|||If SQL Server won't start, is there anything listed in hte Windows Application Log or the SQL Server log that sheds any light?
Regards,
Paul Ibison

replication and datetime columns

I have an interesting server occuring. We have transactional replication running with 1 pub and 6 subscribers. On nearly all the tables we have some sort of date column that's a smalldatetime. If I look at the pub the data will appear like:

2006-11-13 09:59:00

However, on all the subs it appears like:

2006-11-13 09:59:00.000

It's the same column and record but for some reason it's become a datetime. I checked the column types and they are both smalldatetime's.

Why is this happening? What can be done?

Thanks,

Phil

I am unable to repro this on my SQL 2000 and SQL 2005 servers. What version of SQL Server are you running and what SPs?|||

Actually I found the problem. It looks as if when you use the four part name server.db.dbo.tablename from the publisher that the column "appears" to be a datetime. When I actually go to the server and select it.. then it's the normal smalldatetime. I don't know if this has something to do w/ ODBC connection accross linked servers or what...

Thanks,

Phil