Replication Triggers on replicated tables.

Howdy all. I set up Immediate Updating replication on AdventureWorks on 2005
Person.Address and AddressType tables. When this type of replication is
created, a replication trigger is created on the Publisher. However, this
replication trigger fires off the other (User) trigger on the table when a
row is updated, and they continue to fire each other off. I caught all this
in Profiler so Im sure this is whats happening. Anyways, the following
message is then displayed:
Maximum Stored Proc, function, trigger, or view nesting level exceeded
(limit 32).
Here are the triggers:
ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on [Person].[Address]
for update not for replication as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [Person].[Address] set msrepl_tran_version = newid() from
[Person].[Address], inserted
where [Person].[Address].[AddressID] = inserted.[AddressID]
ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Address]
SET [Person].[Address].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
END;
Someone must have encountered this before and have a workaround?
TIA, ChrisR
use set trigger order to have replication fire at the end or make your
triggers not for replication.
Can we see the table schema and triggers?
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
"ChrisR" <ChrisR@.foo.com> wrote in message
news:uN6YxlRMHHA.3556@.TK2MSFTNGP03.phx.gbl...
> Howdy all. I set up Immediate Updating replication on AdventureWorks on
> 2005 Person.Address and AddressType tables. When this type of replication
> is created, a replication trigger is created on the Publisher. However,
> this replication trigger fires off the other (User) trigger on the table
> when a row is updated, and they continue to fire each other off. I caught
> all this in Profiler so Im sure this is whats happening. Anyways, the
> following message is then displayed:
>
> Maximum Stored Proc, function, trigger, or view nesting level exceeded
> (limit 32).
>
> Here are the triggers:
>
> ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on
> [Person].[Address] for update not for replication as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
>
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [Person].[Address] set msrepl_tran_version = newid() from
> [Person].[Address], inserted
> where [Person].[Address].[AddressID] = inserted.[AddressID]
>
>
> ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
> AFTER UPDATE NOT FOR REPLICATION AS
> BEGIN
> SET NOCOUNT ON;
>
> UPDATE [Person].[Address]
> SET [Person].[Address].[ModifiedDate] = GETDATE()
> FROM inserted
> WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
> END;
>
> Someone must have encountered this before and have a workaround?
>
>
> TIA, ChrisR
>
>
>
>

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 to a web server in DMZ

