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 RepliesI'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
Hi, I right click on the publications and hit 'properties' but can't find where the frequency of them is.
Thanks,
-Trevor
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
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.
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
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?
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 RelatedWe 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.
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?
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?
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
hi guys :( ive searched on all the topics here about replication. but now i am wondering what windows xp settings do i have to edit? i read something about dcom settings and changing permissions. what is the RIGHT way to share a file or whatever because the schema cannot be accessed for some reason. i need a whole list of things to do because things are not working out after i read the tutorials on other sites. please someone help me.
basically i need to have two servers that need to be replicating each other. i realized that the instances have to be named so i am just totally confused. ill even let anyone connect remotely to help me out if they want. or explaining will do fine. please! thanks guys!
happy holidays!
We are setting up the clients' database with transactional replication that allows the subscriber to send updates by pull subscriptions.
The plan is to create a second database so the users can keep working on the original database while we get the replication set up and working. Once we feel confident that the replication is working on the second database, the plan was to backup the old one, then detach it, and then restore the new database with the latest data from the old one. My question is whether this would preserve all the replication configurations that we set up? Since the replication adds a column to each table what would happen to that column? Or what is an alternative, which would allow us to set up the replication without disturbing the user's work, and then implement the replication with the latest data?
I am also wondering how to set the synchronization to happen once daily? I do not see where I can set that. I only see options for continuous vs on demand. Does on demand mean I can somehow schedule with an external program to run once a day?
Thanks for your patience, and I hope my questions make sense.
Hi!
View 1 Replies View RelatedHi
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
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
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
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.
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.
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();
Hi All
How can I checked the publication are subscribed to all subscribers?
is there any stored procedure or method to do this?
Thanks alot
Tolga
In 2005 transactional replication, The following procedure worked (without dropping the subscription) when I dropped an article from a replicated database:
Drop article: On Publication Properties, uncheck the article (table, stored procedure or function).
Create a new snapshot.
Synchronize the push subscription.
DROP the article on the Publication and Subscriber databases.
Replication still works!
However, the following article says the subscription needs to be dropped and re-created when an article is dropped from publication: http://msdn2.microsoft.com/en-us/library/ms152493.aspx (Adding Articles to and Dropping Articles from Existing Publications ). For transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.
Under what conditions is dropping the subscription and recreating it absolutely necessary? I do not want to include this extra step.
Linda
Hi,
i have the SQL Server 2005 Express installed but i want to create publications so i´m going to install the Developer Edition.
Shoud i uninstall SQL Server Express before?
IN DE installation i have the following options:
sql server database servicesanalysis servicesreporting servicesnotification servicesintegration servicesworkstations components, books online and development toolsWhat is the minimum i have to install to create publications?
Thanks
SP
Hi,
I am trying to replicate some tables from an Sql Server database to an Oracle database. So the publisher is SQL Server and the Subscriber is Oracle.
Unfortunately I realised that I can't set the replicate_ddl parameter to 1 for an Oracle Subscriber. This is the error I got:
"The property "replicate_ddl" cannot be modified for publications that are enabled for non-SQL Server subscriptions."
I looked on the Internet and I found that this parameter "@replicate_ddl" (used in the add_publication stored procedure) can't be set to 1 if the @enabled_for_het_sub parameter is set to 'true'.
So I thought if I just set the replicate_ddl parameter to 1 and leave the other parameter to false and then use the sp_changepublisher stored procedure to set the enabled_for_het_sub parameter to true that will work. But it didn't. I tried this in Enterprise Manager and although I had no error I realised that the replicate_ddl parameter was reset to false.
Is there any way I can replicate the ddl statements in Oracle automatically or I should to them manually?
I am running AWE on SQL 2000 (the server has 8 gig of RAM). I have the SQL Server set to use a max of 6 gig. The server hung unexpectedly (SQL showed no errors or signs). Has anyone had any problems with using AWE, and or any suggestions or changes I might need to do?
Thanks
Hi,
Can i check installation settings for SQL Server 6.5 ... like what sort order id. has been choosen etc. ? by any query ?
Thanks
New SQL2000 installation into which I've copied data from multiple SQL7 installations. Some of the databases I've copied over from a SQL7 server with code page 850, sort order/collation 42, using the detach-copy mdf/ldf-attach procedure outlined elsewhere on this site (thank you). Other databases have been DTS'd from another SQL7 server with code page 850, sort order/collation 44.
In the end I want all databases to be in the default Latin1_General_CI_AI collation I've chosen for my installation.
I'm off to immerse myself in collation papers and alter database documentation from BOL, but was hoping someone could enlighten me in a less painful way.
Thanks,
Al
Hi all
I've got a problem with an American software vendor, who's asked me to check and adjust the regional settings within SQL Express 2005, installed on an XP SP2 pc
I'm scanning meta data into this database
If I enter a date field with a UK date format, I get an error in the SQL logs
If I enter a date field with a US date format, everything is fine
The XP box is setup with all UK region settings
I've been told that my SQL install should automatically use the region settings from whatever OS that it's installed on
I've checked everywhere within SQL, and it all looks to be setup with UK region settings
I suggested to the software vendor that the problem lies with their software
Can anyone suggest if I've missed anything, or where I should be checking my region settings within SQL?
Kind Regards
Tonasis
From the sql server management studio i cannot see database properties
logical file name of mdf, location of the mdf
logical file name of ldf, location of the ldf
what setup should i change to see the properties.
When SQL Server and Web site are both on same machine,
it runs fine, can get data and display in web page.
but when trying to get data from remote SQL Server, it gives error.
What's the setting for SQL server configure and security settings
in different versions?
MS SQL 2000 | MS SQL 2005
==============================================
Framework 1.x ? | ?
Framework 2.0 ? | ?
Thanks alot.
Please provide some setting help for the ?
is there a simple manual how to setup RS.
with the administrators group im able to view my reports. but normal users are not able to view anything. they can enter reporting services, but not open a report.