one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.
configuration is like this,
publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.
It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:
an exception occurred while executing a T-SQL statement or batch
only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs
could not update the distribution database subscription table, the subscription status could not be changed.
changed database context to 'master',(MSSQL SERVER error 22538)
The problem case for transactional replication in MS SQL 7.0 is as follows: Server A (intended publisher) Server B (distributor, intended subscriber)
When configuring replication, the distributor (B) can be configured easily; the publisher (A) that uses B as its distributor will not allow for a publication to be created using either the Enterprise Manager wizards or TSQL commands. The error '627: Cannot use SAVE TRANSACTION within a distributed transaction' occurs when creating the publication.
I am trying to set up a merge replication between SQL 2005 and SQL Express. When I try to apply the snapshot to the SQL Express database. I get the error "Can not access filepathSnapshot.pre due to error OS 3"
Does anyone know what OS error 3 applies to?
Also, when I try to place the snapshop in the default folder location say D:ReplicationData the create snapshot process will work. If I try to place the snapshot folder not in the default and in a UNC like this \serverIPReplicationData I get an access error. sqlAgent has access to the share, which is fully shared out. Any ideas?
Hi All, We are using SQL2000 servers with transact replication to a warmstand-by server. We are seeing the following error from the Log readeragents.Does anyone have any insight on this error?The process could not execute 'sp_MSadd_repl_commands27hp'TIA Scott B.
one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.
configuration is like this,
publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.
It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:
an exception occurred while executing a T-SQL statement or batch only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs could not update the distribution database subscription table, the subscription status could not be changed. changed database context to 'master',(MSSQL SERVER error 22538)
If I got a TORN PAGE error, or something like that, in normal operation could something automated mark the database as Read-Only, or somesuch, so that all further updates to the database were disabled until we had a chance to look at the problem?
I am trying to disable publishing on a server. When I attempt to do so I get the following error:
"Error 8157: All the queries in a query expression containing a UNION operator must have the same number of expressions in their select lists. Could no use view of function 'sysextendedarticlesview' because of binding errors."
There is more error message text but it's a repeat of the UNION and sysextendedarticlesview problem.
I tried to disable the data compression for granular backup purposes on SQL 2014 with following query: EXEC [dbo].[prc_EnablePrefixCompression] @online = 0, @disable = 1
I received following error message: Msg 2812, Level 16, State 62, Line 1...Could not find stored procedure 'dbo.prc_EnablePrefixCompression'.
When I using Create Publication Wizard for database 'MY DATABASE', and setup 'MY SERVER' as the Distributor, then I get a message:
SQLServer Agent on 'MY SERVER' current uses a system acount, which cause replication between servers to fail. In the following dialog, specify a domain account for the Service startup account.
I don't understand this message (current in SQL Server Agent properties, tab Service startup account using System account)? Anyone can explain to me?
Is it safe to change 'max text repl size (B)' on a live system while it is running with users and services running?? I'm using SQL Server 7.0 running on Windows 2000.
I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?
I'm in trouble again. I want to make merge replication between SS 2000 Enterprise and SS 2005 Express edition. 2000 will be publisher, 2005 will be subscriber. In addition I want to use push subsription at 2000 side, very last thing is it will be download only (I know how to make a merge rep. downloadonly, thanks god!). That's all I want to do.
I made a little search, it seems what I want can't be done automatically, using SMO or scripts is suggested. In one of other forums I saw "distibuter>=publisher>=subsriber" (refering sql server versions). Is it an incontestable truth that I cannot make SS 2000 a publisher where SS 2005 Express is subscriber?
How can I achieve my goal, isn't there anyway to make 2000 publisher for merge replication while a 2005 Express is subscriber?
Hi, We're running a replicationprocess for months now, and 2 days ago it broke down, for no appearant reason I can detect. The setup is this: A SQL2K server has a publication defined on a database, consisting of several dozen tables. It is a transactional publication, running continuously. There is 1 subscriber, a SQL2005. Both servers run with Win2003 and all have the latest servicepacks. Up until 2 days ago there was hardly a problem. Then I received errormessages on being unable to load into a specific table. That table had been changed on that day, namely, 1 column was changed from CHAR(13) to CHAR(12). I do not know if this has any relation with the problem we experience. Currently the databases are structural the same. I could not get the replicationprocess to work and deleted all, publication on server1 and subscription on server2. I tried to setup a new transactional publication, which is not a problem, however, I cannot create a succesful subscription. I receive the message:
The process could not bulkcopy into table 'Tablename', where tablename is the changed table...
Again, both tables in publisher and subscruiber are the same in all respects. When I remove the offending table from the publication, the same message now with another tablename... Removing this table will result in again this message with another tablename...
I am really flabbergasted. Any idea where to look at?
We have VLDBs running on a SQL 2005 server. Some users have questioned speed and response times. When I investigated the server (W2K3) using the perfomrance monitor, I noticed an interesting questionabel result. The Repl. Pending Xacts showed 44,000,000 transactions waiting to be delived to the distribution databse. Problem is - we do not have replication tunred on on any of the db's on the box. In my research, I have not noticed anyone with a similiar issue. Does this amount of transaction take up space, or is it simply a counter that I don't have to be worried about?
I'll start off by saying these questions were submitted to Microsoft and no one can give us an answer without them doing a source code review. Their response was "these are internal BCP scripts that are used by SQL"....yes, I know that....that doesn't help to explain why the system created them. At this point, I don't want my compary to pay support hours for Microsoft personnel to learn what their software does. -------------------- We've had merge publication in place for several years.
Last year, we added some tables/articles to publications.
After the upgrade from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2 Standard, when the snapshot was run, the system created some new files that had never existed before. Also, and more importantly, these files were only created for the articles added after the initial snapshot was created. Articles existing in the intial snapshot did not have these type of files created.
Examples of these files for a SA_MOVE table that was added to an existing publication:
The system wanted to apply these files before it would start synchronizing data so they had to be delivered to the subscriber. It appears it was the sysmergesubsetfilters_ files that it wanted to apply. These are all 0 in size and we do not have filter on(at least not as far as I know...see the addmergearticle code below).
New databases created this year that had all of the articles defined in the publication before the initial snapshot was taken did not get these additional files created, nor did the system want to try to send files to the subscriber before syncrhonization could begin.
This is an example of how an article had been cretead under SQL Server 2000 when adding a new database, before an initial snapshot is run:
We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.
Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?
I am setting up a combination of transactional and merge replicationon a very large database and do not want to apply the initial snapshotsince this would take to long. I am doing this with scripts that werepreviosly created when the database was not this big. I want to copythe publisher DB over to the Subscriber Server. Can I use theparameter @sync_type to 'none' when running the sp_addsubscription?Does anyone have any experience in doing this?Thanks in Advance for any help on this.Alan
We have had Merge Replication working for the past few months (SQL 2005 to SQL Mobile) and suddenly today the replication URL that points to the SqlCESA30.DLL does not work. I have tried re-running the SQL Mobile virtual folder wizard a few times....resetting IIS and even a re-boot...and still the URL is not available.
Is there anything that might provide some clue as to why this has unexpectedly failed?
I've been able to startup a transactional replication between 2 database for some tables that only have different table names. Now there are still some tables that not only have different names but also different column names, can this be done in the wizzard ??
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
We are going to have two SQL 2000 Servers with a SQL 2005 distributor server that will handle 1 way transactional replication. What kind of server should I ask IT to spec out that would satisfy an installation such as this?
Our production DB is 30GB and we are replication about 60% of the data to another server.
Is there a way to disable logging ? I'm about to split a 1000000 rows table into three other tables , and would prefere not to log this action ... Does anyone know how to do it in a SQL 7.0 statement ?
can I disable a trigger in Sqlserver 2000??? When i run a store procedure who works with one table i want that the trigger doesn´t work it. After that the trigger would be enabled again.
I know i can delete it and create it again but something like "ALTER TRIGGER DISABLED" would be ok.