Add New Tables To Replication

Jul 12, 2006

Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:

-break replication,
-stop the subscription,
-make our modifications to the publisher,
-then create a backup of the database,
-push the backup to all of the subscribers,
-restore the database to the subscriber,
-reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot

the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.

this is incredibly cumbersome and time consuming and i know there must be a better way to do this.

i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?

View 1 Replies


ADVERTISEMENT

Replication Conflict Tables

Jul 6, 2001

I have some rogue replication conflict tables that I can't delete because they are system tables. They are names aonflict_<tablename> and bonflict_<tablename>. They are definitely not needed anymore. How can I delete them ? Does anyone know how to delete a system table ?

View 1 Replies View Related

Replication Tables With Forign Key

Apr 28, 2004

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.Danesh

View 1 Replies View Related

Regarding History Tables In Replication...

Feb 1, 2007

Hi,

                             How frequently will the history tables get deleted in Merge Replication and Transactional Replication with updatable subscriptions??

                            When transactional replication with updatable subscriptions is running in the continuous mode, will the history tables get deleted frequently??

Parameters :

Version : SQL Server 2005 SP1

Mode : Continuous running mode

Subscription type : Pull (for transactional)

Regards,

Swapna.B.

View 1 Replies View Related

Miss Tables In Replication

May 3, 2006

I have setup a transactional replication from oracle to MSSQL2005. One of the publication includes 26 articles but I found that there are only 24 articles replicated to the subscriber DB. I found that the repldata folder includes only files for 24 articles only. Besides, I double check the publication properties, totally, there 26 articles. Please advise. Thanks in advise.

View 2 Replies View Related

Removing System Replication Tables.

Jul 23, 2005

Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Add The NOT FOR REPLICATION Option To Existing Tables

Dec 18, 2006



Hello,



How to add the NOT FOR REPLICATION Option to Existing Tables( IDENTITY column,FOREIGN KEY constraints )- TSQL.



Thanks,



Kanna.



View 5 Replies View Related

Deleted Merge Repication Job.. How Can I Know Which Tables Are In That Replication Job?

Apr 6, 2001

Hi, eveyone..
I have a problem.
One of developer deleted Merge Replication Job.
However there is no doc for that replication.
What I want to know is like this:
which tables are in that replication?
Is there any system or user tables track down these replication as well as configuration in that replication?

Thanks in advance:-))
Jay

View 1 Replies View Related

Replication:Unpublishing Not Unmarking Tables Correctly

Dec 30, 1998

A set of tables are published for replication, when I have to unpublish the tables in order to update their structures (i.e. drop and recreate), an error comes up to say that the tables are still marked for replication. I check sysarticles and no tables are listed and even if I completely take the database off of replication, still there are problems. I have looked at the stored procedures that publish/unpublish the selected articles and see that a bit in sysobjects.category is set/unset by a constant of 32. But when unpublishing, it is like this does not work. I have to go into the sysobjects table renumber the category number and try again to drop/recreate and still the problem occurs, so I have to do this once more and then it will work. VERY FRUSTRATING and TIME CONSUMING. I find it hard to believe that this is how to maintain this. Does anyone have any ideas? I unpublish the articles through the replication interface, so it should work.

HELP PLEASE!!

View 1 Replies View Related

SQL 2012 :: Replication To A Database With More Tables Than Source

Apr 14, 2015

We need to replicate data from a live database to a reporting database nightly.

There is data which is only needed for the reporting function - namely the selection criteria for reports which are to be run nightly or monthly.

Is it possible to have extra tables in the target database, holding the selection criteria?

If not, we'll have the reports running heterogeneous queries between the target database and a separate database with the selection criteria in it.

View 1 Replies View Related

Transactional Replication - Modifying Target Tables

May 2, 2006

HiI tried posting this query in microsoft.public.sqlserver.programming but gotno response.I am new to replication but I am trying to setup up a transactionalreplication of tables from one database to another in MSSQL 2000 (SP2).My target tables have primary keys defined. Under publication properties Igo to the snapshot tab and for each table I clear the check box that says"Drop the existing table and re-create-it" and "clustered Indexes." On thispage the nothing is checked. for each table.Whenever the subscription is reinitialized it drops the primary keys on mytarget tablesand replaces them with a unique clustered index on the column that used tobethe Primary key.Is this normal? Is there anyway to stop it from doing this?I don't plan to send the snapshot more than once and let transactionalreplication take over for keeping my source and target in sync, but if Iever have to reinitialize the subscription, it would seem that I (orsomeone) willhave to take the a second step of manually dropping these clustered indexesand recreating the primary keys on the target table.Thanks in advance.---Dick Christoph---Dick ChristophJoin Bytes!612-724-9282

View 2 Replies View Related

Print List Of Tables With Identity Row Set To NOT FOR REPLICATION

Jul 20, 2005

Does anyone know if there's an SQL command i can run that will listthe tables in a database that have an identity column set to NOT FORREPLICATION?Many thanksDan Williams.

View 2 Replies View Related

SQL 2012 :: Error In Conflicts Tables In Merge Replication

Feb 13, 2015

There is an error in one of my merge publication. The error is,

The change for the row with article nickname 2336003 (test), rowguidcol {436456F0-F5AD-E411-80CF-5CF3FC1D2D76} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.

When i checked my tables I got following values in rowguid column

publication 436456F0-F5AD-E411-80CF-5CF3FC1D2D76
subscriptionD824D120-23AD-E411-80E3-00155D0E1001
conflict tables 689C6A61-5359-4BB5-BECD-B03F5F94D79A

View 0 Replies View Related

SQL 2012 :: Transactional Replication Publisher Tables Renamed?

