How Can I Get Transactional Republishing To Work?
Mar 17, 2006
I'm starting a new thread to keep things clean and simple. This is a transactional republishing issue.
In my simplest scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. (And C will need to transactionally replicate to D, but ignore that for now). All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C distribution doesn't work because I get the error:
Command attempted:
drop Table "dbo"."MyReplTable"
(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)
Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)
I've gotten everything working except this. Who out there has done this and what is the secret?
We have not had any problems doing this in SQL 2000 with 36 replicated tables. In an earlier test, I had SQL 2000 Replication running on this box too, but I completely removed SQL 2000 from this box (except for my Virtual PC which is not running). I don't want to convert transactional to merge. I am running merge replication on 13 tables at the same time as the transactional replication, but am replicating different tables in each. I have no desire to increase the number of merge replicated tables.
Thanks for any advice, including any information about why this is an issue with 2005 and wasn't in 2000.
Paul
View 17 Replies
ADVERTISEMENT
Mar 23, 2006
Can anybody help me...
I can't find it at BOL.
View 4 Replies
View Related
Dec 1, 2006
Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
View 4 Replies
View Related
Feb 7, 2007
Hi,
How do we take Transactional Log Backup using sqlserver 2000? Can we take only transactional log back up?
View 1 Replies
View Related
Jul 30, 2001
On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.
View 1 Replies
View Related
Dec 28, 2000
Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.
Thanks!
Kavira
View 1 Replies
View Related
Feb 13, 2001
I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me
Thanks
View 1 Replies
View Related
Nov 1, 1999
I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -
The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'.
Access is denied.
The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the (
epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.
What am I missing?
View 2 Replies
View Related
Oct 25, 1999
Hello,
I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...
Thanks
View 1 Replies
View Related
May 22, 2003
Hi guys can anyone help me with this issue. if so thankyou kindly.
1. created transactional publication (for tables, stored procs & views )
2. created empty database at subscriber
3. set up pull subscription at subscriber
4. ran the initial snapshot agent at the publisher
5. checked the schema files & found out it hadn't scripted the primary keys on the tables.
Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.
? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?
? Should I break my publications into 3; say 1 for each object?
Thank you guys for taking the time to read this.
View 1 Replies
View Related
Feb 24, 2005
Hi
I am tring to implement transactional replication.
How do i publish tables which donot have primary keys?...because transactional replication seems to publish only the tables which has a primary key.
Thanks
Madhukar
View 7 Replies
View Related
Oct 6, 2006
Hi,
I set up the transactional replication (immediate updating) using Central publisher with remote distributor method.
I'm wondering what kind of issues I will face in future if i add many publications under one publisher. Because I will need to create a publication for each subscribers since each subscribers need different set of data.
Thanks in advance
View 1 Replies
View Related
May 1, 2002
Hello all! I am setting up my first Replicated database, and want to get this right.
We are a Newspaper, and I will be Publishing the database that is the back end of the web server. The goal is to load balance the web site as well as to have a second identical server in case something goes wrong. Both the Subscriber and the Publisher need to be kept in sync as new articles are posted, so my client will just be updating the Publisher. But I'd like to keep a single set of usage statistics, so I was thinking of using Immediate Updating so the Subscriber can update the Publisher using two-phase commit.
What will happen if the Publisher shuts down and an update is attempted on the Subscriber? Will it just hang? Let's assume the worst - what if my Publisher is kaput? How do I determine that my other database is a Subscriber; and how do I stop it from being a Subscriber?
Thanks in Advance,
View 1 Replies
View Related
Jun 28, 2002
Hi
I have a requirement for pushing transactional publication to various subscribers. Each subscriber will need to receive a different subset of rows from a table. So I need all rows published, but with horizontal filtering at subscriber level. Is there a way of applying filtering at the subscription level.
Thanks
Tariq
View 1 Replies
View Related
Jan 20, 2006
hi,
Is immediate updating from subscribers to the publisher the same as bi-directional transaction replication? if not what is bi-directional transaction?
Tks for any help.
View 4 Replies
View Related
Sep 11, 2007
I am using transactional replication between two SQL 2005 databases. On Sunday evenings, we run a maintenance plan that rebuilds the indexes on the publisher DB. When the plan runs, the replication receives errors and cannot continue. The error appears to be at the publisher level. The publisher runs continually and the subcriber runs every 10 minutes. The error states "cannot run sp_replcmds" Other than Sunday, everything runs fine.
Thank you
View 1 Replies
View Related
Jul 7, 2015
I want to set up the transactional replication. How many service accounts we need and what kind of permissions it required in windows level?Only two servers we are using. Pub/Distributor and Subsciber.Which option is better Push or pull?
View 1 Replies
View Related
Sep 14, 2006
Can Express edition be a publisher for transactional replication with SQL-DMO? Does scripting out and editing it work? Does stored procedure work for this purpose?
Thanks
abdul
View 3 Replies
View Related
Jul 27, 2007
at the 'specify articles" on the wizard. Some tables have a key next to it that doesn't let me select them to be replicated. This is happens on transactional replication. But if i do a snapshot, the keys next to the tables are not there.
How am I able to select those tables with a key?
=============================
http://www.sqlserverstudy.com
View 8 Replies
View Related
Aug 24, 2007
I am writing an addServer like google adwords.
I have so many concurrent session and my sql cpu is so high.
how can I build a transactional cache in my iis servers?
View 1 Replies
View Related
Feb 20, 2008
what is the script to take hourly transactional log in sql server without using maintenance plan in sql server 2005
View 1 Replies
View Related
Jan 11, 2006
Hi All!
Need help guys... I'm new to SQL Replication, though I successfully created a transactional replication testing on my machine using PUBS DB and a separate DB.
I'm planning to establish/setup a Transactional Replication. There are two SQL Servers, one is being used by WebApp_1 and the other is being used by WebApp_2. Both servers are maintain by different administrators and situated on different domain/location (both used Win2003 Server). We will transfer some data from Server_1 to Server_2. Server_1 will be setup as Publisher and the other is the Subscriber, then additonalServer_3 will be added and will serve as the Distibutor.
My questions are:
1. Do I need to request separate domain account from the two different servers to establish connection to the Distributor?
2. Will the User databases from Publisher in compromise/affected as far as database settings and data are concerned?
3. Any useful links?
Any help provided will be greatly appreciated...
Thanks!
View 1 Replies
View Related
Jul 13, 2001
Hi everybody,
I'm still pretty new when it comes to SQL server and I'd like to get some opinions about replication. I need to set up replication between 10 sites. The database is about 2 GB and at each size about 10 users are entering data. The databases need to be synchronized at least 3 times a day. Now our application developer told us to use transactional replication while I'm thinking of using merge replication.
Can anyone give me some more advice about the pro and cons of either replication type.
TIA
Charlie
View 1 Replies
View Related
Jun 26, 2000
We have 3 sites & would like to do replication btw the 3 sites. 1 publisher & 2 subscribers. All 3 sites should be able to do updates. Subscribers should be able to operate even if publisher is down. Any advice will be appreciated.
View 1 Replies
View Related
Nov 11, 1999
The problem case for transactional replication in MS SQL 7.0 is as follows:
Server A (intended publisher)
Server B (distributor, intended subscriber)
When configuring replication, the distributor (B) can be configured easily; the publisher (A) that uses B as its distributor will not allow for a publication to be created using either the Enterprise Manager wizards or TSQL commands. The error '627: Cannot use SAVE TRANSACTION within a distributed transaction' occurs when creating the publication.
Any suggestions; I need some help ASAP.
Thanks
View 3 Replies
View Related
Feb 7, 2000
I setup Transactional replication between two SQL Servers.
I am getting the following errors: " Could not find stored procedure 'sp_MSins_stores'." and the job fails.
I dont have any idea why I am getting these errors. I am getting this error for every table in the publication.
Any suggestions are welcome.
Thanks in advance.
View 1 Replies
View Related
Jan 19, 2003
Hi Guys,
I have a small problem with the transactional replication when I run the initial snapshot and distributing some of the stored procedures. For eg., Say if the stored procedure has the following statements:
---------------------------------------
create procedure abc
as
declare @paytype varchar(50)
set @paytype='check'
insert into acct select cusname,cusacctno,@paytype from cuspay
---------------------------------------
I get the following error:
for statement:
set @paytype='check'
--------------------
Invalid column name 'check'.
(Source: ServerSQL1 (Data source); Error number: 207)
I tried changing to double quotes. even tried adding article using query analyzer without using EM
exec sp_addarticle @publication = N'ReplicationSrc', @article = N'abc', @source_owner = N'dbo', @source_object = N'abc', @destination_table = N'abc', @type = N'proc schema only', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000001, @status = 16
GO
Still I get the same error. The server has SQL server 2000 SP2 running with remote distributor and pull subscription.
Am I missing anything.
Please help.
Thanks,
Anu
View 2 Replies
View Related
Jan 29, 2005
hi ,
i am trying out transactional replication to replicate over 60 gb of data.
i am having a problem during the initial creation of the article.
the system hangs at article 73 out of a total of 600 + articles
i have tried this using sp_addarticle as well
but in vain
any suggestions
please let me know
thanks
View 3 Replies
View Related
May 25, 2002
Dear All,
I am running Slq2000 (EV) on NT4.0. I have problem is that a size of Transactional Log(*.ldf) file is 3GB. I want to reduced the size to 2GB.Can anyone help me answer this question.
Note: I hv already took the backup of Transactional file by choosing Trunct file after backup but size is not got reduced.
View 4 Replies
View Related
Aug 22, 2002
Does SQL Server 2000 (Enterprise Edition), mandate the creation of a GUID on each table used in transactional replication?
I was told that in addition to defining a Primary Key, I "MUST" also define a GUID on each table. As I recall - this is not true. Can someone who's setup Transactional Replication please field this question?
thx -
~BT
View 1 Replies
View Related
Nov 10, 2001
Hi
This is my problem
1. I have been requested to implement either merge or transactional
replication at my site.The replication will be over a wan
2. The problem i have encountered is that the tables have been denormalized
to such an extent that they do not have primary keys ,this is because all
the logic has been programmed into the application.I do not want to
interfere with the table schema as this will create major problems
in the application
transactional replication requires that tables participating in this type
of replication must have primary keys .IS THERE A WORK AROUND?
3. The second issue is that if i try to implement merge replication i
am sitting with a problem that merge replication requires that all
timestamp fields be removed to participate in merge replication.This
application also has all the logic built into it.Almost all tables have
the timestamp datatype in the rowstamp field. IS
THERE A WORK AROUND FOR THIS PROBLEM
THanks
View 1 Replies
View Related
Jun 19, 2001
Hi,
I have schedule daily full db backup at 2:00 AM and followed by transactional backups every 4 hrs. The transactional backup fails with error 4213 and 3013, informing "nonlogged updates and cannot be rolled forword". Perform full / differential db backup.
1. Is there any way to over come this problem ?.
2. Is there any way to identify that the nonlogged updates occurred, so that I can write a script to perform the full backup if any nonlogged updates performed.
Thanks,
Regards
MAdhu
View 1 Replies
View Related
Jul 14, 2004
Hi friends, Good Morning
Hey there is a question, i have two servers where replication is implemented, The two servers is used to take backup on dailybasis, on one server we take differential backup, and on second server we take transactional backup on same time, when we restore the backups on the opposite servers (backup taken on 1st server will be restored on 2nd server and 2nd server backup will be restored on 1st server)what are contents u find difference in the data
View 2 Replies
View Related