Hello,
We have active/active SQL2000 SP3a on a Win2k3 cluster in our domain. We
need to copy some tables from a
database out to our web server.
The web server, is SQL2000 SP3a on Windows 2000, SP4. It is a stand-alone
server out in the DMZ.
We get the following error when trying to do this:
SubSystem Message - Job 'VIRTUALSERVERNAME\INSTANCENAME-WEBSERVER-13'
(0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection. The
process could not connect to Subscriber 'WEBSERVER'.
Here are some thoughts we have. The SQL server in the domain runs on port
1303. The SQL server in the DMZ is running on a different port. Do the
ports need to match for the replication to occur?
We are not sure why this isn't working. Anyone have some input or some
documentation on how to set up replication to a web server in a DMZ. I am
sure this is something that is done at most companies, but we are missing
something.
Thanks in advance.
Hopefully this article will help out:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I would try SQL Authentication.
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
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:%23%23dsWsu6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hello,
> We have active/active SQL2000 SP3a on a Win2k3 cluster in our domain. We
> need to copy some tables from a
> database out to our web server.
> The web server, is SQL2000 SP3a on Windows 2000, SP4. It is a stand-alone
> server out in the DMZ.
> We get the following error when trying to do this:
> --
> SubSystem Message - Job 'VIRTUALSERVERNAME\INSTANCENAME-WEBSERVER-13'
> (0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection. The
> process could not connect to Subscriber 'WEBSERVER'.
> --
> Here are some thoughts we have. The SQL server in the domain runs on port
> 1303. The SQL server in the DMZ is running on a different port. Do the
> ports need to match for the replication to occur?
> We are not sure why this isn't working. Anyone have some input or some
> documentation on how to set up replication to a web server in a DMZ. I am
> sure this is something that is done at most companies, but we are missing
> something.
> Thanks in advance.
>
|||Thanks Paul, I am looking that over.
Hilary, the web server is using Windows and SQL, is that what you are
referring to?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ORJz63u6FHA.3416@.TK2MSFTNGP15.phx.gbl...
>I would try SQL Authentication.
> --
> 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
> "JJP" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23%23dsWsu6FHA.1276@.TK2MSFTNGP09.phx.gbl...
>
sql

replication tables with forign key

Hi all

i have one central site and 7 remote sites, there are one mssqlserver 2k in each sites.
i have to replicate 4 table in one DB(my DB have about 20 tables) in my 8 sites.
this 4 table have forignkey between themselves and other15 tables
i planed to transactional replication but i cant becuse forign keys occure erros
if needed i can send my DB digram to you.

can anyone help me? :confused:

Thanx
M.J.Daneshhttp://www.microsoft.com/technet/prodtechnol/sql/2000/books/c09ppcsq.mspx for reference on configuring Merge/Transactional/snapshot replication types.

Replication Tables page in BOL doesn't refer to MSMERGE_ARTICLEHISTORY

Hi, Not sure if I should post this here or at connect but I noticed that

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/5696ee73-5d7c-4f26-b7ee-6831c9c3edf7.htm

doesn't include MSMERGE_ARTICLEHISTORY

but that this table is referenced here in BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/7d4c67e4-d683-4ba5-81bd-e6ce4f7489bf.htm

Also if anyone is looking at doing anything with the first link I think it would be great if they subdivided these tables into the types of replication and not just into the databases in which they can be found.

I know it's a bit of a difficult one given tables can exist in more than one place but I think that would be helpful.

Cheers, James

That's great feedback. Two things you might want to note:

We update Books Online quite frequently. I'm not sure which release you have, since it isn't embedded within the URL you have here. The issue you're describing might be fixed in a later release. As always, you can find the latest release of Books Online here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Secondly, it would help us (and everyone who uses Books Online) if you would make this comment again, using the "Feedback" button on the topic. That works for both the web and the installed version of Books Online, and what it does is open a "bug" to the writer assigned to the topic on that page. That way you'll get a quick response from the writer, who might not be trolling these forums. That isn't to say that you shouldn't post things here; on the contrary, questions in a public forum are a great way to let others who are having the same problem see your comments. It's just that we would like the feedback as well so that we can evaluate the page properly and get you a good response.

We have had older versions of Books Online be corrected in later releases, and in some cases made more clear. As you're pointing out here, not every format or layout is useful for every situation. Sometimes its a matter of competing goals, so it becomes more of a challenge to satisfy multiple audiences/goals in a single topic.

Once again, thanks to everyone who uses SQL Server Books Online and who sends feedback, good or bad. We like to hear when we've done things right, but we also need to make sure our documentation is the best in the world. Since we have so many constraints, from legal to the 9 languages we publish in, it's helpful for us to hear back from the community. I was a DBA for 19 years before I came here, so I know how important it is to have accurate info!

Replication tables cleanup

Hi group,
I want to know if the system tables added by replication (MS_%) are
automatically purged in any way?
Or is my task to clean them regularly? If the former is true, what is the
recommended cleanup procedure?
Thanks,
Renato
Renato,
if you're referring to merge metadata, what you needed to do is actually
quiesce your entire enterprise, make sure everybody synchronizes all their
changes, and then pick a quiet time for a maintenance period and run the
stored procedure sp_cleanupmergemetadata. This changed in SP1 as now merge
is enabled to do automatic trimming of metadata based on the publication
retention period, which you can define (there is a profile parameter called
MetadataRetentionCleanup. It's just a Boolean, so you can set it to true or
false, 1 or 0, and turn retention-based cleanup on or off.).
If you're referring to system tables retained after you have removed a
subscriber, as long as the database is no longer involved in publication or
subscription, you can run sp_removedbreplication to reset the database.
HTH,
Paul Ibison
|||Fine! That explain all... Thank you!
Renato
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uiqOTuvIEHA.580@.TK2MSFTNGP12.phx.gbl...
> Renato,
> if you're referring to merge metadata, what you needed to do is actually
> quiesce your entire enterprise, make sure everybody synchronizes all their
> changes, and then pick a quiet time for a maintenance period and run the
> stored procedure sp_cleanupmergemetadata. This changed in SP1 as now merge
> is enabled to do automatic trimming of metadata based on the publication
> retention period, which you can define (there is a profile parameter
called
> MetadataRetentionCleanup. It's just a Boolean, so you can set it to true
or
> false, 1 or 0, and turn retention-based cleanup on or off.).
> If you're referring to system tables retained after you have removed a
> subscriber, as long as the database is no longer involved in publication
or
> subscription, you can run sp_removedbreplication to reset the database.
> HTH,
> Paul Ibison
>
|||replication maintains the replication tables automatically.
With merge replication you might want to run sp_mergecleanupmetadata every
once in a while paying attention to the caveats in
http://msdn.microsoft.com/library/de...repl3_7k89.asp
"Renato Aranghelovici" <renatoa@.rdslink.ro> wrote in message
news:OSs2TRvIEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I want to know if the system tables added by replication (MS_%) are
> automatically purged in any way?
> Or is my task to clean them regularly? If the former is true, what is the
> recommended cleanup procedure?
> Thanks,
> Renato
>
sql

Replication sql 2000 question

If i add NEW COLUMNS to 2-3 existing Articles\Tables using
"sp_repladdcolumn" do i have to re-initialize replication & apply
SNAPSHOT or LOG READER will apply all data in that new columns for
subscriber. i am trying to understand when i add new columns how
columns are getting replicated to SUBSCRIBER & how data will be copied
to new columns.
Any help is appreciated to make me understand & thanking experts in
advance.
Provided you're not intending to modify the PK in trans replication (in
which case it won't work anyway) there's no need to reinitialize. The
columns will be nullable initially and there will be no data in them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||If DDL replication is turned on do you need to use sp_repladdcolumn?
On 4 Feb 2007 21:34:05 -0800, myrights99@.gmail.com wrote:

>If i add NEW COLUMNS to 2-3 existing Articles\Tables using
>"sp_repladdcolumn" do i have to re-initialize replication & apply
>SNAPSHOT or LOG READER will apply all data in that new columns for
>subscriber. i am trying to understand when i add new columns how
>columns are getting replicated to SUBSCRIBER & how data will be copied
>to new columns.
>Any help is appreciated to make me understand & thanking experts in
>advance.
|||In most cases a mini-snapshot of only the modified article will be generated
and sent to all named subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<myrights99@.gmail.com> wrote in message
news:1170653645.410658.238120@.m58g2000cwm.googlegr oups.com...
> If i add NEW COLUMNS to 2-3 existing Articles\Tables using
> "sp_repladdcolumn" do i have to re-initialize replication & apply
> SNAPSHOT or LOG READER will apply all data in that new columns for
> subscriber. i am trying to understand when i add new columns how
> columns are getting replicated to SUBSCRIBER & how data will be copied
> to new columns.
> Any help is appreciated to make me understand & thanking experts in
> advance.
>

Replication setup

How many articles can take part in merge replication?
I need to replicate about 200 tables. From them 25 are common for all my
subscribers and do not need to be filtered. Other I plan to filter with
static filters.
Is there benefit to allocate this 25 common tables in separate publication
or it will be better to include them in all publications where tables with
filters are placed?
Ideally you would have a single publication. There is no performance impact
by having multiple publications. If you are going to have multiple
publications (perhaps a publication for each article), you should group
articles which are related (PK-FK) relationships into the same publication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"massa" <massa@.discussions.microsoft.com> wrote in message
news:460AB955-4C93-4EE0-BC24-3B479E7807CF@.microsoft.com...
> How many articles can take part in merge replication?
> I need to replicate about 200 tables. From them 25 are common for all my
> subscribers and do not need to be filtered. Other I plan to filter with
> static filters.
> Is there benefit to allocate this 25 common tables in separate publication
> or it will be better to include them in all publications where tables with
> filters are placed?
>

replication sequence

I have 2 tables which tab B depends on the record on tab A. In publisher, records are generated to tabA before some audit information record to tabB. In subscriber, there is replication error that the trigger in tab B couldn't find the corresponding record in tab A and cause the transactional replication error. And I found the comand_id within the same xact_segno of record in tab B is smaller than that of record in tab A. Does it mean the data in tab B replicate before tab A?

Also, how to know the replication sequence to the subscriber?

Thanks in advance!

You may want to check your trigger to see which table is committing first. Transactional replication will replicate the commands/transaction in the order they were applied.

Replication says success, but tables not showing in EM?

Hello All,
I am trying to replicate data from a SQL Server (7.0) to another SQL
Server using a one-way immediate push subscription. After executing,
there are no errors in the Snapshot or Push agents, but two of the
tables are missing in the subscription database. Where did they go?
I can find the tables listed in the publication->articles tab and in
the snapshot logs on the publisher. The tables are also listed on the
subscriber database under 'Database Roles Properties' --> Permissions.
Thank You For Your Time And Help,
Nate
Hi Nate,
You may want to check whether subscriptions for the two missing tables were
really created by calling sp_helpsubscription at the publisher. If not,
manually add them by calling sp_addsubscriptions with explicit article names
and rerun snapshot + distribution agents.
-Raymond
"Nate" <nathandeneau@.braintrade.biz> wrote in message
news:1142037215.646558.84890@.i40g2000cwc.googlegro ups.com...
> Hello All,
> I am trying to replicate data from a SQL Server (7.0) to another SQL
> Server using a one-way immediate push subscription. After executing,
> there are no errors in the Snapshot or Push agents, but two of the
> tables are missing in the subscription database. Where did they go?
>
> I can find the tables listed in the publication->articles tab and in
> the snapshot logs on the publisher. The tables are also listed on the
> subscriber database under 'Database Roles Properties' --> Permissions.
>
> Thank You For Your Time And Help,
> Nate
>
|||Everything looks as it should after calling sp_helpsubscription - the
two tables are listed.
|||This looks really strange. If you check the history messages of the snapshot
agent, do you see files for the two missing tables generated? And if you
check the distribution agent history, do you see that the files for the two
missing tables applied? If the tables are relatively small, you may be able
to fix things up by reinitializing the subscription, regenerate the snapshot
and reapply it. You may also want to watch out for processes outside of
replication that may have dropped the tables at the subscriber. What are the
sync_type values of subscriptions to the missing tables?
-Raymond
sql

Replication Report

Hi,
Is there anyway to provide weekly status report of the replication. Like --
1. Which tables are replicated.
2. When replication is not possible with reason.
Thanks in advance.
This shouldn't be too difficult to produce. Sysarticles and sysmergearticles
on the publisher will give the first part. The distribution database
(MSrepl_errors, MSdistribution_history, MSsnapshot_history etc) will give
the latter. Reporting Services for the report then you're done.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You'll probably want to bump your history retention up to something more
than a week - by default it hangs around for 3 days. To do this, right click
on Replication Monitor, select Distributor Properties, and then click on the
properties button. Change History Retention 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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etnRVW5gFHA.2372@.TK2MSFTNGP14.phx.gbl...
> This shouldn't be too difficult to produce. Sysarticles and
sysmergearticles
> on the publisher will give the first part. The distribution database
> (MSrepl_errors, MSdistribution_history, MSsnapshot_history etc) will give
> the latter. Reporting Services for the report then you're done.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql

Replication removed but server says otherwise

I've got a big and urgent problem...
I need to alter some tables in a database. I had those
tables replicated. I removed the replication to make
thoses changes.
All the objects created by the replication are still
there, and the fields added in the tables also. When I
want to change a clustered index, It says :
- Unable to delete index 'PK_rel_cfp_sar'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot alter the table 'rel_cfp_sar' because it is
being published for replication.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
drop constraint. See previous errors.
The problem is, there is no more replication active!!
I've even removed the server as a distributor. Rebooted
the server, nothing works!!
What can I do? HELP ME!!!
Thanks in advance, Hussein.
hussein@.inforoutefpt.org
Hussein,
Running sp_removedbreplication can be used to remove all traces of
replication in the subscriber database, but obviously must only be done if
this database is not also configured as a publisher.
If sp_removedbreplication can't be used, there is a stored procedure to do
this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Alternatively, setting replinfo to 0 in sysobjects for the particular table
should do it.
HTH,
Paul Ibison
|||Thanks Paul writing me back, but it didn't worked.
The problem is at the publisher, I've disabled the server to be a Publisher
or Distributor. So it's very weird. The fields added in the tables for the
replication are still there, and I can't remove them. I can't change the
indexes either.
I've tried the two SP who told me, it says "The command(s) completed
successfully." and "1 row(s) affected", but still can't do anything...
Do you have another idea?
Thanks, Hussein
"Paul Ibison" wrote:

> Hussein,
> Running sp_removedbreplication can be used to remove all traces of
> replication in the subscriber database, but obviously must only be done if
> this database is not also configured as a publisher.
> If sp_removedbreplication can't be used, there is a stored procedure to do
> this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Alternatively, setting replinfo to 0 in sysobjects for the particular table
> should do it.
> HTH,
> Paul Ibison
>
>
|||This is very strange. Can you query replinfo in sysobjects for the problem
table and tell me the value.
TIA,
Paul Ibison
|||The value is set to 1 for the table.
I also have many strored procedures who have a value of
512.
Hussein

>--Original Message--
>This is very strange. Can you query replinfo in
sysobjects for the problem
>table and tell me the value.
>TIA,
>Paul Ibison
>
>.
>
|||Hussein - OK set it to zero for the problem table and then you should be
able to change the table schema.
HTH,
Paul Ibison
"Hussein Abd-Rabbo" <hussein@.inforoutefpt.org> wrote in message
news:137b01c48c2f$e02c2ec0$a601280a@.phx.gbl...[vbcol=seagreen]
> The value is set to 1 for the table.
> I also have many strored procedures who have a value of
> 512.
> Hussein
> sysobjects for the problem
|||also try to run
sp_msforeachtable 'sp_MSunmarkreplinfo ''?'''
in your publication database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:udaTGdBjEHA.1184@.TK2MSFTNGP12.phx.gbl...
> This is very strange. Can you query replinfo in sysobjects for the problem
> table and tell me the value.
> TIA,
> Paul Ibison
>

replication related questions

I have a couple questions about replication (for both 2000 and 2005 servers):

1. which system tables/dmvs/system sprocs can I look at to determine which columns of a table are being replicated?

2. which system tables/dmvs/sprocs can I call to get metadata about publishers and subscribers?

Thanks!1. None of them, since Microsoft changes the table structures and internal details from time to time, and emphatically does NOT guarantee anything about those tables. The sp_helparticlecolumns (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl2_1d6b.asp) stored procedure will give you this information though, in a supported fashion too!

2. Again, none, for the same reason. There are more choices for stored procedures here, can you give an example of what information you're trying to retrieve?

-PatP|||I think I need to do some reading myself first.

These questions are actually straight from a customer of mine who wants me to add documentation of replication related stuff to sqlspec. I've never used replication before so I thought I'd ask here. Premature.

thanks though. I'll come back when I have done my homework!

Replication Recommendation

Hi,
We have 4 different international sites that will need to replicate changes
between 4 tables. Each site will need to make changes to the tables locally
and have those changes propogated out to the other 3 sites. Data volume
will be about 25 changes per site per minute. Latency should be under 10
minitues. The connections between the sites is pretty reliable and fast
(max not available - twice a month - fix in 1 hour).
Which replication type would work best here? i.e, Merge, Transactional with
Immediate Updating Subscribers. Which would be the easiest to recovery from
a failure using a backup if a disaster occurs?
Thanks.
Jerry
Also,
Each server is in a seperate domain. I'm not sure of the setup but I can
create a linked server to each. Should I be using linked servers in this
replication setup as well?
Thanks again
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OMjgWXCeFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have 4 different international sites that will need to replicate
> changes between 4 tables. Each site will need to make changes to the
> tables locally and have those changes propogated out to the other 3 sites.
> Data volume will be about 25 changes per site per minute. Latency should
> be under 10 minitues. The connections between the sites is pretty
> reliable and fast (max not available - twice a month - fix in 1 hour).
> Which replication type would work best here? i.e, Merge, Transactional
> with Immediate Updating Subscribers. Which would be the easiest to
> recovery from a failure using a backup if a disaster occurs?
> Thanks.
> Jerry
>
sql

Replication question from a newbie

Right now we use autogenerated primary keys in various tables of our SQL server 2005 database. Now we have a need to put separate database servers at various remote locations and data will need to sent back to the central server. Some data will be sent back from central server to the remote sites. Data also gets added at the central location.

How can I handle replication when using autogen keys? Can I add some additional column like a SiteID and make the primary key a combination of Autogenerated key and Site ID? Would that work? The autogenerated primary keys are also foreign keys in other tables.

I have read a little about having separate ranges (of autogenerated keys) for various sites but don't think that will work as we already got lots of data in our central server.

Any help is much appreciated.

Anyone?

I am trying to find info on the web on what I can change in my existing database to make it replication friendly and so far coming up with nothing. I have read about using GUIDs vs Autogenerated keys but that would be useful when creating new databases.

I would assume these kind of situations should be common when companies expand so looking for the various solutions out there, if possible.

|||

Both those methods work.

Either uniqueidentifier with the primary key set to default of newid()

Or a composite key like Site, ID so you get records like:

LONDON, 123

LONDON, 124

LONDON, 125

NEWYORK, 321

NEWYORK, 221

The only problem with a composite key is if you want to make it a foriegn key in another table then you are stuck with having to populate the composite key instead of just one column. uniqueidentifiers are handy because the table needs one anyways but it gets really mundain if you are doing work with tables as it is not very human friendly.

Martin

Replication Question about job names and categories

I have a TSQL script to add daily tables to replication and then run
the snapshot agent to distribute them to two subscribers. The script
executes without errors, but when I check the running jobs for each
server I see the following:
JUST AN EXAMPLE
Job1'Category' REPL-Snapshot
Job2'Category' REPL-Distribution
What is the difference between these two categories? Also 'Job1' works
properly and receives the 3 new replicated tables, while 'Job2' seems
to be stuck on Step 2 and isn't receiving the 3 new replicated tables.
Below is a copy of the stored procedure for reference.
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.sp_TESTaddDailyTablesToReplication (@.@.IOI_TABLE
varchar(64), @.@.TRADE_TABLE varchar(64), @.@.CHAT_TABLE varchar(64) ) AS
DECLARE @.SUBSCRIBER_NYPROD2 varchar(64)
DECLARE @.SUBSCRIBER_CTDEV2 varchar(64)
DECLARE @.SP_INSERT_PREFIX varchar(24)
DECLARE @.SP_DELETE_PREFIX varchar(24)
DECLARE @.SP_UPDATE_PREFIX varchar(24)
DECLARE @.INSERT_SP varchar(24)
DECLARE @.DELETE_SP varchar(24)
DECLARE @.UPDATE_SP varchar(24)
SET @.SUBSCRIBER_NYPROD2 = 'INDII_NY2_PROD'
SET @.SUBSCRIBER_CTDEV2 = 'D02'
SET @.SP_INSERT_PREFIX = 'CALL sp_MSins_'
SET @.SP_DELETE_PREFIX = 'CALL sp_MSdel_'
SET @.SP_UPDATE_PREFIX = 'CALL sp_MSupd_'
SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.IOI_TABLE
SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.IOI_TABLE
SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.IOI_TABLE
DECLARE @.SCHEMA_OPTIONS int
SET @.SCHEMA_OPTIONS = 0x000000000000CEA3
exec sp_addarticle @.publication = N'Indii', @.article = @.@.IOI_TABLE,
@.source_owner = N'dbo', @.source_object = @.@.IOI_TABLE,
@.destination_table = @.@.IOI_TABLE, @.type = N'logbased', @.creation_script
= null, @.description = null, @.pre_creation_cmd = N'drop',
@.schema_option = @.SCHEMA_OPTIONS, @.status = 16, @.vertical_partition =
N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd = @.DELETE_SP, @.upd_cmd =
@.UPDATE_SP, @.filter = null, @.sync_object = null, @.auto_identity_range =
N'false'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.IOI_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.IOI_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.TRADE_TABLE
SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.TRADE_TABLE
SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.TRADE_TABLE
exec sp_addarticle @.publication = N'Indii', @.article = @.@.TRADE_TABLE,
@.source_owner = N'dbo', @.source_object = @.@.TRADE_TABLE,
@.destination_table = @.@.TRADE_TABLE, @.type = N'logbased',
@.creation_script = null, @.description = null, @.pre_creation_cmd =
N'drop', @.schema_option =@.SCHEMA_OPTIONS, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd =
@.DELETE_SP, @.upd_cmd = @.UPDATE_SP, @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.TRADE_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.TRADE_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.CHAT_TABLE
SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.CHAT_TABLE
SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.CHAT_TABLE
exec sp_addarticle @.publication = N'Indii', @.article = @.@.CHAT_TABLE,
@.source_owner = N'dbo', @.source_object = @.@.CHAT_TABLE,
@.destination_table = @.@.CHAT_TABLE, @.type = N'logbased',
@.creation_script = null, @.description = null, @.pre_creation_cmd =
N'drop', @.schema_option =@.SCHEMA_OPTIONS, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd =
@.DELETE_SP, @.upd_cmd = @.UPDATE_SP, @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.CHAT_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.CHAT_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
DECLARE @.SNAPSHOT_JOB_NAME varchar(64)
-- Run Snapshot for NY2 server
SET @.SNAPSHOT_JOB_NAME = 'INNYWPP01\PRODUCTION-Indii-Indii-1'
EXEC msdb.dbo.sp_start_job @.job_name = @.SNAPSHOT_JOB_NAME
-- Run Snapshot for CT2 serve
SET @.SNAPSHOT_JOB_NAME = 'innywpp01\production-Indii-Indii-D02-3'
EXEC msdb.dbo.sp_start_job @.job_name = @.SNAPSHOT_JOB_NAME
PRINT 'added ' + @.@.IOI_TABLE + ' from replication'
PRINT 'added ' + @.@.TRADE_TABLE + ' from replication'
PRINT 'added ' + @.@.CHAT_TABLE + ' from replication'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
job1 is the snapshot job which generates the snapshot. The snapshot contains
the schema, data, procs, and metadata required to build the article/table on
the subscriber.
job 2 is the distribution job which distributes the snapshot and all
changes.
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
<war_wheelan@.yahoo.com> wrote in message
news:1110835060.331561.184840@.f14g2000cwb.googlegr oups.com...
> I have a TSQL script to add daily tables to replication and then run
> the snapshot agent to distribute them to two subscribers. The script
> executes without errors, but when I check the running jobs for each
> server I see the following:
> JUST AN EXAMPLE
> Job1 'Category' REPL-Snapshot
> Job2 'Category' REPL-Distribution
> What is the difference between these two categories? Also 'Job1' works
> properly and receives the 3 new replicated tables, while 'Job2' seems
> to be stuck on Step 2 and isn't receiving the 3 new replicated tables.
> Below is a copy of the stored procedure for reference.
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> CREATE PROCEDURE dbo.sp_TESTaddDailyTablesToReplication (@.@.IOI_TABLE
> varchar(64), @.@.TRADE_TABLE varchar(64), @.@.CHAT_TABLE varchar(64) ) AS
> DECLARE @.SUBSCRIBER_NYPROD2 varchar(64)
> DECLARE @.SUBSCRIBER_CTDEV2 varchar(64)
> DECLARE @.SP_INSERT_PREFIX varchar(24)
> DECLARE @.SP_DELETE_PREFIX varchar(24)
> DECLARE @.SP_UPDATE_PREFIX varchar(24)
> DECLARE @.INSERT_SP varchar(24)
> DECLARE @.DELETE_SP varchar(24)
> DECLARE @.UPDATE_SP varchar(24)
> SET @.SUBSCRIBER_NYPROD2 = 'INDII_NY2_PROD'
> SET @.SUBSCRIBER_CTDEV2 = 'D02'
> SET @.SP_INSERT_PREFIX = 'CALL sp_MSins_'
> SET @.SP_DELETE_PREFIX = 'CALL sp_MSdel_'
> SET @.SP_UPDATE_PREFIX = 'CALL sp_MSupd_'
> SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.IOI_TABLE
> SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.IOI_TABLE
> SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.IOI_TABLE
> DECLARE @.SCHEMA_OPTIONS int
> SET @.SCHEMA_OPTIONS = 0x000000000000CEA3
> exec sp_addarticle @.publication = N'Indii', @.article = @.@.IOI_TABLE,
> @.source_owner = N'dbo', @.source_object = @.@.IOI_TABLE,
> @.destination_table = @.@.IOI_TABLE, @.type = N'logbased', @.creation_script
> = null, @.description = null, @.pre_creation_cmd = N'drop',
> @.schema_option = @.SCHEMA_OPTIONS, @.status = 16, @.vertical_partition =
> N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd = @.DELETE_SP, @.upd_cmd =
> @.UPDATE_SP, @.filter = null, @.sync_object = null, @.auto_identity_range =
> N'false'
> exec sp_addsubscription @.publication = N'Indii', @.article =
> @.@.IOI_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
> N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
> @.offloadagent = 0, @.dts_package_location = N'distributor'
> exec sp_addsubscription @.publication = N'Indii', @.article =
> @.@.IOI_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
> N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
> @.offloadagent = 0, @.dts_package_location = N'distributor'
> SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.TRADE_TABLE
> SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.TRADE_TABLE
> SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.TRADE_TABLE
> exec sp_addarticle @.publication = N'Indii', @.article = @.@.TRADE_TABLE,
> @.source_owner = N'dbo', @.source_object = @.@.TRADE_TABLE,
> @.destination_table = @.@.TRADE_TABLE, @.type = N'logbased',
> @.creation_script = null, @.description = null, @.pre_creation_cmd =
> N'drop', @.schema_option =@.SCHEMA_OPTIONS, @.status = 16,
> @.vertical_partition = N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd =
> @.DELETE_SP, @.upd_cmd = @.UPDATE_SP, @.filter = null, @.sync_object = null,
> @.auto_identity_range = N'false'
> exec sp_addsubscription @.publication = N'Indii', @.article =
> @.@.TRADE_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
> N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
> @.offloadagent = 0, @.dts_package_location = N'distributor'
> exec sp_addsubscription @.publication = N'Indii', @.article =
> @.@.TRADE_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
> N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
> @.offloadagent = 0, @.dts_package_location = N'distributor'
> SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.CHAT_TABLE
> SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.CHAT_TABLE
> SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.CHAT_TABLE
> exec sp_addarticle @.publication = N'Indii', @.article = @.@.CHAT_TABLE,
> @.source_owner = N'dbo', @.source_object = @.@.CHAT_TABLE,
> @.destination_table = @.@.CHAT_TABLE, @.type = N'logbased',
> @.creation_script = null, @.description = null, @.pre_creation_cmd =
> N'drop', @.schema_option =@.SCHEMA_OPTIONS, @.status = 16,
> @.vertical_partition = N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd =
> @.DELETE_SP, @.upd_cmd = @.UPDATE_SP, @.filter = null, @.sync_object = null,
> @.auto_identity_range = N'false'
> exec sp_addsubscription @.publication = N'Indii', @.article =
> @.@.CHAT_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
> N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
> @.offloadagent = 0, @.dts_package_location = N'distributor'
> exec sp_addsubscription @.publication = N'Indii', @.article =
> @.@.CHAT_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
> N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
> @.offloadagent = 0, @.dts_package_location = N'distributor'
> DECLARE @.SNAPSHOT_JOB_NAME varchar(64)
> -- Run Snapshot for NY2 server
> SET @.SNAPSHOT_JOB_NAME = 'INNYWPP01\PRODUCTION-Indii-Indii-1'
> EXEC msdb.dbo.sp_start_job @.job_name = @.SNAPSHOT_JOB_NAME
> -- Run Snapshot for CT2 serve
> SET @.SNAPSHOT_JOB_NAME = 'innywpp01\production-Indii-Indii-D02-3'
> EXEC msdb.dbo.sp_start_job @.job_name = @.SNAPSHOT_JOB_NAME
> PRINT 'added ' + @.@.IOI_TABLE + ' from replication'
> PRINT 'added ' + @.@.TRADE_TABLE + ' from replication'
> PRINT 'added ' + @.@.CHAT_TABLE + ' from replication'
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Replication Question about job names and categories

I have a TSQL script to add daily tables to replication and then run
the snapshot agent to distribute them to two subscribers. The script
executes without errors, but when I check the running jobs for each
server I see the following:

JUST AN EXAMPLE
Job1'Category' REPL-Snapshot
Job2'Category' REPL-Distribution

What is the difference between these two categories? Also 'Job1' works
properly and receives the 3 new replicated tables, while 'Job2' seems
to be stuck on Step 2 and isn't receiving the 3 new replicated tables.

Below is a copy of the stored procedure for reference.

GO
SET QUOTED_IDENTIFIER OFF

GO
SET ANSI_NULLS OFF

GO
CREATE PROCEDURE dbo.sp_TESTaddDailyTablesToReplication (@.@.IOI_TABLE
varchar(64), @.@.TRADE_TABLE varchar(64), @.@.CHAT_TABLE varchar(64) ) AS

DECLARE @.SUBSCRIBER_NYPROD2 varchar(64)
DECLARE @.SUBSCRIBER_CTDEV2 varchar(64)

DECLARE @.SP_INSERT_PREFIX varchar(24)
DECLARE @.SP_DELETE_PREFIX varchar(24)
DECLARE @.SP_UPDATE_PREFIX varchar(24)

DECLARE @.INSERT_SP varchar(24)
DECLARE @.DELETE_SP varchar(24)
DECLARE @.UPDATE_SP varchar(24)

SET @.SUBSCRIBER_NYPROD2 = 'INDII_NY2_PROD'
SET @.SUBSCRIBER_CTDEV2 = 'D02'

SET @.SP_INSERT_PREFIX = 'CALL sp_MSins_'
SET @.SP_DELETE_PREFIX = 'CALL sp_MSdel_'
SET @.SP_UPDATE_PREFIX = 'CALL sp_MSupd_'

SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.IOI_TABLE
SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.IOI_TABLE
SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.IOI_TABLE

DECLARE @.SCHEMA_OPTIONS int
SET @.SCHEMA_OPTIONS = 0x000000000000CEA3
exec sp_addarticle @.publication = N'Indii', @.article = @.@.IOI_TABLE,
@.source_owner = N'dbo', @.source_object = @.@.IOI_TABLE,
@.destination_table = @.@.IOI_TABLE, @.type = N'logbased', @.creation_script
= null, @.description = null, @.pre_creation_cmd = N'drop',
@.schema_option = @.SCHEMA_OPTIONS, @.status = 16, @.vertical_partition =
N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd = @.DELETE_SP, @.upd_cmd =
@.UPDATE_SP, @.filter = null, @.sync_object = null, @.auto_identity_range =
N'false'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.IOI_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.IOI_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'

SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.TRADE_TABLE
SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.TRADE_TABLE
SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.TRADE_TABLE

exec sp_addarticle @.publication = N'Indii', @.article = @.@.TRADE_TABLE,
@.source_owner = N'dbo', @.source_object = @.@.TRADE_TABLE,
@.destination_table = @.@.TRADE_TABLE, @.type = N'logbased',
@.creation_script = null, @.description = null, @.pre_creation_cmd =
N'drop', @.schema_option =@.SCHEMA_OPTIONS, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd =
@.DELETE_SP, @.upd_cmd = @.UPDATE_SP, @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.TRADE_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.TRADE_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'

SET @.INSERT_SP = @.SP_INSERT_PREFIX + @.@.CHAT_TABLE
SET @.DELETE_SP = @.SP_DELETE_PREFIX + @.@.CHAT_TABLE
SET @.UPDATE_SP = @.SP_UPDATE_PREFIX + @.@.CHAT_TABLE

exec sp_addarticle @.publication = N'Indii', @.article = @.@.CHAT_TABLE,
@.source_owner = N'dbo', @.source_object = @.@.CHAT_TABLE,
@.destination_table = @.@.CHAT_TABLE, @.type = N'logbased',
@.creation_script = null, @.description = null, @.pre_creation_cmd =
N'drop', @.schema_option =@.SCHEMA_OPTIONS, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = @.INSERT_SP, @.del_cmd =
@.DELETE_SP, @.upd_cmd = @.UPDATE_SP, @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.CHAT_TABLE, @.subscriber = @.SUBSCRIBER_NYPROD2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'
exec sp_addsubscription @.publication = N'Indii', @.article =
@.@.CHAT_TABLE, @.subscriber = @.SUBSCRIBER_CTDEV2, @.destination_db =
N'Indii', @.sync_type = N'automatic', @.update_mode = N'read only',
@.offloadagent = 0, @.dts_package_location = N'distributor'

DECLARE @.SNAPSHOT_JOB_NAME varchar(64)

-- Run Snapshot for NY2 server
SET @.SNAPSHOT_JOB_NAME = 'INNYWPP01\PRODUCTION-Indii-Indii-1'
EXEC msdb.dbo.sp_start_job @.job_name = @.SNAPSHOT_JOB_NAME
-- Run Snapshot for CT2 serve
SET @.SNAPSHOT_JOB_NAME = 'innywpp01\production-Indii-Indii-D02-3'
EXEC msdb.dbo.sp_start_job @.job_name = @.SNAPSHOT_JOB_NAME

PRINT 'added ' + @.@.IOI_TABLE + ' from replication'
PRINT 'added ' + @.@.TRADE_TABLE + ' from replication'
PRINT 'added ' + @.@.CHAT_TABLE + ' from replication'
GO
SET QUOTED_IDENTIFIER OFF

GO
SET ANSI_NULLS ON
GO<war_wheelan@.yahoo.com> wrote in message
news:1110817809.086867.107280@.l41g2000cwc.googlegr oups.com...
>I have a TSQL script to add daily tables to replication and then run
> the snapshot agent to distribute them to two subscribers. The script
> executes without errors, but when I check the running jobs for each
> server I see the following:
> JUST AN EXAMPLE
> Job1 'Category' REPL-Snapshot
> Job2 'Category' REPL-Distribution
> What is the difference between these two categories? Also 'Job1' works
> properly and receives the 3 new replicated tables, while 'Job2' seems
> to be stuck on Step 2 and isn't receiving the 3 new replicated tables.
> Below is a copy of the stored procedure for reference.

<snip
I have no idea myself, but in general you'll probably get a better response
to replication questions if you post in
microsoft.public.sqlserver.replication.

Simon

replication question

I have a transactional replication set up Between servers a as publisher and b as subscriber with tables aaa and bbb.
If i make a change on table aaa on the subscriber side should it over written by the value of the publication table value.

means

i have table aaa

id name date

1 bbbb 7/7/77

I have changed name as cccc on subscriber side, should it be over written to bbbb which is the value on publisher table.

Thanks.Transactional replication is a "one way street", from the publisher to the subscriber(s). You can enable RPC calls to funnel changes from the subscriber back to the publisher, but these aren't enabled by default. They are also a positive pain in the patoot to maintain!

Methinks thou desires Merge replication, bubba!

-PatP|||Thanks and I appreciate your response But, I was not asking whether the changes in the suscriber effects at publisher or not?If i made a change at subscriber like changing name from bbbb to cccc.So now the name at publisher database is bbbb and at subscriber is cccc.
Does the changed name at subscriber database(cccc) will be overwritten as bbbb which the name at publisher database. or it will be cccc at subscriber and bbbb at publisher.If it wont over write that name at subscriber how can we maintain same name at both the ends.
Thanks.|||Nothing will automagically force an update at the subscriber, but the next update to that row at the publisher will either a) update the name value at the subscriber, b) ignore the name value at the subscriber, or c) cause the replication distribution agent to fail. Which of the three will be determined by a combination of subscription and distributor settings.

-PatP

Replication question

Dear Sir/Madam,
I am replicating 5 tables from Server A to Server B. On my first setting, I
included those 5 tables on a single publisher. After that, I recognize when a
table schema changes, I needed to drop and re-create the publishication,
means all 5 tables needed to re-synchronize again, is that true? Will it be
better to create 5 different publishication with each for one table only, so
during recreate publishication, only one table needed to synchronize, more
flexible? What the disadvantage for that?
Another question is, during recreate of one publishication, what is the
sequence of action? Is that stop log reader first, then recreate the one of
the publishication, generate snapshot, distribute the snapshot and finially
restart the log reader? Would this action sequence can make sure the
synchonize of those 5 tables?
Waiting to hearing from you soon. Many thanks!
Yours sincerely,
Henry
I would use sp_repladdcolumn or sp_repldropcolumn for this. SQL 20005 has
better options to replication schema changes seemelessly, otherwise if you
have a lot of changes to our schema you might want to think about log
shipping.
Splitting each article out into a seperate publication is a good idea when
sp_repladdcolumn and sp_repldropcolumn can't do the changes you are
looking for - for instance modifiying pk's. It is also a good idea when
your tables are large as if your snapshot fails on one table you will have
to replicate all tables again.
In general you shoudl group your tables into their own publications by DRI.
The way replication works is that the publication tables are locked and
then the log reader agent starts. Users can't make changes to tables until
the data is bcp'd out. Then when the data is bcp'd out the lock is
released and users changes are written to the tlog (as always) and the log
reader will read the transactions in the log and build commands to
replicate these transactions to the subscriber.
This way all the transactions are replicated and nothing is missed.
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

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!