InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 21 Nov 2009 03:04:47 GMT20http://www.sqlservercentral.com/Forums/Topic822766-291-1.aspx[font="Tahoma"]Hi All,While I find no specific link or documentation that specifically debars anyone from implementing replication between two databases in the same instance, I have been getting recurrent errors in this experiment.Currently we have a need to replicate a single publication from a publishing (source) database to 2 or more subscribing databases in the same instance. While there certainly are other methods to use to achieve the purpose, my client is averse to using triggers for such a need. Besides, if replication is successful (transactional and (luckily) unidirectional), the SQL Server would take care of the process without any extra effort. Am I missing anything? Does any other kind of information provided be helpful? The error code displayed is the general 15517! (relating mostly to permissions and the like, but I am sure that there are no problems on this end with that...Any help or pointers to look for/research would be greatly appreciated.Thanks in advance,Venky[/font]Fri, 20 Nov 2009 15:55:36 GMTVenky Subramaniamhttp://www.sqlservercentral.com/Forums/Topic821964-291-1.aspxI recently upgraded our only system that performs replication and we had our first replication errors and I have come across a problem to which I cannot find a solution.In SQL Server 2000, whenever an error occurred, I could get the subscription details on the offending article and I could see not only the transaction sequence number and command number but also the actual command text that was being presented for replication.In 2005, I still see the transaction sequence number and command number but I cannot find the command text. I have got to find a way to find this because I need to be able to pull some of the identifying information about the particular data being replicated so I can figure out the proper course of action.Sometimes, due to the way we have our replication setup, we can correct the situation by simply performing an additional update inside the application which allows the replication that is failing to succeed on it's next retry then everything works fine. Sometimes, I am able to identify it as a real error and I can simply skip, or delete, the offending transaction from trying to continue.I have checked all of the distribution tables and the replication tables in both the publisher and subscriber databases and I cannot find the data. I'm beginning to think it is encrypted, for security reasons, so that we can't get to the data directly.If anybody knows of a way to find this, I would be extremely grateful.Thanks,hawgThu, 19 Nov 2009 14:20:05 GMThawghttp://www.sqlservercentral.com/Forums/Topic821287-291-1.aspxHello room,Can SQL 2000 with Simple recovery model participant in Backup and Recovery of Snapshot transaction replication?Thanks,TJWed, 18 Nov 2009 16:41:36 GMTEdwin-376531http://www.sqlservercentral.com/Forums/Topic821088-291-1.aspxWe have been seeing this problem off and on with one of our servers.Our replication infrastructure is rather involved.We have 16 merge replication subscribers (SQL 2000 - SP4) with one of those subscribers also a subscriber to many transactional replications from a combination of SQL 2000 and SQL 2005 servers.I did some CPU utilization investigations a few months back and noticed many of our newest (SQL 2005) transactional publications (at the subscriber) were using more CPU cycles then our most busy merge replication subscriptions.We ran some tracing and discovered something rather odd. The statements coming from SQL 2005 were using nvarchar (N'XX') characters which resulted in the SQL 2000 subscriber not using either the clustered or non-clustered indexes.After rebuilding the publication, our CPU issues seemed to be much better, but our update/insert/delete options on the sp_addarticle calls were using stored procedures instead of the native SQL setting.We again rebuilt our SQL 2005 publications using the settings: @ins_cmd='SQL', @upd_cmd='SQL', @del_cmd='SQL' and now our CPU usage is again 26 times higher then our most busy merge job.From this, I concluded that the use of the stored procedures for the @ins_cmd, @upd_cmd, @del_cmd must have caused implicit conversions from nvarchar to varchar which allowed the statements to use both the clustered and non-clustered indexes.I plan on rebuilding one of these publications later tonight using the stored procedure update rather then the native SQL.From the collective knowledge on this site, is this a reasonable plan or did I miss something.Thanks!Wed, 18 Nov 2009 12:22:25 GMTDoug-953304http://www.sqlservercentral.com/Forums/Topic821497-291-1.aspxWhat is the easiest and quickest way to migrate a publisher instance to new hardware?I am planning to:1) Install SQL Server 2005 on the new hardware2) Backup all the database on the older server3) Shutdown old server4) Restore all the user databases to the new server5) Restore the msdb and master database to the new server6) Rename the new server as old serverIs this OK? Is there any other better alternative? Will transactional replication continue without reinitialization?Old server is 32bit and the new server is 64bit.ThanksThu, 19 Nov 2009 04:35:05 GMTSuresh B.http://www.sqlservercentral.com/Forums/Topic820347-291-1.aspxI have been running a SQL Cluster and I have failed over plenty of times. Today when I did so the passive node started yelling about proxy users not being able to authenticate ( I use domain accounts to run the replication agents).Not sure why this is happening, anyone have a clue. I would say roughly 50% of the replication jobs in replication monitor fail when I fail over.Tue, 17 Nov 2009 13:04:52 GMTSQL DBA 808http://www.sqlservercentral.com/Forums/Topic821049-291-1.aspxI have 3 Transactional Replication publications configured on Server A, we have configured 3 publications for administrative reasons dividing articles into 3 publications.We have subscriber B that is the subscriber for Server A. we have combined 3 Tran. Repl. jobs into 1 so that only 1 job updates the Subscriber.Server A to Server B using 3 publications with 1 job (Log Reader)Sometimes we are seeing that the from Publication B - T2 transaction is reaching before Transaction T1 from Publication A, even when we send T1 then T2.How can we address this ?Please help gurusWed, 18 Nov 2009 11:39:21 GMTDBA@SQLhttp://www.sqlservercentral.com/Forums/Topic816705-291-1.aspxRight firstly our Log Agent fell over because it ran out of locks (What the hell?) during a process of 9,000,000 individual delete commands (poor data transfer software we use).So I upped the number of locks to 50,000 and the log reader stayed up and collected the 9,000,000 commands.However, it now has 5,000,000 inserts to implement and it falls over again based on lack of lock resource. So the Log Reader is not running, but the distribution agent should still be able to push through the previous 9,000,000 right? You don't need the log reader to be running to distribute commands already in the dist db?So the dist fell over due to the record it trying to delete not being present (long story but this is correct, so I turned on the Skip Errors). So now it is distributing 9,000,000 deletes and ALL of them will have to be skip errored. This has now been running for 6 hours so far and the command count has not gone up. If I look in the various tables and sps it would appear that it has yet to replicate 1 transaction. BUT on sp_who2 it seems it recognises that it is running.Is it just not updating the stats because it has so much to do? Or is it not doing anything because the log reader is not running and it needs it for reference? (surely not?)_ThanksTue, 10 Nov 2009 11:33:52 GMTShark Energyhttp://www.sqlservercentral.com/Forums/Topic742248-291-1.aspxI'd like to setup transactional replication of a large database and use the initialize with backup option on the subscriber so that I can use a restore to build the subscriber instead of a snapshot. The restore takes a while, so what I'd really like is to do is create the publication, restore a backup of the publisher to the subscriber but use the no recovery option to keep it pending, do a log backup of the publisher, create the subscriber via a sp_addsubscription using the sync_type initialize with backup and then apply the last publisher log to the subscriber with recovery. Does this work with non native SQL Server backup software or do I need to use SQL Server backups and restores as the synchronization process seems to look at LSN in the backup file (according to BOL if I'm reading it correctly). Thanks,JenThu, 25 Jun 2009 15:31:59 GMTJenHhttp://www.sqlservercentral.com/Forums/Topic812662-291-1.aspxI recently added a column to an article in transaction replication. I first stopped replication from the subscription from View Synchronization Status, then added the new column in both databases and updated the article. When I attempted to restart replication from the same place, I received a message about needing to re-initialize the snapshot, and I clicked OK without marking the checkbox to create a new snapshot. A little later I noticed that replication wasn't happening, so I repeated the process and did mark the checkbox. That worked, and the replication is working fine. Today I wanted to stop replication for another reason and went to View Synchronization Status again. The Stop button is greyed out, and when I try Start, it gives me an error that it is already running. My questions are:1. How can I get View Synchronization Status back to normal?2. What other way is there to stop replication?Mon, 02 Nov 2009 17:11:38 GMTCarol Jeanhttp://www.sqlservercentral.com/Forums/Topic819166-291-1.aspxHi there everyone,I'm trying to setup transaction replication on 1 database and 1 table: Configured in the following way:1. \\servename\repldatasnapagent & logagent service account has full control to this folder2. Snapshot agent & logagent account has db_owner to distribution database (i also temporarily gave them sysadmin to try and get this feature working)3. created a trans publication and i'm getting the following error message. I can create snapshots OK, but for some strange reason something strange is going on.Error messages:The process could not execute 'sp_repldone/sp_replcounters' on 'MYCHANGESERVER'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)Get help: http://help/MSSQL_REPL20011The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {00000a93:00001634:0003}. (Source: MSSQLServer, Error number: 18768)Get help: http://help/18768The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)Get help: http://help/MSSQL_REPL22017The process could not execute 'sp_repldone/sp_replcounters' on 'MYCHANGESERVER'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)Any advice or help would be much appreciated.cheersSun, 15 Nov 2009 15:42:03 GMTFree-292871http://www.sqlservercentral.com/Forums/Topic817565-291-1.aspxSQL 2005 SP2-Database Replication failed last week after working great for 6 months.-Recreated Database Replication(couple of different ways) Same errors-Snapshot agent fails - "Executed as user: XXXXX\sql_admin. The system cannot find the path specified. [SQLSTATE 42000] (Error 14151). The step failed.-Log Reader Agent fails- The last step to run was step 3 (Detect nonlogged agent shutdown.).-Checked permissions for \Repldata folder - Sysadmin or domain admin(both Administrators) - same issue, -Tired creating a share\Repldata folder - same issue. -Checked folder permissions for Snapshot.exe-same issue-Tried to manually start Snapshot.exe Fails(see below)D:\Programs\Microsoft SQL Server\90\COM>snapshot.exeSystem.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Replication.StringResources' threw an exception. ---> System.ApplicationException: The native replication resource dll failed to load.at Microsoft.SqlServer.Replication.NativeResourceStringLoader..ctor()at Microsoft.SqlServer.Replication.StringResources..cctor()--- End of inner exception stack trace ---at Microsoft.SqlServer.Replication.ReplMessage..ctor(Exception e)at Microsoft.SqlServer.Replication.HistoryMessage..ctor(Exception e)at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.SnapshotGenerationAgentMessageFactory.CreateAgentMessage(Exception e)at Microsoft.SqlServer.Replication.AgentCore.LogMessage(Exception e)at Microsoft.SqlServer.Replication.AgentCore.ThreadFailedHandler(Exception e)The type initializer for 'Microsoft.SqlServer.Replication.StringResources' threwan exception.-Tried to Snapshot.exe -RegServer - same error as above-Checking out all forums for any type of resolution - priceless! But still failsAny help will be greatly appretiated! Wed, 11 Nov 2009 18:08:43 GMTDeba Monkeyhttp://www.sqlservercentral.com/Forums/Topic818696-291-1.aspxI noticed that when i drop a merge publication that not all system-views are deleted that contain the name of the publication. Specifically there are views left over for each article:MSmerge_<publication-name>_<table-name>_PARTITION_VIEWNote: i still have other publications on the same tables (i didn't drop all publications)I'm just wondering why the drop process didn't clean up these views.Also the the drop process doesn't clean up all references to the old publication in the replication triggersShould I be concerned about this? Impact on performance or storage, etc?Fri, 13 Nov 2009 10:24:18 GMTElmer Miller-184883http://www.sqlservercentral.com/Forums/Topic781261-291-1.aspxTITLE: New Publication Wizard------------------------------New Publication Wizard encountered one or more errors while retrieving publication names.For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1406.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantGetPubNames&LinkId=20476------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Invalid column name 'snapshot_jobid'.Invalid column name 'use_partition_groups'.Invalid column name 'replicate_ddl'.Invalid column name 'publication_number'.Invalid column name 'allow_subscriber_initiated_snapshot'.Invalid column name 'allow_web_synchronization'.Invalid column name 'web_synchronization_url'.Invalid column name 'allow_partition_realignment'.Invalid column name 'retention_period_unit'.Invalid column name 'decentralized_conflicts'.Invalid column name 'generation_leveling_threshold'.Invalid column name 'automatic_reinitialization_policy'.Invalid column name 'snapshot_jobid'.Invalid column name 'use_partition_groups'.Invalid column name 'replicate_ddl'.Invalid column name 'publication_number'.Invalid column name 'allow_subscriber_initiated_snapshot'.Invalid column name 'allow_web_synchronization'.Invalid column name 'web_synchronization_url'.Invalid column name 'allow_partition_realignment'.Invalid column name 'retention_period_unit'.Invalid column name 'decentralized_conflicts'.Invalid column name 'generation_leveling_threshold'.Invalid column name 'automatic_reinitialization_policy'. (Microsoft SQL Server, Error: 207)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1406&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476------------------------------BUTTONS:OK------------------------------ Please help me how to do with!Very Thanks!Wed, 02 Sep 2009 02:17:05 GMTliujptophttp://www.sqlservercentral.com/Forums/Topic816685-291-1.aspxI have a server acting as the publisher with 18 publications.Each publication has 8 subscribers. That's 144 jobs with just the distribution agent alone.I added a new publication and only two of the subscriber jobs will run, and the snapshot will not complete.We have had issues running many processes under the same service account, so our solution would be to add additional service accounts.We did that, and this time it did not work. Any ideas? Is there a hard limit in Windows 2003 that I might be running into as far as job stream?Tue, 10 Nov 2009 10:44:17 GMTSQL DBA 808http://www.sqlservercentral.com/Forums/Topic816392-291-1.aspxI have been attempting to create a snapshot of a database (in SQL Server 2005 in cluster).The snapshot appears to create ok, but when I view snapshot agent status I get the following error:Unable to start execution of step 2 (reason: Error authenticating proxy MYDOMAIN\REPUSER, system error: Logon failure: unknown user name or bad password.). The step failed.If we ran Snapshot Agent as the SQL Agent Account (the cluster service), then the snapshot created correctly. But when we ran Snapshot Agent with MYDOMAIN\REPUSER (this domain user is member of the db_owner fixed database role in the distribution database and have write permissions on the snapshot share) then the snapshot failed and we get the previous error.I have can see some posible solution to my problem. The last was that trusting SQL Server Agent account for delegation i could solve the problem (configuring in Active Directory and trusting it for Kerberos delegation). But i don't know how i can add delegation for SQL Server Agent services only (i only can see or add SQL Services but not SQL Server Agent services).Someone could help me? How i can configure Snapshot Agent account with a domain user? (i test it in a non cluster environment and there are not any problem. The problem are in a cluster environment. I can't find any document to do it.)Many thanks. Tue, 10 Nov 2009 03:56:56 GMTico-601891http://www.sqlservercentral.com/Forums/Topic815669-291-1.aspxHello,Working on transactional replication, pull replicationbetween two servers SQL1 and SQL2. SQL1 is the Distributor, and SQL2 is the Subscriber node. The Distribution agent always fails when running, with error message : The process could not read file 'C:\Ariel\ReplData\unc\SQL1_REPMEIR_REPPUBMEIR\20091106111251\Mytable_2.pre' due to OS error 3.ErrorId = 4005, SourceTypeId = 1ErrorCode = ''I have defined the snapshot location as a share.Somebody could help me ?MeirMon, 09 Nov 2009 02:05:41 GMTmeirbenhttp://www.sqlservercentral.com/Forums/Topic813865-291-1.aspxI am setting replication between a SQL 2005 server as a publisher and Oracle 11g as a subscriber.Connectivity to the Oracle server works fine.When the subscriber is initialized, table MSREPL7 is created in the Oracle Server.I get the following error:ORA-00932: inconsistent datatypes: expected NUMBER got BINARY (Source: MSSQL_REPL_ORACLE, Error number: 932).My guess is that the problem is when SQL tries to insert data in the MSREPL7 table.By reading some posts in Microsoft, they suggest to execute the following stored procedures:sp_MSrepl_DropDatatypeMappings and sp_MSrepl_CreateDatatypeMappings.I did that, but it didn't work.Other posts state that SQL 2005 doesn;t work with Oracle 11g.Is there somebody out there with the same problem, and who knows what to do to fix?Thanks all for your help.Wed, 04 Nov 2009 14:13:18 GMTaron.feldmanhttp://www.sqlservercentral.com/Forums/Topic815596-291-1.aspxSo one of the clients has a pretty high concurrency environment, around 450 accesses to the web application that access the DB for pretty much everything, and 4-5 users using an application that do the heavy lifting duties importing data. All in all it does well, but its only a matter of time before things begin to derail and we want to split the load on the server in a way that would also serve as a high availability plan.My first thought on this was to implement peer-to-peer transactional replication on the main tables that both sides of the system useThe importing app imports data to "intermediary" tables, does that checks, transformations and information gathering that it has to do there then sends the data to the "primary" tables that are then used from the web app, so the plan is to only replicate those primary tables to avoid over-stressing the server during data imports.My concern right now is to the relational information.The client has a windows server 2003 with sql server 2005 enterprise, he said he doesnt opose to migrating to 2008 if it helps accomplishing the goal.I would like to hear from you good people, if anyone have any tips or advices, or if ever done anything like this and if it worked out.Any help is appreciatedThiago DantasSun, 08 Nov 2009 12:56:51 GMTdant12http://www.sqlservercentral.com/Forums/Topic806909-291-1.aspxHello The following is the error i am encoutering.[i]2009-10-12 22:45:19.579 Agent message code 20037. The process could not bulk copyinto table '"dbo"."MY_TABLE"'.2009-10-12 22:45:19.683 Category:NULLSource: Microsoft SQL Server Native Client 10.0Number:Message: Batch send failed2009-10-12 22:45:19.716 Category:NULLSource: Microsoft SQL Server Native Client 10.0Number: 2627Message: Violation of PRIMARY KEY constraint 'MYTABLE_PKEY'. Cannot insert duplicate keyin object 'dbo.MY_TABLE'.2009-10-12 22:45:19.741 Category:NULLSource: Microsoft SQL Server Native Client 10.0Number: 20253Message: To obtain an error file with details on the errors encountered when initializingthe subscribing table, execute the bcp command that appears below. Consult the BOL formore information on the bcp utility and its supported options.2009-10-12 22:45:19.766 Category:NULLSource:Number: 20253Message: bcp "LDTST"."dbo"."MY_TABLE" in"\\025edddb1\SQLREP\unc\SRCSERVER_LDTST_PUB_LDTST\20091012145537\MY_TABLE#1.bcp"-e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SDESTSRVR -T -w[/i]I referred the following URL / website and introduced the [b]-SkipErrors[/b] option. Does not seem to be helping.Skipping Errors in Transactional Replicationhttp://msdn.microsoft.com/en-us/library/ms151331(SQL.90).aspxThe -SkipErrors Parameter[u]Also, when i created the Articles i chose the "keep existing object unchanged" option.[/u]Any help would be appreciated.ThanksWed, 21 Oct 2009 18:41:35 GMTgk-411903http://www.sqlservercentral.com/Forums/Topic815710-291-1.aspxI'm about to setup transactional replication but some of the tables require filtering but filtered on a joined table. I've tried this and it seemed to work (by using a subquery only) and when i created the initial snapshot it worked fine and replicated the filtered rows i expected. Now, i've been reading issues regarding merge replication and join filters not being re-evaluated - is this the same for transactional replication??If so, is there any way i can set up a filter in this way??thanks in advance :-)Mon, 09 Nov 2009 03:55:11 GMTSwirl80http://www.sqlservercentral.com/Forums/Topic815527-291-1.aspxggSat, 07 Nov 2009 23:40:50 GMTsatishthotahttp://www.sqlservercentral.com/Forums/Topic814145-291-1.aspxHi EveryoneWhen i try to republish my articles sql server throws following error message.The republisher's republishing range obtained from its publisher is not large enough to allocate the specified @pub_identity_range.Does this comeup when when identity column is replicated?Does anyone have any idea?Any help would be much appreciated.Many thanks.VivekThu, 05 Nov 2009 05:21:45 GMTVivek Shuklahttp://www.sqlservercentral.com/Forums/Topic814577-291-1.aspxCan anyone offer some information about performance issues, latency...etc when using transactional replication? I realize there are many factors which affect performance and latency, but in general, what would one expect to see on the primary database by enabling transactional replication? I understand the concepts and requirements for replication however, just wanted to see if anyone had some real-world experience they could share..Our goal is to setup a secondary server to use for real time reporting.Thu, 05 Nov 2009 14:32:23 GMTCode2Solutionshttp://www.sqlservercentral.com/Forums/Topic814001-291-1.aspxI have a need to pause replication on my published database occasionally. I do not want to drop or remove transactional replication, I only want to [b]pause[/b] it momentarily and then [b]restart[/b] it with minimum number of issues. What is the easiest way of doing this? Stopping/disabling and then restarting/enabling just the distribution jobs on the publisher? Am I missing anything else that needs to be done? I have read other posts concerning this topic, but they seem vague or incomplete..Wed, 04 Nov 2009 22:24:09 GMTtalltop-969015http://www.sqlservercentral.com/Forums/Topic815113-291-1.aspxSuppose a remote distributor is used to create a snapshot on a different server. Rather than use the default local folder on the remote distributor, the snapshot is required to be placed on the original source server. The UNC of the source server is used as the location of the generated snapshot.Box 1 -> Source DBBox 2 -> Remote distributorIs the data from the source DB copied from box 1 to the distributor (box 2), and then copied back to the source DB? Or is the snapshot agent clever enough to realize the source and destination of the snapshot is on the same machine, and can therefore be done locally?Fri, 06 Nov 2009 11:35:43 GMTrsbaxter17http://www.sqlservercentral.com/Forums/Topic815089-291-1.aspxI'm currently leading a huge data migration with other departments.The idea is to design a process that would automate daily extraction of data from the remote/offsite DB2 database on a mainframe to our central SQL server 2000 database.I'm very familiar with the SQL Server Data Tranfer Services but never worked with a mainframe or DB2.I'd really appreciate ideas or input from some of you who have carried out similar initiatives.ZFri, 06 Nov 2009 10:59:58 GMTjayzeddyhttp://www.sqlservercentral.com/Forums/Topic814549-291-1.aspxI've got two servers that use transactional replication to send data to a reporting server. I've been thinking if either publisher crashed that I could point my applications to the subscriber until the publisher was rebuilt or brought back online. It's been brought to my attention that its not quite that simple. Does anybody know of any documentation on how you can turn a subscriber into a writable copy? Thu, 05 Nov 2009 13:49:16 GMTjason.lewis-831465http://www.sqlservercentral.com/Forums/Topic815003-291-1.aspxIn SQL Server 2000 I used to query the sysmergesubscription table in order to determine (within a t-sql script) if the database was a subscriber in a merge replication. The query I use was basically:IF OBJECT_ID('sysmergesubscriptions') IS NOT NULL SELECT @IsSubscriber = CASE COUNT(*) WHEN 0 THEN 0 ELSE 1 END FROM sysmergesubscriptions WHERE subid <> pubid AND status IN (0,1) AND subscriber_type IN (2,3)I have run into a problem in SQL Server 2005 in that the publisher now includes the subscriber information in its sysmergesubscription which SQL 2000 did not. So now when I run the query on the publisher it indicates it is a subscriber.Is there an easy way to determine through tsql whether a DB is a merger replication publisher, subscriber or not replicating?Thanks.Mark Fri, 06 Nov 2009 09:23:22 GMTMark Johnson-176476http://www.sqlservercentral.com/Forums/Topic814641-291-1.aspxHello! I have removed replication on the server but log space is not being reclaimed because some of the transactions are still marked for replication:select log_reuse_wait_desc,* from sys.databases --shows REPLICATIONFollowing command doesn't work because database is no longer marked for replicationEXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1Database is in SIMPLE recovery mode. Any ideas how I can remove transactions marked for replication from the log?Thanks,IgorThu, 05 Nov 2009 18:37:39 GMTIgor Marchenkohttp://www.sqlservercentral.com/Forums/Topic814609-291-1.aspxCan somebody guide me through the steps to replicate from SQL 2005 to Oracle 11g?Thanks.Thu, 05 Nov 2009 16:09:38 GMTaron.feldmanhttp://www.sqlservercentral.com/Forums/Topic814089-291-1.aspxI have setup snapshot replication form a sql 2005 to sql 2000 box. This has been running for over a year!We had a few issues with timings of various overnight batch jobs being completed before the snapshot is taken, so to ensure that all rows are copied I created a validate rows proc that counts the rows in all tables on the publisher database then counts rows after the snapshot has been applied on the subscriber - does a match count and emails misssing rows in the subscriber db..Today we have 200,000 rows less in the subscriber 'payments' table and I cannot fathom why..I have created and run the below queries which validate that the rows were created into the 32 BCP files during the snapshot, and another query that tells me that all these rows were applied to the subscriber...There are no errors anywhere - but i am missing 200,000 rows... in a very important table!any ideas..??select * from dbo.MSsnapshot_history where start_time >getdate()-1 and comments like '%Bulk copied snapshot data for article ''PAYMENTS%'order by time descandselect * from dbo.MSdistribution_history where start_time >getdate()-1 and comments like '%Bulk copied data into table ''PAYMENTS''%'order by time desc[b]snapshot history Distribution history[/b]84980 8498085570 85570105168 105168105881 105881108869 108869111851 111851116233 116233122843 122843130299 130299143746 143746154634 154634159505 159505161440 161440164830 164830165513 165513166162 166162170606 170606180374 180374193249 193249223372 223372225147 225147231200 231200237087 237087237593 237593238589 238589245009 245009252761 252761282955 282955284929 284929287993 287993312184 312184356008 356008[b]6046580 6046580[/b]Thu, 05 Nov 2009 02:56:48 GMToraculumhttp://www.sqlservercentral.com/Forums/Topic811043-291-1.aspxWhen going through the replication setup wizard, I can choose Generate script at the end. The script works fine for the Publication setup, however, when I run the generated script for Subscription setup, I run into issues and the replication doesn't work. If however, I don't generate the script, but simply have the wizard start the replication itself, everything works. I would really like to automate the subscription setup with a script.The generated subscription script is:[code="sql"]-----------------BEGIN: Script to be run at Publisher 'SERVER\INSTANCE'-----------------use [PublisherDatabase]exec sp_addsubscription @publication = N'PublicationName', @subscriber = N'server\instance', @destination_db = N'SubscriberDatabase', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0exec sp_addpushsubscription_agent @publication = N'PublicationName', @subscriber = N'server\instance', @subscriber_db = N'SubscriberDatabase', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20091028, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'GO-----------------END: Script to be run at Publisher 'FLANDERS\TESTING'-----------------[/code]When I run the above script, it completes without errors. However, the subscription never shows up in the Sql Server Management Studio Replication->Local Subscriptions tree, and the data is never replicated.Oddly however, running the query:[code="sql"]use distributionselect * from MSSubscriptions[/code]gives the result set:[code="sql"]publisher_database_id publisher_id publisher_db publication_id article_id subscriber_id subscriber_db subscription_type sync_type status subscription_seqno snapshot_seqno_flag independent_agent subscription_time loopback_detection agent_id update_mode publisher_seqno ss_cplt_seqno--------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- -------------- ----------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------- --------- ------ ---------------------------------- ------------------- ----------------- ----------------------- ------------------ ----------- ----------- ---------------------------------- ----------------------------------3 0 PublisherDatabase 3 1 -1 virtual 0 1 1 0x00000027000001230003 0 1 2009-10-29 10:41:37.540 1 10 0 0x00000027000001230003 0x000000270000012300033 0 PublisherDatabase 3 1 -2 virtual 0 1 2 0x00000027000001230003 0 1 2009-10-29 10:41:37.603 1 11 0 0x00000027000001230009 0x000000270000012300093 0 PublisherDatabase 3 1 0 SubscriberDatabase 0 1 1 0x000000270000013B0008 0 1 2009-10-29 10:54:58.140 1 12 0 0x000000270000013B0008 0x000000270000013B0008[/code]Which would seem to indicate the the subscription is present.In case it matters, this is a push only publication/subscription, and both the publisher and subscriber databases are on the same server.What else do I need to do to get subscriptions working from a sql script?Thu, 29 Oct 2009 11:27:29 GMTNathan Davis-411378http://www.sqlservercentral.com/Forums/Topic813797-291-1.aspxHi allI received a 20598 error (The row was not found at the Subscriber when applying the replicated command) during transactional replication. Per replication help article at http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=09.00.0758.00&EvtSrc=MSSQLServer&EvtID=20598I set the distribution agent to use -SkipErrors 20598 and the replication caught back upNow I would like to: A: remove this parameter as it does not seem like a good thing to have in place because of data inconsistencies. And B: find the offending data. Does anyone know how I can find the data? The help article suggests using tablediff, however I don't even know which table the row(s) was/were in. I suspect that this information found in the error is the key -- Command attempted:if @@trancount > 0 rollback tran(Transaction sequence number: 0x0059565300021DFE000500000000, Command ID: 1) -- but I can't seem to find how to use it to find what data was missing at my subscriber.Anyone have some guidence on how to find it?Thanks in advanceWed, 04 Nov 2009 12:16:18 GMTsean_denneyhttp://www.sqlservercentral.com/Forums/Topic812302-291-1.aspxHi,I am trying to set a merge replication with "Filter rows" and "Join Filter".my problem is that two of the tables have circular references:First table: "Process" with PK: ProcessId FK: LastStepIdSecond Table: "Step" with PK: StepId FK: ProcessIdThe first table holds foreign key to the last step on the second table.When running the replication, the synchronization completes successfully but the records are not transfered.Mon, 02 Nov 2009 04:31:38 GMTdumbolinohttp://www.sqlservercentral.com/Forums/Topic735653-291-1.aspxDear all,I have configured peer to peer transactional replication with two servers (PRI, BCK)I did the following scenario in testing:1) I have Load Balance point to PRI server, Now when PRI and BCK servers are up, The synchronization is working fine from PRI to BCK 2) when BCK server is disconnected, then make it online, the synchronization return to work good3) when PRI server disconnected the load balance switch to BCK server and theres no problem with this 4) after step (3) when PRI server is up, then when i check the synchronization it give the following error:[b]Error executing a batch of commands. Retrying individual commandsthe row was not found at the subscriber when applying the replicated command.[/b] i have traced the error using [b]browsereplcommand [/b] and i have found there is a command trying to delete record according to ID but there is no records in both databases have this ID the table that give the error contain the following trigger in both databasesset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[SysServicesLog_insert] ON [dbo].[SysServicesLog] FOR INSERT NOT FOR REPLICATIONASDeclare @ServiceType varchar(30)Select @ServiceType = ServiceTypefrom insertedDELETE FROM SysServicesLog WHERE ServiceType=@ServiceType AND SysServicesLogID NOT IN (Select TOP 500 SysServicesLogID FROM SysServicesLog Where ServiceType=@ServiceType ORDER BY LogTimeStamp desc)I don't think there is problem with the trigger because its marked (Not For Replication) but maybe !!!pls need help for this...Thanks, Tue, 16 Jun 2009 07:34:03 GMTahmad.ghazihttp://www.sqlservercentral.com/Forums/Topic811410-291-1.aspxHi All,I have configured Transaction Replication using Updatable Subscription between two sql server 2005 servers. After all transactions are replicated to subscriber I got to see trigger in publisher for one of the table. Triger name begins with sp_mssyn_update_tablenameCan anyone please tell me if system triggers are being created in above scenario ?Fri, 30 Oct 2009 02:59:25 GMTsejal p gudhkahttp://www.sqlservercentral.com/Forums/Topic808646-291-1.aspxHi EveryoneI have been informed by DBA of an organisation from whom we have purchased our product that it is a bad idea to script publication in SQL 2005. When you script up the publication in SQL2005 it doesn’t include all the necessary settings and you will find replication does not work properly.Does anyone know if there is any problem with creating publication scripts in sql server 2005?Any help will be much appreciated.Thanks in advance.VivekMon, 26 Oct 2009 06:52:34 GMTVivek Shuklahttp://www.sqlservercentral.com/Forums/Topic801085-291-1.aspxWe have a transactional publication that fell behind and semed to be hung (could not bring down latency) We decided to recreate the whole publication and subscription. While creating the subscription, I specified – Initialize immediately, with new snapshot, generate snapshot now.The state I am currently in is that when I select the publication and select “view snapshot agent status” I see  “ [100%] A snapshot of 17 article(s) was generated.”But when I select the subscription and select “view synchronization status” I see “The concurrent snapshot for publication 'pubName' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the public”It seems as if the subscription is not talking to the publication. Have you seen this before? This also happens often when we try to reinitialize a subscription.Any help is greatly appreciated.Fri, 09 Oct 2009 14:19:14 GMTsean_denneyhttp://www.sqlservercentral.com/Forums/Topic809834-291-1.aspxWe have a client that currently has a large busy database that they back up using database mirroring. As part of a reporting requirement we need to use some of the data stored in the database to run reports against but we cannot access the data directly. The client also want their data to be as up to date as possible.Is it possible for us to run transaction replication on that server to get our data while it is running database mirroring at the same time? We were planning to use a SQL 2008 database to store this data on. If this is possible would there be any issue with the SQL 2008 subscriber taking data off a SQL 2005 publisher?Wed, 28 Oct 2009 06:06:30 GMTgrowla