SQLServerCentral / SQL Server 7,2000 / Replication

"There are already statistics on table" error 1927 - can not resolve

Brian Jones-401554 — Wed, 09 May 2007 04:41:00 GMT

I have straight forward transactional replication set up on a SQL2000 database to a SQL 2005 database. The distribution agent is on SQL2000 and is push replication.

Recently the replication has failed with the error "There are already statistics on table" error number 1927. When this occurs I have data missing from other tables aswell which is very strange.

The error is correct, and to cure it I have to remove the offending statistic on 2005 and reinitialise the replication. What is also strange is that the statistic name is not _WA but another name (and not the name of an index). Obviously this is not the ideal situation as firstly this could occur at any time and therefore leave the database unusable (and some angry users as this is a 24/7 application), and secondly a reinitialisation of the replication could take some time.

I have found an article in the MS Knowledge Base, however it does not work. The reason it does not work is because it suggests turning off the "Copy objects to destination...Non clustered indexes" option in the article. However this option is disabled and I can not change it.

Does anyone have any insight into this error, I need a direction to investigate desparately.

Thanks,

Brian Jones

Distribution Agent Error re:Could not find stored procedure 'sp_MSins_<table_name>'

Chris O'Donnell — Fri, 07 May 2010 07:52:39 GMT

We have a customer who is receiving an error on their Distribution Agent stating:[b]Could not find stored procedure 'sp_MSins_<table_name>'[/b]The problem is that the 'sp_MSins_<table_name>' Stored Procedure does exist on the subscription database.Here is some background. Our product relies on Transactional Replication, although we do not initialize the subscription database automatically. Instead we create our own replication procedures (i.e. the Insert, Update and Delete procedures) and bcp the data from the publication database and then Bulk Insert into the subscription database. This all works fine and has been replicating using transactional replication for years.A problem occurred recently because the customer attempted to re-initialise the subscription database by simply right clicking on the subscription and selecting "Reinitialize". Subsequently the replication's Distribution Agent failed with errors stating:[b]The process could not bulk copy into table...[/b]This happened for several of the tables it was attempting to initialize.This wasn't too much of a problem though. I checked the earliest transactions that were in the distribution.dbo.MSrepl_commands table and found that 2 transactions (with many commands) relating to attempts to reinitialise, such as "sync" and "SYNCSTAT". I deleted these from the distribution.dbo.MSrepl_commands table and restarted the Distribution Agent, so that the next transactions that were waiting to be replicated could be processed.The current transaction that is waiting to be replicated is now a simple insert into a table, which will use one of the sp_MSins_[tablename] procedures. It should be straight forward. The sp_MSins_[tablename] procedures all exist on the subscription database. However, we are now getting an error stating:[b]Could not find stored procedure 'sp_MSins_<table_name>'[/b]I have checked everything I can think of. The permissions are fine. The Distribution Agent is running under a Sys Admin account. The Stored Procedure exists on the subscription database. I have profiled the Distribution Agent and can see it attempting to execute the sp_MSins Stored Procedure on the correct database using the correct login, but it fails.If I copy the execution of the sp_MSins Stored Procedure from the trace and run this directly on the subscription database while logged on as the login that the Distribution Agent is using, it is successful (albeit that I put this inside a transaction that I then rollback).Any help on this would be most appreciated.

Replication/Log Shipping across a firewall

John Mitchell-245523 — Thu, 21 Jul 2005 01:57:00 GMT

Hello

We're trying to decide between transactional replication and log shipping, and I think it's going to come down to how easy it will be for the two servers to communicate. For log shipping we will need for one server to map a drive to the other, meaning the account that starts the service on one server needs permissions to a share on the other - not easy to set up if there are firewall policies to overcome and no trusted domains between the two.

As I understand it, transactional replication only needs port 1433 to be open on the firewall for the two servers to communicate (or three if we use a separate Distributor). Am I right in this?

Second, I have read that replication generates more network traffic than log shipping. Is there any way of quantifying this?

ThanksJohn

Accidental Merge Replication Reinitialize

Ian Scarlett — Tue, 26 Apr 2011 02:42:33 GMT

A client accidentally marked all subscriptions for reinitialization in one merge replication publication. Everything is back up and running (after a lengthy outage), and I'm now tasked with seeing if it's possible to "undo" this should it happen again.I started working my way through the metadata changes that are made when this is done, and it's looking to be either a major task or completely impossible.Does anybody have any scripts or articles or advice that may help me?

Merge Replication SQL2000

kai-753082 — Fri, 11 May 2012 15:01:57 GMT

Hi There,I an trying to set up a Merge replication and I receive the error 'The remote server is not defined as a subscription server.' on the Merge agent.The new publication appears on the subscriber.I have already a snapshot and transactional replication successfully running for years. The Publisher is on my local network behind a firewall, the subscriber is a webhost. In preparation, I opened the appropriate ports on the firewall on the network of the publisher and successfully registered the publisher at the subscriber.What am I missing here? ThanksKai

Process could not execute 'sp_replcmds'

pdchandran — Tue, 08 Dec 2009 10:21:17 GMT

Hi all,We have Sql Server 2000 Ent Edition with SP4. There is a heavily used OLTP database from which we publish articles to various subscribers to offload reporting activity. We observe that whenever we run the Index ReOrganize job (desingned using the Maintenance Plan wizard) the replication fails. Failure point it the logreader for all the publications in the database. The err msg is - Process could not execute 'sp_replcmds'. We had to fire the sp_Repldone command to get this working again. Can someone pls advise as to why the log-reader fails when we run the Index maintenance. We have now faced this issue a couple of times and had to advertise downtime for just carrying out the index maintenance job.

The specified LSN for repldone log scan occurs before the current start of replication in the log

jshaver — Tue, 31 Aug 2004 09:57:00 GMT

Anybody know how to clean this up on a Log Reader Agent:

The specified LSN (0000f7b8:000000f6:0001) for repldone log scan occurs before the current start of replication in the log (00014d5f:00000814:0004).

Thanks,

Jeff

Replication Problem : Invalid column name compensate_for_errors

Draconian — Mon, 16 Apr 2012 23:49:04 GMT

Hello,I have a replicated database and i have added two new articles. but in one of them i am getting below mentioned error.[quote]Invalid column name 'compensate_for_errors'.Invalid column name 'compensate_for_errors'.(Source: W2K3SRV8402 (Data source); Error number: 207)---------------------------------------------------------------------------------------------------------------The process could not deliver the snapshot to the Subscriber.(Source: Merge Replication Provider (Agent); Error number: -2147201001)---------------------------------------------------------------------------------------------------------------[/quote]Below is my table structure for the table in question[quote]CREATE TABLE Dbo.[PendingAppReminder] ( [SiteCode] [smallint] NOT NULL , [FromBCNT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FromPIO] [int] NOT NULL , [Status] [tinyint] NULL , [ModifyOperatorNo] [int] NULL , [ModifyDateTime] [datetime] NULL , CONSTRAINT [PK_PendingAppReminder] PRIMARY KEY CLUSTERED ( [SiteCode], [FromBCNT], [FromPIO], [ToBCNT], [ToPIO], [ReceiptNo], [FeeReceiptDate] ) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY]GO[/quote]SQL Server Publisher is version : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) SQL Server Subscriber version where replication is successful : Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) SQL Server Subscriver version where replication is unsuccessful : Microsoft SQL Server 2000 - 8.00.2039PS: On Publisher i have added other articles in other databases as well. but they are all working fine. I have got problem in this one subscriber only.

Replication Problem : Copies all the rows everytime

Draconian — Mon, 12 Mar 2012 01:22:28 GMT

I am having a problem in ReplicationThe scenario is, We have a vewry big database (100GB+) there are 200+ tables and only 50 odd tables are being replicatedWe have setup a publisher and there are 10+ subscribers are getting data from there. Now i have to add a new subscriber.I followed following steps to add new push subscriber and it added it successfully. and started getting records- I created a blank DB on subscriber- Created push subscription- It started replication.but the main problem is everytime it downloads full snapshot. ie. there is one table having 20 million rows and it downloads it everytime.- deleted subscription- I restored existing backup for other tables (only after above problem encountered)- created new push subscriptionand still i was getting problem.It doesnt happen on anyother subscriber. it happens only in this subscriber.What am i doing wrong?

Transactional Replication with a Twist

Rofty — Thu, 26 Jan 2012 03:56:01 GMT

I have two databases in a stock trading company that will use replication to sync data. The propose method will be Transactional Replication at 1min intervals.[u]MAIN- Main Database (Located at Head Office)[/u]This database contains all trading and admin and performance data.[u]SUB - Trading Database (Located at Johannesburg Stock Exchange)[/u]This database contains only live trades that are currently in the market.I will use replication to sync data from SUB to MAIN with replication. Everyday 100s of trade data records are inserted on SUB on daily basis. This data need to be sync to MAIN where it will be processed for admin and performance statistics. After a Trade has been closed, all its data needs to be removed from Thor. This means that SUB will contain limited data, which will ensure very small high speed trading database.[u][b]Here is my question:[/b][/u]I will delete this "old" data on SUB after I have synced it to MAIN. But because I'm using the Transactional method of Replication, I fear when I sync again the log will state that I deleted data on SUB and thus delete it on MAIN.SO how can I remove the data on SUB and not worry about it being deleted on MAIN due to the Transactional method.

Sp_AddArticle vs general article addition behaviour

prn9974 — Tue, 19 Apr 2005 12:18:00 GMT

SQL Server 2000

Two issues with ReplicationIssue 1 & 2 are on different servers

Issue 1:

When you run SP_HelpArticle procedure, the value for column 'status' is the one that is set while adding the article, which is SP_addarticle.....@Status = ....

However the values that can be set for status are 0, 8, 16 & 24. Incidentally this is the value I set while adding the article, but the SP_Helparticle returns '17'. How can this happen? or what does this mean? When I try to set '17' as a value, I get an error for wrong value passed.

Issue 2:

I used sp_addarticle so many times before. Today, when I ran this proc, I observed the following1. SP_EnumFullSubscribers returned null2. Subscription property said only some of the articles are subscribed, I was allowed to drop the articles and the property changed to 'all'.3. Finally I had to use the Enterprise Manager to add the articles and the replication is fine.

No idea why it happened.

Any help on these issues will be a great help. Please pass any information you know.

More investigation::

On Issue 1: I run the following or using Enterprise manager wizard

exec sp_addarticle @publication = N'ReplDB', @article = N'ReplTable', @source_owner = N'dbo', @source_object = N'ReplTable', @destination_table = N'ReplTable', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'none', @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_ReplTable', @del_cmd = N'CALL sp_MSdel_ReplTable', @upd_cmd = N'CALL sp_MSupd_ReplTable', @filter = null, @sync_object = null, @auto_identity_range = N'false'

Then if I run the following

Exec SP_HelpArticle @Publication = 'ReplDB'Go

I get a result set with the article details. In which the Status is displayed as 17

I checked with the various values that can be set, such as 0, 8, 16, 24. every time I query the values return as the value I set + 1.

For example

When I set @status = 0, the Return 'Status' value is 1When I set @status = 8, the Return 'Status' value is 9When I set @status = 16, the Return 'Status' value is 17When I set @status = 24, the Return 'Status' value is 25.

I guess this one is killing me not knowing why this happens.

As to Issue 2: still no progress. using 'SP_AddArticle', does not add the article completely. But using the Ent Man does.

Thanks in advance

Snapshot agent job scheduler keeps disabling itself!

Adonia Delavigne-521648 — Mon, 19 Sep 2011 22:38:05 GMT

Hi I have a legacy prod 2000 setup which I replicate to a dr server. I've had to rebuild the replication solution on both servers, from scratch as I started getting the odd snapshot agents' schedules disabling themselves for no apparent reason. Since the rebuild ALL snapshot agents continually have their job schedule disabled daily, which I've been setting to 'enabled' manually, but this is not a workable solution.I cannot see any reason for this to occur as I am the only person who logs into either of these servers directly and uses SQLServer Enterprise Manager.Many Thanks in AdvanceAdonia

Txn Replication sp_addarticle requesting @force_invalidate_snapshot = 1

Express12 — Tue, 28 Feb 2006 11:08:00 GMT

