All Replication Publications Suddenly Disappear
Jan 8, 2007
Hi:
Did you have any experience that all publications and their related jobs suddenly disappear?
I had a transactional pull replication between two servers, and distributor and subscriber in the same box; publisher was an AA cluster server.
The schedule of pulling was once a week.
Last Friday morning everything was working perfectly, but I found all publications and their related jobs disappear around 5pm
I checked with the sysadmin and he said nothing was changed on that period.
Did you have any idea about it?
Did you have any idea that I can prevent this happen again or any alert I can set up to monitor who drop it, when it was dropped and so on?
It is a SQL 2000 box and system table doest not take trigger.
Thanks in advance
Theresa
View 2 Replies
ADVERTISEMENT
Dec 8, 1999
I'm trying to set up replication between two servers that both used to have SQL Server 6.5 on them. I was previously getting a 'The ID '114' was not found in the tasks collection' message and so decided to be on the safe side and remove and re-install SQL Server on both machines. Having successfully re-installed SQL Server 6.5 on both machines I then thought I'd try and set-up replication again. BUT as soon as try to install the publishing side of it the only option I have is to uninstall publishing ???? SQL Server on both machines appeared to uninstall and install nicely so how come it's still asking if I want to uninstall publishing ????
Any ideas would make my Xmas.
thanks Paul
View 1 Replies
View Related
Jun 26, 2006
Hi, I right click on the publications and hit 'properties' but can't find where the frequency of them is.
Thanks,
-Trevor
View 1 Replies
View Related
Jun 12, 2006
Hi.
I have a database with two publications.
One publication is used to replicate data among 4 SQL Servers, while the other is used for the replication with SQL Server CE clients.
The problem here is that the SQLCE receive a number of changes that it is not correct.
Before establishing the publication for SQL Servers the SQL CE clients were working great, but now i have this problem.
Is there any patch or fix I should install on the server?, or do you know how can I solve this?
Thank you.
Omar Rojas
View 6 Replies
View Related
Jan 31, 2007
Hi,
We are using transactional replication with updatable subscriptions in SQL Server 2005. In our case both the publisher and the distributor is the same server. The subscription type is the Pull Subscription. It is set to run in the continuous running mode.
Can we have more than one publication in the same Distributor?
Please refer the link below :
http://msdn2.microsoft.com/en-us/library/ms152471.aspx
This link has a recommendation of the following under the heading "Queued Updating Subscriptions"
"There is only one Queue Reader Agent for each Distributor. It is recommended that for each Distributor, you configure at most one publication that is enabled for queued updating subscriptions."
In our case we need to have 6 publications with 4 subscriptions each (totally 24 Subscriptions).
Pls provide a reply for this asap.
Thanks and Regards,
Swapna.B.
View 1 Replies
View Related
Nov 16, 2015
We have many transactional publications, and would like to have identical settings on each of them. Is any way to compare settings of these publications using script?
View 3 Replies
View Related
Apr 23, 2007
This is strange.
We use Merge (pull) replication to get data from the "mother ship" updated to the laptops of 15 sales reps before they head out into the field, where they work disconnected. At end of day, they all reconnect and all the data is once again shared.
We've been having problems with the replication, but that's another topic.
This morning, I logged onto the server to check the replication monitor, and found that all of the subscriber names for that particular subscription are blank! They show the open/close bracket ( [] ) followed by the database name. This is under the My Publishers >> [server name] >> [publication name] node in the explorer tree. If I try to double-click on one of the subscribers to get their detail, I get tne message, "Replication Monitor could not open the Detail Window." and "Specified cast is not valid".
If I look at the publication in SQL Server Management Studio, all of the subscriber names show up just fine. But they do NOT show up under Replication Monitor.
No one touched the database over the weekend, and this was working just fine on Friday afternoon.
Has anyone seen this before? What to do?
View 3 Replies
View Related
Aug 31, 2006
I am working on a replication design and getting closer to implementation. One of my major concerns is maintaining and updating stored procs and/or user defined functions.
The current design is a single publication, including tables, procs and functions (no views yet). All told there are about 686 articles in the publication. The tables are horizontally partitioned using dynamic filters based on the hostname of the subscriber. There are around 50 subscribers. Most will have small databases (< 250 MB). A few (2-3) will have much larger databases. I am mostly concerned that whenever I have to update a proc or function, I have to re-initialize the subscriptions and that pushes a TON of data out over the network and may interrupt service at the subscriber locations.
I see three options for the procs and functions:
1. Include them in the publication with the data tables
2. Place them in a separate (snapshot only) publication
3. Exclude them entirely from replication and maintain them manually
I am starting to lean towards option #2; but I am a bit concerned about maintaining a duplicate set of replication agents for each subscriber.
Any thoughts and/or comments?
Regards,
hmscott
Am I overlooking something? Is there an option that I have not considered?
View 5 Replies
View Related
Jul 20, 2005
Greetings All, I was hoping that a replication sage might be able toanswer a question for me.I want to have one subscriber subscribing to N publishers. Iessentially have a company that has a main headquarters and threesatellite offices. I want each of the satellite offices to push theirdata up to the master database. From what I have read it seems likethis should not be a problem. Some questions that come to mind are:1.)Does the master need to be read only or can it be configured to beupdateable as well?2.)Can the distribution agent on all the publishers be set tocontinuously distribute or should it be staggered so as not to cause aproblem when another distribution agent is running?3.)If the distribution is set to "delay distribution" will this causechanges on the subscriber to be pushed out to the publishers?In this database guid's are used as pk's so the issue of pk collisionsis not a problem.I hope that this question is not too vague. My experience thus farwith replication has been simple one way transactional and simplemerge replication.Regards, Louis Frolio
View 3 Replies
View Related
May 10, 2015
We have setup Transactional Replication that was originally initialized from a backup. Our subscriber recently had some hardware failures causing the publications to fail. We now have the server back up however the transactions for a few days have been missed, and data is now out of sync.
How do I go about resyncing that missing data? We have too many table to manually resync the data, and I cannot restore the database directly from a backup due to some permission issues and differences from the publisher. I've attempted to generate a new snapshot
However I get a message "[0%] A snapshot was not generated because no subscriptions need initialization." My current understanding that is that I should be able to take a snapshot from the publisher and apply it to the subscriber, thus syncing all the data.
View 8 Replies
View Related
Apr 20, 2007
Hi,
i'm doing POS system by using C# in visual studio 2005. Recently, i installed the microsoft sql server management studio on my pc, i plan to do merge replication through local publications between my pocket pc and my pc. So, i opened the replication file which is under Object Explorer in microsoft sql server management studio, it ONLY show me the local subscription folder under the replication file.Do i install incomplete set of microsoft sql server management studio?Local publications and local subscription, which one is more suitable for POS system?
View 11 Replies
View Related
Sep 2, 2015
We need to replicate multiple databases (publications) to one central subscriber. The schema of those articles are identical in all publications and also the primary keys in publications do not have any overlap.
Is this possible?If yes is there any specific thing that I should consider for it's implementation? Should each publication has it's own dedicated distributor or all of them can share one distributor?
View 2 Replies
View Related
Jan 30, 2007
I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
Everyone known how to fix this problem?
Thanks,
Frank
View 1 Replies
View Related
Jan 9, 2008
I'm running SQL Server Express 2005 on Windows XP.
I've been using SQL Server Express for the past month or so, and I've always been able to connect. Now all of a sudden I can't connect, it's giving me error 26.
I've always used Windows authentication with no need for a password.
View 7 Replies
View Related
May 11, 2006
I've been building a simple test application and things have been working all day. Suddenly, about a half an hour ago - I started getting this error every time I attempt to go to the main web page:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I have no idea what changed... Can someone help me figure this out please?
Thanks!~A~
View 3 Replies
View Related
Jul 6, 2007
Simple (4 lines) stored procedure was working, now is timing out.
Profiler shows hundreds of lines and a lot of time doing:
EventClass: SQLTransaction
ObjectName: Cache Coherency
What are these lines profiler is capturing?
Why is the server doing this, and how can I stop it?
Thanks!
View 1 Replies
View Related
Apr 9, 2008
I've had this connection used for months and months. The setupQL Express + VS2008. In that particular app I use only one DB Connection but there are about 10 other Sql Server Dbs that I connect to in other apps.
I am still in a development stage.
Today I've decided to connect also via the Server Explorer in VS2008. All connections were established easy. Immediately after that a connection.Open ( ) statement failed with the error message
System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
At the Open statement I did a break and with Ctrl + Alt + E got all exceptions. They are too numerous to show in here. One of the messages I found was that the failure occured in System.dll.
Any ideas as to why it happened?
I disconnected (deleted) the DB in question--no change. I deleted ALL DB connections from the server explorer in VS2008 - no change. I see the only option now and that is to reinstall the VS2008. I am kind of sick and tired of doing it.
Also I've been trying to move on to Linq and it is my understanding that in order to do Linq I have to establish connections via VS2008 not just connection strings. Is it right? Perhaps I am mistaken?
This particular connection is not that crutial. I can survice without it with this app but for others it would be impossible to continue development.
Any help will be appreciated.
Thanks.
P.S.
Some additional information has transpired. I checked Sql Server Object Explorer and immediately determined that the DB in question lost ALL its tables. There was just an empty shell: the name, no tables, no system tables, no Programmability, nothing.
I "deleted" this DB and reconnected it. The connection with my app miraculously restored itself. Now I could run the app with no problem and the connection to this DB could be Opened in the app. However, when I tried to set up the connection again in the Server Explorer of VS2008 the same thing promptly happened. Now I got this exception back.
It is not the way VS2005 behaved. Is it normal?
Thanks.
View 39 Replies
View Related
Feb 12, 2007
Is it not possible to execute one package and then design another package? I am executing one package and trying to design another package and just have a grey window in the control tool box that says:
"there are no usable controls in this group. Drag an item onto this text to add it to the toolbar"
Can I only get my controls by dragging when another package is executing? Where do I drag them from?
Thanks,
Kayda
View 4 Replies
View Related
Dec 21, 2006
Hallo all :
How to disappear the generic bar in the report, or to disappear some options (example export). I speak about the bar which in the top of the report or there are the numbers of pages.
Thank's.
View 4 Replies
View Related
May 5, 2007
Hi
My first attempt at RMO is to create a job that will generate the SQL scripts necessary to recreate publications on a warm stanby server.
I thought I'd got a grip on the RMO object model, but whenever I call the script method, an empty string is generated.
Can anyone see what I'm doing wrong?
' Create connection to local server
serverConnection = New ServerConnection(".")
Try
' connect to publisher
serverConnection.Connect()
replicationServer = New ReplicationServer(serverConnection)
replicationDatabases = replicationServer.ReplicationDatabases
For Each replicationDatabase In replicationDatabases
If replicationDatabase.HasPublications Then
replicationDatabase.LoadProperties()
Console.WriteLine(replicationDatabase.Script(ScriptOptions.Creation))
End If
Next
Catch ex As Exception
Finally
serverConnection.Disconnect()
End Try
View 3 Replies
View Related
Feb 15, 2002
Hi Everybody !!
I have a problem with SQL mail on SQL 6.5 with SP 5a . I have created a job called as mailtest and is scheduled to run every morning .
The task was running successfully till last week when suddenly it stopped responding on some of the SQL 6.5 Servers .
When i stop the SQL Mail and start again through the Enterprise Manager , and then run the task , it shows as successful .
Every morning i have to do it to check whether the mailtest is O.K .
But the automated mailtest task has stopped suddenly . I have checked the MAPI profile and is correct as the same is configured on other servers on which my mailtest runs successful.
Can somebody guess what the problem could be ?
Thanks for your help in advance .
Regards
Ashish.
View 2 Replies
View Related
Nov 20, 2007
Hi all,
This issue is on PRODUCTION.
The server is in use almost a year now, but suddenly this afternoon , i saw that Management Studio is missing in start --> Sql Server 2005 --> Management Studio is missing here. ( SQL Server 2005 , Standard Edition, 32-bit, SP1 )
Could somebody tell what could be the reason and how to fix it.
What i need to do , so that i get back the management studio & where should i go & investigate what file is missing.
Any help is greatly appreciated.
Thanks.
View 8 Replies
View Related
Nov 10, 2007
Hello All
I hope somebody can help. This is the second time I've seen this issue. The first time I thought it was a one off, but now I'm getting concerned.
I have two clients - one had Merge replication set up on a database that had a log log file that just grew and grew. The database was set to SIMPLE recovery. I could understand the log file growing if it was set up for transactional replication, but it wasn't transactional, just merge. Looking in sys.databasese the log_reuse column was set to REPLICATION.
I ran the sp_repldone stored proc on this database and all was well - I could shrink the log file down to a reasonable size. This server was SQL Server 2005, Service Pack 2.
Now I have yet another client who has a growing transaction log file. Low and behold the log_reuse for the database is set to REPLICATION again. This server has never had any type of replication set up at all!!!! The log file is still growing and DBCC OPENTRAN indicates undistributed transcations. This particular server is SQL Server 2005 Service Pack 1.
Can someone PLEASE shed some light on what's going on here? Is there some kind of bug that I don't know about? Searching Goolgle Groups seems to indicate that other people have had this problem, and they have have to set up replication and run sp_repldone to resolve it, but that's surely uncalled for on a database that has never seen replication?
Thanks
SQLGal2
View 2 Replies
View Related
Feb 17, 2004
I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?
View 8 Replies
View Related
Oct 7, 2015
I have questions regarding SQL 2008 r2 and Excel 2013.The below code users run from excel and it display all the data first time and when user click refresh under Data in excel the JOBID under JOB column disappear but all other column does not.
SELECT Ind_DHK_ProdRpt_view.Job, Ind_DHK_ProdRpt_view.suffix, Ind_DHK_ProdRpt_view.item, Ind_DHK_ProdRpt_view.description,
Ind_DHK_ProdRpt_view.qty_released, Ind_DHK_ProdRpt_view.[Current WC], Ind_DHK_ProdRpt_view.EndDate, dt.trans_Date AS IntoQue,
Ind_DHK_ProdRpt_view.co_num, Ind_DHK_ProdRpt_view.Bill_Name, Ind_DHK_ProdRpt_view.UsedHrs, Ind_DHK_ProdRpt_view.start_date,
[code]....
View 0 Replies
View Related
Jul 20, 2005
I has a strange question.My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.The system is busy and handle a lot of SELECTs and INSERTs all the time.Sometimes, some transactions are blocked by some other transactions.For those INSERT transactions, we usually call a stored procedure and passparameters in to do the INSERT, at the end of the INSERT stored procedure,we always check @@ERROR = 0 and retrieve the @@IDENTITY (it is usually theauto number primary key) to confirm the INSERT is success. But the strangethings is, sometimes, @@ERROR is equals to 0 and I can get a value from@@IDENTITY but when I fetch the record by the primary key (the value of@@IDENTITY) it returns nothing!! The record is disappear and the primarykey is skipped! I found that this happens usually when the INSERT executeat the time when some other transactions are blocking. Anyone knows why therecord is disappear while @ERROR = 0 and the stored procedure can returnvalue from @@IDENTITY?? Anyone has such case happen in their server aswell? Please tell me some solutions on how to solve this, thank you x10000000 times.
View 10 Replies
View Related
Nov 28, 2007
Hello.
I have a parameter with multi value option.
When I run the report in Visual studio, The first value in the drop down of this parameter is (Select All).
When I check this option it check all the other values for this parameter and when I unCheck this value it uncheck all the ther values for this parameter.
The problem is thet when I'm publishing the report to the server and then open it in IE the (Select All) value does't appear and I only get all the other values.
Any body know how to fix this?
Thanks.
View 5 Replies
View Related
Apr 20, 2006
hi all,
our datbase was restore but the publications were all gone.
however replication monitor shows publication and subscription.
worst. can create the publication with the same name because
it raises an error saying that it can't drop publication becuase
there is a subscription in it.
how can i remove the publication when the are already gone in the publication
folder but exist in the replication monitor
thanks for the help
View 4 Replies
View Related
Mar 30, 2007
Hi
I created a DB named 'TestDB' and created a table called Users. This user table having 3 columns (Uname and pwd,version).
I need to create two publication for this user table.
1) Create a publication using all columns.
2) create a publication using Uname and pwd (not version column).
I am using Merge publication.
When I create first publication (any one - all 3 columns or any 2 coulmns) it create successfully.
When I create second publication it throws error. The details are below.
TITLE: New Publication Wizard
------------------------------
SQL Server Management Studio could not create article 'Users'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An article with a different subscriber_upload_options value already exists for object '[dbo].[Users]'.
Changed database context to 'TestDB'. (Microsoft SQL Server, Error: 20053)
How can i create the second publication? Is it possible? If yes, please give me the steps.
Thanks
View 3 Replies
View Related
Sep 6, 2006
Dear All,
Greetings!!
I have installed SQL server 2005 Express and SQL server Mobile, and I was reading the Books online section to find out how to maintain the two databases consistancy.
Found : replication Option.
Problem : It only shows Local Subscriptions in SQL Server and In SQL Server Mobile only Subscription. I dont get a Publication Option.
Please correct if Wrong : SQL Serv Mobile is the one should Publish and SQL Serv hould Subscribe ya?
Please help me to sort this out.
View 1 Replies
View Related
Apr 18, 2007
The setup is 3 sql 2000 servers, one with a number of publications, and two with transactional pull subscriptions.
The publications were accidentally deleted from the primary server when replication was removed from an older server that unfortunately had been cnamed to the primary server.
The subscriber servers now error "the subscription does not exist".
I had thought restoring the master and msdb on the publisher would bring the publications back but this has not happened. There are good backups of all the system and user databases on the publisher available, but not the scripts that created the publications for replication. Can anyone suggest how or what to restore to get the publications back on the primary server so we can restart replication?
Thank You!
[edit]
It also may be worth noting that the publisher is it's own distributor in this scenario.
View 5 Replies
View Related
Nov 27, 2006
I have two publications on a SQL Server 2000 database.
I am able to create two subscriptions from another SQL Server 2000 database
and synchronize both in succession.
However when I try to repeat this going from SQL Server 2000 to SQLCE 2.0
it fails. The first goes OK. The second fails.
I get error 80004005, 28521 (The SQL Server CE database is already enabled for publication.
Is it possible to do what I am trying to do on CE?
The reason I have 2 publications is because the first is non-filtered and goes very fast via bcp files when reinitialized.
The second is dynamically filtered and not as fast. Breaking them up makes reinits go much faster.
My code is included below.
Thanks
Ed Santosusso
esantosusso@omicron.com
--------------------------------------------------------
// Create and initialize the Replication object
hr = CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER, IID_ISSCEMerge, (LPVOID *)&CEMerge);
CEMerge->put_InternetURL(L"http://myiisserver/sqlce20/sscesa20.dll");
CEMerge->put_InternetLogin(L"<userid>");
CEMerge->put_InternetPassword(L"<password>");
CEMerge->put_Publisher(L"mysqlserver");
CEMerge->put_PublisherDatabase(L"MyDataBase");
CEMerge->put_Publication(L"Publication1");
CEMerge->put_PublisherSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge->put_PublisherLogin(L"sa");
CEMerge->put_PublisherPassword(L"mypassword");
CEMerge->put_PublisherNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge->put_Subscriber(L"TestReplAndMerge");
CEMerge->put_SubscriberConnectionString
(L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\mydatabase.sdf");
CEMerge->put_Distributor(L"mysqlserver");
CEMerge->put_DistributorNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge->put_DistributorSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge->put_DistributorLogin(L"sa");
CEMerge->put_DistributorPassword(L"mypassword");
CEMerge->put_HostName(L"some host name");
CEMerge->put_ExchangeType((REPL_EXCHANGE_TYPE)BIDIRECTIONAL);
CEMerge->put_Validate(NO_VALIDATION);
bool NewFile = false;
// see if we need a file
if (FileExists(L"\mydatabase.sdf") == false)
{
hr = CEMerge->AddSubscription(CREATE_DATABASE);
if (!SUCCEEDED(hr))
{
ShowMergeErrors(L"Add Subscription failed",CEMerge);
return 0;
}
NewFile = true;
}
hr = CEMerge->Initialize();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Static Merge Initialize failed",CEMerge);
return 0;
}
hr = CEMerge->Run();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Static Merge Run failed",CEMerge);
return 0;
}
// Destroy the Replication object
if (CEMerge)
CEMerge->Release();
// set up for Second Merge
// Create and initialize the Replication object
hr = CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER, IID_ISSCEMerge, (LPVOID *)&CEMerge2);
CEMerge2->put_InternetURL(L"http://myiisserver/sqlce20/sscesa20.dll");
CEMerge2->put_InternetLogin(L"<userid>");
CEMerge2->put_InternetPassword(L"<password>");
CEMerge2->put_Publisher(L"mysqlserver");
CEMerge2->put_PublisherDatabase(L"MyDataBase");
CEMerge2->put_Publication(L"Publication2");
CEMerge2->put_PublisherSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge2->put_PublisherLogin(L"sa");
CEMerge2->put_PublisherPassword(L"mypassword");
CEMerge2->put_PublisherNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge2->put_Subscriber(L"TestReplAndMerge");
CEMerge2->put_SubscriberConnectionString
(L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\mydatabase.sdf");
CEMerge2->put_Distributor(L"mysqlserver");
CEMerge2->put_DistributorNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge2->put_DistributorSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge2->put_DistributorLogin(L"sa");
CEMerge2->put_DistributorPassword(L"mypassword");
CEMerge2->put_HostName(L"some host name");
CEMerge2->put_ExchangeType((REPL_EXCHANGE_TYPE)BIDIRECTIONAL);
CEMerge2->put_Validate(NO_VALIDATION);
if (NewFile)
{
hr = CEMerge2->AddSubscription(EXISTING_DATABASE);
if (!SUCCEEDED(hr))
{
ShowMergeErrors(L"Add Subscription failed",CEMerge2);
return 0;
}
}
hr = CEMerge2->Initialize();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Tech Merge Initialize failed",CEMerge2);
return 0;
}
hr = CEMerge2->Run();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Tech Merge Run failed",CEMerge2);
return 0;
}
// Destroy the Replication object
if (CEMerge2)
CEMerge2->Release();
View 1 Replies
View Related