Apr 1, 2015

I am using SQL 2012 SE and implementing transactional replication. I need to insert the rows from publisher database tables to new tables, drop the old tables and rename the new tables with the old table names.

For example:

Publisher database tables that are being replicated:

Table1
Table2
Table3

and I am going to create new tables in publisher database

Table1_new
Table2_new
Table3_new

Move data from

Table1--->Table1_new
Table2--->Table2_new
Table3--->Table3_new

Drop constraints from and then tables (does this require articles to be removed from replication?)

Table1
Table2
Table3

Rename

Table1_new to Table1
Table2_new to Table2
Table3_new to Table3

Does this require replication to set up from scratch or add the three articles only to replication? Is there a way this can be done without pausing or reinitializing replication or without removing articles and adding them back?

View 0 Replies View Related

Dependencies Not Correct With Temporary Tables --&&> Replication Is Failing

Jul 13, 2006

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@GuichetId int,
@Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @GuichetID, @Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @GuichetID, @Validateur


SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id

DROP TABLE #tReservations
GO


The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable
spU_GUI_AppliquePerteFinancement
spU_GUI_AppliquePerteAgrement
FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

View 3 Replies View Related

Merge Replication Replicate Master And Related Tables

Jan 19, 2007



hi all,

Could we configure a merge replication such as replicate the master table and its all related tables (relation deep could be 1.)

We dont want to manually find master table relations and configure replication for the related tables.

please help for that configuration

kinds

View 5 Replies View Related

How To Publish Tables Without Primary Keys In Transactional Replication

May 16, 2007

Hi All

I am a newbie to replication. I just want to know Is there any way to publish tables (articles) without primary keys in transactional replication.



Early Thanks,

Salman Shehbaz.

View 1 Replies View Related

Merge Replication Conflict Tables Stored Proces And Views

May 4, 2006

We have merge replication running with anamous subscribers
We have generested lots of views tables and stored procedures like
sp_ins_C435D35DDEC04FE2517CCD52A9024EC4

ctsv_07BA7383A12B4654B4D3A69B3053B227
aonflict_DH_tblReplicationRegion

How do we get rud of these I am concerned it will fill up the publisher database
Can any one advise

View 1 Replies View Related

SQL Server 2008 :: Snapshot Replication Of 1 Table To A Target DB With 100 Tables

Apr 25, 2015

Regarding Snapshot replication....

If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....

Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?

View 2 Replies View Related

Stored Procedure Doesn't Recompile After Replication Updates Underlying Tables

Mar 21, 2007

We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?

View 3 Replies View Related

View Of All User Objects (Tables, Views) With Their Replication State NEEDED...

Jun 22, 2007

Hi!



There is a view in our replicated SQL-2000 database, that returns all user tables and views with replication state (0 if not included into publication, 1 if included):






Code Snippet

CREATE VIEW [dbo].[ViewREPL_PublishedObjects]

AS

SELECT TOP 100 PERCENT

CASE [xtype]

WHEN 'U' THEN 'Table'

WHEN 'V' THEN 'View'

ELSE NULL END AS [Object Type],

[name] AS [Object Name],

CASE WHEN [replinfo] = 0

THEN 0 ELSE 1

END AS [Replicated]

FROM [sysobjects]

WHERE

[xtype] in ('U', 'V')

AND [status] > 0

ORDER BY

(CASE [xtype]

WHEN 'U' THEN 1

WHEN 'V' THEN 2

ELSE 10

END),

[name]



Now we need to upgrade our database to SQL-2005, but [sysobjects] table have been changed, so neither Replicated state could be determined according on [replinfo] column value, nor User/System object according on [status].



So, I need a view with same functionality, that will work under SQL-2005 and 2008.



Please, help!

View 2 Replies View Related

SQL Server 2008 :: Add Target Specific Extended Properties To Replication Subscriber Tables

Oct 6, 2015

I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?

View 0 Replies View Related

SQL Server Admin 2014 :: Replication - Subscription Database Created But Tables Not Populated

Nov 6, 2015

As per attachment, i have been created replications but in local subscription it is not populated any thing at the same time, Subscription database has been created but tables is not populated as per publication table.

View 2 Replies View Related

Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?

Jan 24, 2008

I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.

I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!

So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?

Thanks,
Keith

View 4 Replies View Related

SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.

Jun 15, 2007

I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.

View 3 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

Adding New Table In Replication And Changing One Column Replication Database

Jan 17, 2002

Hi,

In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don

View 1 Replies View Related

DBCC OPENTRAN Shows REPLICATION On A Server That Is Not Configured For Replication

Aug 22, 2007

Hello,

I have this problem on a Production database.

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.

Thank you.

View 4 Replies View Related

Identity Range Managed By Replication Is Full And Must Be Updated By A Replication Agent. Error Message Makes NO SENSE.

Mar 6, 2007

Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm

View 1 Replies View Related

Replication :: Difference Between Snapshot And Transaction And Merge Replication?

May 26, 2015

What is the main difference between snapshot and transactional and merge replication?

View 5 Replies View Related

SQL Express Replication And Problems Accessing Data After Replication

Jul 28, 2006

Hi,

I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows

- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem

- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine

however.......

- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error

- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.

I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.

I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc

Any advice appreciated !

Thanks
Ronan





View 2 Replies View Related

Does Replication Affect Tempdb GROWTH Replication

Jun 28, 2007

Hi all,

I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????



Thanks in advance

Jacx

View 3 Replies View Related

How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?

Aug 30, 2007

Hi all,

I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.

I am using SQLSERVER 2005 (SP1).

View 3 Replies View Related







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