I am executing sp_dropmergesubscription, but the rows are still in dbo.msmerge_subscriptions, and are still shown in the replication monitor as expired; the last synch dates were in april (expiration is set to 10 days). The 'expired subscription clean up' job appears to be running okay. I need to remove these subscriptions from the publisher as the subscribers are mobile devices, which sometimes are lost.
Greetings,
We periodically get the following message in our three server peer-to-peer transactional replication environment:
"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."
What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?
Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.
Thanks,
BCB
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..
View 2 Replies View RelatedThere is a push subscription to an immediate-updating transactional publication with a queued failover. The publisher was also the distributor.
The publisher melted down and has been removed from service. The subscriber continues to queue up transactions for the now nonexistent publisher.
How do you remove the push subscription directly from the subscribing machine?
Thanks for your help,
Jim Schweitzer
Anobi Technology
I have a program running on Pocket PCs that synchronizes with a desktop SQL database using SQL Mobile. My problem is that I can't delete old subscriptions from the publication; now the publication has 25 subscriptions and won't accept any more (limit of SQL Workgroup edition). I can delete non-SQL Mobile subcriptions just fine.
Running sp_replmonitorhelpsubscription provides this information:
subscriber: LVCONTACTSMERGE - 5FA305234F0C
subscriber_db: Program FilesPocketLVContactsLVContacts.sdf
publisher_db: LVContacts
publication: LVContactsMerge
So I set up this bit of code:
USE LVContacts
EXEC sp_dropmergesubscription
@publication = 'LVContactsMerge',
@subscriber = 'LVCONTACTSMERGE - 5FA305234F0C',
@subscriber_db = 'Program FilesPocketLVContactsLVContacts.sdf',
@subscription_type='all';
GO
No errors are reported, but the subscription doesn't get deleted.
I've tried shutting down SQL Server Agent (mentioned in another thread); makes no difference.
Any ideas?
--Evan
does anyone has idea why the server hang when i try to drop merge subscriptions?
any suggestion would be appreciate.
thanks.
We have a two SQL Server 2005 databases set up using Transactional Replication. My manager has asked me to set it up to replicate constraints (default, fk, et) on the tables in the main publication. I said sure, I can do it, but I will have to re-initialize the subscription. He said there was a way to do it without re-initializing the subscription. I cannot find anything in my research to indicate that there is a method to do so (via call sp_ functions, etc). Is it indeed possible to replicate constraints on replicated tables without re-initializing the who subscription? The reason he does not want me to perform a re-initialization is that we are a few weeks before moving into production, and a full re-initialization takes 1.5 days, which would impact system availability.
Thanks in advance for any advice.
Hi,
We are implementing push transaction replication from Production SQL Server 2000 database to SQL Server 2005 reporting server and SQL Server 2000 Test server.
SQL Server 2000 PROD to 2000 Test is simple but from SS2000 to SS2005 I have following questions:
Is it feasible to create push subscription from SQL Server 2000 to SQL Server 2005 database?
When I am trying to setup publisher/subscription properties, It only give me options to pick SQL Server 7.0 or 2000 or heterogeneous databases.
When I am trying to register SQL Server 2005 database in EM of SS2000 it don't allow me to add it and through exception that I need to use Management studio, Is there any alternate way to register?
Thanks
--rubs
Hi, has anyone got the following error message before? It seems like it occurred when the snapshot creation agent reached the UDF fn_getOwner():
StartIndex cannot be less than zero.
Parameter name: startIndex
Error messages:
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Command Text:
select 'number' = convert(int, 0), 'definition' = definition
from sys.sql_modules
where object_id = object_id(@qualified_object_name)
union all
select 'number' = convert(int, procedure_number), 'definition' = definition
from sys.numbered_procedures
where object_id = object_id(@qualified_object_name)
Parameters: @qualified_object_name = [dbo].[fn_getOwner]
Stack: at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.Script()
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonTableArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, SqlSmoObject smoObject, Boolean quotedIdentifierOn, Boolean ansiNullsOn, Boolean textMode)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateFunctionArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 52006)
Get help: http://help/52006
Source: mscorlib
Target Site: System.Text.StringBuilder Remove(Int32, Int32)
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Stack: at System.Text.StringBuilder.Remove(Int32 startIndex, Int32 length)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.ProcessGetObjectScriptResult(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) (Source: mscorlib, Error number: 0)
Get help: http://help/0
Here's the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/* various comments here. */
ALTER
FUNCTION [dbo].[fn_getOwner] (@moduleID integer, @FY char(4))
RETURNS varchar(5)
AS
BEGIN
declare @tin char(9),
@owner varchar(5),
@ownerCount integer
-- Get owner via ModuleID
select @owner = owner
from tblControlPage
where moduleid = @moduleID and AuditFY = @FY;
-- Get owner via ATIN (This is when the moduleId is not on control page or no owner.
-- Check to see if the ATIN is on control page and use ATIN's Owner)
if @owner is null
begin
-- get module's TIN.
select @tin = tin from tblModule where moduleid = @moduleID
-- count the number of owners by ATIN those owner is not null via entity association.
select @ownerCount = count(*) from
(
select owner from vwControlPage c inner join vwEntityAssociation e
on c.tin = e.atin and e.tin = @tin
and auditfystart <= @FY and @FY < auditfyend
where owner is not null
group by owner
) owners
if @ownerCount = 1
begin
-- good, there's only one owner, get the ATIN owner.
select top 1 @owner = owner from vwControlPage c inner join vwEntityAssociation e
on c.tin = e.atin and e.tin = @tin
and auditfystart <= @FY and @FY < auditfyend
where owner is not null
group by owner
end
-- else there are multiple owners, set owner value to 'Multi'
if @ownerCount > 1
begin
set @owner = 'Multi'
end
end
-- Get owner via TIN if @owner is still null
if @owner is null --or len(@owner) = 0
begin
-- get TIN of module (Commented out the following because it was retrieved on prior "if" condition)
-- select @tin = tin from tblModule where moduleid = @moduleID
-- count number of owners by that TIN those not null.
select @ownerCount = count(*) from
(
select owner from vwControlPage
where tin = @tin and auditFY = @FY and owner is not null
group by owner
) owners
if @ownerCount = 1
begin
-- good, there's only one owner.
select top 1 @owner = owner from vwControlPage
where tin = @tin and auditFY = @FY and owner is not null
group by owner
end
-- else there are multiple owners, set owner value to 'Multi'
if @ownerCount > 1
begin
set @owner = 'Multi'
end
end
return @owner
end
What happens when Publisher goes down in Transactional
Replication, no corruption (power , hardware failure)??
Thanks,
John
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?
Hi,
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.
So, can anybody help me.....
I am using merge replication between two servers.
When i insert data into a perticular table ..the data gets deleted after some time.
Anyone faced this problem?
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?
Thanks
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
View 4 Replies View RelatedI 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
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.
Thanks in advance
I am setting up  transaction replication in SQL Server 2012 with three individual servers for Distributor, Publisher, Subscriber, But I want to know how to set the folder for Snapshot agent like whether the folder should be a network shared folder or normal local folder.
View 4 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 RelatedWe've got x2 data centers in the US:
NYC & SAC
Sadly the hostnames for the boxes I am trying to replicate are similar:
- db1.nyc.mydomain.tld
- db1.sac.mydomain.tld
The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.
Hi..
I have configured publisher, distributer,subscriber... and tried to replicate it... it has done successfully...
When i try to add datas to the publisher ... it says
"conflict error"
can you please tell me how to check the replication is successfull or not.
and i want to add datas to publisher so that it should frequently updated in subscriber...
I had a multi publisher single subscriber replication in my job and now i want remove it. i disabled the subscriber. Now if i disable the publisher and distributor then it gives me a error cannot drop the distribution DB because it is currently in use. this DB can never be dropped since there is at least one publisher at one time that accesses this DB everytime. How should I disable this publication.
We have a publisher that got red-crossed(Run to problem). I decide to remove and recreate it. However, after remove it, the publisher still stays in Replication Monitor. The remain thing has no distributor and logreader, but only snapshot agent. When to check the property of the agent, we got a error message basically say the job does not exist, which makes sense.
Now, it does not show up in any places, except Replication Monitor. Well, it cannot be removed from Replication Monitor. Can any one tell us how to clear it from Replication Monitor?
Thank you.
More Infor: The replication was set up on 2000.(2000 pub, 2000 dis and 2000 sub) with 2005 Management Studio, and the publication was removed with 2005 Management Studio.
Hi,
I`m trying to setup our sql2000 sp3a sql server as a Publisher.When i use the wizard to config Publishing and Distribution wizard. I recieve the following error:
Sql Enterprise manager could not configure server as the distributor. Error 14133 Could not execute instdist.sql. Check instdist.out in the install directory.
I`ve checked the install directory and cannot find the file instdist.out file. I assume that it failed before completeing this stage.
Has anyone come across this error before and any ideas on how to resolve would be appreciated.
Thanks in Advance,
Steve
Hi,
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?
Thanks so much,
David
Hi everybody, I'm quite new to SQL 2005 and I€™m trying to understand some key concepts regarding replicas. I need to develop an application with characteristics similar to the Sales Order Sample for Merge Replication, on the client side it should run with the express version of sql server and also the synchronization should only work via web. I try to run the sample but I got an exception in the CreateSubscription method on invoking publisherConn.Connect();
TITLE: Microsoft.SqlServer.ConnectionInfo
------------------------------
Failed to connect to server XXX.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
I can€™t understand how this connection could work if it isn€™t aware of the fact that it should use https to connect with the server.
Can someone point me in the right direction?
Thanks
Angelo
I have been trying to set up replication with updateable subscriptions. I first followed the tutorial on MSDN and set up basic transactional replication. This all worked fine. I then tried, using that tutorial as a basis, to set up replication with updatable subscriptions.
On the Agent Security page you are asked for a Snapshot Agent, a Log Reader Agent and a Queue Reader Agent. I assigned these to the following accounts, which I created and added as logins, PUBLISHERSERVER
epl_snapshot, PUBLISHERSERVER
epl_logreader and PUBLISHERSERVER
epl_queuereader.
I then tried to set up a subscriber on SUBSCRIBERSERVER. Under the publication I add new subscriber, select the publisher, add SUBSCRIBERSERVER as the new SQL server Subscriber. In the Distribution Agent Security page of the wizard it asks for process account, which will be run when synchronizing the subscription. I entered PUBLISHERSERVER
epl_distribution here and selected the other two default options of connecting to the distributor and subscriber by impersonating the process account. I then took the default options for the next few screens and finally get to:
Login for updateable subscriptions. This offers the option of a login or using a linked server. I have tried various logins here, initially trying the €˜sa€™ login and password. This produced the error: The user is not associated with a trusted SQL Server connection. The servers are set up for mixed mode operation. I then tried PUBLISHERSERVER
epl_distribution and subsequently every other account I had created on PUBLISHERSERVER. All of these failed. I tried linking the servers, but this also failed. How exactly do you set up subscriber with the ability to login to the publisher? I have spent days trying to set this up, and am as you can gather new to this technology, any help would be greatly appreciated.
I have recently deployed a sql replication system into a production environment and I€™m experiencing a few problems.
I have a server holding consolidated data (4 processor/2GB ram) and fifteen subscribers, each being a 400Mhz Celeron with 256MB ram.
Transactional replication is used to pull base data from the main server to the subscribers. This data does not change very often at the publisher, but the subscribers need an immediate update, so continuous transactional replication is configured at the subscribers. This data will never be changed at the subscriber.
The server has another three tables used for historical data. Each subscriber has been configured for merge replication on these three articles. An initial snapshot is run to setup each subscriber. Each subscriber writes up to 10 records per minute into these local tables and continuous merge replication replicates these 10 records from the subscriber to the publisher. As all subscribers are doing the same, part of the merge operation will potentially bring down 140 records to each subscriber (10 records from each subscriber merged to the publisher and back to each subscriber).
Anyway on to the problem€¦
Each subscriber system has a Microsoft.Net application writing these 10 records per minute to the local database. Refreshing a graph within the application occurs once every minute. Refreshing, queries the three local tables used for the merge replication. This application shows an error (randomly) with a sql timeout message trying to get a connection to the local database.
Has anybody got any ideas why this will occur?
Hi all,
I'm a rather newbie, not only to this forum but also to sql server having a question to the following issue:
Is it possible to drop/remove/delete the "orphan" of a merge subscription on one instance of sql server without having the (former) distributor/publisher (on other instance) available?
The background is: I had a small replication infrastructure with two instances (on two machines), one the publisher and distributor, the other the subscriber. Now it happened that the publisher/distributor machine was completely set up new without having the replication dropped in advance, what remained on the subscriber is now a database with all the merge/replication tables and the guid columns in the user tables.
Moreover this, an entry remained in the subscription saying that a subscription with the former publisher exists...
Can I remove these "orphans" without having to setup the instance again?
Thank you,
Andy
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?
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!
View 1 Replies View RelatedHow to speedup data from subscriber to Publisher on Merge replication?
Is there any we can speed up data from Subscriber to Publisher (uploading).
Subscriber downloading data from publisher running more than 20 hours after drop and recreate subscriber.
Could you please post reply.
Regards
Ponnu
If I have one table in one publication in transactional replication
replication between primary and replicate is broken because subscription is marked as inactive.
If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken
If I do
Reinitialize subscriptions
start the snapshot agent
start the distribution agent
Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.
Any help is appreciated