x

Queue Agent Reader Error

In my environment we have a central server running Microsoft SQL Server 2008 and have enabled replication to all of our 27 remote locations, all of which are running MSDE 2000 SP3. The publications are setup with transactional queued replication.

The problem is the Queue Reader Agent is reporting the error:

The Queue Reader Agent has encountered the error ''The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION ....

The publication articles that are defined are not based on views, however they are defined with a filter intended to limit the replication of data to a given remote location. The following are the basic replication structures.

Publication 1 (There is a unique publication for each location to try and limit the amount of data that is replicated between remote location and the central location)
Table: CustomerInfo 
Fields: ID, FirstName, LastName, Address, City, State, HomeLocation
Filter:  Where CustomerInfo.ID IN(Select Distinct CustomerID From CustomersShipping WHERE LOCATION IN('MYLOC1','AllLOC'))
Publication 2: One publication with all remote databases defined as subscribers
Table: CustomerShipping
Fields: ID, CustomerID, Location
Filter: NO FILTER, all data is replicated

The replication monitor is reporting transactions being delivered from the subscriber to the distributor but I am not sure if some or no transactions are being replicated to the other subscribers that meet the filter requirement of the first publication example. I am not a DBA but need to get this resolved as soon as I can and any help IS GREATLY APPRECIATED!!!!!

If there are any other details needed please let me know.

Thanks for everything!

Richard

more ▼

asked Oct 21, 2009 at 03:04 PM in Default

user-237 (google) gravatar image

user-237 (google)
11 1 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

Does the replication works perfectly if you remove the WHERE condition on your filter ?

Its because, on a view, the WITH CHECK option specifies that during a insert/update/delete you cant change any criteria that would remove a row from the view.

Basically a view with: WHERE CONDITION = 1 cant have an update adjusting contintion to 2, because otherwise those rows would be eliminated from the view.

In your case, you have a where clausule: Where CustomerInfo.ID IN(Select Distinct CustomerID From CustomersShipping WHERE LOCATION IN('MYLOC1','AllLOC'))

And if the CustomersShipping table is being changed, or it is not up to date, or its being updated in any wrong order ( before/after ) that violates the clausule, than the WITH CHECK will alerts.

Do some tests, make CustomersShipping so that the same select executed on both servers returns the same values, test the order of the publications, maybe you are removing something from the view minutes before you try to update it.

And, if possible, test it without the where clausule or with static values in it, this will let us know you that your replication is working with other parameters.

Let us know of your results.

more ▼

answered Oct 21, 2009 at 04:46 PM

Gustavo gravatar image

Gustavo
592 4 4 7

Thanks! I have ran some tests between updating/deleting transactions at the subscriber and distributor, tracked the replication process to the remote locations. Answer: Partially, some completely replicated while others did not. I have yet to change/remove the where clause but feel pretty confident what you are describing is the case, I do have two publications and one publication is dependent on the other. Assuming that how could I work around the issue? Have both tables/articles defined in a single publication to a single remote location? Replicate the entire table? Thanks Much!
Oct 22, 2009 at 09:58 AM user-237 (google)

You might need to work with the order of the publications, be them on the same article/job/publication or not. What seems logical is that CustomersShipping should go before CustomerInfo.

Depending on the amount of data replicated, copying all of it can be a solution, you can apply the filters at the destination.

Another idea, if you dont need a quick refresh on your sites, you coult try merge replication, its not my favorite but maybe it could solve your problem.
Oct 22, 2009 at 10:25 AM Gustavo
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x305

asked: Oct 21, 2009 at 03:04 PM

Seen: 1831 times

Last Updated: Oct 26, 2009 at 10:11 AM