Using SS 2K, I want to ADD 2 articles to an existing Publication (txn'l / continuous) WITHOUT having to regenerate the entire Snapshot which takes 4 hours. I've followed instructions in a few articles and have run into a few roadblocks.

1) I DTS the 2 new Articles from the source DB to the target DB (and create the PK's, indexes etc.)

2) I exec sp_addarticle using identical attributes from my other Articles in the Publication (as per Microsoft's instructions to script out the Publication and copy model syntax) The sp_addarticle FAILS w/ this msg:

Server: Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 54Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

When I incorporate the @force_invalidate_snapshot = 1, the process works BUT it then invokes a FULL Snapshot of all Articles.

Is there any way to avoid generating the FULL Snapshot?

Merge replication - msmerge_contents generation = 1 rows

Douglas Elwood — Tue, 14 Feb 2012 12:24:37 GMT

We have an issue on SQL 2000 (SP4) with our merge replication configuration.Some of our subscribers experienced the dreaded "sentgen set too high" error when we initially attempted to create a "push" subscriber.We later changed the subscriptions to "pull" instead, but the bulk of the msmerge_contents rows remained from the subscription that failed.Right now, we find millions of these extra rows in our msmerge_contents tables, all with a generation value of 1.Most of our subscribers have the generation = 1 entries in msmerge_genhistory, but these appear to be anchor records for the initial subscriptions. (the date of coldate is 2004)I would love to remove anything that is not being actively used, but I fear damaging "in-flight" merge replication rows.Nothing I have found about generation = 1 rows in msmerge_contents leads me to believe that this action would be safe, but it is causing a number of our subscribers to perform much worse then they should.Any help, guidance or pity would be greatly appreciated.Thanks!Doug

Merge replication - conflicts

rubinasd — Mon, 16 Mar 2009 02:39:41 GMT

I have a merge replication setup, and the replication upon completion on the specific agent of the subscriber gives message:Merge completed after processing 8 data change(s) (1 insert(s), 0 update(s), 7 delete(s), [b]78 conflict[/b](s))While the replication is in process, i saw the messagesThe merge process is retrying a failed operation made to article 'Oil Export$Document Mgt Printout Log' - Reason: 'The merge process was unable to synchronize the row.'.The change for the row with article nickname 8056061 (Oil Export$Document Mgt Printout Log), rowguidcol {6EF18D46-F00F-DE11-BA45-00C09F3D165D} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.This 78 conflicts is included for every replication thats taking place as per the schedule. Can someone adviceIf its criticalWhat needs to be done to get rid of this conflict.

Synchronization of tables

Steve2012 — Fri, 03 Feb 2012 03:44:03 GMT

Hello,Is there a script that can synchronize two tables on SQL Server 2000 ?The structure is equal, just the content of the target is different.The tables are in different databases.Thank you, in advance.Steve.

Distribution Agent does not run correctly in transactional replication

ico-601891 — Mon, 30 Jan 2012 09:06:52 GMT

Hi all,I have defined a transactional replication between two sql server 2000 databases. The publication only have one article. When snapshot is applied, for sincronizing, first all rows are deleted.The main replication agents schedules are: - Snapshot Agent --> Is disabled. I generate the snapshot manually when is neeeded. - Log Reader --> It is running continuosly. - Distribution Agent --> It is running every hour between 0:00:00 hours and 23:59:59.The snapshot and Log Reader are running correctly, but when Distribution agent begin to run (as i say every hour), it automatically delete all register in the target database table, and start a new bulk insert process (similar to the first sincronization process). I dont undertstand this comportament, because distribution agent only should have replicate the changes (and only the changes) succeded between hours.I have see in sql server and agent log but i can't see any error message. I have to say that the time needed for the first sincronization process is 2:30 hours.Somebody help me?Many thanks in advance.

Can the snapshot agent be paused?

Adam Durr — Thu, 26 Jan 2012 14:07:26 GMT

In SQL 2000 can you pause the snapshot agent and then resume it at another time?We would like to stop the agent since it is impacting the business and start it back up at night, but my concern is that it will continue where it left off? This is possible in SQL 2005, but does anyone know if this same behavior is found in SQL 2000?

enabling concurrent snapshot processing

Adam Durr — Thu, 26 Jan 2012 11:28:33 GMT

Hi,I am trying to fix a production issue where the snapshot has locked the articles being published. I wanted to unlock these tables and saw that I could make this setting change.My question is if I enable this setting will it cause all replicated objects to be re-init or just the ones that have been flagged for a re-init?Also enabling this, will it cause the snapshot to stop and if so will it resume from where it left off or start over?I know in SQL 2005+ snapshot generation can be paused, but I am not sure in SQL 2000.Thanks,Adam

Replication error

Roust_m — Fri, 12 Mar 2004 08:41:00 GMT

Hi!

I get this error message when subscribing or reinitializing subscriptions on one of my databases.

(I am running MS SQL 2000 Ent Server + sp3)

JUPITER-stock-STG_Data-545 Executed as user: DEALINE.CENTER\SQL_Admin. Cannot insert the value NULL into column 'publisher', table 'tempdb.dbo.MSreplication_agent_status'; column does not allow nulls. INSERT fails. [SQLSTATE 23000] (Error 515) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.; MsgID: 3621;

How can I correct this?

Thanks.

The merge process could not initialize the Pub...

iypatel — Tue, 24 Jun 2003 04:53:00 GMT

Context:Merge replication with central publisher and a single remote subscriberPull subscription with Continuous synchronizationProblem:The merge process could not initialize the Publisher '<Server>:<DB>'.Attempt/Finding:We have configured another replication with same configuration for testing with enabling merge agent to log information in output file with verbose level 2. After functioning properly for quite a some time, it again has produced the following problem:The merge process could not initialize the Publisher '<Server>:<DB>'.When we look into the log, the last step and actual error occured are:1. Step from Subscriber to Publisher: master..sp_MScheck_agent_instance N'Some Agent/Job Name', 42. Error occured during the step: Another merge agent for the subscription(s) is running.When look into the list of Job through Enterprise Manager Management/SQL Server Agent/Jobs, we have not found any name like 'Some Agent/Job Name'. However, when again through Enterprise Manager we look into the list of current processes running, it list multiple process for the 'Some Agent/Job Name' trying to running different TSQL Batch commands.It looks to us as kind of Orphan Agent/Job created due to some problem and caused to stop actual Merge Agent/Job. This is because when we kill all the processes of that Orphan Agent/Job and running merge agent on subsriber again, it starts functioning again but not for long and we keep experiencing the same problem again.We'll appreciate any help to fix this problem.Regards,IP

Log reader error

Kay1632 — Sat, 07 Jan 2012 16:37:49 GMT

SQL Server 2000 SP3. (We can't upgrade to SP4 due to JD Edward limitations)Cluster was failed over and later I am getting following error in Logreader.There is no rows returned from MSrepl_commands and 1 row returned from MSrepl_transactions. Results are copied below. We can't afford downtime or reinitializing replication. What is the best and easiest way to resolve this issue.Error message:The process could not execute 'sp_replcmds' on 'SRVSSQL'.Error details:The specified begin LSN (013a16d2:0000016b:0001) for replication log scan occurs before replbeginlsn (013a16d2:0000016b:0002).(Source: SRVSSQL (Data source); Error number: 18767)---------------------------------------------------------------------------------------------------------------Invalid begin LSN (013a16d2:0000016b:0001) for commit record (013a16d2:0000016b:0009). Check DBTABLE.(Source: SRVSSQL (Data source); Error number: 18762)---------------------------------------------------------------------------------------------------------------publisher_database_id xact_seqno type article_id originator_id command_id partial_command command --------------------- ---------------------------------- ----------- ----------- ------------- ----------- --------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ (0 row(s) affected)publisher_database_id xact_id xact_seqno entry_time --------------------- ---------------------------------- ---------------------------------- ------------------------------------------------------ 1 0x013A16D20000016A0001 0x013A16D20000016A0004 2012-01-07 16:21:34.580(1 row(s) affected)

Trying to setup Merge Replication

Patti Johnson — Mon, 26 Sep 2011 16:14:48 GMT

Hey Everyone,I am trying to setup Merge Replication on SQL Server 2008 R2. The idea is to have 2 identical databases on 2 separate instances on two separate servers. Each database on the instance/server can have updates made and all updates are replicated back to the other database on the other instance/server and vice versa. I know I have to create a distributor and publication on one instance/server. What I am not sure about is should I create another distributor and publication on the other server/instance? Then the next question is the subscriber. On the first server/instance do I have to create a subscriber that points to the database on the second server/instance? And on the second server/instance do I create a subscriber that points to the database on the first server/instance? I hope this makes sense to you all.Patti

Start SnapShot agent in T-SQL

Jules Bonnot — Tue, 11 Jul 2006 07:23:00 GMT

SQL2005

I need to automate the starting of the snapshot agent. At the moment i have to go into replication monitor right click on snapshot agent and click start.

Is this possible in T-SQL or do you have to use the DOS cmd. Any sample code would be nice,

Thanks,

Jules

New Schema Option for Replicating Identity Columns in Transactional Publications

swanand.bagve — Sun, 04 Dec 2011 07:18:03 GMT

Hi Guys,The Microsoft document for SP4 lists a)To specify that an identity column should be replicated as an identity column: b)When creating the table at the Publisher, specify the NOT FOR REPLICATION option for the identity column. This ensures that only user inserts, not replication agent inserts, increment the identity column. For more information, see "CREATE TABLE" in SQL Server Books Online.c)When adding an article with an identity column, set the option 0x4 for the @schema_option parameter of sp_addarticle. For more information about this parameter, see "sp_addarticle" in SQL Server Books Online.I have tried many times but unable to get this workingNo matter if i set @schema_option as 0x40 or 0x04 still i get an error stating failed to create an index as table does not exist..on checking the tablename.sch file in snapshot folder i see only the drop table command and not the create table again. though the article properties are set to drop the existing table and create it again...any one ever got this to work ? i know the 2000 server is outdated but business still not allowing upgrades..Regards,Swanand

