replication transaction logs

Hello every body.
How can I find the tables or files where the replication log is stored.
I mean, I would like to know how the replication worked during the weekend
and what data insert, update or delete.
Thanks a lot, Lina
There is no log per se. However the commands are stored in the distribution
database, in the msrepl_commands table. You can read them using
sp_browsereplcmds.
However for named subscriptions these commands are removed every 10 minutes.
For anonymous subscriptions, these commands are removed by default every 2
days.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lina Manjarres" <Lina Manjarres@.discussions.microsoft.com> wrote in message
news:642C6E7E-BC57-42E3-B7FE-E351E7A52859@.microsoft.com...
> Hello every body.
> How can I find the tables or files where the replication log is stored.
> I mean, I would like to know how the replication worked during the weekend
> and what data insert, update or delete.
> Thanks a lot, Lina
|||Lina,
you might also want to examine the contents of the _history tables in the
distribution database.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks a lot!
"Hilary Cotter" wrote:

> There is no log per se. However the commands are stored in the distribution
> database, in the msrepl_commands table. You can read them using
> sp_browsereplcmds.
> However for named subscriptions these commands are removed every 10 minutes.
> For anonymous subscriptions, these commands are removed by default every 2
> days.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Lina Manjarres" <Lina Manjarres@.discussions.microsoft.com> wrote in message
> news:642C6E7E-BC57-42E3-B7FE-E351E7A52859@.microsoft.com...
>
>
|||Thank you!
"Paul Ibison" wrote:

> Lina,
> you might also want to examine the contents of the _history tables in the
> distribution database.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Replication scenario - seeking suggestion

I have two sites. Site A and Site B

Each site has two databases

Site A

Db1

Db2

Site B

Db1

Db2

Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.

I started Site A as pubisher and distributor and Site A and Site B both as subscriber.

Site B is in a different geographical area (state).

-

Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.

-

Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.

-

P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.

You can publish to Db1 then use Db1 as a republisher to publisher to Db2.

See in Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/a1485cf4-b1c4-49e9-ab06-8ccfaad998f3.htm

Martin

|||

Thank you!!

looks good.

For Site A - Db1

publisher, distributor and subsriber(Site A- Db2)

Db 2

Publisher Site A

Distributor Site B

Subscriber Db1

Subscriber Db2

I think, this is what you are suggesting.

|||

My suggestion would have the distributor on the same machine so:

A - DB 1 (master publisher)

A - DB 2 (subscriber)

B - DB 1 (subscriber , republisher)

B - DB 2 (subscriber (to B - DB 1)

Martin

|||

Thank you!!

I never tried republisher, I am running Sql Server 2000.

Let me read it, if I will have any question then I will get back.

Moreover, I could not access that help, this does not work from my computer.

This is exactly I would prefer, because otherwise it seems stupid to send data twice to the other site.

|||

Oh this is for SQL 2005 only. You need to install the SQL2005 books online to view the help link.

Martin

|||Thank you!

replication question!

Hi,
How do i truncate transaction log when the transactional replication is in place?
Thanks in advance.You don't, you back it up.

Replication question

Hi,
SQL 2000
I disabled the transaction replication from publisher server and everything
cleaned up from publisher, but I still can see agents under subscription on
subscriber server. How can I reomove the agent from subscriber server ?
Thanks.
PatIs that a pull subscription? You should be able to delete the pull agent.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Patrick" <patriarck@.gmail.com> wrote in message
news:%2392B8lCIFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi,
> SQL 2000
> I disabled the transaction replication from publisher server and
everything
> cleaned up from publisher, but I still can see agents under subscription
on
> subscriber server. How can I reomove the agent from subscriber server ?
> Thanks.
> Pat
>|||No, it was a push !
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e$T9N9CIFHA.588@.TK2MSFTNGP15.phx.gbl...
> Is that a pull subscription? You should be able to delete the pull agent.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:%2392B8lCIFHA.1476@.TK2MSFTNGP09.phx.gbl...
> everything
> on
>

replication question

Hi guys,
I have a question, not sure if it's do-able.
I have a transaction replication setup on Server A (Publisher) and Server B
is my Subscriber) I have over 100 tables in this publication.
I want to change order of columns to just one table in Publisher. is
there a way that I can do it without dropping publication?
let said my table have column A , column B, column C
I want to change order to Column B, Column C , Column A
nope. You would have to drop replication, script out your tables, export
your database, drop the tables, recreate them with the correct column order
and then reimport the data.
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
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:O%23hiljsuFHA.3588@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have a question, not sure if it's do-able.
> I have a transaction replication setup on Server A (Publisher) and Server
B
> is my Subscriber) I have over 100 tables in this publication.
> I want to change order of columns to just one table in Publisher. is
> there a way that I can do it without dropping publication?
>
> let said my table have column A , column B, column C
> I want to change order to Column B, Column C , Column A
>

Replication problems after SP4 upgrade

Hi,
We're experiencing a problem with transaction replication on SP4.
The snapshot is taken without any problems and applies, however, when it
comes to processing the additional transactions which have taken place since
the snaphot, the process starts processing in parrallel and blocks itself.
The process eventually fails and rollbacks.
This used to work fine on SP3a, but not any more... anyone any ideas?
Thanks in advance
Dave
Dave Wall
what do you mean by processing in parallel? Can you log the output to a text
file and post it here.
Follow these step on how to enable logging.
http://support.microsoft.com/default...b;en-us;312292
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
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:3B4815C4-7438-4C5C-8F81-B9720EB1B0B3@.microsoft.com...
> Hi,
> We're experiencing a problem with transaction replication on SP4.
> The snapshot is taken without any problems and applies, however, when it
> comes to processing the additional transactions which have taken place
since
> the snaphot, the process starts processing in parrallel and blocks itself.
> The process eventually fails and rollbacks.
> This used to work fine on SP3a, but not any more... anyone any ideas?
> Thanks in advance
> Dave
> --
> Dave Wall
|||Hi Dave, it would be great if you can post the [abridged] result of sp_who2
on the distributor\subscriber (or wherever the blocking occurs) as well.
Thanks.
-Raymond
"Dave Wall" wrote:

> Hi,
> We're experiencing a problem with transaction replication on SP4.
> The snapshot is taken without any problems and applies, however, when it
> comes to processing the additional transactions which have taken place since
> the snaphot, the process starts processing in parrallel and blocks itself.
> The process eventually fails and rollbacks.
> This used to work fine on SP3a, but not any more... anyone any ideas?
> Thanks in advance
> Dave
> --
> Dave Wall
|||Hi Raymond,
Got the thing sorted last night. Two things helped. The first was to
increase the minimum query plan threshold for considering paralell execution.
Secondly - we found that our networks team hadn't set the server up with PAE
extensions and so SQL wasn't using all the memory i'd assigned it. Once
these two were changed - replication went through first time.
Looks like a similar problem we had with a another server (see another post)
were processes were blocking themselves. Without the additional memory, IO
went through the roof and the spids started blocking themselves (whilst
waiting for IO)
Sorted the memory, and alls well now.
Thanks
Dave
Dave Wall
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Dave, it would be great if you can post the [abridged] result of sp_who2
> on the distributor\subscriber (or wherever the blocking occurs) as well.
> Thanks.
> -Raymond
> "Dave Wall" wrote:
|||Dave,
Am curious which server needed the extra memory. Was it pub, sub, or
dist?
Thanks
Mark Andersen

Replication Problem? Very high undistributed commands --transaction replication

Can anybody help me
we have a transactional replication going on from server A to Server B.
(sql server 2005)
The replication working properly till yesterday..but now i dont find
the recent data at subscriber .. synchronization status shows
"Delivering replicated transactions" When i checked replication monitor
there are so many undistributed commands . replicated commands are not
getting applied at the subscriber..
how to resolve this issue?
thanksJust because you have a high number of undistributed commands doesn't
necessarily mean they are not getting distributed. What it means is that
they are waiting to be distributed. This could be a performance problem, or
it could be that there are a lot of text commands slowing the overall
throughput, or it could mean that you have subscribers which are offline.
The most significant thing you can do to improve throughput is to set
subscriptionstreams to something like 8.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> Can anybody help me
> we have a transactional replication going on from server A to Server B.
> (sql server 2005)
> The replication working properly till yesterday..but now i dont find
> the recent data at subscriber .. synchronization status shows
> "Delivering replicated transactions" When i checked replication monitor
> there are so many undistributed commands . replicated commands are not
> getting applied at the subscriber..
> how to resolve this issue?
> thanks
>|||i dont find the subscriptionstreams option in the distribution agent
profile..is there any other way? should i stop the distribution agent
and start again' or should i reinitialize the subscription? or
should take the snapshot by starting snapshot agent?'
there are around 2207484 undistributed commands
plzz help
thanks
Hilary Cotter wrote:[vbcol=seagreen]
> Just because you have a high number of undistributed commands doesn't
> necessarily mean they are not getting distributed. What it means is that
> they are waiting to be distributed. This could be a performance problem, o
r
> it could be that there are a lot of text commands slowing the overall
> throughput, or it could mean that you have subscribers which are offline.
> The most significant thing you can do to improve throughput is to set
> subscriptionstreams to something like 8.
> --
> 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
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...|||You have to add it manually to the distribution agent like
this -SubscriptionStreams 8
You don't have to create another snapshot, you have to figure out why it is
so slow. For example are their triggers hanging off the subscriber tables?
Are their subscribers who have not connected in some time? Are you
replicating text - run profiler on the subscriber to determine what commands
are flowing right now.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
>i dont find the subscriptionstreams option in the distribution agent
> profile..is there any other way? should i stop the distribution agent
> and start again' or should i reinitialize the subscription? or
> should take the snapshot by starting snapshot agent?'
> there are around 2207484 undistributed commands
> plzz help
> thanks
>
> Hilary Cotter wrote:
>|||thanks for ur reply..
Could you plzz tell me how do i add the SubscriptionStreams manually'
Yes there are 3 triggers on the tables at the subscriber side.which get
fired when there is an insert/update from the publisher to those
tables..
However, as per replication monitor the status is excellent..latency is
0..only thing is replicated transactions are not gettting applied at
the subscriber..the undistributed transaction count keeps on
growing..i dont see new records aded at the subscriber in last 20-22
hrs'
Earlier the synchronization status used to show the message something
like this 'Delivered 18 transactions with 18 commands"..however now it
always shows this message "Delivering replicated transaction"..
could u plzz tell me wat should i do'
thanks
Hilary Cotter wrote:[vbcol=seagreen]
> You have to add it manually to the distribution agent like
> this -SubscriptionStreams 8
> You don't have to create another snapshot, you have to figure out why it i
s
> so slow. For example are their triggers hanging off the subscriber tables?
> Are their subscribers who have not connected in some time? Are you
> replicating text - run profiler on the subscriber to determine what comman
ds
> are flowing right now.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...

Replication Problem? Very high undistributed commands --transaction replication

Can anybody help me
we have a transactional replication going on from server A to Server B.
(sql server 2005)
The replication working properly till yesterday..but now i dont find
the recent data at subscriber .. synchronization status shows
"Delivering replicated transactions" When i checked replication monitor
there are so many undistributed commands . replicated commands are not
getting applied at the subscriber..
how to resolve this issue?
thanksJust because you have a high number of undistributed commands doesn't
necessarily mean they are not getting distributed. What it means is that
they are waiting to be distributed. This could be a performance problem, or
it could be that there are a lot of text commands slowing the overall
throughput, or it could mean that you have subscribers which are offline.
The most significant thing you can do to improve throughput is to set
subscriptionstreams to something like 8.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> Can anybody help me
> we have a transactional replication going on from server A to Server B.
> (sql server 2005)
> The replication working properly till yesterday..but now i dont find
> the recent data at subscriber .. synchronization status shows
> "Delivering replicated transactions" When i checked replication monitor
> there are so many undistributed commands . replicated commands are not
> getting applied at the subscriber..
> how to resolve this issue?
> thanks
>|||i dont find the subscriptionstreams option in the distribution agent
profile..is there any other way? should i stop the distribution agent
and start again' or should i reinitialize the subscription? or
should take the snapshot by starting snapshot agent?'
there are around 2207484 undistributed commands
plzz help
thanks
Hilary Cotter wrote:
> Just because you have a high number of undistributed commands doesn't
> necessarily mean they are not getting distributed. What it means is that
> they are waiting to be distributed. This could be a performance problem, or
> it could be that there are a lot of text commands slowing the overall
> throughput, or it could mean that you have subscribers which are offline.
> The most significant thing you can do to improve throughput is to set
> subscriptionstreams to something like 8.
> --
> 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
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> > Can anybody help me
> >
> > we have a transactional replication going on from server A to Server B.
> > (sql server 2005)
> >
> > The replication working properly till yesterday..but now i dont find
> > the recent data at subscriber .. synchronization status shows
> > "Delivering replicated transactions" When i checked replication monitor
> > there are so many undistributed commands . replicated commands are not
> > getting applied at the subscriber..
> >
> > how to resolve this issue?
> >
> > thanks
> >|||You have to add it manually to the distribution agent like
this -SubscriptionStreams 8
You don't have to create another snapshot, you have to figure out why it is
so slow. For example are their triggers hanging off the subscriber tables?
Are their subscribers who have not connected in some time? Are you
replicating text - run profiler on the subscriber to determine what commands
are flowing right now.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
>i dont find the subscriptionstreams option in the distribution agent
> profile..is there any other way? should i stop the distribution agent
> and start again' or should i reinitialize the subscription? or
> should take the snapshot by starting snapshot agent?'
> there are around 2207484 undistributed commands
> plzz help
> thanks
>
> Hilary Cotter wrote:
>> Just because you have a high number of undistributed commands doesn't
>> necessarily mean they are not getting distributed. What it means is that
>> they are waiting to be distributed. This could be a performance problem,
>> or
>> it could be that there are a lot of text commands slowing the overall
>> throughput, or it could mean that you have subscribers which are offline.
>> The most significant thing you can do to improve throughput is to set
>> subscriptionstreams to something like 8.
>> --
>> 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
>> "Vishal" <rvishal1305@.gmail.com> wrote in message
>> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
>> > Can anybody help me
>> >
>> > we have a transactional replication going on from server A to Server B.
>> > (sql server 2005)
>> >
>> > The replication working properly till yesterday..but now i dont find
>> > the recent data at subscriber .. synchronization status shows
>> > "Delivering replicated transactions" When i checked replication monitor
>> > there are so many undistributed commands . replicated commands are not
>> > getting applied at the subscriber..
>> >
>> > how to resolve this issue?
>> >
>> > thanks
>> >
>|||thanks for ur reply..
Could you plzz tell me how do i add the SubscriptionStreams manually'
Yes there are 3 triggers on the tables at the subscriber side.which get
fired when there is an insert/update from the publisher to those
tables..
However, as per replication monitor the status is excellent..latency is
0..only thing is replicated transactions are not gettting applied at
the subscriber..the undistributed transaction count keeps on
growing..i dont see new records aded at the subscriber in last 20-22
hrs'
Earlier the synchronization status used to show the message something
like this 'Delivered 18 transactions with 18 commands"..however now it
always shows this message "Delivering replicated transaction"..
could u plzz tell me wat should i do'
thanks
Hilary Cotter wrote:
> You have to add it manually to the distribution agent like
> this -SubscriptionStreams 8
> You don't have to create another snapshot, you have to figure out why it is
> so slow. For example are their triggers hanging off the subscriber tables?
> Are their subscribers who have not connected in some time? Are you
> replicating text - run profiler on the subscriber to determine what commands
> are flowing right now.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
> >i dont find the subscriptionstreams option in the distribution agent
> > profile..is there any other way? should i stop the distribution agent
> > and start again' or should i reinitialize the subscription? or
> > should take the snapshot by starting snapshot agent?'
> > there are around 2207484 undistributed commands
> >
> > plzz help
> >
> > thanks
> >
> >
> > Hilary Cotter wrote:
> >> Just because you have a high number of undistributed commands doesn't
> >> necessarily mean they are not getting distributed. What it means is that
> >> they are waiting to be distributed. This could be a performance problem,
> >> or
> >> it could be that there are a lot of text commands slowing the overall
> >> throughput, or it could mean that you have subscribers which are offline.
> >> The most significant thing you can do to improve throughput is to set
> >> subscriptionstreams to something like 8.
> >> --
> >> 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
> >>
> >> "Vishal" <rvishal1305@.gmail.com> wrote in message
> >> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> >> > Can anybody help me
> >> >
> >> > we have a transactional replication going on from server A to Server B.
> >> > (sql server 2005)
> >> >
> >> > The replication working properly till yesterday..but now i dont find
> >> > the recent data at subscriber .. synchronization status shows
> >> > "Delivering replicated transactions" When i checked replication monitor
> >> > there are so many undistributed commands . replicated commands are not
> >> > getting applied at the subscriber..
> >> >
> >> > how to resolve this issue?
> >> >
> >> > thanks
> >> >
> >sql

Replication problem with stored procedure

I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:

Insert Error: Column name or number of supplied values does not match table definition.

The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.

The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.

Please advise how to slove this problem. Below is the part of the store procedure and the error message:

THANK YOU IN ADVANCE!

STOREd PROCEDURE:

ALTER PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]

@.wsSTUAPP_WEXP_iID int

, @.wsSTUAPP_iID int

, @.wsSTUAPP_WEXP_iOrder int

, @.wsSTUAPP_WEXP_sEmployerName varchar(75)

, @.wsSTUAPP_WEXP_sJobTitle varchar(75)

, @.wsSTUAPP_WEXP_sJobDESC varchar(360)

, @.wsSTUAPP_WEXP_dStart datetime

, @.wsSTUAPP_WEXP_dEnd datetime

AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)

BEGIN

INSERT INTO tbl_wsSTUAPP_WEXP

VALUES(

@.wsSTUAPP_iID

,@.wsSTUAPP_WEXP_iOrder

,@.wsSTUAPP_WEXP_sEmployerName

,@.wsSTUAPP_WEXP_sJobTitle

,@.wsSTUAPP_WEXP_sJobDESC

,@.wsSTUAPP_WEXP_dStart

,@.wsSTUAPP_WEXP_dEnd

)

END

ELSE

BEGIN

UPDATE tbl_wsSTUAPP_WEXP

SET wsSTUAPP_WEXP_iOrder= @.wsSTUAPP_WEXP_iOrder

,wsSTUAPP_WEXP_sEmployerName= @.wsSTUAPP_WEXP_sEmployerName

,wsSTUAPP_WEXP_sJobTitle= @.wsSTUAPP_WEXP_sJobTitle

,wsSTUAPP_WEXP_sJobDESC= @.wsSTUAPP_WEXP_sJobDESC

,wsSTUAPP_WEXP_dStart= @.wsSTUAPP_WEXP_dStart

,wsSTUAPP_WEXP_dEnd= @.wsSTUAPP_WEXP_dEnd

WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID

END

ERROR MESSAGE:

Command attempted:

CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)

Error messages:

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Hi Jim,

You are likely to be hitting the same issue as discussed in the following forum thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493307&SiteID=1

and this is basically the unintended consequence of the interaction among the following factors:

1) Replicated identity columns are marked "not for replication" by default at the publisher when you setup replication through SSMS in SQL2005

2) Any insert statements into a table with an identity column marked 'not for replication' executed from a replication agent (distribution agent for example) must supply an explicit value for the identity column

3) In SQL2005, the server verifies the validity of insert statements in stored procedures and triggers during the creation of these objects and raises error if it determines that an embedded insert statement does not supply all the required values.

In more concrete terms, 1) will basically lead to all identity columns being marked 'not for replication' at both the publisher and the subscriber and given that your replicated stored procedure is going to be created through a distribution agent connection, 2) and 3) will lead the subscriber server into thinking that the insert statement embedded in your stored procedure would need to supply an explicit value for the identity column marked 'not for replication' and so it raises an error when an explicit identity value is found missing. Now, the stored procedure that you are replicating is probably intended to be executed outside of replication even though it is created through replication so the check is technically invalid despite the good intentions behind it.

The only workaround that I can think of is to use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher and then re-initialize your subscriber using a new snaphsot. This is admittedly not a very good workaround but the good news is that this particular problem is fixed for snapshot processing in SP2. The bad news is that the same problem applies to our ddl replication logic (alteration of stored procedure e.g.) and we don't have a general solution in that part of replication yet. My suggestion would be to separate your schema objects (stored procedures) into a separate snapshot publication so changes to your stored procedures etc. can be refreshed through snapshot processing when SP2 comes out.

Hope that hope,

-Raymond

|||

The failure may be caused by implict column list in INSERT statement. Do you replicate all the columns in the table or are there any identity column in the replicated table? You can try to explicitly specify the column list in your INSERT statement.

Hope it helps.

Peng

|||

Thanks Raymond and Peng,

The problem has been sloved by the following

"use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher"

I really appreciate it!

Jim

|||

Hello Raymond,

I failed to use 'sp_identitycolumnforreplication' to disable the 'not for replication' property on the identity columns. However, it was succeessful before. But it doesn't work any more.

I tried to restore the database, and re-install SQL server, no use.

How to fix 'sp_identitycolumnforreplication' ?

Here is my query:

declare @.int int

set @.int =object_id('tbl_wsSTUAPP_WEXP')

exec sys.sp_identitycolumnforreplication @.int,0

GO

Thank you!

Replication problem with stored procedure

I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:

Insert Error: Column name or number of supplied values does not match table definition.

The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.

The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.

Please advise how to slove this problem. Below is the part of the store procedure and the error message:

THANK YOU IN ADVANCE!

STOREd PROCEDURE:

ALTER PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]

@.wsSTUAPP_WEXP_iID int

, @.wsSTUAPP_iID int

, @.wsSTUAPP_WEXP_iOrder int

, @.wsSTUAPP_WEXP_sEmployerName varchar(75)

, @.wsSTUAPP_WEXP_sJobTitle varchar(75)

, @.wsSTUAPP_WEXP_sJobDESC varchar(360)

, @.wsSTUAPP_WEXP_dStart datetime

, @.wsSTUAPP_WEXP_dEnd datetime

AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)

BEGIN

INSERT INTO tbl_wsSTUAPP_WEXP

VALUES(

@.wsSTUAPP_iID

,@.wsSTUAPP_WEXP_iOrder

,@.wsSTUAPP_WEXP_sEmployerName

,@.wsSTUAPP_WEXP_sJobTitle

,@.wsSTUAPP_WEXP_sJobDESC

,@.wsSTUAPP_WEXP_dStart

,@.wsSTUAPP_WEXP_dEnd

)

END

ELSE

BEGIN

UPDATE tbl_wsSTUAPP_WEXP

SET wsSTUAPP_WEXP_iOrder= @.wsSTUAPP_WEXP_iOrder

,wsSTUAPP_WEXP_sEmployerName= @.wsSTUAPP_WEXP_sEmployerName

,wsSTUAPP_WEXP_sJobTitle= @.wsSTUAPP_WEXP_sJobTitle

,wsSTUAPP_WEXP_sJobDESC= @.wsSTUAPP_WEXP_sJobDESC

,wsSTUAPP_WEXP_dStart= @.wsSTUAPP_WEXP_dStart

,wsSTUAPP_WEXP_dEnd= @.wsSTUAPP_WEXP_dEnd

WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID

END

ERROR MESSAGE:

Command attempted:

CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)

Error messages:

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Hi Jim,

You are likely to be hitting the same issue as discussed in the following forum thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493307&SiteID=1

and this is basically the unintended consequence of the interaction among the following factors:

1) Replicated identity columns are marked "not for replication" by default at the publisher when you setup replication through SSMS in SQL2005

2) Any insert statements into a table with an identity column marked 'not for replication' executed from a replication agent (distribution agent for example) must supply an explicit value for the identity column

3) In SQL2005, the server verifies the validity of insert statements in stored procedures and triggers during the creation of these objects and raises error if it determines that an embedded insert statement does not supply all the required values.

In more concrete terms, 1) will basically lead to all identity columns being marked 'not for replication' at both the publisher and the subscriber and given that your replicated stored procedure is going to be created through a distribution agent connection, 2) and 3) will lead the subscriber server into thinking that the insert statement embedded in your stored procedure would need to supply an explicit value for the identity column marked 'not for replication' and so it raises an error when an explicit identity value is found missing. Now, the stored procedure that you are replicating is probably intended to be executed outside of replication even though it is created through replication so the check is technically invalid despite the good intentions behind it.

