I use Transactional publication with updatable subscriptions for replication. Just what to know whenever I want to add new table to replication, do I need to regenerate the new snapshot again. However, once I do it, i found that the subscriber will recreate all table and bulk copy data all over again.. Is there any option or way that I can avoid it. THX
View 2 RepliesHi,
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 everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies View RelatedI have an ASP.Net Application that connects to a SQL DB. I also have a backup of the application and the db on a second server.
I have a snapshot replication setup between the two servers. Every night the snapshot is taken at 3:00am and distribution happens at 4:00am
Everything works perfectly.
However, whenever I want to make a change to a table it won't let me make the change because the table is part of a replication. So I do the following; I delete the replication, make the table change, and then rebuild the replication.
I have to believe there is an easier way to do this.
Is there a way to make table changes without deleting and rebuiling my replication?
Thanks a lot,
Chris
I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.
What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error
'tblProducts' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.
So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).
Does anyone know why? We are running SQL7 SP2.
Email me on ftowicz@icontact.com.au
I'm new to replication and would like to know how to drop an article published for replication. I believe I need to follow these steps, but I am unsure.
Step (1)
exec sp_dropsubscription @publication = N'db name',
@article = N'article name',
@subscriber = 'subscriber name',
@destination_db = N'db name'
Step (2)
exec sp_droparticle @publication = N'db name',
@article = N'article name'
Step (3)
drop the article on the publisher
Step (4)
drop the article on the subscriber
Is this the correct sequence or am I missing something.
Thanks, Dave
SQL Trasnsactional Replication.
I've set up a transactional replication, but I need to specify in which order the tables are to be replicated (tables with foreign keys last).
How/where do set table order ?
/Carl
We would like to copy sensitive prod data into a devleopment environment and use a view to alter some of the sensitive rows. (incl. ssn's)
We have views that will "scrub" the data and substitute ssn's with an alternate key for R.I.
Some would like us to use replication to copy from our scrubbed views to our dev tables. Anyone have suggestions on how to accomplish this?
Only ops has access to distributors on-site, including my laptop :( so I can't do much experimenting and have to back-seat drive.
Is there a way to replicate the table permissions from publisher to
subscriber? I noticed that when replication takes place, the permissions
that were set up on tables on the subscriber are wiped out. I need the permissions to be send to the subscriber automatically.
HI,
I am trying to find a replication solution. It does not have to be real-time, but snapshot will not work since the database is too big. I was trying to configure Transactional replication. The replication itself worked good, but the Truncate table is not allowed in the transactional replication and merge replication. We have to use "Truncate table" in another processes during replication. Is there any other option or third party application I can use to do the replication with truncate table working. I tried Replication Exec, but it does not support 64bit system, which we have.
Hi,How to add new table to merge replication ?When I do this in Enterpriise Manager i got an error:Server: Msg 2758, Level 16, State 1, Procedure sp_addmergearticle,Line 466RAISERROR could not locate entry for error 20085 in sysmessages.What is wrong ?Thank for any helpLuk
View 1 Replies View RelatedHi, I'm using MS SQL server 2005.I have two identical databases - with one exception: they have differentnames (etc database1 and databse2), but their structure is completely same.Is it possible to replicate one table between those databases?Regards
View 2 Replies View Relatedpublisher - sql2000 sp4 distributor - sql 2005 sp1 subscribers - mix of sql2005 sp1 and sql 2000 sp4 we have a database with around 300 tables and replicate it to around 10 different subscribers. some in the same datacenter and others in different offices. We have around 50-60 different publications for this. Some have 20 or so tables others have only one table if they are large tables. Tables range in size from a few hundred rows to over 20 million. Some tables replicate a few commands, others 100000 or more commands on a daily basis. Around 6 weeks ago we started having problems with one table. It's 1.4 million rows and replicates around a few thousand commands on a daily basis. We saw a backlog of around 150000 to 400000 commands. We had some replication issues at this time with this distributor and we traced these problems to memory errors and the replication job would not start or stop. After restarting the job it pushed all the commands through to the subscribers. A few weeks ago we started noticing another backlog again and this time it wouldn't clear. A few times we just ran another snapshot at night but then we started to investigate. We noticed that some people were doing mass updates of 30000 or more rows and this time they weren't going through. It would just build up and cause blocking on the subscribers along with spiking the CPU to 50% or more on a constant basis. As soon as we turn off the replication for this publication everything goes back to normal. Meanwhile all the other publications are happily replicating tens of thousands of rows with no problems. One thing I noticed is that these updates cause a command to transaction ratio of thousands to one where everything else is less than 10 to 1. I thought this was the cause but then the senior DBA updated around 20000 rows in another publication in one update and it went through within a few minutes. Microsoft is absolutely no help. They are looking through logs and they tried to tell us to upgrade our hardware but we have some ancient subscribers for other busy publications with no issues. Right now we are thinking that it might be corruption on the publisher table and we'll be creating a new one this weekend. Any other ideas?
View 16 Replies View RelatedVersion: SQL 2008 R2
Problem: I have a user who wants to remove table, but when she tries to remove it ( drop the table), she gets an error:
===========================================================
MSG 3724, Level 16, State 2, Line 6: Cannot drop the table, 'dbo.<table name>' because it is being used for replication.
MSG 2714, Level 16, State 6, Line 2
There is already an object named '<table name>' in the database.
============================================================
To investigate, I saw the following:
In the SSMS---> Replcation---> Local Publication--> [XYZ].Pub_XYZ
[SERVER Name].[XYZ_Replication]
Local Subscriptions:
[Reference DB].[ServerInstance].[Instance Name]:ABC.ABC_TO_XYZ.Reference DB).
How do I remove that table? Do I need to edit Replication? How? Which one?
Hi all,
View 10 Replies View RelatedI am trying to find a replication solution. It does not have to be real-time, but snapshot will not work since the database is too big. I was trying to configure Transactional replication. The replication itself worked good, but the Truncate table is not allowed in the transactional replication and merge replication. We have to use "Truncate table" in another processes during replication. Is there any other option or third party application I can use to do the replication with truncate table working. I tried Replication Exec, but it does not support 64bit system, which we have.
Thanks
Dear friends
I restore one database in two database servers which is running on SQL server 2000.I replicated these two through snapshot relication.Snapshot agent is creating snapshot.But when I am starting to synchronize it's telling can't drop table because that table is in replication. Backup I taken from a replicated database.so it's having rowguid both the servers.Another thing the table which it is telling not able to drop it's having primary key.please tell me what may be the problem.In replication why it is going to drop table it's only what to transport data na
Filson
Good morning folks,
I'm getting the above error message when trying to drop a table. I'm not to familiar with the setup of this SQL server, so I not sure if the replication is enabled. I have never had to use replication so I don't know to much about it. Does anyone know how I guess to remove this table from the replication list so that it could be dropped?
Thanks much
Dom
Does anyone know if there is a limit to the number of fields you can replicate from one table ??
View 1 Replies View RelatedHi,
Has anyone had any problems on one row updates on a table where you have defined horizontal and vertical partitioning of the data to be replicated?
When I execute an update clause that modifies just one row the log reader misses the modification and it does not get replicated to the other databases.
If I do the same update clause but on several rows then all the modifications are read by the log reader and the replication task goes ok.
What might be wrong?
-janne
Changing datatype in a table used for merge replication ?
I'm trying to change the data type on a column in a table that is used for a merge-replication with another (identical) DB.
I get this error:
Cannot alter the table '[Tablename]' because it is being published for replication.
I've tried to remove that specific table from the publication (Publication properties -> Articles tab) so I can change the data type and then put the table back into the publication, but I can't (probably because it's a MERGE rep.)
Any ideas ?
/CN
I have a merge where the subscriber can do changes to its tables ... But as I found out the changes are made only if publisher simulate an row change or changes something effectively
I'm not sure that at every replication the publisher table would be changed so i need something to simulate a change in the publisher table
Can anyone please help me with this ?
I have two program (App1 And App2) on two separate system.on App1 there is a database named DB1 and on App2 there is a database named DB2.on DB1 i create a table named Table_1 .i define replication for DB1 to push publication (for table_1) .
I define DB2 as a subscriber to receive all changes on [DB1].[Table_1]. every thing is ok but just one thing. when App1 and App2 both are running if i change the contents of [DB1].[Table_1] this change only shown if one time i exit from App2 and rerun it . how can i refresh App2 without rerun it?
A friend said to me it is possible with trigger, but how? I don't know how can i define a trigger for this reason . or any way that exist for this reason.
Hello all gurus out there.
I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can.
First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day.
We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that.
All you help is appreciated.
Thanks much.
Hi,
I have a table used in Merge Replication. If i add a new column in that table it gives me an error for tables in which either this table is a key or this table has key from other tables "Error validating the default for column 'rowguid'." Also for views i'm getting an error
"View 'dbo.vw_TestCaseBasic': indexes and schema binding will be removed."
If i proceed further i get an error and m not able to add the column.
"It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted."
Is there any solution for the same?
Regards,
Ruchir Jain
Hi,How can I modify table with publication (change of one column length)without completely breaking replication.Thanks in advance
View 1 Replies View RelatedHi all,I'm using SQL Server 2000 SP3 to store data for real time transactionprocessing.I have set up replication to another server using a push subscription togive me immediate backup.I need to alter the data type of one of the columns and am using thefollowing basic sql:alter table Voucheralter column SerialNumber varchar(20) NOT NULLHowever I keep getting this error message:Server: Msg 4929, Level 16, State 1, Line 1Cannot alter the table 'Terminals' because it is being published forreplication.Is there anything I can do to allow this update taking place, short ofdeleting the subscription and recreating it. (I want to try and avoidthis as the same update needs to be applied to about 10 databases thatare also replicated in the same way).All help is appreciated.Brian.*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedI want to replicate a database to a subscriber that will be used as a readonly copy. The data has to be replicated as close to instantly as possible.To do this I set up a database export of objects and data to populate thesubscriber, then I set up transactional replication. To verify thatreplication is working successfully, I count the rows in each table, thereare 3 tables in total. For one of the tables, the replication completes butalmost immediately afterward, the table starts to shrink, and after severalhours the record count is zero. This isn't happening to the other twotables, and I can't figure out why.If you have no idea what might be causing this, perhaps you can suggestsome places to start looking. This is Win2k SP4 with SQL 2000 SP3.Thanks much.
View 1 Replies View RelatedWe are doing Reporting for a transaction system. since we do not want to hamper the live database we are planning to do the transactional replication.
Few questions for transactional system.
1. If we replicate a database , then what ever changes happened for the source db will be transferred automatically?
for ex: If i change a column name of a table in source system, then will it transferred automatically to the replicated db?
2. If we do any change to any of the tables in source system, do we need to recreate the replication and reload the entire data?
3. Also we are planning to enable cdc on this replicated db to enable incremental load to my warehouse. So if we disable the cdc and do a full load into the replicated db, then do we need to perform full refresh on warehouse?
4. Can we replicate on a table level? so that if we reload only the changed table and then reload then there wont be any impact on the over all flow of other tables.
I want to add some new tables to the existing pub.We are using transactional replication.
As per my knowledge,by using below option we can do it.
exec sp_changepublication
We recently upgraded one of our servers to SQL 2005 - SP2. This server is a subscriber. Both the publisher and the distributor are SQL 2000. We are now having issues with some of our Snapshot publications.
One of the tables in the publication has statistics created on it - not sure how they were created. This table is small - has a PK on it but no other indexes - this is similar to other tables in the same publication that aren't having issues.
When the snapshot runs each night it tries to recreate these stats on the tables. Since they already exist we get and error saying There are already statistics on table XXX named ZZZ.
We can go in and delete the stats on the table and then replication will go through and recreate them but obviously this isn't something we want to do everyday.
Does anyone have a work around for this??
Dear all
I have a problem about replication, so I want to modify some columns in a table, but my database using replicate. How to do it?
Please solution.
Thank.
PC Kaveesin.
I got a problem when replicating a table with an image field.
First I started replication and it seemed to work. Then after a day or so I noticed that the replication had stopped and it had a few errors like this:
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000182EB0000025E001100000000, Command ID: 1)
Error messages:
NULL textptr (text, ntext, or image pointer) passed to UPDATE TEXT function. (Source: MSSQLServer, Error number: 7133)
Get help: http://help/7133
NULL textptr (text, ntext, or image pointer) passed to UPDATE TEXT function. (Source: MSSQLServer, Error number: 7133)
Get help: http://help/7133
I tried adding a filter to prevent rows which are NULL from being replicated with the filter "[content] is not null ", but I got the error message:
Cannot save properties for article "xxxxxxxxx"
Aditional Information
An Exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SQLServer.Rmo)
Replication filter procedures may not contain columns of large object, large value, XML or UDT type.
Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the were clause of a select statement to produce a valid query.
Changed Database context to 'xxxxxxxxxx'. (Microsoft SQL server, Error: 341).
Now Im left wondering how to get the replication to work?