is there an option to sync first snapshot without deleting data?

fcaglayan — Wed, 23 Nov 2011 07:32:13 GMT

I am testing to move DB to a new disk formation but while a replication on the DB is working, it doesn't allow me to do it.Because of that reason first I want to try a few test to do this on my computer instead of live DB.I thought I set the same settings as on Live DB but I get an error on the second day that it says "The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized."Then I try to do what it says but this time I get"Violation of PRIMARY KEY constraint 'PK_table'. Cannot insert duplicate key in object 'table'.As I understand that its reason is I have chosen at the snapshot "keep the existing table unchanged"Actually I don't want it to drop tables or delete all datas at the subscription because it may take too much time.If I am sure about both 2 DBs are sync, is there an option for snapshot without deleting data at subscriber?Or is there an easy method to move DB without removing replication?

Partition Usage in SQL Server 2008

smitha.bangalore — Wed, 23 Nov 2011 04:28:48 GMT

Hi,I have my table which is daily partitioned. When I execute the select query with [b]date [/b]as a parameter, for the case 1 (given below) the partition count is 1 where as the partition count is 50 (all the partitions) for the case 2.case 1. where DT='2011-08-22' case 2. where RPT_DT={d '2011-08-22'} If you have come across the scenario, pls help me with the reasons why case 2 fails to use the curresponding partition.Smitha

Is Replication causing my problem

Xavier Valdes — Thu, 10 Nov 2011 12:58:52 GMT

I have a simple web based application with SQL 2005 i have 3 environments development, testing and production, each with its own copy of the DB which is less than 2gb in size. Production and testing are on the same server both applications point to their own copy of the DB in a single SQL instance. Development is the same both appl and sql on their own box which is different than production and testing in fact it is a PCacting as a server, all are Windows 2003 + SQL 2005.in development everything works fine :)in test everything works fine :)in production some cases exhibit hanging.issue: at random on a particular form when i select a record it hangs, we have come to the conclusion it is a DB issue not applicationand here is why and how we came to this conclusion.I pointed production application to the test DB it works fine no flaws no hangups, I pointed the test application to the production DB it hangs on this form.I made a fresh copy of the application website and point to production db it hangs if i point to the copy of the production DB it works.You may ask what is the difference between test and production, well replication is the only difference.My replication is a transactional type where my production server pushes to a server in another facility where i have a copy of the application but only some basic forms and a search form are made available to users of that facility when needed.The only test i have not performed yet but will do on Monday is to turn off replication and test the production application problem with replication turned off to help identify if replication could be the cause or not.In looking over the whole DB I found the "msrepl_tran_version" is not present in all tables, but all tables are being replicated.Questions:"msrepl_tran_version" is a column added by SQL for verification of uniqueness in records, correct?Does this column need to exist in all tables that are replicated?if so in my case i have some tables that do not have this column.I have had to drop the replication for one reason or another, then i create a new snapshot and recreate the subscription.still some of these tables do not have the "msrepl_tran_version" column.Do i need to drop this column where it exists before i create a new snapshot for replication?if yes is there an easy way to drop the column on 225 tables in one shot?, do i need to?Could the absence of this column cause my problem?How can I force SQL to regenerate this column on all tables?Do i need to force SQL to regenerate the value in this field?I am maybe 90% sure my problem lies in the DB/replication, but other than testing the issue the way i didi have no way of being 100% sure it is not the DB/replication.Any thoughts / suggestions will be greatly appreciated.Thank youXavier

Job Suspended - WHY???

jlerman — Fri, 22 Nov 2002 17:44:00 GMT

Why would a push subscription job be suspended immediately after I create it?I just don't understand - boo hoo!Thanksjulie

Replication of n(I mean numerous) databases in SQL2000.

yes.fareed — Thu, 03 Nov 2011 09:32:54 GMT

Hello Experts,Can anyone explain me the process if it is possible for replication on n number of databases?My application creates at-least 50 databases per a working day. I would like to replicate the created database.All I need to know if it is really possible to replicate the database with numerous databases. If yes could you please post me the process?Thanks,Fred.

how to tie sys entries for replication publication and the job?

alapides — Tue, 01 Nov 2011 09:42:12 GMT

