I tried to drop a merge replication publication and it is giving me an invalid object name dbo.ms_mergeagents message. I tried to drop using:
SSMS
sp_dropmergepublication
sp_replicationdboption
I got the last two from Microsoft KB article 324401. The subscription was never created because it gave another error invalid object_name msmerge_subscriptions. I can create new publications but not drop the existing ones.
Please assist...
I am having a problem with merge replication after recreating a publication. It is a simple two-way replication between two servers allowing applications to update data at both ends, i.e. 1 publisher, 1 subscriber for all tables except some junk ones. The problem started after I did the following:
1) I dropped and recreated the publication to allow for some changes to the database schema. These caused problems so I dropped the subscription and publication and restored the databases at each end from backups taken before we started.
2) When I created the publication again, it wouldn't let me use the same name as it thought the publication already existed. It let me use a different name instead.
3) Most things work OK but one of the tables is not replicating inserts from the subscriber to the publisher. There are no errors and no conflicts, other similar tables replicate OK. Inserts go across the other way OK.
4) I am getting replication conflicts on another table that says 'Unable to synchronize the row because the row was updated by a different process outside of replication'
I believe the problem is to do with the original publication details still being in the restored databases, so am looking to drop the publication & subscription, remove the remnants of the old publication, fix the data and recreate pub & sub. What do I need to do to get rid of the old replication data in the database?
Any help much appreciated.
Hi,
I am stuck when trying to create a new publication. I fill all the wizard tabs but when finishing I get that message (6 times, one for every table):
Code Block
SQL Server Management Studio could not create article 'applicationModules'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
This article cannot use the 'subscriber_upload_options' feature because the publication compatibility level is less than 90. Use sp_changemergepublication to set the publication_compatibility_level of publication 'testMR' to '90RTM'.
Changed database context to 'test'.
Publication 'testMR' has the following property: 'compensate_for_errors'. SQL Server subscribers below version '8.00.0858' will ignore this setting. (Microsoft SQL Server, Error: 21522)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21522&LinkId=20476
The error seemed pretty clear, so I end up doing:
EXEC sp_changemergepublication
@publication = N'testMR',
@property = N'publication_compatibility_level',
@value = N'90RTM'
and running the wizard again.
But I got the same error.
The link on the message take me nowhere.
and by googling for error 21522 I couldn't find any help at all.
Please, could anyone help?
Cheers
Hello.I am trying to use Transact SQL to prepare publication for replication (SQL Server 2000 SP4 and SQL Mobile). I can easily create publication, add filters etc. but there is one thing which I can't find : parameter which tells me subscriber type and allow me to change subscriber type for publication. I don't know where I can change (or even if I can change) this property.
If someone made publication for mobile devices and replication with filters,resolvers etc. will it be a problem to share experiences here? Maybe some sample code with script or something what will guide me. Thanks in advance. Maciej Wysocki
I'm trying to setup merge replication and get the following error from the snapshot agent after I've finished the 'New Publication Wizard'. This doesn't make sense to me as it is an automatic process. Also, it looks like it is complaining about a 'dbo.MSmerge_cont...' view which is a view created automatically for the merge publication.
ERROR MESSAGE:
The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128.
FULL DETAILS:
Message: The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128.
Command Text: IF OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP VIEW [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>]
Parameters:
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteAdHocQueryDiscardResults(String strQuery, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.GenerateSystemTableBcpFiles()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPostArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 103)
Get help: http://help/103
Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128. (Source: MSSQLServer, Error number: 103)
Get help: http://help/103
Hi,
I disabled a merge publication on my server. I then ran sp_removedbreplication to clean the system tables. Thats fine. When I recreate a new publication on my server now, I still get the red x on my publisher node in my replcation monitor from the error I was getting before. Evenm though this publication is error free. Is it saving an srror flag in one of the sytem tabbles
Any ideas on this?
Thanks
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)?
Your help is greatly appreciated.
Gary
Help!!! This is really frustrating me down!
I was trying to follow the MS official tutorial "Creating a mobile application with SQL server mobile" could be found in this link
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
In the step of creating a merge publication, as I noticed, the snapshot agent is started sucessfully. But very soon later (few seconds), it was shut down with an error message. I checked in the error history, the following message I found:
"... The replication agent encountered a failure. The step failed"
I also went to msdb.dbo.sysjobsteps and found "Detected nonlogged agent shutdown".
Anyone could help?
Hello
I've got the following problem: Our product is delivered with SQL2000 and SQL2005.
Now, there are some schema changes, which I'd like to deploy with T-SQL on the publishers. With SQL2000 I do it with the sp_addmergecolumn etc and on SQL 2005 (if replication compatibility level is 90) with replication of DDL. So far so good.
But how can my T-SQL Script determine, wether the replication of DDL is on? I know there exists sp_helpmergepublication, but how do I get the column replicate_ddl of the result-set?
thanks for your advices.
Aline
Hi all,
I have a merge publisher with Snapshot agent for that scheduled to run everyday morning, This was runnning till yesterday. But it is not running today and giving the below error.
Can anyone help me out to solve this error? This is occuring in production environment, i really need a solution as fast as possible.
Error messages:
· Message: Failed to read column data
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strDataFile)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutWorkItem.DoWork(MergeSnapshotProvider snapshotProvider, IBcpOutProvider bcpOutProvider)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
· Message: Shared Memory Provider: No process is on the other end of the pipe.
Stack: (Source: MSSQLServer, Error number: 233)
Get help: http://help/233
· Message: Communication link failure
Stack: (Source: MSSQLServer, Error number: 233)
Get help: http://help/233
· Message: Shared Memory Provider: No process is on the other end of the pipe.
Stack: (Source: MSSQLServer, Error number: 233)
Get help: http://help/233
Message: Communication link failure
Stack: (Source: MSSQLServer, Error number: 233)
Thanks,
Thams
Has anyone had problems with this limitation before or could explain to me what the purpose of this limit is for? It obviously does allow you to publish more than 246 (we have been publishing more than 700 for years). Is this new with SQL 2005? Is my publication going to stop one day and tell me there are too many articles?
Thanks in advance.
Martin
Hello,
I am looking to preserve the ROWGUID column that is created automatically when a table is added to a publication as I have noticed that the column is removed and readded (with new values) each time the publication is deleted and recreated. We are planning on using the values in the rowguid column for a non-replication purpose, and don't wish the column to be removed or the values to be reset in the event the 'owning' publication is dropped for whatever reason. I've done some research and have identified the preserve_rowguidcol column of the sysmergearticles table as a candidate for achieving the desired goal. Before moving forward, I wanted to ask for input regarding this. Are there any best practices I've overlooked on this topic? Neither Google nor this forum's search results yield any real discussion on this topic.
Thanks!
DanD
Hi Mate !
I was searching to enable replication publication on my SQL 2005 Express, but as far as I look, this feature don't seem to be included.
Am I, correct?
Is there a way to enable this fearture?
Thanks
Zak
:mad:
I have one Publisher! (SQL 2005)
Two Subscribers. (SQL 2005)
Each subscriber requires different row filter from the published tables.
So, i need to create Two publications for each subscriber for the same db.
When i setup first publication, and apply filters to published tables.
All goes fine.
As soon i create an other publication with same published tables but DIFFERENT row filter; the log reader agent fails!
The process could not execute 'sp_replcmds' on 'DB1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Execution of filter stored procedure 2076702271 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
Get help: http://help/18764
The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {0008443a:000003b5:003e}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
:confused:
I have tried to re-initialze the subscriptions and re-run snapshots but no luck.
As i remove the row filter from the second publication; log reader starts working fine.
Previously i was using Merge replication and all was going BUT, on very large DML on publisher, we use to receive deadlock errors on application.
I decided to use Tran repl instead because log reader reads the changes from tran-log instead of locking the published tables; NOW I AM STUCK!
Ideas please!!!!
Hi,I am trying to use Enterprise Manager Wizard to create a newPublication database but I got the following message.Error 2812: Could not find stored procedureMyDB.dbo.sp_MSadd_logreader_agentThere seems that some replication stored procedures.Any help?Thanks in Advance
View 1 Replies View RelatedIs there an easy way to copy a replication publication definition from one server to another? In our environment we have a development environment, testing environment and soon a production environment. Right now keeping the replication definition between the development and testing environments is a manual process, are there tools that can do a diff on this or a way to keep this in sync?
For example when we change what information gets replicated in the development environment I need a way to propogate that change to the testing environment.
I know I can generate scripts from within SQL Server Management Studio, but it creates scripts very machine specific. So short of going through that and modifying the server names to match the different environments, is there a better way?
What's the relationship among replication, publication and subscription?
I want to create replication database, what things should I know first?
I am at my wits end here. For Replication the Books Online clearly state:
"The option to allow transformations is set at the time you create a publication"
However, I cannot find any options that allow me to do this in the Create Publication Wizard.
Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.
Where am I going wrong?
I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.
I need to configure a replication which will gather logs from the publication once in a day.
Is there any possibility?
I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?
I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.
Does it behave differently for small and large number of articles?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
I'm in a middle of a project where replication established between Oracle 8i and SQL server 2005. Now we need to drop one column from on of the tables replicated from Oracle side. My question : Is there anyway I can do this in Oracle and automatically reflected on SQL server? If not what is the proper steps to do it. Realy appreciate your fast response.
Regards
Hi seniors
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
regards
Ahmad Drshen
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedHi,
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
I am currently running sql 2000 with 2 way merge replication... I have a problem of I need to delete about 3 million rows from a table that is constantly being accessed buy an e-commerce site. I have thought about setting up a filter to only replicate new or altered rows beyond a a certain indexed number... then delete from both sites the unneeded rows... this should work.. but can I delete the filter after I am done??
If this is not an option, any other suggestions?? I cannot take either site offline and I cannot do anything that will take more than a few seconds to replicate... by deleting these rows. my table size will be reduced by almost 8GB...
thanks
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
How to Insert,Update and delete through script without using merge.I have simple requirement of Deleting,Inserting and Update from one table to other table...Here is the Sample Data
CREATE TABLE #Table1
(ID INT ,Name VARCHAR(30),DATEKEY INT)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (1,'Mohan',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (2,'Raj',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (3,'Majja',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (4,'Majjaa',20131231)
[code]...
So now i need to update 1st record and add another new record..So i need to update as well as delete the existing data in Target table.
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (5,'Macha',20131231)
My output should come like this one :
IDNameDATEKEY
1Mohan20131231
5Macha 20131231
with out using merge how can i handle Update,insert and Delete through TSQL
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,
[Code] ....
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
[Code] ....
Delete and merge duplicate records from joined tables? I am trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
Select a1z103acno AccountNumber, a1z103frnm FirstName, a1z103lanm LastName, a1z103ornm OrgName, a3z103adr1 AddressLine1, A3z103city City, A3z103st State, A3z103zip Zip, a6z103area AreaCode, a6z103phon PhoneNumber, a8z103mail Email from proddta.fz103a1 with (nolock) inner join proddta.fz103a2 with (nolock) ON a1z103acno = a2z103acno INNER JOIN proddta.fz103a3 with (nolock) ON a2z103adid = a3z103adid and a2z103actv = 'Y' and a2z103prim = 'Y' LEFT OUTER JOIN proddta.fz103a5 with (nolock) ON a1z103acno = a5z103acno and a5z103actv = 'y' and a5z103prim = 'Y' INNER JOIN proddta.fz103a6 with (nolock) ON a5z103phid = a6z103phid LEFT OUTER JOIN proddta.fz103a8 with (nolock) ON a1z103acno = a8z103acno and a8z103actv = 'Y' and a8z103prim = 'Y'
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State
[code]...
I have created a Dynamic Merge statement SCD2 Store procedure , which insert the records if no matches and if bbxkey matches from source table to destination table thne it updates old record as lateteverion 0 and insert new record with latest version 1.
I am getting below error when I ahve more than 1 bbxkey in my source table. How can I ignore this.
BBXkey is nothing but I am deriving by combining 2 columns.
Msg 8672, Level 16, State 1, Line 6
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.