The only workaround that I can think of is to use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher and then re-initialize your subscriber using a new snaphsot. This is admittedly not a very good workaround but the good news is that this particular problem is fixed for snapshot processing in SP2. The bad news is that the same problem applies to our ddl replication logic (alteration of stored procedure e.g.) and we don't have a general solution in that part of replication yet. My suggestion would be to separate your schema objects (stored procedures) into a separate snapshot publication so changes to your stored procedures etc. can be refreshed through snapshot processing when SP2 comes out.

Hope that hope,

-Raymond

|||

The failure may be caused by implict column list in INSERT statement. Do you replicate all the columns in the table or are there any identity column in the replicated table? You can try to explicitly specify the column list in your INSERT statement.

Hope it helps.

Peng

|||

Thanks Raymond and Peng,

The problem has been sloved by the following

"use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher"

I really appreciate it!

Jim

|||

Hello Raymond,

I failed to use 'sp_identitycolumnforreplication' to disable the 'not for replication' property on the identity columns. However, it was succeessful before. But it doesn't work any more.

I tried to restore the database, and re-install SQL server, no use.

How to fix 'sp_identitycolumnforreplication' ?

Here is my query:

declare @.int int

set @.int =object_id('tbl_wsSTUAPP_WEXP')

exec sys.sp_identitycolumnforreplication @.int,0

GO

Thank you!

Replication problem with begin transaction

SQL Server 2000 sp4
Could you feed me about replication problem when using
begin transaction and
commit transaction command ?
what is the exact error message and number you are getting?
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
"rl" <rlavoie@.lgs.com> wrote in message
news:eqE6fy8iFHA.320@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2000 sp4
> Could you feed me about replication problem when using
> begin transaction and
> commit transaction command ?
>
>
|||Ok let me explain my real problem
I update table abc to SERVER A
SERVER A push a replication to SERVER B
and SERVER B push to SERVER A and SERVER C
SERVER A and C are configure to replicate to SERVER B and
SERVER B is configure to replicate to SERVER A and SERVER C
(same tables are replicate)
I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
TRANSACTION
When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
error
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23hRaQeFjFHA.2444@.tk2msftngp13.phx.gbl...
> what is the exact error message and number you are getting?
> --
> 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
> "rl" <rlavoie@.lgs.com> wrote in message
> news:eqE6fy8iFHA.320@.TK2MSFTNGP09.phx.gbl...
>
|||What form of replication are you using?
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
"rl" <rlavoie@.lgs.com> wrote in message
news:eVepVEGjFHA.576@.tk2msftngp13.phx.gbl...
> Ok let me explain my real problem
> I update table abc to SERVER A
> SERVER A push a replication to SERVER B
> and SERVER B push to SERVER A and SERVER C
> SERVER A and C are configure to replicate to SERVER B and
> SERVER B is configure to replicate to SERVER A and SERVER C
> (same tables are replicate)
> I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
> TRANSACTION
> When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
> error
> --
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23hRaQeFjFHA.2444@.tk2msftngp13.phx.gbl...
>
|||transactional
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMvy4dIjFHA.3608@.TK2MSFTNGP12.phx.gbl...
> What form of replication are you using?
> --
> 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
> "rl" <rlavoie@.lgs.com> wrote in message
> news:eVepVEGjFHA.576@.tk2msftngp13.phx.gbl...
>
sql

replication problem

I have two servers A and B wchich has got transaction replication between them(database name is xx). In that server A is publisher and server B is subscriber.Now i want to copy the database xx from server B Onto server C.When i wanted to this with copy database wizard itz saying that the database has got replication and can not be copied.I wanted to stop this replication and copy this databse xx onto server C.How can do this by stopping replication on server B.
ANY idea is greatly appreciated.
Thanks.Try backup and restore.

replication problem

