cannot deliver sp_repldropcolum schema change

Hello
As our customers use our product often offline, we implemted a sql server
2000 merge replication with msde anonymous subscribers.
With the new product release go some schemachanges, which we implemted with
sp_repladdcolumn and sp_repldropcolumn (e.g." exec sp_repldropcolumn
@.source_object = 'verlauf_ver', @.column = 'c_mandant',
@.force_reinit_subscription = 1").
I can complete the changes on the publisher without errors (only the
warning, that you can synchronize with sql 2000 and above only).
When I synchronize the subscriber, firstly it seems to work, but the it
stops and says, it cannot deliver the changes "exec sp_repldropcolumn..."
Are there any ideas?
Is it possible, there's a kind of restriction on the amount of changes?
Thanks for any help
Regards
Please can you post up the exact error message. Also you might want to
enable logging and see if that gives any more info (and post that up as well
please).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul
At the moment, I'm checking out something else.
But after that, I'll do what you ask me for. But can you tell me, how I can
enable Logging? And where to find this Log?
Thanks a lot
Aline
"Paul Ibison" wrote:

> Please can you post up the exact error message. Also you might want to
> enable logging and see if that gives any more info (and post that up as well
> please).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Hi Aline - this article explains it well:
http://support.microsoft.com/?id=312292
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul
Thanks for the Link, which was very useful. I didn't realise, I could enable
such a logging (though I've already read about it before)
So here's the statement:
exec sp_repldropcolumn @.source_object = 'c_controldef', @.column = 'mandid',
@.force_invalidate_snapshot = 1, @.force_reinit_subscription = 1
and here's the log (in German, sorry):
Microsoft SQL Server-Merge-Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Percent Complete: 0
Verbindung mit Abonnent 'winxpsp2\syseca'
Connecting to Abonnent 'winxpsp2\syseca.challenge'
Server: winxpsp2\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:53]winxpsp2\syseca.challenge: {call sp_MSgetversion }
[16.11.2006 13:55:53]winxpsp2\syseca.challenge: {?=call
sp_helpsubscription_properties (N'vistatestpc\syseca', N'challenge',
N'challenge')}
Distributor security mode: 0, login name: rai_application, password: ********.
Percent Complete: 0
Verbindung mit Verteiler 'vistatestpc\syseca'
Connecting to Verteiler 'vistatestpc\syseca.'
Server: vistatestpc\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: guest
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:53]vistatestpc\syseca.: {call sp_MSgetversion }
[16.11.2006 13:55:53]vistatestpc\syseca.: {call sp_helpdistpublisher
(N'vistatestpc\syseca') }
[16.11.2006 13:55:53]vistatestpc\syseca.distribution: select datasource,
srvid from master..sysservers where upper(srvname) =
upper(N'vistatestpc\syseca')
[16.11.2006 13:55:53]vistatestpc\syseca.distribution: {call
sp_MSadd_merge_anonymous_agent(N'0', N'challenge', N'challenge',
N'challenge', N'winxpsp2\syseca',?, 667)}
[16.11.2006 13:55:53]vistatestpc\syseca.distribution: {call
sp_MShelp_profile (2, 4, N'')}
Percent Complete: 0
Connecting to Verleger 'vistatestpc\syseca.challenge'
Initialisiert
Percent Complete: 1
Verbindung mit Verleger 'vistatestpc\syseca'
Server: vistatestpc\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:53]vistatestpc\syseca.challenge: set nocount on declare
@.dbname sysname select @.dbname = db_name() declare @.collation nvarchar(255)
select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname,
N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as
'CodePage', collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Connecting to Verleger 'vistatestpc\syseca.challenge'
Server: vistatestpc\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:54]vistatestpc\syseca.challenge: {call sp_MSgetversion }
Percent Complete: 3
Ruft Publikationsinformationen ab
Percent Complete: 4
Ruft Abonnementinformationen ab
Connecting to Abonnent 'winxpsp2\syseca.challenge'
Server: winxpsp2\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
Server: winxpsp2\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:54]vistatestpc\syseca.challenge: exec sp_datatype_info 0, 3
Percent Complete: 4
[16.11.2006 13:55:54]winxpsp2\syseca.challenge: exec sp_repldropcolumn
'[dbo].[c_controldef]', 'mandid', 1
Eine Spalte wurde zur replizierten Tabelle hinzugefügt oder aus dieser
gel?scht.
Das Schemaskript 'exec sp_repldropcolumn '[dbo].[c_controldef]', 'mandid',
1' konnte nicht an den Abonnenten weitergegeben werden.
Percent Complete: 0
Das Schemaskript 'exec sp_repldropcolumn '[dbo].[c_controldef]', 'mandid',
1' konnte nicht an den Abonnenten weitergegeben werden.
Percent Complete: 0
Category:NULL
Source: Mergereplikationsprovider
Number: -2147201001
Message: Das Schemaskript 'exec sp_repldropcolumn '[dbo].[c_controldef]',
'mandid', 1' konnte nicht an den Abonnenten weitergegeben werden.
at the end it says: The schemascript sp_repldropcolumn
'[dbo].[c_controldef]', 'mandid', 1' couldn't be delivered to the subscriber
Unfortunately it doesn't tell my why.
There are a lot of different changes before which work fine. And afterwards
are also 46 sp_repldropcolumn.
Strangely, if I comment out these 47 sp_repldropcolumn's, update the
publisher, synchronize the subscriber, and then update the publisher with
these 47 47 sp_repldropcolumn's and synch the subscriber, it works...
As the product is install at more than 100 customers, I need something
automatic.
Any further help?
Thanks
Aline
|||My guess is that something references the column on the subscriber. Please
take a look at this article and see if it applies:
http://support.microsoft.com/kb/816755
One way to test this would be to restore a backup of the subscriber database
to another database nema and try to drop the column directly and hopefully
you wont be able to and will get a message referring to the constraint that
prevents the drop. If you can get this, you can drop the constraint using
sp_addscriptexec (assuming it is nothing to do with a filter).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul
It still doesn't work correctly. I've got the following error, when adding a
script:
Script:
....
exec sp_addscriptexec @.publication = N'challenge',
@.scriptfile='c:\programme\syseca\DB-Scripts\Replikation\ReplicationScripts\DropConstra ints.sql'
exec sp_repldropcolumn @.source_object = 'c_controldef', @.column = 'mandid',
@.force_invalidate_snapshot = 1, @.force_reinit_subscription = 1
...
error:
xpsql.cpp: Fehler 997 von GetProxyAccount in Zeile 604
Meldung 21330, Ebene 16, Status 1, Server VISTATESTPC\SYSECA, Prozedur
sp_MScopyscriptfile, Zeile 31
Fehler beim Erstellen eines Unterverzeichnisses unter dem
Replikationsarbeitsverzeichnis. (md
"\\vistatestpc\snapshot\20061116210031497")
--> error while building subdirectory...
I think, the path should be something like this:
"\\vistatestpc\snapshot\unc\SERVERNAME$INSTANCENAM E_PUBLICATION_DATABASE\20061116210031497"
Shouldn't the distributor handle this? By the way: Distributor and Publisher
are installed on the same server and dbms.
what can I do?
Thanks a lot
Aline
|||I'm not familiar with the normal log messages, but it looks like you have
configured a proxy account. Is the proxy account that the merge agent is
using in the Local admins group on the server? (or at least set up with the
necessary rights)? (it's generally much easier to have the merge agent run
as the sql server agent although SQL Serever 2005 uses these proxies by
default) Also, is there enough space on the server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||sql server agent runs as a local administrator. so I think it has all
necessary rights.
what confuses me is, why does it try to make a new folder and why are these
typical "unc\servername..." missing in the path?
|||I'll take a proper look tomorrow when I get back to work if nobody's solved
it by then.
Before that, can you confirm is you are using a proxy account for
non-sysadmins or not, and that the account (if you are) has the necessary
rights.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .