Replication between remote offices, along with linked servers, began failing. Network and server admins indicate that nothing has been changed. The DBA of the remote server also says nothing has been changed. Replication failes with the following message.
The process could not connect to Subscriber 'ProdServer1\Production1'.
SQL Server does not exist or access denied.
(Source: CDB0202\PRODUCTIONLFG (Data source); Error number: 17)
I am able to connect to the remote server using EM and Query Analyzer. I am also able to create linked servers from the local production server to a remote test server.
Any tips on how to troubleshoot this problem?
Thanks, DaveThe problem has been resolved. WINS had expired on the clustered remote server. The network admins don't understand why it happened and can't explain why DNS didn't didn't resolve the name, but at least it is fixed. I'm a bit confused why EM and Query Analyzer did not experience any problems.
Thanks, Dave
Showing posts with label failing. Show all posts
Showing posts with label failing. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Production error: "The schema script [...] could not be propagated to the subscriber"
Hi,
A member of my team recently made a few minor changes to our database.
Several (not all) subscribers are now failing to replicate with the
following error:
The schema script 'exec sp_repldropcolumn
'[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
A column was added to or dropped from the replicated table.
(Source: l07052 (Agent); Error number: 27160)
------
' because it is a primary key column.
(Source: l07052 (Data source); Error number: 21264)
------
This was a problem a few months ago but seemed to have been resolved.
In particular, nothing at all has changed recently in the
IP_Application_Updates table. I'm very happy for that table to remain
exactly as it is.
Can anyone suggest a way to fix this? I've got a bunch of users who
are unable to replicate their database and they'll turn nasty shortly.
In the absence of an elegant fix I'll cheerfully go with a way to
change the offending script to something innocuous, but I don't know
where or why the script exists or why the subscribers are trying to run
it. I will be very grateful if anyone can help me to get my
subscribers past this error.
Many thanks
Alex
Whenever the Merge Agent runs, it first makes sure that the schema [any
change as a result of DDL] is in sync, before the
data could be replicated. All the schema changes in the publisher are stored
in sysmergeschemachange table on the
publisher database. You can take a look at this table and see the schema
changes that have been executed at the publisher.
I believe the Merge Agent is trying to drop the column at the subscriber,
but unable to do it because it is the primary key column.
One work around would be to remove the primary key constraint at the
subscriber, if you don't need that column any more.
<WombatDeath@.gmail.com> wrote in message
news:1169249897.423772.218970@.l53g2000cwa.googlegr oups.com...
> Hi,
> A member of my team recently made a few minor changes to our database.
> Several (not all) subscribers are now failing to replicate with the
> following error:
> The schema script 'exec sp_repldropcolumn
> '[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
> propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> A column was added to or dropped from the replicated table.
> (Source: l07052 (Agent); Error number: 27160)
> ------
> ' because it is a primary key column.
> (Source: l07052 (Data source); Error number: 21264)
> ------
> This was a problem a few months ago but seemed to have been resolved.
> In particular, nothing at all has changed recently in the
> IP_Application_Updates table. I'm very happy for that table to remain
> exactly as it is.
> Can anyone suggest a way to fix this? I've got a bunch of users who
> are unable to replicate their database and they'll turn nasty shortly.
> In the absence of an elegant fix I'll cheerfully go with a way to
> change the offending script to something innocuous, but I don't know
> where or why the script exists or why the subscribers are trying to run
> it. I will be very grateful if anyone can help me to get my
> subscribers past this error.
> Many thanks
> Alex
>
|||Hi Gayathri
Thank you for replying so quickly. The problem I have is that the
table in question is in use, and the column in question should be the
PK of that table.
We have a scheduled job which recreates the snapshot once a week, on
Saturday. I've just taken a look and most tables have three records in
sysmergeschemachange, but the IP_Application_Updates table has an
additional four:
exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
alter table [dbo].[IP_Application_Updates] drop column update_id
exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
IDENTITY (1, 1) NOT NULL], '%', 1
alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
(1, 1) NOT NULL
This is presumably what's causing the problem. I could perhaps "fix"
it by just deleting those four records from the system table but I can
just imagine causing all manner of havoc with my ignorant tinkering.
Assuming that nobody in my team has had any reason whatsoever to make
any alterations to this table in recent months, is it possible to
hazard a guess as to why the merge agent is attempting to recreate the
update_id column? And is there any way to make it stop?
Thanks
Alex
|||Hi,
The only reason I see a DDL row in 'sysmergeschemachange' table is because
of a user action after
the intial snapshot has been taken.
Trying to delete the rows from the system table could pop up some more
issues.
You could do two things:
1. drop the primary key constraint at the failing subscriber. This should
probably allow
the merge agent to complete succesfully [if this is the only problem].
2. Else, you might have to re-initalize the failing subscribers ...
Hope this helps.
-- Gayathri TK
<WombatDeath@.gmail.com> wrote in message
news:1169255444.925914.213970@.11g2000cwr.googlegro ups.com...
> Hi Gayathri
> Thank you for replying so quickly. The problem I have is that the
> table in question is in use, and the column in question should be the
> PK of that table.
> We have a scheduled job which recreates the snapshot once a week, on
> Saturday. I've just taken a look and most tables have three records in
> sysmergeschemachange, but the IP_Application_Updates table has an
> additional four:
> exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
> alter table [dbo].[IP_Application_Updates] drop column update_id
> exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
> IDENTITY (1, 1) NOT NULL], '%', 1
> alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
> (1, 1) NOT NULL
> This is presumably what's causing the problem. I could perhaps "fix"
> it by just deleting those four records from the system table but I can
> just imagine causing all manner of havoc with my ignorant tinkering.
> Assuming that nobody in my team has had any reason whatsoever to make
> any alterations to this table in recent months, is it possible to
> hazard a guess as to why the merge agent is attempting to recreate the
> update_id column? And is there any way to make it stop?
> Thanks
> Alex
>
|||Hi Gayathri
Thanks very much; we'll try dropping the PK constraint and see what
happens.
Thanks again for your help,
Alex
A member of my team recently made a few minor changes to our database.
Several (not all) subscribers are now failing to replicate with the
following error:
The schema script 'exec sp_repldropcolumn
'[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
A column was added to or dropped from the replicated table.
(Source: l07052 (Agent); Error number: 27160)
------
' because it is a primary key column.
(Source: l07052 (Data source); Error number: 21264)
------
This was a problem a few months ago but seemed to have been resolved.
In particular, nothing at all has changed recently in the
IP_Application_Updates table. I'm very happy for that table to remain
exactly as it is.
Can anyone suggest a way to fix this? I've got a bunch of users who
are unable to replicate their database and they'll turn nasty shortly.
In the absence of an elegant fix I'll cheerfully go with a way to
change the offending script to something innocuous, but I don't know
where or why the script exists or why the subscribers are trying to run
it. I will be very grateful if anyone can help me to get my
subscribers past this error.
Many thanks
Alex
Whenever the Merge Agent runs, it first makes sure that the schema [any
change as a result of DDL] is in sync, before the
data could be replicated. All the schema changes in the publisher are stored
in sysmergeschemachange table on the
publisher database. You can take a look at this table and see the schema
changes that have been executed at the publisher.
I believe the Merge Agent is trying to drop the column at the subscriber,
but unable to do it because it is the primary key column.
One work around would be to remove the primary key constraint at the
subscriber, if you don't need that column any more.
<WombatDeath@.gmail.com> wrote in message
news:1169249897.423772.218970@.l53g2000cwa.googlegr oups.com...
> Hi,
> A member of my team recently made a few minor changes to our database.
> Several (not all) subscribers are now failing to replicate with the
> following error:
> The schema script 'exec sp_repldropcolumn
> '[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
> propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> A column was added to or dropped from the replicated table.
> (Source: l07052 (Agent); Error number: 27160)
> ------
> ' because it is a primary key column.
> (Source: l07052 (Data source); Error number: 21264)
> ------
> This was a problem a few months ago but seemed to have been resolved.
> In particular, nothing at all has changed recently in the
> IP_Application_Updates table. I'm very happy for that table to remain
> exactly as it is.
> Can anyone suggest a way to fix this? I've got a bunch of users who
> are unable to replicate their database and they'll turn nasty shortly.
> In the absence of an elegant fix I'll cheerfully go with a way to
> change the offending script to something innocuous, but I don't know
> where or why the script exists or why the subscribers are trying to run
> it. I will be very grateful if anyone can help me to get my
> subscribers past this error.
> Many thanks
> Alex
>
|||Hi Gayathri
Thank you for replying so quickly. The problem I have is that the
table in question is in use, and the column in question should be the
PK of that table.
We have a scheduled job which recreates the snapshot once a week, on
Saturday. I've just taken a look and most tables have three records in
sysmergeschemachange, but the IP_Application_Updates table has an
additional four:
exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
alter table [dbo].[IP_Application_Updates] drop column update_id
exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
IDENTITY (1, 1) NOT NULL], '%', 1
alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
(1, 1) NOT NULL
This is presumably what's causing the problem. I could perhaps "fix"
it by just deleting those four records from the system table but I can
just imagine causing all manner of havoc with my ignorant tinkering.
Assuming that nobody in my team has had any reason whatsoever to make
any alterations to this table in recent months, is it possible to
hazard a guess as to why the merge agent is attempting to recreate the
update_id column? And is there any way to make it stop?
Thanks
Alex
|||Hi,
The only reason I see a DDL row in 'sysmergeschemachange' table is because
of a user action after
the intial snapshot has been taken.
Trying to delete the rows from the system table could pop up some more
issues.
You could do two things:
1. drop the primary key constraint at the failing subscriber. This should
probably allow
the merge agent to complete succesfully [if this is the only problem].
2. Else, you might have to re-initalize the failing subscribers ...
Hope this helps.
-- Gayathri TK
<WombatDeath@.gmail.com> wrote in message
news:1169255444.925914.213970@.11g2000cwr.googlegro ups.com...
> Hi Gayathri
> Thank you for replying so quickly. The problem I have is that the
> table in question is in use, and the column in question should be the
> PK of that table.
> We have a scheduled job which recreates the snapshot once a week, on
> Saturday. I've just taken a look and most tables have three records in
> sysmergeschemachange, but the IP_Application_Updates table has an
> additional four:
> exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
> alter table [dbo].[IP_Application_Updates] drop column update_id
> exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
> IDENTITY (1, 1) NOT NULL], '%', 1
> alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
> (1, 1) NOT NULL
> This is presumably what's causing the problem. I could perhaps "fix"
> it by just deleting those four records from the system table but I can
> just imagine causing all manner of havoc with my ignorant tinkering.
> Assuming that nobody in my team has had any reason whatsoever to make
> any alterations to this table in recent months, is it possible to
> hazard a guess as to why the merge agent is attempting to recreate the
> update_id column? And is there any way to make it stop?
> Thanks
> Alex
>
|||Hi Gayathri
Thanks very much; we'll try dropping the PK constraint and see what
happens.
Thanks again for your help,
Alex
Saturday, February 25, 2012
process could not execute 'sp_repldone/sp_replcounters'
I have had to fully restored my publisher database. Since then my
transaactional replication no longer works with the 'Log Reader' failing to
start and reporting the error message "The process could not execute
'sp_repldone/sp_replcounters' on ServerName".
I have run via Enterprise Manager 'Validate Subscriptions' and
'Re-initialize All Subscriptions' but the Log Reader still fails. Should I
simply re-start the server or run sp_replrestart?
Any assistance would be greatly appreciated.
Kind regards
Robert
the fastest way to fix this is to drop and recreate your subscription.
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
"Robert Paynter" <Robert Paynter@.discussions.microsoft.com> wrote in message
news:AD6E580D-02D3-4722-8AA0-E33979A1F0FD@.microsoft.com...
> I have had to fully restored my publisher database. Since then my
> transaactional replication no longer works with the 'Log Reader' failing
to
> start and reporting the error message "The process could not execute
> 'sp_repldone/sp_replcounters' on ServerName".
> I have run via Enterprise Manager 'Validate Subscriptions' and
> 'Re-initialize All Subscriptions' but the Log Reader still fails. Should
I
> simply re-start the server or run sp_replrestart?
> Any assistance would be greatly appreciated.
> Kind regards
> Robert
|||Hi Hilary
Many thanks for your reply. Just so I am clear in Enterprise Manager I
would delete the existing pulication and create a 'New Publication'
Also could I ask a related question ..... On the destination server the
existing Subcription (Enterprise Manager\Replication\Subscriptions\....)
does not delete when a publication is deleted via Enterprise Manager. In the
past I have investiigated how to delete old subscriptions without success.
Is there a way of doing this of which you are aware?
Again thank you for your kind assistance.
Kind regards
Rob
"Hilary Cotter" wrote:
> the fastest way to fix this is to drop and recreate your subscription.
> --
> 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
> "Robert Paynter" <Robert Paynter@.discussions.microsoft.com> wrote in message
> news:AD6E580D-02D3-4722-8AA0-E33979A1F0FD@.microsoft.com...
> to
> I
>
>
|||Yes, if you script out your publication first it will be easier for you to
rebuild it.
Yes, there are two reasons why you get these orphaned subscribers, well
three actually, the third involves Paul Ibison and a big bottle of Scotch,
but he has sworn me to secrecy on this one.
If you restore an unpublished database onto a published database you can
orphan your subscribers. If you drop a pull subscription through a script on
the publisher only you can orphan them.
I can normally right click on the subscription and then delete it. If not I
manually remove the entry from mssubscription_properties.
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
"Robert Paynter" <RobertPaynter@.discussions.microsoft.com> wrote in message
news:E433EA9D-8239-4256-908C-9CA841D24DF1@.microsoft.com...
> Hi Hilary
> Many thanks for your reply. Just so I am clear in Enterprise Manager I
> would delete the existing pulication and create a 'New Publication'
> Also could I ask a related question ..... On the destination server the
> existing Subcription (Enterprise Manager\Replication\Subscriptions\....)
> does not delete when a publication is deleted via Enterprise Manager. In
the[vbcol=seagreen]
> past I have investiigated how to delete old subscriptions without success.
> Is there a way of doing this of which you are aware?
> Again thank you for your kind assistance.
> Kind regards
> Rob
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
failing[vbcol=seagreen]
Should[vbcol=seagreen]
|||Hi Hilary
Again I would like to thank you for your reply and the time you give to do
this. I shall proceed with fixing the problem as advised and we'll be able
to get the subscription database functioning again.
Again many thanks.
Kind regards
Rob
"Hilary Cotter" wrote:
> Yes, if you script out your publication first it will be easier for you to
> rebuild it.
> Yes, there are two reasons why you get these orphaned subscribers, well
> three actually, the third involves Paul Ibison and a big bottle of Scotch,
> but he has sworn me to secrecy on this one.
> If you restore an unpublished database onto a published database you can
> orphan your subscribers. If you drop a pull subscription through a script on
> the publisher only you can orphan them.
> I can normally right click on the subscription and then delete it. If not I
> manually remove the entry from mssubscription_properties.
>
> --
> 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
> "Robert Paynter" <RobertPaynter@.discussions.microsoft.com> wrote in message
> news:E433EA9D-8239-4256-908C-9CA841D24DF1@.microsoft.com...
> the
> message
> failing
> Should
>
>
transaactional replication no longer works with the 'Log Reader' failing to
start and reporting the error message "The process could not execute
'sp_repldone/sp_replcounters' on ServerName".
I have run via Enterprise Manager 'Validate Subscriptions' and
'Re-initialize All Subscriptions' but the Log Reader still fails. Should I
simply re-start the server or run sp_replrestart?
Any assistance would be greatly appreciated.
Kind regards
Robert
the fastest way to fix this is to drop and recreate your subscription.
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
"Robert Paynter" <Robert Paynter@.discussions.microsoft.com> wrote in message
news:AD6E580D-02D3-4722-8AA0-E33979A1F0FD@.microsoft.com...
> I have had to fully restored my publisher database. Since then my
> transaactional replication no longer works with the 'Log Reader' failing
to
> start and reporting the error message "The process could not execute
> 'sp_repldone/sp_replcounters' on ServerName".
> I have run via Enterprise Manager 'Validate Subscriptions' and
> 'Re-initialize All Subscriptions' but the Log Reader still fails. Should
I
> simply re-start the server or run sp_replrestart?
> Any assistance would be greatly appreciated.
> Kind regards
> Robert
|||Hi Hilary
Many thanks for your reply. Just so I am clear in Enterprise Manager I
would delete the existing pulication and create a 'New Publication'
Also could I ask a related question ..... On the destination server the
existing Subcription (Enterprise Manager\Replication\Subscriptions\....)
does not delete when a publication is deleted via Enterprise Manager. In the
past I have investiigated how to delete old subscriptions without success.
Is there a way of doing this of which you are aware?
Again thank you for your kind assistance.
Kind regards
Rob
"Hilary Cotter" wrote:
> the fastest way to fix this is to drop and recreate your subscription.
> --
> 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
> "Robert Paynter" <Robert Paynter@.discussions.microsoft.com> wrote in message
> news:AD6E580D-02D3-4722-8AA0-E33979A1F0FD@.microsoft.com...
> to
> I
>
>
|||Yes, if you script out your publication first it will be easier for you to
rebuild it.
Yes, there are two reasons why you get these orphaned subscribers, well
three actually, the third involves Paul Ibison and a big bottle of Scotch,
but he has sworn me to secrecy on this one.
If you restore an unpublished database onto a published database you can
orphan your subscribers. If you drop a pull subscription through a script on
the publisher only you can orphan them.
I can normally right click on the subscription and then delete it. If not I
manually remove the entry from mssubscription_properties.
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
"Robert Paynter" <RobertPaynter@.discussions.microsoft.com> wrote in message
news:E433EA9D-8239-4256-908C-9CA841D24DF1@.microsoft.com...
> Hi Hilary
> Many thanks for your reply. Just so I am clear in Enterprise Manager I
> would delete the existing pulication and create a 'New Publication'
> Also could I ask a related question ..... On the destination server the
> existing Subcription (Enterprise Manager\Replication\Subscriptions\....)
> does not delete when a publication is deleted via Enterprise Manager. In
the[vbcol=seagreen]
> past I have investiigated how to delete old subscriptions without success.
> Is there a way of doing this of which you are aware?
> Again thank you for your kind assistance.
> Kind regards
> Rob
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
failing[vbcol=seagreen]
Should[vbcol=seagreen]
|||Hi Hilary
Again I would like to thank you for your reply and the time you give to do
this. I shall proceed with fixing the problem as advised and we'll be able
to get the subscription database functioning again.
Again many thanks.
Kind regards
Rob
"Hilary Cotter" wrote:
> Yes, if you script out your publication first it will be easier for you to
> rebuild it.
> Yes, there are two reasons why you get these orphaned subscribers, well
> three actually, the third involves Paul Ibison and a big bottle of Scotch,
> but he has sworn me to secrecy on this one.
> If you restore an unpublished database onto a published database you can
> orphan your subscribers. If you drop a pull subscription through a script on
> the publisher only you can orphan them.
> I can normally right click on the subscription and then delete it. If not I
> manually remove the entry from mssubscription_properties.
>
> --
> 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
> "Robert Paynter" <RobertPaynter@.discussions.microsoft.com> wrote in message
> news:E433EA9D-8239-4256-908C-9CA841D24DF1@.microsoft.com...
> the
> message
> failing
> Should
>
>
Subscribe to:
Posts (Atom)