New Table Added To P-t-p Transactional Publication Does Not Replicate - Why?

Feb 15, 2007

Hello,

We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."

This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.

Can someone point me in the right direction?

Thanks,

BCB

View 1 Replies


ADVERTISEMENT

Data Added Before Publication Created Does Not Replicate - Why?

Feb 15, 2007

Hello,

I have a small three server development environment where I am getting my feet wet with replication. I have set up peer-to-peer transactional replication and it works fine for data added to the publication's table after the publication was created. However, rows in the table that existed prior to the publication's creation have never replicated. If any of the "old" rows are edited they cause an error on the subscribing servers when the replicator attempts to apply updates to rows that do not exist.

How can I get the old rows that predate the publication to replicate?

Thanks,

BCB

View 1 Replies View Related

Transactional Publication With Updatable Subscriptions

Apr 24, 2007

Hi to evebody.
I'm working with the transactional publication with updatable subscriptions provided by SQL Server 2005. The replication works pretty good from the publisher to the subscriber, but I'm having some problems when the data must go from the subscriber to the publisher.

When I do an update in a subscriptor's table, the database engine shows the following error:

21064 - 16 - The subscription is unavailable for immediate updating because it is marked for reinitialization. Try again after the reinitialization completes.

And rollbacks the transaction.

Does anybody knows what to do to solve this problems.

The publisher is a Windows XP with the SQL Server 2005 Developer edition with SP2
The subscriber is a Windows 2003 Server with SQL Server 2005 Developer edition without SP2
I'm using also the inmediate updating subscriptions. Both operative systems have the MSDTC runing.

Thank you in advance.

Sebastian.-

PS: Sorry about my english, it's been a long time without using it.

View 1 Replies View Related

Problem Creating A New Transactional Publication

Oct 23, 2007

Hi,

I'm creating a new transactional publication and when the process begins to add articles i got the follow error:

Messages
* SQL Server Management Studio could not create article 'Tbl_Menu'. (New Publication Wizard)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Automatic identity range support is useful only for publications that allow updating subscribers.
Changed database context to 'sodimprumde4'. (Microsoft SQL Server, Error: 21231)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=21231&LinkId=20476



Thanks for any help, If anyone know how to resolve this problem.

View 2 Replies View Related

Transactional Replication Setup To Replicate Views

Mar 20, 2007

Hello,

I setup the transactional replication to replicate remote database that has 50 tables.

Two of the tables with huge columns.

I splitted the columns by creating several views before running the initial snapshot.

Questions:

1. Can the database replication copy the views.?

2. Where the view will be stored at the subscriber database?

Thank you.

Edwin

View 3 Replies View Related

Row Filter In Transactional Publication With Updateble Publisher Not Working

Oct 5, 2007

I have configured transactional publication where some tables have a simple row filter. And I made a supscription to this publication. When I make snapshot and replication initialization, all rows are updated correctly and passed to subscriber. But when I change some column at a row included by filter at publisher, it is never updated on the subscriber. Other tables and that same table when I remove filter is updated correctly.

What can be problem?

I am using SQL Server 2005 Standard for publisher, and SQL Express 2005 for subscriber, both with SP2.

Any help would be apritiated.

Thanks,
Tomas

View 7 Replies View Related

SQL 2012 :: Adding Articles To Existing Publication In Transactional Replication

Sep 26, 2014

I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?

I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.

Does it behave differently for small and large number of articles?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------

View 0 Replies View Related

Transactional Publication With Updatable Subscriptions In SQL Server 2005, SvPk 2

Jul 31, 2007


Subscription to "Transactional Publication with Updateable Subscriptions" works only one way. Changes take effect on subscriber, but the subcriber is unable to update data on publisher.

I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.' All agents are running at the Distributor (Publishing Server.)

The subscriber is unable to connect to the Distributor using the SQL Server login.

Following is the error message I get:


Creating Subscription(s)...

- Creating subscription for 'SQL3' (Warning)



Messages

Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

MSDTC on server 'SQL3' is unavailable.
Changed database context to 'DB_SQL1_to_SQL3_on_3'. (Microsoft SQL Server, Error: 8501)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476


Please suggest procedure to end this dilemma. The link has no info available.

View 2 Replies View Related

SQL Server 2008 :: Initialize Transactional Publication From Backup Loss Of Data

Mar 18, 2015

I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.

Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.

Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?

View 0 Replies View Related

Automatically Adding Records To Child Table When Record Added To Parent Table

Aug 19, 2006

In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks

View 1 Replies View Related

How Many Triggers Can Be Added In A Table

Jan 4, 2006

How many triggers can be added in a table?

View 2 Replies View Related

I Added A Column To A Table, But, I Can't See It.

May 15, 2007

I ran the code below that adds a column to an SQL table.

I refreshed Data Sources, but, the new column does not appear.

I refreshed Server Explorer and the new column does not appear.

I tried running the code again and I got:

"Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once."

so the column did actually get added, but, I can't see it.



Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Try

Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Patient.mdf;Integrated Security=True;User Instance=True")

Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"

Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection)

sqlConnection.Open()

command.ExecuteNonQuery()

sqlConnection.Close()

'Catch

'End Try

Close()

End Sub

End Class

View 5 Replies View Related

Is It Possible To Find Out When A Particular Column Was Added To A Table?

Nov 4, 2005

Is it possible to find out when a particular column was added to any table?

Thanks

View 1 Replies View Related

Dbo Schema Added When I Rename Table

Apr 11, 2006

i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05

my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one

1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.

DROP TABLE aMgmt.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT'
GO

any help?

View 2 Replies View Related

Can A New Field Be Added To An Existing Table.

Jul 20, 2005

Can a new field be added to a table that already exists.Thanks in advance.

View 2 Replies View Related

Do I Have To Reinitialize Snapshot If A New Table Added?

May 2, 2007

Hello,



After creating a publication for Merge Replication, if I need to add or drop a simple table, do I have to reinitialize the snapshot all over again?



Thanks

Ekrem Önsoy

View 1 Replies View Related

Replicate With New Table

Jan 30, 2007

Hi all, I have done the replication its working good.Now i have created the new table in publisher. but i have created the same table in sub scription too. how can i replcate this new table..?


Thanks
Krishna

View 3 Replies View Related

How Do I Replicate A Table In The Same Database

Sep 5, 2004

i want to replicate the data from one table into another table in the same database,all the transactions in table "a" should be replicated in table "b" and vice versa.
Note:this should be done without using triggers

View 10 Replies View Related

How To Replicate A Creation's Table

Mar 2, 2007

Hi,

i'v send this email to understand how i can replicate a creation's table between two sql servers ?

the documentation of the replication explain the replication use the alter table !!

it's possible to detect with a query this creation of table and start after the replication ?

another solution ?

Regard's





View 1 Replies View Related

Publication Cannot Drop Table.

Nov 13, 2000

We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.

Thanks in Advance,
Phillip M. Triocli

View 1 Replies View Related

How To Add A Table To An Existing Publication?

Feb 22, 2002

Hi,
I would like to know the procedure to be followed to add a table to the existing publication. The subcriber doesn't have the object yet. I have data in the source table and I want to add this table in a existing publication for transactional replication. Any advise is appreciated.

Thanks,
Ravi

View 1 Replies View Related

Problems With A Table In A Publication

Aug 19, 2006

I have a database with two publications, one for SQL CE Clients with filters that send each client its data and keeps it database with "low weight" filtering data not older than a week, and the other publications for SQL Servers.
The publication for SQL CE Clients works excelent, the other one has this problem:

In the publisher i can insert data that should be replicated to subscribers, but subscribers don't receive it. And if I try to insert both in the publisher and the subscriber, the subscriber throws a Primari Key exception.
In the other way, if i insert the data in the subscriber, when it synchronizes, the publisher pulls this data to the table and keeps it, but deletes the subscriber table due to a "conflict". And it never sends the data back to the subscriber, keeping the subscriber's table always clear.

Any Idea?

Thank you.

Omar.Rojas

View 1 Replies View Related

Alter Table In A Publication

Dec 11, 2006

Can i alter a table included in a Publication?

I am having problems with that.

View 8 Replies View Related

Replicate Schema/Replicate Data Only

Sep 7, 1999

Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!

Thanks for ANY information you can give me...
~Jepadria

View 2 Replies View Related

Replicate (Duplicate) A Row's Data Into Same Table

Apr 20, 2008

 I'm working on a web app that needs to be able to take a row in the database and duplicate it, creating a new row in the same table with the same data except for the ID field and reference field.So basically: table1.row1 references table2.row1. I need to duplicate the data in table1.row1 (creating table1.row2) with the same reference to table2.row1.Is there any easy way to do this in SQL? I'm just looking for some ideas or a framework to accomplish this. 

View 1 Replies View Related

Replicate Table Additions And Modifications

Feb 23, 2007

I am new at replication and it has been going well, but have more of an information question that a problem. Basically I have transactional replication without updates. So i am pushing from the publisher to the subscriber only. I have added a table to the publisher side, and it is not replicating to the subscriber. Data pushes fine. I have read about reinitializing, adding article, etc. I do not know which is the way to go, I need it to happen automatically.

Which way and how? Any help is appreciated greatly.

Thanks

View 1 Replies View Related

Multiple Updatable Publication On The Same Table!!

Oct 17, 2006

Hi,

I have created a publication for sql server 2005 Mobile Edition subscriber,

I need to add another Publication/merge replication on the same tables,

Scenario:

-Server A and mobile devices (publication for sql server 2005 Mobile Edition subscriber)

-The same Server A send data "1 way direction" to Server B by merge replication publication.

NB: My Tables are updatble in both publications.

Any idea?

Thanks,

Tarek Ghazali

SQL Server MVP

View 1 Replies View Related

SQL 2012 :: Change Properties Of A Table Which Have Filestream When Column Added To It

Dec 6, 2014

I store files in db in sql server 2008 by filestream. But when a column would be added to table which have filestream, properties of table would be changed. by every things change on table, retrieve files will faced to error. but store process work probably.

and filestream filegroup at following address will be empty. why?

Right click on table --> properties --> storage --> filestream filegroup

View 0 Replies View Related

Create An Indexed View And Replicate To Table

Mar 20, 2008

All,

I have a huge problem to solve.

1) Need to create an Indexed View that joins multiple tables.I know how to create a regular View, but How do I create an Indexed View ?

2)Need be able to replicate the Indexed View [above] across to another server B into a table.

I have to get this done and seriously don't know where to start.

Do HELP me out .Thank You.

View 2 Replies View Related

How To Pull Data Or Replicate A Table From One Server To Another

Aug 21, 2015

pulling data from a table in Server A and creating the same table on Server B.

From what I've read you can't export/import or use a wizard as the db is of the type dbo.md_*****.

View 3 Replies View Related

Problem In Multiple Publication On Single Table

Jul 7, 2006


Hello Everybody, If any one have solution then please help me.
Thanks in Advance.
I do the following steps:

I have created two publications on my SQL SERVER for merge replication
Publication A €“ which returns all rows from the Table1
Publication B €“ which returns all rows from the Table1 where the field MANAGER =€™ABC€™
I have two clients who have MSDE
Client 1 is subscribed to Publication A and Client 2 is subscribed to Publication B
All works fine till now and I am able to make transfers from the two clients and get all the changed data
However, now If I change the filter rules for Publication B and set that it should return all rows from the Table1 where the field MANAGER = €˜DEF€™ , SQL Server tells me that I have to reinitialize all snapshots for all subscriptions. If I don€™t do this it doesn€™t allow me to make a transfer from my Client 1.
As you can see in my example I have not made any changes to Publication A to which Client 1 is subscribed. So this seems to be illogical.
In our case it would not be possible for us to reinitialize the subscriptions for all Publications when the rules for only one Publication are changed because we may have a lot of clients connected to our Server and if we reinitialize the subscription then all the data is sent again.
If anybody know that how to restrict the other publications re-initialization then please tell me how we can do.

View 5 Replies View Related

Can Not Delet A Publication In SQL Server 2005: The Publication Does Not Exist [SQL Server Error: 20026]

Sep 21, 2007

Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.

Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.

View 6 Replies View Related

SQL 2012 :: Cannot Get New Table / Article In Publication To Show Up At Subscriber

Jan 22, 2015

I do not understand why I have this issue.

SQL 2012 SP2, Transactional Replication.

I have added a new table to a database (existing publication) using T-SQL, I then opened up publisher properties, and ticked the new table/article so that it would be added to the subscriber. It did not show up.

I did not use a snapshot to initialize the subscription.

Immediate Synch is 0.
allow_anonymous is 0.

I mark the subscription to be reinitialized. When I start the snapshot agent I get '0% A snapshot was not generated because no subscriptions needed initialization'.

What could I be doing wrong, or missed out? Do I need to drop and recreate the subscription to get the article to show up?

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved