a group of sp_MSins stored procs has 2 members?

I decide to check with the forum because I haven't got any lucky result.
Here is the transactional replication between two SQL 2000 standard edition
+ sp3 servers I inherited a few month ago. On the subscriber, when I pushed
a new subcription there was an error "Cannot insert duplicate key row in
object bla bla with unique index 'bla bla'". Well, the errors are easy to
understand and fix. To my surprise, I noticed that the replication insert
stored proc has two stored procs defined in it. It looks like this:
create procedure "sp_MSins_tablename" @.c1 smalldatetime,@.c2 int,@.c3
smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
AS
BEGIN
insert into "smfPrice"(
"PriceDate", "SecurityId", "SecurityVersion", "AdjustedPrice", "Price",
"AuditInformationId", "IsoCurrencyCode", "DataSourceId"
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8
)
END
GO
create procedure "sp_MSins_tablename";2 @.c1 smalldatetime,@.c2 int,@.c3
smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
as
if exists ( select * from "tablename"
where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
)
begin
update "tablename" set "AdjustedPrice" = @.c4,"Price" =
@.c5,"AuditInformationId" = @.c6,"IsoCurrencyCode" = @.c7,"DataSourceId" = @.c8
where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
end
else
begin
insert into "tablename" (
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf
ormationId","IsoCurrencyCode","DataSourceId" ) values (
@.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8 )
end
GO
I think the second version is better writen than the first one and can
suppress the said error. But obviously SQL server replication is not able to
pick the right one. When I swaped the procs titles while leaving the
definitions unchanged, the error went away.
My puzzle is: What event triggers the generation of the second set of procs?
What internal mechanism guarantees replication to use the correct version?
I'm thinking of removing the first instance of procs because the second one
already covers it. But for the future, I want to find out why it happens and
potentially come up with a better solution. I hope this is not a bug.
I'd like to hear your thoughs.
Richard
the second proc is a custom proc generated by a user. I suspect each proc
has a different owner. You should be able to check this by doing an sp_help
sp_MSins_tablename
or by browsing them in EM,
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:u7IKTHttEHA.4044@.TK2MSFTNGP09.phx.gbl...
>I decide to check with the forum because I haven't got any lucky result.
> Here is the transactional replication between two SQL 2000 standard
> edition
> + sp3 servers I inherited a few month ago. On the subscriber, when I
> pushed
> a new subcription there was an error "Cannot insert duplicate key row in
> object bla bla with unique index 'bla bla'". Well, the errors are easy to
> understand and fix. To my surprise, I noticed that the replication insert
> stored proc has two stored procs defined in it. It looks like this:
> create procedure "sp_MSins_tablename" @.c1 smalldatetime,@.c2 int,@.c3
> smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
> AS
> BEGIN
> insert into "smfPrice"(
> "PriceDate", "SecurityId", "SecurityVersion", "AdjustedPrice", "Price",
> "AuditInformationId", "IsoCurrencyCode", "DataSourceId"
> )
> values (
> @.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8
> )
> END
> GO
> create procedure "sp_MSins_tablename";2 @.c1 smalldatetime,@.c2 int,@.c3
> smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
> as
> if exists ( select * from "tablename"
> where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
> )
> begin
> update "tablename" set "AdjustedPrice" = @.c4,"Price" =
> @.c5,"AuditInformationId" = @.c6,"IsoCurrencyCode" = @.c7,"DataSourceId" =
> @.c8
> where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
> end
> else
> begin
> insert into "tablename" (
> "PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf
> ormationId","IsoCurrencyCode","DataSourceId" ) values (
> @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8 )
> end
> GO
> I think the second version is better writen than the first one and can
> suppress the said error. But obviously SQL server replication is not able
> to
> pick the right one. When I swaped the procs titles while leaving the
> definitions unchanged, the error went away.
> My puzzle is: What event triggers the generation of the second set of
> procs?
> What internal mechanism guarantees replication to use the correct version?
> I'm thinking of removing the first instance of procs because the second
> one
> already covers it. But for the future, I want to find out why it happens
> and
> potentially come up with a better solution. I hope this is not a bug.
> I'd like to hear your thoughs.
>
> Richard
>
>
|||These two procs are actually in one definition, except they have a suffix.
And they all belong to dbo.
I am able to reproduce what has happened by checking the "do not block"
option on and off on the snapshot tab in publicatio properties, I could see
two different version of procs are generated. I can also see the diference
is with the sp_addpublication @.sync_method variable. One is 'native' that
creates one sp_MSins proc; When I check the said option, SQL uses
'concurrent' and creates two procs in one set.
To clarify this, the two procs in one set means two "create proc" statements
in the GUI when you double click on the proc, one of which is tagged with
";2" as in sp_MSinstablename;2
99% of my publication are created this way and they have been running well
with no problems. That means SQL Server replicaiton knows when to use which
proc to do the job. My current error indicates that replication somehow lost
its judgement like it used to. It takes the default proc (the top one)
instead of taking the second (bottom one). So far, I've no luck in finding
out any useful hints.
Microsoft developers, possible to shed some light on this?
Richard
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eEzksEutEHA.3200@.TK2MSFTNGP14.phx.gbl...
> the second proc is a custom proc generated by a user. I suspect each proc
> has a different owner. You should be able to check this by doing an
sp_help[vbcol=seagreen]
> sp_MSins_tablename
> or by browsing them in EM,
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:u7IKTHttEHA.4044@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
insert[vbcol=seagreen]
@.c3[vbcol=seagreen]
@.c3[vbcol=seagreen]
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf[vbcol=seagreen]
able[vbcol=seagreen]
version?
>
|||I've made a bit more progress:
It appears sp_script_reconciliation_insproc created sp_MSins;2.
sp_scriptinsproc created sp_MSins.
sp_depends shows nobody depends on sp_script_reconciliation_insproc.
Looks like two separate processes triggered each stored proc individually
with sp_scriptinsproc doing it first.
Is this a normal behavior?
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:u9mlzW5tEHA.3200@.TK2MSFTNGP09.phx.gbl...
> These two procs are actually in one definition, except they have a suffix.
> And they all belong to dbo.
> I am able to reproduce what has happened by checking the "do not block"
> option on and off on the snapshot tab in publicatio properties, I could
see
> two different version of procs are generated. I can also see the diference
> is with the sp_addpublication @.sync_method variable. One is 'native' that
> creates one sp_MSins proc; When I check the said option, SQL uses
> 'concurrent' and creates two procs in one set.
> To clarify this, the two procs in one set means two "create proc"
statements
> in the GUI when you double click on the proc, one of which is tagged with
> ";2" as in sp_MSinstablename;2
> 99% of my publication are created this way and they have been running well
> with no problems. That means SQL Server replicaiton knows when to use
which
> proc to do the job. My current error indicates that replication somehow
lost[vbcol=seagreen]
> its judgement like it used to. It takes the default proc (the top one)
> instead of taking the second (bottom one). So far, I've no luck in finding
> out any useful hints.
> Microsoft developers, possible to shed some light on this?
>
> Richard
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eEzksEutEHA.3200@.TK2MSFTNGP14.phx.gbl...
proc[vbcol=seagreen]
> sp_help
result.[vbcol=seagreen]
in[vbcol=seagreen]
> to
> insert
"Price",[vbcol=seagreen]
> @.c3
=
> @.c3
>
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf[vbcol=seagreen]
> able
> version?
second[vbcol=seagreen]
happens
>

A fix for "SelectMethod= cursor"?

I haven't seen this mentioned anywhere, is this covered under the
"Update Performance" and/or "Updateable recordsets" headings? Having
to include that in the connection string greatly reduced our
performance in previous attempts to use SQL Server from our Java
application. If so I will start down the path of trying to convince
the powers that be of the need to switch from Oracle to SQL Server.
The main reason I got shot down before was performance of the free
drivers, and cost of the not-free drivers with good performance.
Thanks,
Ed
EdGmail wrote:
> I haven't seen this mentioned anywhere, is this covered under the
> "Update Performance" and/or "Updateable recordsets" headings? Having
> to include that in the connection string greatly reduced our
> performance in previous attempts to use SQL Server from our Java
> application. If so I will start down the path of trying to convince
> the powers that be of the need to switch from Oracle to SQL Server.
> The main reason I got shot down before was performance of the free
> drivers, and cost of the not-free drivers with good performance.
> Thanks,
> Ed
>
There is no way around the selectMethod=cursor setting if you want
transactional behavior, for example. Also, even with selectMethod=direct
the old MS driver will cache pretty much everything to memory.
Your choices are the new MS driver for SQL Server 2005 (a copy of the
JNetDirect driver, which is currently in beta and still works --
although undocumented -- with older versions of SQL Server) or jTDS (you
would probably be surprised that the performance and stability of jTDS
is at least comparable to that of the commercial drivers).
Disclaimer: I am a jTDS developer.
Alin,
The jTDS Project.
|||I remember now why the "SelectMethod=Cursor" was causing a problem. It
wasn't so much performance as it was causing exceptions when we tried
to do our initial system load with Ant and Torque. I never researched
it thoroughly, but apparently Torque (or ant) spawns threads which
attempt to clone the connection, which the original MS driver choked
on. We tried the JNetDirect driver and that worked OK w/o
"SelectMethod", but we (ok, managment) just didn't want to cough up the
dough for it. Is that particular problem addressed by jTDS or the new
MS driver?
Thanks,
Ed
|||EdGmail wrote:
> Is that particular problem addressed by jTDS or the new MS driver?
Actually that's only a problem of the DataDirect driver and its
derivatives, including the old MS driver. It's not a limitation of SQL
Server, so the other drivers don't have this issue.
Alin,
The jTDS Project.