hi guys,
Just a question, I set up transaction replication between 2 servers.
it's working fine with data, table structures are also being replicated to
destination (I do reinitialize subscription). However, indexes and foreign
key are not being replicated. what is going on?
I checked the replication monitor, I dont' see any errors.
any hint?
thanks
kevin
By default the replication of fk's and pk's is not done. You can select to
include DRI in the articles property dialog. Right click on your
publication, select publication properties, click on the article tab, and
click on the browse button to the right of each table. In the snapshot tab
at the bottom you will find this option.
HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data is
flowing from your publisher to your subscriber and ideally your DML will be
occuring on your publisher not your subscriber. So your DRI will be
maintained on your publisher and there is no reason to include it on your
subscriber.
If you are doing DML on your subscriber as well and replication does work
with this, it is advisable to put the NFR attribute on your constraints on
your subscriber tables.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> hi guys,
> Just a question, I set up transaction replication between 2
> servers. it's working fine with data, table structures are also being
> replicated to destination (I do reinitialize subscription). However,
> indexes and foreign key are not being replicated. what is going on?
> I checked the replication monitor, I dont' see any errors.
> any hint?
> thanks
> kevin
>
|||okay.. you're right.
I shouldn't worry about foreign key, but I do worry about indexes.
also, I added an article to subscription using sp_addarticle, but when I
force to run snapshot agent, snapshot agent said
no snapshot is available. why doesn't snapshot agent know I just added an
article?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
> By default the replication of fk's and pk's is not done. You can select to
> include DRI in the articles property dialog. Right click on your
> publication, select publication properties, click on the article tab, and
> click on the browse button to the right of each table. In the snapshot tab
> at the bottom you will find this option.
> HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data
> is flowing from your publisher to your subscriber and ideally your DML
> will be occuring on your publisher not your subscriber. So your DRI will
> be maintained on your publisher and there is no reason to include it on
> your subscriber.
> If you are doing DML on your subscriber as well and replication does work
> with this, it is advisable to put the NFR attribute on your constraints on
> your subscriber tables.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>
|||causer you have to issue a sp_refreshsubscriptions. You might have to
re-rerun the snapshot agent as well.
Keep in mind that on the subscriber the PK's are by default replaced by
unique indexes which are the functional equivalent of a pk, only it will
allow a single null - but the unique key is a clustered index.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
> okay.. you're right.
> I shouldn't worry about foreign key, but I do worry about indexes.
> also, I added an article to subscription using sp_addarticle, but when I
> force to run snapshot agent, snapshot agent said
> no snapshot is available. why doesn't snapshot agent know I just added an
> article?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
>
|||sorry, Last question.
it looks like after I did sp_refreshsubscriptions, the destination table
(subscriber) was truncated first, now it's buck copying data to destination
table at subscriber.
But originally, I had 5200000 rows in destination table(subscriber), In
same table in Publisher, It has 8000000 rows. I thought it would just copy
over 2800000 rows over instead of doing everything all over again. is
there a system procedure that can do such ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
> causer you have to issue a sp_refreshsubscriptions. You might have to
> re-rerun the snapshot agent as well.
> Keep in mind that on the subscriber the PK's are by default replaced by
> unique indexes which are the functional equivalent of a pk, only it will
> allow a single null - but the unique key is a clustered index.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||No there is no proc to do this. In SQL 2005 snapshots are restartable which
is probably what you are looking for.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:u8IS%23IO1FHA.2132@.TK2MSFTNGP15.phx.gbl...
> sorry, Last question.
> it looks like after I did sp_refreshsubscriptions, the destination table
> (subscriber) was truncated first, now it's buck copying data to
> destination table at subscriber.
> But originally, I had 5200000 rows in destination table(subscriber), In
> same table in Publisher, It has 8000000 rows. I thought it would just
> copy over 2800000 rows over instead of doing everything all over again.
> is there a system procedure that can do such ?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
>

Replication problem

I've got 2 SQL 2000 servers within my company, and I've recently
changed some transaction replication objects on a SQL 2000
installation, but I now getting:
Error Code:
7137
MessageUPDATETEXT is not allowed because the column is being processed
by a concurrent snapshot and is being replicated to a non-SQL Server
Subscriber or Published in a publication allowing Data Transformation
Services (DTS).
This only happens on the subscriber when I try to insert data into the
publisher. Any ideas why this is happening? More importantly - how do I
fix it?
Thanks in advanceI deleted all the replication objects, rebooted the servers, and then
re-created them - problem was solved!
pinhead wrote:
> I've got 2 SQL 2000 servers within my company, and I've recently
> changed some transaction replication objects on a SQL 2000
> installation, but I now getting:
> Error Code:
> 7137
> MessageUPDATETEXT is not allowed because the column is being processed
> by a concurrent snapshot and is being replicated to a non-SQL Server
> Subscriber or Published in a publication allowing Data Transformation
> Services (DTS).
> This only happens on the subscriber when I try to insert data into the
> publisher. Any ideas why this is happening? More importantly - how do I
> fix it?
> Thanks in advance

Replication problem

I've got 2 SQL 2000 servers within my company, and I've recently
changed some transaction replication objects on a SQL 2000
installation, but I now getting:
Error Code:
7137
MessageUPDATETEXT is not allowed because the column is being processed
by a concurrent snapshot and is being replicated to a non-SQL Server
Subscriber or Published in a publication allowing Data Transformation
Services (DTS).
This only happens on the subscriber when I try to insert data into the
publisher. Any ideas why this is happening? More importantly - how do I
fix it?
Thanks in advanceI deleted all the replication objects, rebooted the servers, and then
re-created them - problem was solved!
pinhead wrote:
> I've got 2 SQL 2000 servers within my company, and I've recently
> changed some transaction replication objects on a SQL 2000
> installation, but I now getting:
> Error Code:
> 7137
> MessageUPDATETEXT is not allowed because the column is being processed
> by a concurrent snapshot and is being replicated to a non-SQL Server
> Subscriber or Published in a publication allowing Data Transformation
> Services (DTS).
> This only happens on the subscriber when I try to insert data into the
> publisher. Any ideas why this is happening? More importantly - how do I
> fix it?
> Thanks in advance

Replication on MSDE/Win2000 Pro?