[b]In brief:[/b]I need to find my way through already existing hundreds of replication jobs.Hence I need a query to interrogate replication and job sys tables.[b]In detail:[/b]First, lets take a look from Management Studio (and I have 2005 studio looking into 2000 database)Replications are sitting under Replication node. There are also jobs to execute each replication and those jobs are sitting under SQL Server Agent-JobsAll is simple when there is one entry in replication and one entry in the job node.Just looking on the job entry one can step on job-Property, highlight Steps, navigate to appropriate step, hit Enter, navigate to Command and there in the command there will be a line that will specify subscriber, publisher, distributor and also a Publication.Something like this:Step command: -Publisher [BURNCOSQL2] -PublisherDB [BRP_OrderDispatch] -Publication [BRP_OrderDispatchInventory5401] -Subscriber [1401RETAIL] -SubscriberDB [BRP_OrderDispatch] -Distributor [BURNCOSQL2] -DistributorSecurityMode 1 -ContinuousI.e. from there one can see the name of the publication, in this case: BRP_ OrderDispatchInventory5401Then one can navigate to Replication-Local Publications and find it there.[b]The first minor Q by passing is: but how from Replication find the appropriate job in this fantastic GUI?[/b]I.e. one can find from job’s GUI the publication (although it is buried down inside the command) but I can’t unearth the job from Publication’s GUI.Continuing with my main issue. This is all easy-peasy when you have one or two jobs. When you have hundreds with one kilometer long cryptic names it is not. In this case data dictionary report is necessary.[b]Hence the Q: what is the query to tie jobs and replications?[/b]My best friend google didn’t give me much, only this:http://www.replicationanswers.com/General.aspHe recommends the query:select a.publication, b.name as CurrentJobName from distribution.dbo.MSdistribution_agents ainner join msdb.dbo.sysjobs bon a.job_id = b.job_id unionselect a.publication, b.name as CurrentJobName from distribution.dbo.MSlogreader_agents ainner join msdb.dbo.sysjobs bon a.job_id = b.job_id unionselect a.publication, b.name as CurrentJobName from distribution.dbo.MSsnapshot_agents ainner join msdb.dbo.sysjobs bon a.job_id = b.job_idI run the query and it didn’t return me all the replication entries for every job entry.I.e. there are more job entries that publications returned from this query.I looked into missing jobs and traced them from GUI as described before and for each of those there was a Publication and this publication was indeed present under Replication node. Meaning that the query is incomplete.I then looked into the dictionary and noticed that there are actually four agent tables in the distribution database, and he references only three, by some reason he didn’t include distribution.dbo.MSmerge_agents table.I modified the query to include 4 unions.I of course changed the alias to reasonable “j” instead of “b”. (“a” stays because I can identify it with “agents”)I also joined to distribution.dbo.MSpublications table to see publication_id, just in case.And I also include the flag to indicate on the end from which union does the record in the resultset arrives.select a.publication,p.publication_id,j.name as JobName,'DSTR' as type from distribution.dbo.MSdistribution_agents ainner join msdb.dbo.sysjobs jon a.job_id = j.job_idinner join distribution.dbo.MSpublications pon a.publication=p.publicationunionselect a.publication,p.publication_id,j.name as JobName,'Log' as typefrom distribution.dbo.MSlogreader_agents ainner join msdb.dbo.sysjobs jon a.job_id = j.job_id inner join distribution.dbo.MSpublications pon a.publication=p.publicationunionselect a.publication,p.publication_id,j.name as JobName,'SNP' as typefrom distribution.dbo.MSsnapshot_agents ainner join msdb.dbo.sysjobs jon a.job_id = j.job_id inner join distribution.dbo.MSpublications pon a.publication=p.publicationunionselect a.publication,p.publication_id,j.name as JobName,'MRG' as typefrom distribution.dbo.MSmerge_agents ainner join msdb.dbo.sysjobs jon a.job_id = j.job_id inner join distribution.dbo.MSpublications pon a.publication=p.publicationorder by 3 This returned me more records than the original query. I.e. another union to MSmerge_agents brought me some records indeed.However still not all publications are returned! There are still missing ones.[b]And this is my main specific Q: what else should I join to to receive the full list of publications?[/b]I noticed there is another table distribution.dbo.MSqreader_agents and I tried to join to it, but it didn’t help. Any ideas?

Logreader error: The process could not execute 'sp_replcmds' on 'PRODSERVER'.

Kay1632 — Mon, 24 Oct 2011 07:50:28 GMT

Environment:SQL Server 2000 SP3Transaction replicationOur cluster failed over yesterday and now replication is failed. Following is the error. I already have recommended to go for SQL SP 4 but it will take a while to implement. Please advice. The specified begin LSN (0115f66a:00000095:0001) for replication log scan occurs before replbeginlsn (0115f66a:00000095:0002).(Source: PARSSQL (Data source); Error number: 18767)---------------------------------------------------------------------------------------------------------------Invalid begin LSN (0115f66a:00000095:0001) for commit record (0115f66a:00000095:000b). Check DBTABLE.(Source: PARSSQL (Data source); Error number: 18762)---------------------------------------------------------------------------------------------------------------

Cleaning up Replication Monitor

Chris Wasser — Mon, 12 Jul 2004 15:00:00 GMT

I have sucessfully removed an old publication and it subscriptions, but I am still seeing references to it in the Replication Monitor under Publishers, Snapshot Agents, and Distribution Agents. When I try to delete the listing under Publishers I get an error message that states the following:Error 21776:[SQL-DMO] The name 'Publication1' was not found in the TransPublications collection. If the name is a qualified name, use{} to separate various parts of the name and try again.When I try to look at the properties of the agents I get the following error message:Error 14262: The specified @job_id('xxxyyyzzz') does not exist.So it appears that the old publication and its components are in fact gone (or at least to SQL Server they cannot be found), but these error messages are still there and I cannot remove them. While this is not affecting any functionality, it "looks" like something is wrong when viewed through SQL-EM.I am using SQL_EM to manage these replication services, but can run procs through Query Analyzer if that will help.Any help or direction would be greatly appreciated.-Chris

The subscription(s) have been marked inactive.....

jmittmann — Tue, 11 Jan 2005 05:03:00 GMT

Hallo,

from time to time my transactional (push) replications on SQL2000 SP3a servers send me the following error message:

Error Message : The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.Error Detail : The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: Server Name (Data source); Error number: 21074)

My subscriptions have been very active for at least 2 years and in the properties "Subscriptions never expire" is marked.

I know that I can restart replication by setting the status column in distribution..MSsubscriptions to 2 but can anyone tell me why this problem occurs and how I can prevent it in future?

Thank you for your help

Login failed for user '(null)'

