Problems adding an article to an existing merge replication if owner is not dbo

Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
Just some more info. It seems like the problem occurs when the snapshotagent
generates the bulk copy data for system table
ms_merge_contents_<<newtable>>. It seems like this procedure doesn't use the
right owner to generate the bcp file.
Stefan
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> schreef in bericht
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I created a new table on the publication database the owner of the new
> table is not dbo. We then called sp_addmergearticle to add the article to
> the publication, using the @.source_owner and @.destination_owner parameters
> to specify the owner is different from dbo, and the
> @.force_invalidate_snapshot parameter.
> When we start the snapshot agent, it prepares the newly added table for
> replication, and generates the necessary scripts. At one moment the
> snapshot agent stops with an error stating 'invalid object name' and the
> name of the new table. I think this occurs when the snapshot agent wants
> tot generate the bcp files.
> Inspecting the article's properties dialog, showed 'that source table
> owner' and 'destination table owner' are correct. And except for the
> tablename an article name the properties are no different to the
> previously published articles.
> I retried this for a table that has dbo as owner. This worked without
> problems.
> Does anybody experienced the same problems?
> Best regards,
> Stefan
>
>
|||I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs in merge replication system stored procedures. Try looking for my messages (end of last year) - you should find attached archive with those fixes. if you will not find it, I could re-post it in this thread (I have fixes for SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||OK - I've put them on the downloads section. I can't give you the precise link, other that request that you browse to www.replicationanswers.com, as this website is currently done on the cheap (soon to change providers so I can have absolute urls).
Rgds,
Paul Ibison (SQL Server MVP)
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message news:%23AaQjgHBFHA.2552@.TK2MSFTNGP09.phx.gbl...
Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan

Problem with valid owner

I have just downloaded the MS SQL Server Management Studio Express, and are following a tutorial on www.learnvisualstudio.net

Now I am having trouble with some owner information. The message says :

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTORIZATION statement to set the database owner to a valid login, then add the database diagram support objects"

I should mention that my WIN XP username is "Bj?rn" with the Norwegian letter "?" in the middle. I suspect it to have a problem with that letter?

Any better suggestions?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120849&SiteID=1

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

HTH

|||

Thank you for taking the time to help me.

The compatibility level was already set to 90, so that didn't solve the problem.

When I tried to set a new owner, I got this message:

TITLE: Microsoft SQL Server Management Studio Express

Set owner failed for Database 'MyCompany'. (Microsoft.SqlServer.Express.Smo

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15353)

|||

Hi,

Do you fin a solution ? I have the same problem ...

Thanks

|||Yes. I think I changed the owner to sa (server admin I think it is)

Problem with valid owner

I have just downloaded the MS SQL Server Management Studio Express, and are following a tutorial on www.learnvisualstudio.net

Now I am having trouble with some owner information. The message says :

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTORIZATION statement to set the database owner to a valid login, then add the database diagram support objects"

I should mention that my WIN XP username is "Bj?rn" with the Norwegian letter "?" in the middle. I suspect it to have a problem with that letter?

Any better suggestions?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120849&SiteID=1

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

HTH

|||

Thank you for taking the time to help me.

The compatibility level was already set to 90, so that didn't solve the problem.

When I tried to set a new owner, I got this message:

TITLE: Microsoft SQL Server Management Studio Express

Set owner failed for Database 'MyCompany'. (Microsoft.SqlServer.Express.Smo

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15353)

|||

Hi,

Do you fin a solution ? I have the same problem ...

Thanks

|||Yes. I think I changed the owner to sa (server admin I think it is)