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 system stored procedures parameter defaults ?

Hi there

This is a pretty straight forward question.

When using sp_droparticle or sp_changepublication etc, basically any replication system stored procedure.

There are many parameters for these sp's basically all i want to know is if i provide the relavant paramaters,that is publication name, subscriber name , specific parameter i wish to change etc, are all the other paramters defaulted to the current publication/subscriber properties.

In other words sometimes i really dont know what to provide for all the parameters i am pretty sure as long as i provide the necessary ones the other ones are defaulted correctly, BOL is not 100% clear on all the parameters ?

ThanxHi Sean,

You are right. If you dont specify a parameter value in a stored procedure, it will get set to the default value.

But when you call a sp_change*** procedure, only parameters you have specified will be changed. Others will continue to be set to what they were (They will not be reset to the default values)

For Eg: you call:

sp_addpublication @.publication='testPub', @.allow_push='true', @.allow_pull='true', @.allow_anonymous='true'

[Note that defaults for @.allow_pull and @.allow_anonymous are false]

And now if you call:

sp_changepublication @.publication='testPub', @.allow_pull='false'

this call will only set @.allow_pull='false' and will not touch @.allow_anonymous. It has already been set to 'true' and it will continue to be true.|||Hi Mahesh

Yes that helps thanx.
I had a problem with sp_addarticle i did not specify push or pull because i thought it would look at the subscriber and stay at pull, but it automatically defaulted to push, which i found strange as the subscriber/publication relationship was pull ?

Thanx|||Hi Sean, subscription-type has nothing to do with sp_addarticle, did you reference the wrong stored procedure name in your post?

Assuming you meant to say sp_addsubscription, if you look it up in Books Online, for parameter @.subscription_type, the default is PUSH. This is the only place where you can assign a subscription to be push or pull.
|||Hi Greg

Yes sorry i meant sp_addsubscription.

Replication stored procedures

Is there a list anywhere of the system stored procedures that are used
strictly in replication? Thanks.
David
David,
if you search in BOL for system stored procedures, the procedures are
categorized (or here:
http://msdn.microsoft.com/library/de...sp_00_519s.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication set-up stored procedures

Hello everyone,
I have two questions:
1. about sp_adddistributor - in the case when a remote distributor is being
used does it have to be run both at the publisher and at the distributor?
2. about sp_addsubscriber - where does it fit in the chain of replication
setup SPs? I have this sequence so far:
sp_adddistributor (@.distributor)
sp_adddistributiondb (@.distributor)
sp_adddistpublisher (@.distributor/@.publisher)
sp_replicationdboption (@.publisher)
sp_addpublication (@.publisher)
sp_addarticle (@.publisher)
sp_addsubscriber (@.publisher) is this the right point of execution?
sp_addsubscription (@.publisher)
thanks in advance
sp_adddistributor is run only on the distributor. You have a correct
placement for the add subscriber statement, but really it can be run anytime
after the distributor is created.
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
"MorDeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
news:E505E5B2-6FDD-46A1-8D40-CD2D241A4B5C@.microsoft.com...
> Hello everyone,
> I have two questions:
> 1. about sp_adddistributor - in the case when a remote distributor is
> being
> used does it have to be run both at the publisher and at the distributor?
> 2. about sp_addsubscriber - where does it fit in the chain of replication
> setup SPs? I have this sequence so far:
> sp_adddistributor (@.distributor)
> sp_adddistributiondb (@.distributor)
> sp_adddistpublisher (@.distributor/@.publisher)
> sp_replicationdboption (@.publisher)
> sp_addpublication (@.publisher)
> sp_addarticle (@.publisher)
> sp_addsubscriber (@.publisher) is this the right point of execution?
> sp_addsubscription (@.publisher)
> thanks in advance
>
|||Hi Hilary,
I'm glad you picked up on this question. My confusion started from comparing
the EM generated scripts to the setup procedures described in your book where
I could not find the add subscriber stored procedure.
also, do I have to run grant access statements, or is there a default set of
rights that gets assigned to these objects that could be good enough?
Thanks
Mor
"Hilary Cotter" wrote:

> sp_adddistributor is run only on the distributor. You have a correct
> placement for the add subscriber statement, but really it can be run anytime
> after the distributor is created.
>
> --
> 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
>
> "MorDeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
> news:E505E5B2-6FDD-46A1-8D40-CD2D241A4B5C@.microsoft.com...
>
>

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 of StoredProcedures

I am trying to replicate stored procedures (after the fact) using the
following script
EXEC sp_addscriptexec @.publication = 'BSC_UserPubl',
@.scriptfile = '[\\MGROVEDB10\data$\ReplData\iDeal\DynSnapShot\Sc ript\MGroveDB10_Ideal_Client_Repl_ScriptVER1.sql]'
,
@.skiperror = 1
and i get this error
Server: Msg 21331, Level 16, State 1, Procedure sp_MScopyscriptfile,
Line 40
Failed to copy user script file to the Distributor.(copy
"[\\serverDB10\data$\ReplData\iDeal\DynSnapShot\Sc ript\MGroveDB10_Ideal_Client_Repl_ScriptVER1.sql]"
"\\serverdb4\data$\iPlan\ReplData\2004051209421711 0")
Could some one help me with pointers as tyo what needs to be done to
get this going. This help will be very much apprciated
Thanks
RG
Ramesh,
can you try sharing the repldata folder and use
\\MGROVEDB10\ReplData\MGroveDB10_Ideal_Client_Repl _ScriptVER1.sql
HTH,
Paul Ibison
|||Thanks Paul, I will give it a shot and let you know of the progress
|||I shared the repldata folder and used a different publication
EXEC sp_addscriptexec @.publication = 'BSC_UserPubl',
@.scriptfile = '\\MGROVEDB10\ReplData$\MGroveDB10_Ideal_Client_Re pl_ScriptVER1.sql' ,
@.skiperror = 1
and I still get the error
Server: Msg 21331, Level 16, State 1, Procedure sp_MScopyscriptfile, Line 40
Failed to copy user script file to the Distributor.(copy "[\\MGROVEDB10\data$\Script.sql]" "\\mgrovedb10\data$\iPlan\ReplData\200405130913011 10")
In fact whene i tried executing the sp_MScopyscriptfile proc with the appropraite parameters i still get the same error.
Any pointers will be greatly appreciated
Thx
RG
-- Paul Ibison wrote: --
Ramesh,
can you try sharing the repldata folder and use
\\MGROVEDB10\ReplData\MGroveDB10_Ideal_Client_Repl _ScriptVER1.sql
HTH,
Paul Ibison
|||Ramesh,
you're still using hidden shares and I think this may be the issue. Can you
change to a normal share, add rights to allow the sql server agent and see
if that works. If not, then there is a known error with this type of
behaviour: http://www.mcse.ms/message641761.html and I would be tempted to
use linked servers or use remote registrations to do this.
Regards,
Paul Ibison

Replication of Stored Procedures

Mike,
if you are using transactional replication, have a look at
the article properties for the stored procedure. There is
the option to replicate execution of the stored procedure.
AFAIR by default this is not done, so the stored procedure
itself (rows in sysobjects and syscomments etc) is
replicated. Changes to the stored procedure TSQL are not
replicated in the future, as this would be replication of
system table data which we can't yet do.
Replication of the execution of the stored procedure and
the rows affected are 2 separate issues and have no
overlap.
HTH,
Paul Ibison
Hi, Paul. Thanks for responding.
Maybe I need to restate my question.
Actually the execution of the SP and the rows affected was my question.
In particular, if a table is being replicated and a stored procedure that
inserts, updates, deletes rows from that same table is replicated, how does
replication know only to execute the SP and not to transfer the rows
affected by the execution of the SP?
So, for example, if a developer inserts 8,000 records into a replicated
table via a stored procedure, I don't want the 8,000 rows to be transferred
to the subscriber, but rather the "EXEC" statement.
Any idea if/how replication handles this?
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d6701c46fd0$e2744d50$a401280a@.phx.gbl...
> Mike,
> if you are using transactional replication, have a look at
> the article properties for the stored procedure. There is
> the option to replicate execution of the stored procedure.
> AFAIR by default this is not done, so the stored procedure
> itself (rows in sysobjects and syscomments etc) is
> replicated. Changes to the stored procedure TSQL are not
> replicated in the future, as this would be replication of
> system table data which we can't yet do.
> Replication of the execution of the stored procedure and
> the rows affected are 2 separate issues and have no
> overlap.
> HTH,
> Paul Ibison
|||I'm not sure how it knows, but it knows!
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mike" <MichaelLopez@.inds.com> wrote in message
news:OplHRT$bEHA.2940@.TK2MSFTNGP10.phx.gbl...
> Hi, Paul. Thanks for responding.
> Maybe I need to restate my question.
> Actually the execution of the SP and the rows affected was my question.
> In particular, if a table is being replicated and a stored procedure that
> inserts, updates, deletes rows from that same table is replicated, how
does
> replication know only to execute the SP and not to transfer the rows
> affected by the execution of the SP?
> So, for example, if a developer inserts 8,000 records into a replicated
> table via a stored procedure, I don't want the 8,000 rows to be
transferred
> to the subscriber, but rather the "EXEC" statement.
> Any idea if/how replication handles this?
> Thanks again,
> Mike
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:1d6701c46fd0$e2744d50$a401280a@.phx.gbl...
>
|||So, then the answer to the "If" part is "Yes, the EXEC command is
transferred, not the changed rows", and in that case the "How" part really
is irrelevant.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23cHgXRFcEHA.3792@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I'm not sure how it knows, but it knows!
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mike" <MichaelLopez@.inds.com> wrote in message
> news:OplHRT$bEHA.2940@.TK2MSFTNGP10.phx.gbl...
that
> does
> transferred
>

Replication of SP's mistake

When setting up replication of Stored Procedures & Views I made a mistake, I did not change the properties to tell replication to replicate the execution of the stored procedures.
How can I change this?
Originally, I just selected publish all & now when I look at Articles on the publication this is not checked, once the snapshot ran. I'm a bit confused.....
I want to the stored procedures to execute on my reporting/replicated db.
Thanx!
That's really not a very good option for me, is there any other way?
If I drop this publication, then I will need to reinitialize for >1000
tables only to get the stored procedures & views replicated? Not really
what I want to do, any other options available?
JUDE
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:023b01c4abcd$4d80f980$a301280a@.phx.gbl...
> The easiest way is to script out the publication then
> drop it.
> Change the scripts so
> @.type = N'proc exec'
> @.status = 0
> Then recreate the publication and initialize.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
I replicate my views and stored procedures in a separate publication to
avoid snapshotting the tables when my views change - perhaps this would be
an easier option for you?
Alternatively you could drop the subscription to each article and remove the
article eg:
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
The readd the article, run the snapshot agent and synchronize.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||A separate publication sounds good, these views & SP's seem to be ever
changing at times.
I think I will script the publication, pull out the views & sp's, drop
subscriptions & articles, then setup a separate publication for the views &
sp's.
Another question...
What's the benefit of replicating the view execution (Actually, is there
such a thing?)
When I replicate views & SP's, can it be both schema & execution being
replicated to the reporting server, or an either or situation?
Thanx!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORXYiI%23qEHA.896@.TK2MSFTNGP12.phx.gbl...
> JLS,
> I replicate my views and stored procedures in a separate publication to
> avoid snapshotting the tables when my views change - perhaps this would be
> an easier option for you?
> Alternatively you could drop the subscription to each article and remove
the
> article eg:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> The readd the article, run the snapshot agent and synchronize.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
for views you don't get the same option.
For each stored proc article you get the options listed as the @.type options
in sp_addarticle. So, the 'structure' or 'structure and execution' are the
main options. If you select the latter, the schema will go down as well as
the execution, and it is not possible to select just the latter.
BTW,
I also
(a) apply a post-snapshot script to apply permissions.
(b) have a separate publication for udfs to force them to go first
(c) sometimes resort to sp_addscriptexec when the dependencies get mixed up.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication of Dynamic SQL Stored Procedures

I'm trying to set up replication on a Db that I just took over. When
replicating the SProcs the Distribution Agent stops with an error on the
Subscriber whenever it encounters an SP that is building a dynamic SQL
statement. I typically get the following error:
are not allowed. Add a name or single space as the alias name.
(Source: EVESTMENTDB6 (Data source); Error number: 1038)
There is nothing before the "are not allowed." Lookinig for error 1038 has
so far been fruitless. I have been rewriting the Dynamic SQl but I know that
at least some of the SProc's are really going to need it.
Jay Croft
Senior Systems Architect
Can you post one of the problem procs here?
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
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:654660AB-4A31-4275-BD3E-A432D815B7E4@.microsoft.com...
> I'm trying to set up replication on a Db that I just took over. When
> replicating the SProcs the Distribution Agent stops with an error on the
> Subscriber whenever it encounters an SP that is building a dynamic SQL
> statement. I typically get the following error:
> are not allowed. Add a name or single space as the alias name.
> (Source: EVESTMENTDB6 (Data source); Error number: 1038)
> There is nothing before the "are not allowed." Lookinig for error 1038
> has
> so far been fruitless. I have been rewriting the Dynamic SQl but I know
> that
> at least some of the SProc's are really going to need it.
> --
> Jay Croft
> Senior Systems Architect
|||These SP's were written by an untrained junior programmer, but they've been
working in a production DB for two months.
/****** Object: StoredProcedure
[dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date: 02/08/2006
18:14:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE procedure [dbo].[eaSP_analytics_select_benchmark_perf_data]
@.benchmark_type varchar(20),
@.view_name varchar(40),
@.risk_benchmark_id int,
@.earliest_date datetime,
@.latest_date datetime,
@.qorm char(1)
as
begin
SET NOCOUNT ON
declare @.strSQL varchar(1000)
if @.benchmark_type = ''Index''
Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
year, value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
if @.benchmark_type = ''DB Product''
Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
product_category_code, vehicle_type, gross_or_net, base_currency, product_id
as benchmark_id, product_name, return_date, month, year, product_performance
as value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
product_performance is not null "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
SET NOCOUNT OFF
end
'
END
GO
Jay Croft
Senior Systems Architect
"Hilary Cotter" wrote:

> Can you post one of the problem procs here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Hi Jay,
Replication of text objects that use " instead of ' for delimiting string
literal is not supported (or simply broken) in SQL2000 although the SQL2005
snapshot agent will automatically convert these "s into 's. If there are not
many procedures that use " for delimiting string literals, you may want to
consider rewriting them to use ' instead. Otherwise you would need to use
the Database Import\Export wizard to transfer these stored procedures
instead.
HTH
-Raymond
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:0D89C509-557E-4DA1-A99F-560BD91688C7@.microsoft.com...
> These SP's were written by an untrained junior programmer, but they've
> been
> working in a production DB for two months.
> /****** Object: StoredProcedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date:
> 02/08/2006
> 18:14:39 ******/
> SET ANSI_NULLS OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
> OBJECTPROPERTY(id,N'IsProcedure') = 1)
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
>
>
> CREATE procedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data]
> @.benchmark_type varchar(20),
> @.view_name varchar(40),
> @.risk_benchmark_id int,
> @.earliest_date datetime,
> @.latest_date datetime,
> @.qorm char(1)
> as
> begin
> SET NOCOUNT ON
> declare @.strSQL varchar(1000)
> if @.benchmark_type = ''Index''
> Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
> year, value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> if @.benchmark_type = ''DB Product''
> Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
> product_category_code, vehicle_type, gross_or_net, base_currency,
> product_id
> as benchmark_id, product_name, return_date, month, year,
> product_performance
> as value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
> product_performance is not null "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> SET NOCOUNT OFF
> end
> '
> END
> GO
> --
> Jay Croft
> Senior Systems Architect
>
> "Hilary Cotter" wrote:
>

