looking for more info on merge repl problem

I was informed about an issue with merge replication on sql server 2000 sp3
and sp3a. This issue is that if your replicated tables are not owned by
'dbo' then you will have a variety of problems. (thank you Kestutis
Adomavicius). I have been trying to find more information on this. I came
across similar issues where people had to change the db owner AND enable
something called 'cross database ownership'?
Basically I am looking for more information on this asap. I have a server
that I thought was ready for production until I had to delete and recreate
replication for something and began to run into problems with not being able
to delete the publication/subscriptions. I need to know what I need to do to
make this merge replication stable.
I currently plan to change owner of all my tables to dbo and recreating
repl. So now what about 'cross database ownership'? do I need to enable
that? if so how? what else might I need to do that I won't find out about
until its to late? I can't beleive I couldn't find anything about this on
MS's site? I must have missed it from staring at the screen to long! does
anyone know of any KB articles on this?
any more info on this would be greatly appreciated...
There is an issue if you create a publication deploy it to the subscriber,
and then go back and change the destination owner name in the publication
and reinitialize.
Any insert/update/delete activity will give you the below message.
Server: Msg 550, Level 16, State 1, Procedure
ins_300B4B5F04354E02B65347D9070DE47F, Line 57
The attempted insert or update failed because the target view either
specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION
and one or more rows resulting from the operation did not qualify under the
CHECK OPTION constraint.
The statement has been terminated.
I have reported the above problem to Microsoft.
However - I know of no problems with having different owner names on the
Publisher or Subscriber, or non dbo owner names on both your Publihser and
Subscriber. I also don't know of any problems with cross database ownership.
If all he is referring to is the dropping of the conflict tables - this
isn't per se a problem because you can drop them from within ISQL (drop
table conflict...) but not EM.
If your problem is that it is trying to drop a table which replication can't
see or doesn't exist you can always create a bogus table with the same name,
ie
create table HesstechUser.conflict_MergeReplLIVE_VendorCustomer (pk int)
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"djc" <noone@.nowhere.com> wrote in message
news:eG1wbqSZEHA.716@.TK2MSFTNGP11.phx.gbl...
> I was informed about an issue with merge replication on sql server 2000
sp3
> and sp3a. This issue is that if your replicated tables are not owned by
> 'dbo' then you will have a variety of problems. (thank you Kestutis
> Adomavicius). I have been trying to find more information on this. I came
> across similar issues where people had to change the db owner AND enable
> something called 'cross database ownership'?
> Basically I am looking for more information on this asap. I have a server
> that I thought was ready for production until I had to delete and recreate
> replication for something and began to run into problems with not being
able
> to delete the publication/subscriptions. I need to know what I need to do
to
> make this merge replication stable.
> I currently plan to change owner of all my tables to dbo and recreating
> repl. So now what about 'cross database ownership'? do I need to enable
> that? if so how? what else might I need to do that I won't find out about
> until its to late? I can't beleive I couldn't find anything about this on
> MS's site? I must have missed it from staring at the screen to long! does
> anyone know of any KB articles on this?
> any more info on this would be greatly appreciated...
>
|||here is the repro of the earlier problem
use master
go
create database test
go
create database sub
go
sp_addlogin 'test' , 'test', 'test'
go
use test
go
create table test.test (col char(20))
go
use sub
go
exec sp_grantdbaccess N'test', N'test'
use test
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'test', @.optname = N'merge publish',
@.value = N'true'
GO
use [test]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'test', @.description = N'Merge
publication of test database from Publisher HILARY2KP.', @.retention = 14,
@.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.centralized_conflicts = N'true', @.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false',
@.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14,
@.keep_partition_changes = N'false', @.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false', @.add_to_active_directory = N'false',
@.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'test',@.frequency_type = 8,
@.frequency_interval = 64, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 1, @.frequency_subday = 1,
@.frequency_subday_interval = 0, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 4300, @.active_end_time_of_day = 0,
@.snapshot_job_name = N'HILARY2KP-test-test-4'
GO
exec sp_grant_publication_access @.publication = N'test', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'test', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'test', @.login =
N'HILARY2KP\Administrator'
GO
exec sp_grant_publication_access @.publication = N'test', @.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'test', @.article = N'test',
@.source_owner = N'test', @.source_object = N'test', @.type = N'table',
@.description = null, @.column_tracking = N'true', @.pre_creation_cmd =
N'drop', @.creation_script = null, @.schema_option = 0x000000000000CFF1,
@.article_resolver = null, @.subset_filterclause = null, @.vertical_partition =
N'false', @.destination_owner = N'test', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
-- Adding the merge subscription
exec sp_addmergesubscription @.publication = N'test', @.subscriber =
N'HILARY2KP', @.subscriber_db = N'sub', @.subscription_type = N'push',
@.subscriber_type = N'local', @.subscription_priority = 0.000000, @.sync_type =
N'automatic', @.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false',
@.offloadagent = 0, @.use_interactive_resolver = N'false'
GO
--start the snapshot agent, when complete start the merge agent.
-- drop subscription
exec sp_dropmergesubscription @.publication = N'test', @.subscriber =
N'HILARY2KP', @.subscriber_db = N'sub', @.subscription_type = N'push'
--changing destination object name
exec sp_changemergearticle @.publication = N'test', @.article = N'test',
@.force_invalidate_snapshot = 1, @.force_reinit_subscription = 1, @.property =
N'destination_owner', @.value = N'dbo'
--adding the merge subscription
exec sp_addmergesubscription @.publication = N'test', @.subscriber =
N'HILARY2KP', @.subscriber_db = N'sub', @.subscription_type = N'push',
@.subscriber_type = N'local', @.subscription_priority = 0.000000, @.sync_type =
N'automatic', @.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false',
@.offloadagent = 0, @.use_interactive_resolver = N'false'
--start the snapshot agent, when complete start the merge agent.
use sub
insert into dbo.test (col)values ('test')
--error message is
Server: Msg 550, Level 16, State 1, Procedure
ans_1984A1DAA3F24E3AB74B251657E7710D, Line 57
The attempted insert or update failed because the target view either
specifies WITH CHECK OPTION
or spans a view that specifies WITH CHECK OPTION and one or more rows
resulting from the operation did
not qualify under the CHECK OPTION constraint.
The statement has been terminated.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OdvX5oaZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> There is an issue if you create a publication deploy it to the subscriber,
> and then go back and change the destination owner name in the publication
> and reinitialize.
> Any insert/update/delete activity will give you the below message.
> Server: Msg 550, Level 16, State 1, Procedure
> ins_300B4B5F04354E02B65347D9070DE47F, Line 57
> The attempted insert or update failed because the target view either
> specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK
OPTION
> and one or more rows resulting from the operation did not qualify under
the
> CHECK OPTION constraint.
> The statement has been terminated.
> I have reported the above problem to Microsoft.
> However - I know of no problems with having different owner names on the
> Publisher or Subscriber, or non dbo owner names on both your Publihser and
> Subscriber. I also don't know of any problems with cross database
ownership.
> If all he is referring to is the dropping of the conflict tables - this
> isn't per se a problem because you can drop them from within ISQL (drop
> table conflict...) but not EM.
> If your problem is that it is trying to drop a table which replication
can't
> see or doesn't exist you can always create a bogus table with the same
name,[vbcol=seagreen]
> ie
> create table HesstechUser.conflict_MergeReplLIVE_VendorCustomer (pk int)
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "djc" <noone@.nowhere.com> wrote in message
> news:eG1wbqSZEHA.716@.TK2MSFTNGP11.phx.gbl...
> sp3
came[vbcol=seagreen]
server[vbcol=seagreen]
recreate[vbcol=seagreen]
> able
do[vbcol=seagreen]
> to
about[vbcol=seagreen]
on[vbcol=seagreen]
does
>
|||Just found Kestutis Adomavicius's zip of fixed procs. It looks like Kestius
has found something. I will be forwarding these to a contact of mine at
Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OdvX5oaZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> There is an issue if you create a publication deploy it to the subscriber,
> and then go back and change the destination owner name in the publication
> and reinitialize.
> Any insert/update/delete activity will give you the below message.
> Server: Msg 550, Level 16, State 1, Procedure
> ins_300B4B5F04354E02B65347D9070DE47F, Line 57
> The attempted insert or update failed because the target view either
> specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK
OPTION
> and one or more rows resulting from the operation did not qualify under
the
> CHECK OPTION constraint.
> The statement has been terminated.
> I have reported the above problem to Microsoft.
> However - I know of no problems with having different owner names on the
> Publisher or Subscriber, or non dbo owner names on both your Publihser and
> Subscriber. I also don't know of any problems with cross database
ownership.
> If all he is referring to is the dropping of the conflict tables - this
> isn't per se a problem because you can drop them from within ISQL (drop
> table conflict...) but not EM.
> If your problem is that it is trying to drop a table which replication
can't
> see or doesn't exist you can always create a bogus table with the same
name,[vbcol=seagreen]
> ie
> create table HesstechUser.conflict_MergeReplLIVE_VendorCustomer (pk int)
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "djc" <noone@.nowhere.com> wrote in message
> news:eG1wbqSZEHA.716@.TK2MSFTNGP11.phx.gbl...
> sp3
came[vbcol=seagreen]
server[vbcol=seagreen]
recreate[vbcol=seagreen]
> able
do[vbcol=seagreen]
> to
about[vbcol=seagreen]
on[vbcol=seagreen]
does
>
|||> If all he is referring to is the dropping of the conflict tables - this
> isn't per se a problem because you can drop them from within ISQL (drop
> table conflict...) but not EM.
I think you should agree that if some functionality works when owner of the
tables is "dbo", it should work the same way if the owner is "user1". When
it is not working you CAN go to QA and drop something manually, but you also
CAN decide to SKIP using software which is not working as you expect it to
work.
Of course sometimes there are problems which are caused not by software, but
by misuse of it, but I am sure that this is not the case. If you would like,
I could post a workflow of recreating those problems that I mentioned, just
need some free time to write it down.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OdvX5oaZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> There is an issue if you create a publication deploy it to the subscriber,
> and then go back and change the destination owner name in the publication
> and reinitialize.
> Any insert/update/delete activity will give you the below message.
> Server: Msg 550, Level 16, State 1, Procedure
> ins_300B4B5F04354E02B65347D9070DE47F, Line 57
> The attempted insert or update failed because the target view either
> specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK
OPTION
> and one or more rows resulting from the operation did not qualify under
the
> CHECK OPTION constraint.
> The statement has been terminated.
> I have reported the above problem to Microsoft.
> However - I know of no problems with having different owner names on the
> Publisher or Subscriber, or non dbo owner names on both your Publihser and
> Subscriber. I also don't know of any problems with cross database
ownership.
> If all he is referring to is the dropping of the conflict tables - this
> isn't per se a problem because you can drop them from within ISQL (drop
> table conflict...) but not EM.
> If your problem is that it is trying to drop a table which replication
can't
> see or doesn't exist you can always create a bogus table with the same
name,
> ie
> create table HesstechUser.conflict_MergeReplLIVE_VendorCustomer (pk int)
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
|||That sounds nice.
I mean having a contact at Microsoft Thanks for the initiative to provide
some info to MS, that might lead to something I guess.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:enXC7yaZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> Just found Kestutis Adomavicius's zip of fixed procs. It looks like
Kestius
> has found something. I will be forwarding these to a contact of mine at
> Microsoft.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
|||ok.. i'm getting ready to fix up my issue here. One last question. Should I
just change the owner on each table individually to user 'dbo' or should I
change the actual database owner to user 'dbo' also?
thanks again. And I'm glad that my badgering of you 2 experts helped lead to
getting this info to MS! I would like a sticker that reads 'Annoying n00bs
help get info to MS' hehe.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:enXC7yaZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> Just found Kestutis Adomavicius's zip of fixed procs. It looks like
Kestius[vbcol=seagreen]
> has found something. I will be forwarding these to a contact of mine at
> Microsoft.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OdvX5oaZEHA.3508@.TK2MSFTNGP09.phx.gbl...
subscriber,[vbcol=seagreen]
publication[vbcol=seagreen]
> OPTION
> the
the[vbcol=seagreen]
and[vbcol=seagreen]
> ownership.
> can't
> name,
2000[vbcol=seagreen]
by[vbcol=seagreen]
> came
enable[vbcol=seagreen]
> server
> recreate
being[vbcol=seagreen]
> do
recreating[vbcol=seagreen]
enable
> about
> on
> does
>