I only have MSDE installed on Win2000 pro machines. Can I still set up the
database replication, such as "transaction replication"? The SQL Server 2000
Standard edition can not run on Win2000 Pro, right?
Thanks a lot,
FL
No, but you can set up snapshot and merge replication. MSDE databases can
subscribe to transactional publications.
SQL Server 2000 standard cannot run on Win2k Pro - check out
http://www.microsoft.com/sql/evaluat...00/default.asp for more
info.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"FLX" <nospam@.hotmail.com> wrote in message
news:uhzhST19EHA.2700@.TK2MSFTNGP14.phx.gbl...
> I only have MSDE installed on Win2000 pro machines. Can I still set up the
> database replication, such as "transaction replication"? The SQL Server
2000
> Standard edition can not run on Win2000 Pro, right?
> Thanks a lot,
> FL
>
|||Thanks a lot.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OhHCtf19EHA.1084@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> No, but you can set up snapshot and merge replication. MSDE databases can
> subscribe to transactional publications.
> SQL Server 2000 standard cannot run on Win2k Pro - check out
> http://www.microsoft.com/sql/evaluat...00/default.asp for more
> info.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uhzhST19EHA.2700@.TK2MSFTNGP14.phx.gbl...
the
> 2000
>

Replication latency

Hi,
I have set up a transactional replication on two SQL Server
2000 (SP3) machines.
How do I determine the time it passes between the
transaction was placed in the transaction log at the
Publisher and it has finished at the Subscriber?
--
Many thanks,
OskRun performance monitor... YOu should notice that there will now be new
counters for replication, one of which is latency...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:2aa901c509c4$9d8b76d0$a501280a@.phx.gbl...
> Hi,
> I have set up a transactional replication on two SQL Server
> 2000 (SP3) machines.
> How do I determine the time it passes between the
> transaction was placed in the transaction log at the
> Publisher and it has finished at the Subscriber?
> --
> Many thanks,
> Osk

Replication is renaming my Stored Procedures on Client only

I am new to replication but I have a question, I have my server which is using a transaction replication process and a client with using SQL Express with replication turned on. I have the identical DB on both nodes yet when I subsrcibe to the server from the client, data is transfer but for some reason, all of my stored procedures are renamed with sp_MSDel_DBOxxxx. How do I keep SQL from renaming these SP's? The server maintains the origial names to the SPs its just happening on my client.

Thanks

You can customize the names of these stored procs in the article properties.|||

If I understand correctly, the SPs you're seeing on client db are not the renamed copy of your original sps, those sp_MSDel_DBOxxxx sps are created and used exclusively by replication, they should not affect your existing sps in anyway. However, if you want to control how those replication sps are named you can use the approach Greg suggested.

Thanks,

Zhiqiang Feng

|||

My Server SP's are named properly, for example usp_AddActivityLog is the proper name of my custom SP. After replication occurs the name of the same SP on my client is now named sp_MSdel_dboActivityLog.

In the Article Property <Default is based on stored procedure name> is in the destination object name. And here is the code for SP:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[usp_AddActivityLog]

@.activityLogID bigint,

@.agencyofficer_id bigint,

@.lookup_id int,

@.remarks varchar(200) = '',

@.caseLinkID bigint = null,

@.startTime dateTime = null,

@.endTime datetime = null,

@.location varchar(200) = '',

@.callForServiceID bigint = 0

AS

SET NOCOUNT ON

DECLARE @.ret int

IF (@.activityLogID <> 0)

BEGIN

UPDATE ActivityLog SET

EndTime = @.endTime,

fk_CaseLinkID = @.caseLinkID,

remarks = @.remarks,

location = @.location,

fk_CallForServiceID = @.callForServiceID

WHERE pk_activityID = @.activityLogID

SET @.ret = @.@.ERROR

IF @.ret <> 0

BEGIN

INSERT INTO ErrorLog VALUES ('Update of ActivityLog failed.', GetDate())

RETURN -1

END

ELSE

RETURN @.activityLogID

END

ELSE

BEGIN

INSERT INTO ActivityLog (fk_AgencyOfficersID, fk_ActivityLookupID, StartTime, EndTime,Remarks, fk_CaseLinkID, location, fk_CallForServiceID)

VALUES (@.agencyofficer_id, @.lookup_id, @.startTime, '', @.remarks, @.caseLinkID, @.location, @.callForServiceID)

SET @.ret = @.@.ERROR

IF @.ret <> 0

BEGIN

INSERT INTO ErrorLog VALUES ('Insert into ActivityLog failed.', GetDate())

RETURN -1

END

ELSE

RETURN @.@.identity

END

Any Idea why they are being renamed?

Thanks

|||

Distribution agent doesn't rename anything, it just applies what's in the snapshot folder. Look in your snapshot folder to see what stored procedures are being generated. If the proper names are there, then the procs you see must have previously existed. If the incorrect proc names are being generated, then you need to doublecheck the article properties to see what the problem could be.

I just tried your scenario on a SQL 2005 machine with different proc names, they were created properly at the subscriber. IF there's something else i missed, please let me know.