SQL 2005: Transactional Replication &> Executing Sp On Subscribershould Execute On Publisher
Jan 16, 2008
Hi,
I am trying to set up a Updatable Subscriptions for Transactional
Replication with SQL 2005 (queued updating).
I have a stored procedure "SYS_spShellTest" which is replicated to the
subscriber via
the replication option "Execution of the stored procedure":
-------------------------------------------------------------
ALTER procedure [dbo].[SYS_spShellTest] @cmd varchar(1000)
WITH EXECUTE AS owner
as
BEGIN
EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT
Return (@result)
END
-------------------------------------------------------------
1) If I call "exec SYS_spShellTest" on the publisher the call is also
executed on the subscriber.
2) If I call "exec SYS_spShellTest" on the subscriber the call is NOT
executed on the publisher.
How can I make 2) work so that it is also executed on the publisher
please?
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
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
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?
I have taken over a transactional replication setup that is being usedfor fault tolerance (I know, I know...).The scenario I am concerned with is where the publisher goes down due tofailure, so we need to point our application at the subscriber and startupdating the data there. We are not using the immediate updating option.How do I go about re-syncing the publisher with the data that been addedto, or changed on the subscriber, when the publisher comes back online?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below
In the sql server logs I am getting this message: Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher. Error: 14151, Severity: 18, State: 1
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.
I was wondering if it is possible to setup replication between a 2005 SQL Publisher and a 2000 SQL Subscriber? Is there any special setup steps I need?
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?
I am trying to configure Transactional replication with updatable subscription sql server 2005 with service pack 2.
Creating distributor and publisher on the same box, but subscriber on a different server.
Distributor and publisher are configured without any problem but when I try configuring subscriber, it throws below warning.
TITLE: New Subscription Wizard ------------------------------
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.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK_QA-WIN2K3-1413475264_ADVENTUREW1643387368_WELLCORE-542849619_ADVENTUREW1643387368" was unable to begin a distributed transaction. Changed database context to 'AdventureWorks'. OLE DB provider "SQLNCLI" for linked server "REPLLINK_QA-WIN2K3-1413475264_ADVENTUREW1643387368_WELLCORE-542849619_ADVENTUREW1643387368" returned message "The transaction manager has disabled its support for remote/network transactions.". (Microsoft SQL Server, Error: 7391)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3152&EvtSrc=MSSQLServer&EvtID=7391&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
I tried setting this up directly on the Subscriber machine using sp_link_publication but the same error.
Any help or suggestion are welcome.
Thanks Sachin
Don't sit back because of failure. It will come back to check if you still available. -- Binu
Hi; In http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71679 topic, it is stated that SQL-DMO can be used to make msde publisher for transactional replication. But in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71983 topic, Rishi said that "No Express Edition cant act as Publisher". I am not clear about the difference btw msde and express. Why msde can be publisher for transactional replication by SQL-DMO and why express edition can not be? Thank you so much...
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.
I setup transactional replication between 2005-> 2000 database for only one table. It works fine no problem. I checked replication monitor everything works well. My Subscription was Push Subscription on Publisher.
This morning I restored main database at publication server, I saw all my publication configuration were gone.
I then went to create a publication and push subscription again..and did it, But when I went to replication monitor. I checked that Snapshot are being created, but from distributor to Subscriber is saying below message?
"The concurrent snapshot for publication is not available because it has not been fully generated.....lONG CHPPPED off meassage"
WHen creating publciations under 2005 i saw a very interesting option under stament delivery, for inserts , updates , deletes there is an option that simply says insert/update/delete statement.
I could find very little in BOL about this under "Article Properties", is this what it soudns like ? FOr example if you say :
update Table set Cloumn = 'whatever', this will not trigger the update sp for each row at the subscriber , will it actually deliver the update statements and literally do the update/insert/delete statement at the subscriber?
I have just upgraded from 2000 to 2005 and my transactional replication is running very slow, I already have a latency of 10 mins and its getting worse. I'm just using the default agent profile, is there anything I need to change?
I have one Oracle Server (Version 10g) with some simple tables which should be replicated to an SQL 2005 Enterprise Server.
i got this error:
Error messages: The process could not bulk copy into table '"dbo"."S_AGRZ"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037 Batch send failed Violation of PRIMARY KEY constraint 'MSHREPL_38_PK'. Cannot insert duplicate key in object 'dbo.S_AGRZ'. (Source: MSSQLServer, Error number: 2627) Get help: http://help/2627 To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253) Get help: http://help/20253 bcp "RS"."dbo"."S_AGRZ" in "F:Microsoft SQL ServerMSSQL.2MSSQLReplDatauncACPRS2_DISTRIBUTION_S_AGRZ20070518023422S_AGRZ_2.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SSVIESQL04RS2 -T -w (Source: MSSQLServer, Error number: 20253)
i set the publisher to drop the table and create a new one when i reinitialize with a new snapshot. it only works when i remove the unique key constraint. but after that the data in the replicated table has wrong entries. a select count ... will show the exacly same number as an select count ... on the original table direktly on the oracle server. i reinitialzied the replication multiple times with new snapshots. but the problem does not resolve. first i got a unique key constraint. then i typed truncate table ..., after that i removed the the unique constraint and than all data will be imported but there are some entries that are not existing in oracle.
my server is patched with the newest updates for sql 2005 (Version 9.0.3159).
for exempla the following statement will show the same results on oracle and sql 2005:
Oracle: SELECT COUNT(*) FROM S_AGRZ Result: 471.066
MS-SQL: SELECT COUNT(*) FROM S_AGRZ Result: 471.066
but, for example this will show differences:
Oracle: SELECT * FROM S_AGRZ WHERE art_nr='C7972A' Result:
I have a publication on 2000 machine, replicating by pull subscription to a 2005 machine. I added a table to the Articles in the publication. Reinitialized the subscription, and also restarted both the snapshot and log reader agent. I was kind of assuming the publication would automatically create the table on the subscriber, but after a very long wait it is still not there. Am I missing anything?
PS. Distination Object setting : "Action if name is in use" is set to drop and create new, not that that should make any difference since name is not in use anyway.
I'm a newbie to Replication and recently setup the following.
Publisher and Distributor on the same SQL2005 server, then I've got 7 subscribers(SQL2000 servers) and I'm using push subscriptions. I'm replicating 5 SQl tables which don't have too many changes and these are scheduled to run every 3 hours. In a few days a large one off SQL update with add an additional 10,000 rows to one of the replicated tables. I was wondering what impact this would have on the above setup i.e are there any sort of limitations here. I'm assuming not but thought I would check. I'm thinking it will just cause additional overhead on the server, but the update is being applied when no users will be using the database.
We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database. We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text. We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size. Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?
We previously having two servers A and B. Server A is used for updation of data and the data then replicated to server B. Server B is used for
Server A : purpose : used for database updation/ modification SQL Server version : SQL Server 2000 SP 2
Server Z : purpose : used for Reporting SQL Server version : SQL Server 2000 SP 2
We were doing Transactional replication from Server A to Server B.
Last month we have broght another server (Server B) with same hardware configuration but having SQL SERVER 2005 installed. This is to speed up our database update process. We have moved some of the database on this new server so that we can achieve our deadlines.
Server B : purpose : used for database updation/ modification SQL Server version : SQL Server 2005
I have set up the transactional replication from Server B to Server Z and replication works fine. However, the issue is after it is started replicating from this new server (Server B) performance of all the queries reduced a lot.(making my life harder)
I didnt expected this as our reporting server is still SQL server 2000. I have restored the backup of database which was replicated from server A (sql server 2000) and compared execution plan for one of our common query (which is used in most of the reports and which is now taking longer time to provide results)
I found that database which is replicated from Server B (Sql server 2005) is having primary keys. which was not present in the database which replicated from server A(Sql server 2000).
I have then removed the primary key and make the indexes same as previous copy of database(which was replicated from server A) But still the query takes long time.
Execution plan now shows "Table Spool" which was not present in previous copy of database.
Almost every query for this database is taking longer time now.
Can someone suggest me what is wrong and what should I need to fix.
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.
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.
I am trying to test simple replication (only tables) of a database that resides on a SQL Server 2005 instance to a SQL Server 2000 instance. The Publisher and Distributer are set up on the SQL Server 2005 instance for Transactional replication. The subscriber is set up on in the 2000 instance. Replication Monitor shows the following error after applying a few scripts: "Category: SQLSERVER Source SQLSERVER2000 Number: 170 Message: Line 6: Incorrect syntax near 'max'."
Here SQLSERVER2000 is the name of my 2000 instance, as should be obvious.
Beyond this point, replication fails. Any pointers as to where the problem could lie? Is this a known backward compatibility issue? I've checked all tables in the database and none contain any datatype that is new to 2005 (the database was actually created in and for SQL Server 2000.
Replication from 2000 to 2005 works fine, but the other way round is failing as described above. Any clues?
Is it possible to replicate data from one publishers to a multiple subscribers in transaction replication? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 10 articles from A to B but only 1 from A to C. In other words I want to use the same publisher but select one article from that publisher instead of all 10 to Server C/database C.
If it is possible what will be the drawbacks of such implementation? Will performance be a hit?
Before this, I've 2 difference site that run SQL server 2000 as database server. One at north and the other at west.
At north, I've a group of terminal that run SQL server 2000. I've 1 main server that run as publisher and distributor to all the terminal. And also the main database is at the main server. The terminal will replicate together using merge replication. So that the data would be same at all terminal. At west it also have the same scenario.
Last week my boss ask me to replicate both main server ( north and west ). So that he want the data would be same at all place.
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),
SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
What if a Publication and Distribution server machine fail to work. Do I need to reinitiliaze the whole snapshot on a new machine and retransmit the data to the subscriber. Or can I make use of replication script to recreate the publication and distribution and then replicate to existing subscriber using same machine name?
This is the first time that I am trying to implement replication. Icame across with a problem while I was creating my publisher anddistributer. I want to create the publisher and the distributer at thesame machine however I got this error" SQL Server Agent on <machine name> currently uses the system account,which causes replication between servers to fail. In the fallowingdialog box, specify another account for the Service start up account"If I skip this error does it cause any problem? or how can I fix thisproblem? If I give different login and password does the Service startup create any abnormality in the Database?If someone can help me I really appreciate it?RegardsAs