kai-753082 — Sat, 01 Oct 2011 05:42:43 GMT

Hi,I have had successfully set up a couple of transactional replications on our server to publish to a webhost(both SQL 2000). Recently the webserver had a disk array issue and now was replaced by a new machine. The same configuration as the current server was set up (Server 2003 R2 / SQL 2K) and I was able to connect to the new instance through Enterprise manager. Mixed authentication mode is set up as was on the old server, I can log in as sa and set up an admin user account for myself. this is what I did back when I set up the replication on the old server:* added the new server registration as new subscriber* added a new subscription on the publisher to publish to the new subscriber.* after generating a new snapshot the distribution agent stopped with error message Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.I can recall I had a similar issue when I first did this 5 years ago, but I have no recollection how I resolved it at the time. The new server is registered with a DNS name, the new one is referred by IP address, I can recall that there was an issue with this back when I first did this, but don't recall if this was the problem back thenI tried both - register as user 'sa' with SQL authentication and with windows authentication - error message is the same. What am I missing here?ThanksKai

MS_biXXX_v_YYY system views deleted

Jeff Gray — Mon, 19 Sep 2011 11:06:51 GMT

An interesting thing happened the other day. I was trying to add a new anonymous merge publication, and when I ran the snapshot agent, I got an "Invalid column" error 207. The error is as described in MSKB 821535. The server does have SP4 applied and in fact has been patched to build 2187, so the KB article is either incorrect, or the fix contained in SP4 didn't completely fix the problem. After removing the new publication, hundreds of merge subscribers started failing. Reinitializing the subscriptions did not fix the problem. It turns out that the removal of the new publication caused two of the system views (the ones starting with MS_bi) to disappear. I had to manually create the views and now the merge subscribers are working correctly.What's puzzling is that none of this happened in the dev, test, and UAT environments that were created from a recent restore of the production database.I'm concerned that there's a time bomb waiting to go off, but it has been smooth sailing now for over a month.The fact that I can't reproduce this in a test environment seems to indicate that the problem gets corrected when replication is reset after the database restore.I need to get the new publication installed in production, just wondering if anyone has seen this problem before.

Replication - Article removal...

Benjamin Shelton — Wed, 11 Feb 2009 22:48:08 GMT

All,SQL Server 2000 SP4, Transactional ReplicationI am trying to remove an article from replication so that the schema can be modified and then I will re-insert the article back into replication. I have gone through this process many times in the past with no issues at all. However, this time, when I try to remove the tables from replication, I get the following error message for each table:[b]Msg 14046, Level 16, State 1, Procedure sp_droparticle....Could not drop article. A subscription exists on it.[/b]Here is the procedure that I use:[b]sp_dropsubscription ... sp_droparticle ... (error is after this one)modify object/articlesp_addarticle ...sp_addsubscription ...[/b]My frustration is that I have tried everything in my power to see what other subscription is hanging on to this article to keep me from removing it from replication. Any and all help on this would be great!!!Thanks again,Ben

ORA-01005: null password given; logon denied The process could not connect to Subscriber ''mars''. The step failed.

CooLDBA — Sun, 22 Jul 2007 11:02:00 GMT

Distribution Agents failed - The process could not connect to Subscriber 'mars'.ORA-01005: null password given; logon denied(Source: mars (Data source); Error number: 1005)--------------------------------------------------------------------------------------------------------------- Driver's SQLSetConnectAttr failed(Source: ODBC Driver Manager (ODBC); Error number: IM006)--------------------------------------------------------------------------------------------------------------- Driver's SQLSetConnectAttr failed(Source: ODBC Driver Manager (ODBC); Error number: IM006)---------------------------------------------------------------------------------------------------------------This is a transactional replicationI tested the ORACLE ODBC connection - connection successfulI'm just wondering where do you provide the password.

error 20598

Sammoayedi — Wed, 19 Mar 2003 14:48:00 GMT

I am troubleshooting replication issue which after few minutes we get error 20598I look at sp_MSupd_tblname procedure, which saysif @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598Our value 1s 134218488Can somebody tell me what is the meaning? of @@microsoftversion>0x07320000 is ?What is it verifying

Renaming destination table name and destination column name at subscriber for transactional replication

jlp3630 — Mon, 08 Aug 2011 10:55:16 GMT

I have a publisher database running on SQL Server 2000 to be published to a SQL Server 2008 database via transactional replication. Is it possible to do the following:1. Rename the table on the subscriber database (i.e., SQL 2000's Table A will appear as Table B in SQL 2008).2. Rename a table column on the subscriber database (i.e., SQL 2000's Table A, Column A1 will appear as Table A, Column B1 in SQL 2008.)3. Rename a table and corresponding column on the subscriber database (i.e., SQL 2000's Table A, Column A1 will appear as Table B, Column B1 in SQL 2008.)