May 21, 2009 at 4:50 am
Dears,
Recent days, I configured a transactional replication with filter time-based, as like:
"WHERE [DayID] between convert (nvarchar(10),DATEADD(month,-2,GETDATE()),112)
and convert (nvarchar(10),getdate(),112)";
DayID and UserID is the primary key of the published table.
At the beginning, everything looks like normal. About 1 day,the replication monitor alert errors:
" if @@trancount > 0 rollback tran Transaction sequence number: 0x00053BDC000004A1000400000000, Command ID: 1)套用複寫的命令時在訂閱者端找不到資料列。(Source: MSSQLServer, Error number: 20598)"
I executed sp_browsereplcmds '0x00053BDC000004A1000400000000','0x00053BDC000004A1000400000000'
and the command result: {CALL [sp_MSupd_dboUser_Duty_Schedule] (,,,,,,,,,,,,,'1',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,N'20090515',N'W10732486',0x002000000000)}
But I don't know how to resolve it, desire help! 🙂
May 21, 2009 at 6:48 am
I would run a trace with a filter on application name that would be something like %YourDBName% against the subscriber instance. Watch for the rollback statement and then grab the update procedure call. Execute that directly against the database via SSMS and see what the error says. Wondering if you are missing the row that you are trying to update. Can't tell from the output that you are seeing.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 22, 2009 at 2:34 am
I start profiler against the subscription db,and got the statement:
exec [sp_MSdel_dboUser_Duty_Schedule] N'20090522',N'W10832850';
and execute it on the subscription db,the error says:
(0 row(s) affected)
Msg 20598, Level 16, State 1, Procedure sp_MSreplraiserror, Line 20
????????????????????
And I find that the data contain "N'20090522',N'W10832850'" doesn't exist in the subscription db.
May 22, 2009 at 2:56 am
And after I manually insert that data into the subscription table, start synchronizing, another error follows:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00053CD400000399003300000000, Command ID: 3)
I also find the corresponding command, and manually insert... , start synchroning... ,
at last, the subscription status turns normally.
But when thousands of items in it and an unknown number are going to cause problems, I couldn't easily lose a great deal of valuable time trying to fix this problem.
Any better ideas ~~;-)
May 22, 2009 at 5:59 am
You can do a couple of things, first would be to reinitialize so that you repush the snapshot. This is the cleanest approach and would be my recommendation.
If that is difficult due to production activity and / or you have a huge table then you could do the following:
1. Create a table containing columns ID (pk), tablename varchar, Key varchar (in case you have non int keys guids, etc), completed_dt
2. Modify the replication update procedure to include a check to see if the key it is updating exists, if it doesn't insert the tablename, key into the table you just created. You should put this after the first begin statement in the procedure.
3. Modify the update stored procedure further to comment out the three lines of error checking. You will have to do this after both sections of update code.
4. Create a script that will take the keys in the table you just created and insert those missing records from the source.
Doing things this way will allow the replicated commands to keep processing even when there is a missing row and you will capture the missing row key information so that you can insert it later.
Seems like a bit of a hack but I have been able to use this to catch missing rows following replication modifications and still have replication working. I haven't worked out a generic script to process the contents of the table that I shared or I would provide the scripts.
Hope this is helpful.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 22, 2009 at 8:18 am
Thanks for your advice.
But I can't understand why only when putting [b]filters [/b]in the transactional replication, errors occurred.when I remove the filters, replicate the whole tables,it runs all right.
Could it be that...? That is a bug of sql server2005??;-)
May 22, 2009 at 8:55 am
Hard to say but if it is clearly missing records in your destination table then for some reason the replication filter is sending updates for records that are not present. I would investigate how the table was originally populated and ensure that the updates meet the filter criteria that you have specified.
I don't think it is a bug but I would keep it in your consideration.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 27, 2009 at 12:37 am
Dear David,
In recent days,I also find if the filters are limited only at one side as:
" WHERE [DayID]>= convert (nvarchar(10),DATEADD(month,-2,GETDATE()),112);"
or only to,
"WHERE [DayID]<= convert (nvarchar(10),getdate(),112)";
Can't believe the replication runs all right all the time. No any error alert!
But~ the filters can't be limited at both sides, otherwise it runs error.;-)
May 27, 2009 at 6:10 am
sunny1_liu (5/27/2009)
Dear David,In recent days,I also find if the filters are limited only at one side as:
" WHERE [DayID]>= convert (nvarchar(10),DATEADD(month,-2,GETDATE()),112);"
or only to,
"WHERE [DayID]<= convert (nvarchar(10),getdate(),112)";
Can't believe the replication runs all right all the time. No any error alert!
But~ the filters can't be limited at both sides, otherwise it runs error.;-)
Can you explain further? I don't understand what you mean by "side". Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 28, 2009 at 12:51 am
Sorry, I'm wrong.
In fact, only when the row filter has been set as
" WHERE [DayID]>= convert (nvarchar(10),DATEADD(month,-2,GETDATE()),112);"
there's no error alert.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply