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
Dear Sir,
I am working on mysql (linux). I cant add forign key to my tables.
ALTER TABLE test
ADD CONSTRAINT ldf_fk
FOREIGN KEY(lead_code)
REFERENCES source(lead_code)
regards
Joby
i have a database sturcture :
| tbl_Customer | -> one to many ->| tbl_Orders |
What i want is to delete one customer from the tbl_Customer table, and automatically delete all the child orders to that customer.
i could by using loops, look through all the childs and delete the records one by one and the customer last.
so i wounder if there are an SQL state that automatically look up childs and drop them all by calling an stored procedure.
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?
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 RelatedHi,
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.
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 RelatedHi, 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
Hello,
How to add the NOT FOR REPLICATION Option to Existing Tables( IDENTITY column,FOREIGN KEY constraints )- TSQL.
Thanks,
Kanna.
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
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!!
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.
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 RelatedDoes 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 RelatedThere 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
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?
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
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
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.
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
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?
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 RelatedHi!
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!
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 RelatedAs 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 RelatedI'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
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 RelatedHi,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 RelatedHi,
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
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.
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 RelatedWhat is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedHi,
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