Hi, i had a server that was a publisher/distributor. We decide to change
the server name. When we want to execute the sp_dropserver and
sp_addserver display some errors, so we decided to disable the the
publisher/distributor option. When we did it, the distribution database
wasnt deleted.
Do somebody know if is correct that the distribution database already
exists? Can i delete it manually?
I i try to reconfigure the publisher/distributor options it reports to
me that already exists a database with the distribution name, and i have
to select another name.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Maria,
please have a look at sp_dropdistributiondb and sp_dropdistributor.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label publication. Show all posts
Showing posts with label publication. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Problems creating subscription...
I am attempting to subscribe to a publication and I receive the
following message...
SQL 2005 & Win 2005 on both ends.
===================================
SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
(New Subscription Wizard)
===================================
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand)
at
Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
commandIn)
at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
at Microsoft.SqlServer.Replication.Subscription.Creat e()
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
subscriber, SubWizardPublication pubInfo)
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
executeNow)
===================================
String or binary data would be truncated.
Changed database context to 'STOREMAIN'.
Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy. (.Net SqlClient Data Provider)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
Server Name: r00s-sqla
Error Number: 8152
Severity: 16
State: 10
Procedure: sp_MSget_synctran_commands
Line Number: 198
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
Hi Larry
Can you script out the subscription and run it from Query Analyzer/SSMS?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168884119.848778.223260@.m58g2000cwm.googlegr oups.com...
> I am attempting to subscribe to a publication and I receive the
> following message...
> SQL 2005 & Win 2005 on both ends.
> ===================================
> SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
> (New Subscription Wizard)
> ===================================
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand)
> at
> Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
> commandIn)
> at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
> at Microsoft.SqlServer.Replication.Subscription.Creat e()
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
> subscriber, SubWizardPublication pubInfo)
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
> executeNow)
> ===================================
> String or binary data would be truncated.
> Changed database context to 'STOREMAIN'.
> Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy. (.Net SqlClient Data Provider)
> --
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
> --
> Server Name: r00s-sqla
> Error Number: 8152
> Severity: 16
> State: 10
> Procedure: sp_MSget_synctran_commands
> Line Number: 198
>
> --
> Program Location:
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
> methodName, Boolean async)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
>
|||That is what I did to fix the problem.
Thanks,
Larry...
|||Sooy...different problem...
I scriped the creation of the publication, it took 23 min to create it.
I scripted the creation of the subscription & received the following
message......
Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
Line 198
String or binary data would be truncated.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
AHIA,
Larry...
|||I am looking at the code for sp_MSget_synctran_commands and this is what it
has around line 198
if @.command_only = 0
select * from #art_commands order by id
else
select commands from #art_commands order by id
Do you have any really long table names?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168927021.980769.34300@.q2g2000cwa.googlegrou ps.com...
> Sooy...different problem...
> I scriped the creation of the publication, it took 23 min to create it.
> I scripted the creation of the subscription & received the following
> message......
> Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
> Line 198
> String or binary data would be truncated.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> AHIA,
> Larry...
>
|||The longest would be 'tblReceivingHeader'
That is not long in my book!!!
Larry...
|||Nope, its not. Enable logging to find out where it is bombing.
If you narrow this down, please post the schema of the problem table, and
publication and subscription scripts here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1169050867.439445.84200@.11g2000cwr.googlegrou ps.com...
> The longest would be 'tblReceivingHeader'
> That is not long in my book!!!
> Larry...
>
following message...
SQL 2005 & Win 2005 on both ends.
===================================
SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
(New Subscription Wizard)
===================================
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand)
at
Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
commandIn)
at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
at Microsoft.SqlServer.Replication.Subscription.Creat e()
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
subscriber, SubWizardPublication pubInfo)
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
executeNow)
===================================
String or binary data would be truncated.
Changed database context to 'STOREMAIN'.
Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy. (.Net SqlClient Data Provider)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
Server Name: r00s-sqla
Error Number: 8152
Severity: 16
State: 10
Procedure: sp_MSget_synctran_commands
Line Number: 198
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
Hi Larry
Can you script out the subscription and run it from Query Analyzer/SSMS?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168884119.848778.223260@.m58g2000cwm.googlegr oups.com...
> I am attempting to subscribe to a publication and I receive the
> following message...
> SQL 2005 & Win 2005 on both ends.
> ===================================
> SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
> (New Subscription Wizard)
> ===================================
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand)
> at
> Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
> commandIn)
> at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
> at Microsoft.SqlServer.Replication.Subscription.Creat e()
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
> subscriber, SubWizardPublication pubInfo)
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
> executeNow)
> ===================================
> String or binary data would be truncated.
> Changed database context to 'STOREMAIN'.
> Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy. (.Net SqlClient Data Provider)
> --
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
> --
> Server Name: r00s-sqla
> Error Number: 8152
> Severity: 16
> State: 10
> Procedure: sp_MSget_synctran_commands
> Line Number: 198
>
> --
> Program Location:
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
> methodName, Boolean async)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
>
|||That is what I did to fix the problem.
Thanks,
Larry...
|||Sooy...different problem...
I scriped the creation of the publication, it took 23 min to create it.
I scripted the creation of the subscription & received the following
message......
Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
Line 198
String or binary data would be truncated.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
AHIA,
Larry...
|||I am looking at the code for sp_MSget_synctran_commands and this is what it
has around line 198
if @.command_only = 0
select * from #art_commands order by id
else
select commands from #art_commands order by id
Do you have any really long table names?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168927021.980769.34300@.q2g2000cwa.googlegrou ps.com...
> Sooy...different problem...
> I scriped the creation of the publication, it took 23 min to create it.
> I scripted the creation of the subscription & received the following
> message......
> Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
> Line 198
> String or binary data would be truncated.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> AHIA,
> Larry...
>
|||The longest would be 'tblReceivingHeader'
That is not long in my book!!!
Larry...
|||Nope, its not. Enable logging to find out where it is bombing.
If you narrow this down, please post the schema of the problem table, and
publication and subscription scripts here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1169050867.439445.84200@.11g2000cwr.googlegrou ps.com...
> The longest would be 'tblReceivingHeader'
> That is not long in my book!!!
> Larry...
>
Tuesday, March 20, 2012
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
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
Monday, March 12, 2012
Probleme with Merge replication
Hi all
I am trying to configure a merge replication of publication in LAN
The whole Dynamic Filter snapshot runs well, all the data are pumped to the subscriber and all dri, trg etc. scripts are executed without problem. Then it launch a synchronization. Logically and usually there is no data to be merged. But here it start to replicate a lot of change. It's been 8hours it runs and I've got 600000 update.
I have verified the snapshot, the subcribers parameters... and as usal there is no mistake or error. Nothing change on my Server (SQL 2000 SP3a running on W2K SP2 English).
All was running well before. Since one week the problem appears.
Any Idea ?Check with your sysadmin, see if he implemented any policies. Also see if service accounts on the participants are the same (preferred)|||I'm the sysadmin !
And nothing change even on the AD ! No rights, no GPO...
....
Thanks for your answer
I am trying to configure a merge replication of publication in LAN
The whole Dynamic Filter snapshot runs well, all the data are pumped to the subscriber and all dri, trg etc. scripts are executed without problem. Then it launch a synchronization. Logically and usually there is no data to be merged. But here it start to replicate a lot of change. It's been 8hours it runs and I've got 600000 update.
I have verified the snapshot, the subcribers parameters... and as usal there is no mistake or error. Nothing change on my Server (SQL 2000 SP3a running on W2K SP2 English).
All was running well before. Since one week the problem appears.
Any Idea ?Check with your sysadmin, see if he implemented any policies. Also see if service accounts on the participants are the same (preferred)|||I'm the sysadmin !
And nothing change even on the AD ! No rights, no GPO...
....
Thanks for your answer
Subscribe to:
Posts (Atom)