Replication Not working for a table

We have about 20 tables setup for replications . All std.
setup . Replication is using stored procs created by SQL
Server instead of the Statements. All tables are fine
except one table stpped changing data at all. No errors
etc.
What should I do.
Table has 2 coulms as PK . using SQL stored proc,
sp_msIns..,sp_msUpd.., sp_msdel..
What could be wrong. PLease help. All other tables are
fine.
No Primary key or columns have been changed at all .
ThanksYour table name contains white spaces or special characters? If so,
rename your stored procedures.
MoreThanInsane
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1340150.html

Replication job does not end - even when its idle on the server

I am the two stored procedures below, used in a similar way, to add a
subscription. But for some reason, the replication completes but it does not
stop. If I add a subscription using Enterprise manager (EM), the job does
stop. I scripted out the subscription that EM had created and noticed that
extra code had been added to create the replication jobs (ie. if you generate
the script for a job, there is an option to "script creation of replication
jobs"). Do I need to do this too?
Any help would be very much appreciated.
ServerName...../
use NewTest
GO
exec sp_addpullsubscription @.publisher = @.@.ServerName, @.publisher_db =
'pubs',
@.publication = 'pubs', @.independent_agent = 'true',
@.subscription_type = 'anonymous', @.update_mode = 'read only',
@.immediate_sync = 1
GO
exec sp_addpullsubscription_agent @.publisher = @.@.ServerName, @.publisher_db =
'pubs', @.publication = 'pubs', @.distributor = @.@.ServerName,
@.subscriber_security_mode = 1, @.distributor_security_mode = 1, @.use_ftp =
'true', @.publication_type = 1
GO
/..... End: Script to be run at Subscriber: @.@.ServerName ...../
What does the replication job which does not stop say?
Scripting replication jobs is not necessary unless you have customized them.
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
""Julian Reed via droptable.com"" <forum@.droptable.com> wrote in message
news:50DAB174F7810@.droptable.com...
> I am the two stored procedures below, used in a similar way, to add a
> subscription. But for some reason, the replication completes but it does
not
> stop. If I add a subscription using Enterprise manager (EM), the job does
> stop. I scripted out the subscription that EM had created and noticed
that
> extra code had been added to create the replication jobs (ie. if you
generate
> the script for a job, there is an option to "script creation of
replication
> jobs"). Do I need to do this too?
> Any help would be very much appreciated.
> ServerName...../
> use NewTest
> GO
> exec sp_addpullsubscription @.publisher = @.@.ServerName, @.publisher_db =
> 'pubs',
> @.publication = 'pubs', @.independent_agent = 'true',
> @.subscription_type = 'anonymous', @.update_mode = 'read only',
> @.immediate_sync = 1
> GO
> exec sp_addpullsubscription_agent @.publisher = @.@.ServerName, @.publisher_db
=
> 'pubs', @.publication = 'pubs', @.distributor = @.@.ServerName,
> @.subscriber_security_mode = 1, @.distributor_security_mode = 1, @.use_ftp =
> 'true', @.publication_type = 1
> GO
> /..... End: Script to be run at Subscriber: @.@.ServerName ...../

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.

replication insert from subscriber

:confused:

I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.

The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.

Thoughts?What replication is it? Snapshot, Trx, Merge? What type? Push, Pull, Push RAA? Be more specific and someone will help.|||I am using merge replication with 'push'|||Manually generated PK based on INT datatype is not a good idea in merge replication scenario. For that you need GUID or a combination of INT and another field that would uniquely distinguish the physical location.|||Hi all!
I have just worked with Replication data, i can replication data between two server in local by program (script, VB) but i can't do that with a server in local and server on internet. Can you send me source (script, VB) do that?. Thanks!