Merge Replication Error
-
Hi All,
Running : SQL 2008 Standard
I could really do with some help on this problem. I accidentally changed a data type of a field from int to bit. This table was on the subscriber side of a merge replication.
Replication now errors with the following error :
Object referenced by the given @article or @artid 'D15CCC90-AB85-424C-8043-093A8B1625FF' could not be found. (Source: MSSQLServer, Error number: 20669)
Ive checked the sysmerge articles tables on both the subscriber and the publisher and they look the same.. what have I done here?
--Subscriber side
SELECT *
FROM [GLOAD].[dbo].[sysmergearticles]
where name = 'tbl_asn'
--Publisher Side
SELECT *
FROM gmsql4.[GLOAD].[dbo].[sysmergearticles]
where name = 'tbl_asn'Time to make a change - Antony Symonds - Monday, November 13, 2017 7:17 AM
When you accidentally made these changes, did you by chance do it through the GUI?
Sue
-
Yes I did!
Time to make a change - Antony Symonds - Monday, November 13, 2017 8:10 AM
That will always bite you when you do that and have replication running. The GUI will often drop and recreate the objects.
I'd try to drop the article and then recreate it - you may be able to just reinit the article but that depends on several different factors. Or reinitialize the publication if possible.
Otherwise you would need to track down the object id that does not match back to an object in sys.objects on the given server and then hack the tables to update the objects ids in the replication table. So the tables may have looked the same but there is an object id in that article (or another) does not map back to a valid object id on the server.Sue
-
Hi,
Thank you for your response. I had already got to the solution between your first and last post.. mainly because of you mentioning the GUI, I insta knew where you were going.As you said you in your last post the ID of the table changed in the background but visibly looked the same, due to SSMS dropping and recreating the table. So I queried sys.objects to find the new object_id on the Subscriber.
declare @tbl varchar(50)
SET @tbl = 'tbl_asn'
SELECT *
FROM gload.sys.objects
WHERE name = @tbl --modify_date > GETDATE() - 10
ORDER BY modify_date;I then went into the sysmergearticles on the subscriber and found it was pointing to an object_id that no longer existed.
SELECT *
FROM [GLOAD].[dbo].[sysmergearticles]
where name = @tblSo I ran an update on sysmergearticles to set the objid to = the new object_id of the table that got dropped and re created.
update
[GLOAD].[dbo].[sysmergearticles] set objid = 231372289
where name = 'tbl_asn' and artid = 'D15CCC90-AB85-424C-8043-093A8B1625FF'Replication came to life and all was well in the world again.
Thank you for your assistance, I hope my solution can help others should they fall foul of this stupidty of mine.
Thanks
Antony
Time to make a change -
Object referenced by the given @article or @artid 'D15CCC90-AB85-424C-8043-093A8B1625FF' could not be found is a s a result of missing objectid in the sysmergerarticles table which is the table your database use to cross check all data meant for replication with source and destination table. Once there is no record for the table carrying data from the missing artcieldid in sysmergearticles table, replication will stop and display this error.
However, there is a simple work around to fix this.
Step 1- Backup your database
Step 2- find out the missing table by querying your sysmergearticle table using the articleid
select * FROM sysmergearticles
wherr artid=255FC7CD-A9D3-4299-BEBA-4BAF19F354D1
This is expected to return no record as that table does not have record in the sysmergearticle table .
Step 3 - execute the query in step 2 on another database server that subscibed to the same replication database
select * FROM sysmergearticles
wherr artid=255FC7CD-A9D3-4299-BEBA-4BAF19F354D1
the query will return details of the table that is missing in your sysmergearticle table. The idea is to insert this record in the sysmergearticle table on the database that is not replicating because of this error. However, the column objid is different for each subscriber database on the replication. it is very important to get the objid right else the problem persist
Step4- query the subscriber database that have this error to get the objectid
select * from sys.objects
order by name
copy the objectid , then create a new row for the missing record in sysmerarticles table. in my case i used excel to create a new row where i made necessary changes before updating sysmergeartciles table
step 5- paste the objectid from step 3 above to columns objid and sync_objid
for merge replication, the objid = syncobjid as shown below
Step 6 - Create a new row for the missing articleid in the sysmergearticle table of the database having replication problem
go to the database having replication issue, expand database, expand tables, expand system tables to locate sysmerarticles table, then copy the new row you created using excel (or tool you must have used to edit the row to your specifics), right click sysmerarticles, select edit top 200 rows , locate the last empty row and paste your prepared data copy from excel in to this row.
Step 7- Go to replication and check the syncronyzation status of the affected subscriber database. status should show that database is replicating like in the picture below
Warning: Please do not update system tables if you are not a professional database administrator and very conversant with the database involved in this kind of issue.
Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply