Invisible rows after failed syncronization

Publisher: SQL Server 2000 SP4
Subscriber: SQL Server 2005 Mobile Edition

Sometimes, after a failed merge replication(due to communication error) some rows on the subscriber became invisible for the publisher.

I've reproduced a case:

[START Short Version]
All rows inserted between a comm failed sync and a fine sync became invisible.
This seems to be caused because the subscriber keeps generating rows with the same generation number and the publisher doesn't look for this generation anymore.
[END Short Version]

[START Detailed case]
STEP 0: INITIAL STATE
Publisher
Orders Table
EMPTY

Subscriber
NO DATABASE

STEP 1: After FIRST SYNC and INSERTING 2 Orders

Publisher

Orders Table

EMPTY

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 81
000002 4 4 81

STEP 2: After SECOND SYNC

Publisher

Orders Table

OrderId

000001

000002

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

STEP 3: INSERT ANOTHER ORDER

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

000003 6 6 81

STEP 4: After THIRD SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table

OrderId

000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81
Note: Publisher has the row but subscriber keeps the state in 81.

STEP 5: INSERT ANOTHER ORDER (BEFORE A GOOD SYNC)
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81
Note: Orders 000003 and 000004 have the same system info

STEP 6: After FOURTH SYNC
Publisher
Orders Table

OrderId
000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81

Note: Even with a fine merge process Publisher didn't get the 000004 row and the Subscriber didn't update the __sysMC

STEP 7: INSERT ANOTHER ORDER

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 81

000004 6 6 81

000005 8 8 81

STEP 8: After FIFTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81

000004 6 6 81

000002 8 8 80

Note: Data from generation 8 merge correctly, but data from generation 6 keeps invisible to publisher.
STEP 9: DUMMY UPDATE ON GEN 6 ROWS
Action: I made a non relevant update to force the Subscriber update the generation.
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 10 81
000004 6 10 81
000005 8 8 80

Note: sysCG get its value updated correctly.

STEP 10: After SIXTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000004

000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 80

000004 6 6 80

000002 8 8 80

Note: Data merges correctly.
[END Detailed case]

I could code a system that checks all the publication tables and updates all the last generation rows in case of communication error but i would really like to avoid doing it.

I don't know if I'm missing something or if this is a bug or a known issue.

This is a big problem for me because communication errors on cellular phone based connection are quite common and users keep working even if there's an error.

Any comment will be appreciated.Finally, I've modified my replication process solve this manually.

On a normal case (no previous error):
1. I get the current generation (__sysMergeSubscriptions.LastUploadedGen+1)
2. Launch a normal sync.
3. If it fails I save the failed generation for using it on next sync.

On the previous error case:
1. Do a normal sync (to get a valid __sysMergeSubscriptions.LastUploadedGen)
2. Do a dummy update on all rows (from all published tables (from __sysMergeArticles)) that __sysIG or __sysCG equals the wrong generation number.
3. Do a normal sync to send all the invisible rows to the publisher.

I don't like my own solution, but that's the only way I have to keep the system working as I haven't find any reference to this problem anywhere.|||

Hey MeZKaL,

I just have few questions to narrow downt he problem point in the whole path of sync components.

In the same above scenario, can you please also let me know the contents of __sysMergeSubscription and
__sysMergeSubscriptionProperties table for that subscription in each of the above STEPs (esp. Steps 4 to 6)?

Basically, I just want to know which of the following is true (after a failed sync)

1) Client did not enumerate the changes and hence did not send the changes

2) Client has enumerated the changes, sent the changes but metadata does not say its new change

3) Client has enumerated, sent the changes, metadata also says its a new change, but publisher ignores it for some internal
reasons like publisher metadata says its a false change ...etc

Thanks,

Laxmi

|||I repeated the case saving a copy of the mobile db for each step.

There are no changes on __sysMergeSubscriptionProperties so I will put it just one time.

__sysMergeSubscriptionProperties
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
Distributor
DistributorAddress
DistributorNetwork: 0
DistributorSecurityMode: 1
DistributorLogin
DistributorPassword
ExchangeType: 3
InternetLogin: domain\user
InternetURL: URL
InternetPassword: *******
LoginTimeout: 15
ProfileName: DEFAULT
Publisher: SERVER\INSTANCE
PublisherAddress
PublisherNetwork: 0
PublisherDatabase: DBName
PublisherSecurityMode: 1
PublisherLogin: domain\user
PublisherPassword: *******
Publication: PubName
QueryTimeout: 300
Validate: 0
HostName
Subscriber: domain\user
InternetProxyServer
InternetProxyLogin
InternetProxyPassword
ConnectionRetryTimeout: 120
CompressionLevel: 1
ConnectionManager: 0
SnapshotTransferType: 99

Many of the field value from __sysMergeSubscriptions didn't change during the process.

__sysMergeSubscriptions
Publisher: server\instance
PublisherDatabase: DBName
Publication: PubName
PublicationId: 03e497f6-83b6-4de3-87e3-e97835880c94
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
SyncPartnerId: 751a4dfe-2eba-7a4a-b11a-35beec9a8bf3
SyncAnchorId: 00000000-0000-0000-0000-000000000000
SchemaVersion: 481
SchemaVersionId: 00000000-0000-0000-0000-000000000000
ReplNick (casted to int): -312916250
Retention: 0
MachineId: 374677b5-b002-d9f5-d4e4-4628538f2ccd
PathName: Path\DB.sdf
ReinitRequested: 0
ReplProp: NULL
HostName:
ValidationHostName: 0
ReplicaState: 00000000-0000-0000-0000-000000000000
LastCleanupDate: 15/11/2006 16:30
ReconcilerVersion: 8
Subscriber: domain\user

[START Detailed case]

STEP 1: After FIRST SYNC
Publisher
Orders Table
EMPTY

Subscriber
Orders Table
EMPTY

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 2: After INSERTING 2 Orders
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 3: After SECOND SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 4: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 5: After THIRD SYNC
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 6: DUMMY UPDATE
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 81
57001 4 6 81
57002 4 6 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 7: After FOURTH SYNC
Publisher
Orders Table
OrderId
57000
57001
57002

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 80
57001 4 6 80
57002 4 6 80

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5213 7 15/11/2006 17:35 72
SentGen80Id
SentGen80Id: 4171a971-b963-a541-300a-0b1602902e39

[__sysMergeSubscriptions DETAIL]
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
step1 4977 0 15/11/2006 16:30 70
step2 4977 0 15/11/2006 16:30 70
step3 4977 0 15/11/2006 16:30 70
step4 4977 0 15/11/2006 16:30 70
step5 5095 5 15/11/2006 17:22 71
step6 5095 5 15/11/2006 17:22 71
step7 5213 7 15/11/2006 17:35 72

SentGen80Id
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
e5ff3df6-195c-3145-273e-86784ace7e16
e5ff3df6-195c-3145-273e-86784ace7e16
4171a971-b963-a541-300a-0b1602902e39|||Can you open a case with CSS please for this? That way we can get a better handle on this in a timely manner.|||My workaround runs pretty well and I don't have time now for spending hours and hours crossing mails and test results with a support service.

If anyone can help with the previous data, it'll be welcomed but I won't spent more time testing and logging for this issue. I've lots of work and not many time.

Invalid SQL when @centralized_conflicts='false'

The further I delive into SQL Server replication, the more apparent it
becomes what a complete and utter lash-up the whole "technology" is.
Has anyone ever encountered this particular problem? I have transactional
push replication in use, with a publication with
@.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
both publisher and subscriber).
However, when a conflict occurs, SQL Agent falls over with an error
"Incorrect syntax near the keyword 'null'". On further investigation, the
SQL generated by Agent for inserting the conflict into the subscriber's
conflict table is missing a comma in the VALUES list!!
It seems incredible to me how something as important as data replication can
have been implemented using nothing more than a whole load of (pretty poorly
written) triggers and stored procedures that may or may not be restored when
you backup and restore a database, that cause replication to fail when you
restore a published database on the subscriber unless you first run the
pathetic sp_scriptpublicationcustomprocs to generate the world's longest
nvarchars containing dynamically generated SQL, and which cause utterly
meaningless errors about invalid SQL to be logged to the event log.
The rant is over now, but the serious question remains - is anyone out there
actually using SQL Server Replication within a serious Enterprise class
application or should I just accept that it really is the half-hearted,
conceived-at-the-last-minute, hack that it appears to be?It works well for my clients. I have worked on replication projects with
several hundred subscribers.On one implementation we had a publisher
replicating 2500 transactions per sec! Barnes and Noble runs an
implementation with over 600 subscribers. Ticket Master uses transactional
replication and is the largest implementation of transactional replication
or was last time I spoke with some of the MS folks. In one of the channel 9
videos Bren Newman talks about repro'ing a topology with over 200. Its done,
its stable, it offers high performance.
I am curious though as to what method of transactional replication you are
using? There is a problem with the GUI in transactional replication methods.
IIRC it seems to indicate that you can log conflicts centrally or locally;
whereas you can only log them centrally. Perhaps this is what you are
encountering. What sp and version of SQL (7 or 2000) are you running?
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
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
> The further I delive into SQL Server replication, the more apparent it
> becomes what a complete and utter lash-up the whole "technology" is.
> Has anyone ever encountered this particular problem? I have transactional
> push replication in use, with a publication with
> @.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
> both publisher and subscriber).
> However, when a conflict occurs, SQL Agent falls over with an error
> "Incorrect syntax near the keyword 'null'". On further investigation, the
> SQL generated by Agent for inserting the conflict into the subscriber's
> conflict table is missing a comma in the VALUES list!!
> It seems incredible to me how something as important as data replication
> can have been implemented using nothing more than a whole load of (pretty
> poorly written) triggers and stored procedures that may or may not be
> restored when you backup and restore a database, that cause replication to
> fail when you restore a published database on the subscriber unless you
> first run the pathetic sp_scriptpublicationcustomprocs to generate the
> world's longest nvarchars containing dynamically generated SQL, and which
> cause utterly meaningless errors about invalid SQL to be logged to the
> event log.
> The rant is over now, but the serious question remains - is anyone out
> there actually using SQL Server Replication within a serious Enterprise
> class application or should I just accept that it really is the
> half-hearted, conceived-at-the-last-minute, hack that it appears to be?
>|||Thanks for the info, Hilary. It's interesting to see how and where it is
being used for real.
The number of transactions and subscribers doesn't surprise me - I've
thrashed it myself with good throughput results. Neither am I surprised, I
guess, that some companies have achieved results with it... but it doesn't
change my opinion that it's quite clearly an afterthought that is
implemented by clear "hacks", and which make it very messy to deal with. I
guess that with enough time, patience and frustration, you could get
TicketMaster running on a million networked Commodore 64s with BASIC as the
programming language. But that wouldn't make it an elegant, sensible or
extensible solution.
I'm using transactional replication with a single queued updating
subscriber. sp_addpublication allows you to specify @.centralized_conflicts
= false, which is supposed to ensure conflicts are logged at both publisher
and subscriber (but which causes the error):
http://msdn.microsoft.com/library/d... />
l_4s32.asp .
I'm using SQL Server 2000 SP4 (Microsoft SQL Server 2000 - 8.00.2039 (Intel
X86)).
Cheers,
Chris.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OOT0%232%23zFHA.3124@.TK2MSFTNGP12.phx.gbl...
> It works well for my clients. I have worked on replication projects with
> several hundred subscribers.On one implementation we had a publisher
> replicating 2500 transactions per sec! Barnes and Noble runs an
> implementation with over 600 subscribers. Ticket Master uses transactional
> replication and is the largest implementation of transactional replication
> or was last time I spoke with some of the MS folks. In one of the channel
> 9 videos Bren Newman talks about repro'ing a topology with over 200. Its
> done, its stable, it offers high performance.
> I am curious though as to what method of transactional replication you are
> using? There is a problem with the GUI in transactional replication
> methods. IIRC it seems to indicate that you can log conflicts centrally or
> locally; whereas you can only log them centrally. Perhaps this is what you
> are encountering. What sp and version of SQL (7 or 2000) are you running?
> --
> 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
> "Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
> news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
>

Invalid SQL when @centralized_conflicts='false'

The further I delive into SQL Server replication, the more apparent it
becomes what a complete and utter lash-up the whole "technology" is.
Has anyone ever encountered this particular problem? I have transactional
push replication in use, with a publication with
@.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
both publisher and subscriber).
However, when a conflict occurs, SQL Agent falls over with an error
"Incorrect syntax near the keyword 'null'". On further investigation, the
SQL generated by Agent for inserting the conflict into the subscriber's
conflict table is missing a comma in the VALUES list!!
It seems incredible to me how something as important as data replication can
have been implemented using nothing more than a whole load of (pretty poorly
written) triggers and stored procedures that may or may not be restored when
you backup and restore a database, that cause replication to fail when you
restore a published database on the subscriber unless you first run the
pathetic sp_scriptpublicationcustomprocs to generate the world's longest
nvarchars containing dynamically generated SQL, and which cause utterly
meaningless errors about invalid SQL to be logged to the event log.
The rant is over now, but the serious question remains - is anyone out there
actually using SQL Server Replication within a serious Enterprise class
application or should I just accept that it really is the half-hearted,
conceived-at-the-last-minute, hack that it appears to be?
It works well for my clients. I have worked on replication projects with
several hundred subscribers.On one implementation we had a publisher
replicating 2500 transactions per sec! Barnes and Noble runs an
implementation with over 600 subscribers. Ticket Master uses transactional
replication and is the largest implementation of transactional replication
or was last time I spoke with some of the MS folks. In one of the channel 9
videos Bren Newman talks about repro'ing a topology with over 200. Its done,
its stable, it offers high performance.
I am curious though as to what method of transactional replication you are
using? There is a problem with the GUI in transactional replication methods.
IIRC it seems to indicate that you can log conflicts centrally or locally;
whereas you can only log them centrally. Perhaps this is what you are
encountering. What sp and version of SQL (7 or 2000) are you running?
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
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
> The further I delive into SQL Server replication, the more apparent it
> becomes what a complete and utter lash-up the whole "technology" is.
> Has anyone ever encountered this particular problem? I have transactional
> push replication in use, with a publication with
> @.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
> both publisher and subscriber).
> However, when a conflict occurs, SQL Agent falls over with an error
> "Incorrect syntax near the keyword 'null'". On further investigation, the
> SQL generated by Agent for inserting the conflict into the subscriber's
> conflict table is missing a comma in the VALUES list!!
> It seems incredible to me how something as important as data replication
> can have been implemented using nothing more than a whole load of (pretty
> poorly written) triggers and stored procedures that may or may not be
> restored when you backup and restore a database, that cause replication to
> fail when you restore a published database on the subscriber unless you
> first run the pathetic sp_scriptpublicationcustomprocs to generate the
> world's longest nvarchars containing dynamically generated SQL, and which
> cause utterly meaningless errors about invalid SQL to be logged to the
> event log.
> The rant is over now, but the serious question remains - is anyone out
> there actually using SQL Server Replication within a serious Enterprise
> class application or should I just accept that it really is the
> half-hearted, conceived-at-the-last-minute, hack that it appears to be?
>
|||Thanks for the info, Hilary. It's interesting to see how and where it is
being used for real.
The number of transactions and subscribers doesn't surprise me - I've
thrashed it myself with good throughput results. Neither am I surprised, I
guess, that some companies have achieved results with it... but it doesn't
change my opinion that it's quite clearly an afterthought that is
implemented by clear "hacks", and which make it very messy to deal with. I
guess that with enough time, patience and frustration, you could get
TicketMaster running on a million networked Commodore 64s with BASIC as the
programming language. But that wouldn't make it an elegant, sensible or
extensible solution.
I'm using transactional replication with a single queued updating
subscriber. sp_addpublication allows you to specify @.centralized_conflicts
= false, which is supposed to ensure conflicts are logged at both publisher
and subscriber (but which causes the error):
http://msdn.microsoft.com/library/de..._repl_4s32.asp .
I'm using SQL Server 2000 SP4 (Microsoft SQL Server 2000 - 8.00.2039 (Intel
X86)).
Cheers,
Chris.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OOT0%232%23zFHA.3124@.TK2MSFTNGP12.phx.gbl...
> It works well for my clients. I have worked on replication projects with
> several hundred subscribers.On one implementation we had a publisher
> replicating 2500 transactions per sec! Barnes and Noble runs an
> implementation with over 600 subscribers. Ticket Master uses transactional
> replication and is the largest implementation of transactional replication
> or was last time I spoke with some of the MS folks. In one of the channel
> 9 videos Bren Newman talks about repro'ing a topology with over 200. Its
> done, its stable, it offers high performance.
> I am curious though as to what method of transactional replication you are
> using? There is a problem with the GUI in transactional replication
> methods. IIRC it seems to indicate that you can log conflicts centrally or
> locally; whereas you can only log them centrally. Perhaps this is what you
> are encountering. What sp and version of SQL (7 or 2000) are you running?
> --
> 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
> "Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
> news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
>
sql

Invalid object name 'syspublications'

Before reinstalling sql server and restoring old system databases I
forgot to delete and replication that was no longer needed. Now,
Expired Subscriptions Clean Up job fails with the following error:
Error 208: Invalid object name 'syspublications'. I don't need this
replication and would like to delete that completed but everytime I
try I get the same error message.
Please advise how can I delete all manually.
Thank you,
T.
Locate the problem database. do this sp_replicationdboption 'Problem
Database','Publish','false'
http://www.zetainteractive.com - Shift Happens!
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
"T" <T@.discussions.microsoft.com> wrote in message
news:20736A29-69AF-4B7E-8BB8-C5B1BF5313CF@.microsoft.com...
> Before reinstalling sql server and restoring old system databases I
> forgot to delete and replication that was no longer needed. Now,
> Expired Subscriptions Clean Up job fails with the following error:
> Error 208: Invalid object name 'syspublications'. I don't need this
> replication and would like to delete that completed but everytime I
> try I get the same error message.
> Please advise how can I delete all manually.
> Thank you,
> T.
|||This is what I get:
Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
"Hilary Cotter" wrote:

> Locate the problem database. do this sp_replicationdboption 'Problem
> Database','Publish','false'
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "T" <T@.discussions.microsoft.com> wrote in message
> news:20736A29-69AF-4B7E-8BB8-C5B1BF5313CF@.microsoft.com...
>
>
|||is this table there? if not run this script in the problem database.
We may need to go back and forth to add the necessary objects a few times.
CREATE TABLE [dbo].[syspublications](
[description] [nvarchar](255) NULL,
[name] [sysname] NOT NULL,
[pubid] [int] IDENTITY(1,1) NOT NULL,
[repl_freq] [tinyint] NOT NULL,
[status] [tinyint] NOT NULL,
[sync_method] [tinyint] NOT NULL,
[snapshot_jobid] [binary](16) NULL,
[independent_agent] [bit] NOT NULL,
[immediate_sync] [bit] NOT NULL,
[enabled_for_internet] [bit] NOT NULL,
[allow_push] [bit] NOT NULL,
[allow_pull] [bit] NOT NULL,
[allow_anonymous] [bit] NOT NULL,
[immediate_sync_ready] [bit] NOT NULL,
[allow_sync_tran] [bit] NOT NULL,
[autogen_sync_procs] [bit] NOT NULL,
[retention] [int] NULL,
[allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
[snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
[alt_snapshot_folder] [nvarchar](255) NULL,
[pre_snapshot_script] [nvarchar](255) NULL,
[post_snapshot_script] [nvarchar](255) NULL,
[compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
[ftp_address] [sysname] NULL,
[ftp_port] [int] NOT NULL DEFAULT ((21)),
[ftp_subdirectory] [nvarchar](255) NULL,
[ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
[ftp_password] [nvarchar](524) NULL,
[allow_dts] [bit] NOT NULL DEFAULT ((0)),
[allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
[centralized_conflicts] [bit] NULL,
[conflict_retention] [int] NULL,
[conflict_policy] [int] NULL,
[queue_type] [int] NULL,
[ad_guidname] [sysname] NULL,
[backward_comp_level] [int] NOT NULL DEFAULT ((10)),
[allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
[min_autonosync_lsn] [binary](10) NULL,
[replicate_ddl] [int] NULL DEFAULT ((1)),
[options] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
http://www.zetainteractive.com - Shift Happens!
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
"T" <T@.discussions.microsoft.com> wrote in message
news:1C8FA2D9-4811-4975-81C8-EE744C25C7D4@.microsoft.com...[vbcol=seagreen]
> This is what I get:
> Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
> Invalid object name 'syssubscriptions'.
>
> "Hilary Cotter" wrote:
|||Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
"Hilary Cotter" wrote:

> is this table there? if not run this script in the problem database.
> We may need to go back and forth to add the necessary objects a few times.
> CREATE TABLE [dbo].[syspublications](
> [description] [nvarchar](255) NULL,
> [name] [sysname] NOT NULL,
> [pubid] [int] IDENTITY(1,1) NOT NULL,
> [repl_freq] [tinyint] NOT NULL,
> [status] [tinyint] NOT NULL,
> [sync_method] [tinyint] NOT NULL,
> [snapshot_jobid] [binary](16) NULL,
> [independent_agent] [bit] NOT NULL,
> [immediate_sync] [bit] NOT NULL,
> [enabled_for_internet] [bit] NOT NULL,
> [allow_push] [bit] NOT NULL,
> [allow_pull] [bit] NOT NULL,
> [allow_anonymous] [bit] NOT NULL,
> [immediate_sync_ready] [bit] NOT NULL,
> [allow_sync_tran] [bit] NOT NULL,
> [autogen_sync_procs] [bit] NOT NULL,
> [retention] [int] NULL,
> [allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
> [snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
> [alt_snapshot_folder] [nvarchar](255) NULL,
> [pre_snapshot_script] [nvarchar](255) NULL,
> [post_snapshot_script] [nvarchar](255) NULL,
> [compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
> [ftp_address] [sysname] NULL,
> [ftp_port] [int] NOT NULL DEFAULT ((21)),
> [ftp_subdirectory] [nvarchar](255) NULL,
> [ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
> [ftp_password] [nvarchar](524) NULL,
> [allow_dts] [bit] NOT NULL DEFAULT ((0)),
> [allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
> [centralized_conflicts] [bit] NULL,
> [conflict_retention] [int] NULL,
> [conflict_policy] [int] NULL,
> [queue_type] [int] NULL,
> [ad_guidname] [sysname] NULL,
> [backward_comp_level] [int] NOT NULL DEFAULT ((10)),
> [allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
> [min_autonosync_lsn] [binary](10) NULL,
> [replicate_ddl] [int] NULL DEFAULT ((1)),
> [options] [int] NOT NULL DEFAULT ((0))
> ) ON [PRIMARY]
>
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "T" <T@.discussions.microsoft.com> wrote in message
> news:1C8FA2D9-4811-4975-81C8-EE744C25C7D4@.microsoft.com...
>
>
sql

Invalid object name 'sysmergearticles'.

Hi,
When trying to push a subscription I am getting the above error. It's a 2005
database upgraded from 2000.
I had disabled replication on the server before moving to 2005. The db has
some 2000 tables, and recently I dropped many unwanted tables usin T-SQL and
a table with 'useful tables' entries.
When I looked at the original database (backup before cleaning) , I could c
the 'sysmergearticles' table there. The [sysmergearticles] and
[sysmergepublications] tables are there with data in it.
[sysmergesubscriptions] table has no entries.
I tried using sp_replicationdboption with False and the table gets dropped ,
but then it doesn't even allow to create publication. Gives error :
Invalid column name 'column 1'
gives a long list of such columns.
Any suggestions?
So, you completely disabled publishing on 2000 before migrating to 2005
correct? Do you have remote connections enabled on the 2005 instance? Does
publishing configure properly?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:CDAAD96B-4854-4B7B-89F1-3CA648DD9BF3@.microsoft.com...
> Hi,
> When trying to push a subscription I am getting the above error. It's a
> 2005
> database upgraded from 2000.
> I had disabled replication on the server before moving to 2005. The db has
> some 2000 tables, and recently I dropped many unwanted tables usin T-SQL
> and
> a table with 'useful tables' entries.
> When I looked at the original database (backup before cleaning) , I could
> c
> the 'sysmergearticles' table there. The [sysmergearticles] and
> [sysmergepublications] tables are there with data in it.
> [sysmergesubscriptions] table has no entries.
> I tried using sp_replicationdboption with False and the table gets dropped
> ,
> but then it doesn't even allow to create publication. Gives error :
> Invalid column name 'column 1'
> gives a long list of such columns.
> Any suggestions?
>

Invalid object name 'ctsv_F9F22D34FACE4A1BA4D2B061ADBEE5C1'

Hi,
I've a replication in a SQL Server 2000 to syncronize with a SQL CE 2.0.
When I try to update a row, I get this error: Invalid object name
'ctsv_F9F22D34FACE4A1BA4D2B061ADBEE5C1'
I get this error if I try to modify a row from enterprise manager too!
Can you help me please?
Thanks!
your merge replication metadata is out of sync. Script out your publication,
drop your publication, and then recreate it from script.
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
"Andrea Grandi" <andrea@.nospam.com> wrote in message
news:uytKvf$TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've a replication in a SQL Server 2000 to syncronize with a SQL CE 2.0.
> When I try to update a row, I get this error: Invalid object name
> 'ctsv_F9F22D34FACE4A1BA4D2B061ADBEE5C1'
> I get this error if I try to modify a row from enterprise manager too!
> Can you help me please?
> Thanks!
|||Hi,

> your merge replication metadata is out of sync. Script out your publication,
> drop your publication, and then recreate it from script.
wich script? Can I drop/create from Enterprise Manager?
Anyway, now I've another problem... I exported only the 4 tables I was
interested in to another instance. I dropped the whole database and
re-imported the 4 tables. Now I cannot write on those tables
I tried to remove the rowguid column but I get this error "rowguid
column is not valid". I know it's not valid, I removed it!
Is there a way to fix my database?
Thanks!
|||Most probably some artefacts are left on you tables, such as merge replication triggers. They try to write to replication views, but views are not existing..
If triggers are not there, then we should think about something else..
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Andrea Grandi" <andrea@.nospam.com> wrote in message news:%23EqCibBUFHA.3076@.TK2MSFTNGP12.phx.gbl...
Hi,

> your merge replication metadata is out of sync. Script out your publication,
> drop your publication, and then recreate it from script.
wich script? Can I drop/create from Enterprise Manager?
Anyway, now I've another problem... I exported only the 4 tables I was
interested in to another instance. I dropped the whole database and
re-imported the 4 tables. Now I cannot write on those tables
I tried to remove the rowguid column but I get this error "rowguid
column is not valid". I know it's not valid, I removed it!
Is there a way to fix my database?
Thanks!

Invalid Object Name

I have merge replication setup on MY SLQ server 2000 service pack 2 server.

I was having no issue with replication for the past three month however now I am getting this error when I try to update a record in the database.

In\valid Object Nmae 'ctsv_.....'

I am not sure what is wong with it. Any help or suggestion.

Thanks

Tanweer

you have to let us know what changes were made to your publication. Do you know if the object does in fact exist?|||

No structure changes were made however our system regenerate sanpshot weekly should I turned it off.

Thanks

Tanweer

|||If this worked previously, but not now, something must have changed. Either that or you should describe your scenario in more detail. Does the object exist in question exist?

Invalid Object Name

I have merge replication setup on MY SLQ server 2000 service pack 2 server.

I was having no issue with replication for the past three month however now I am getting this error when I try to update a record in the database.

In\valid Object Nmae 'ctsv_.....'

I am not sure what is wong with it. Any help or suggestion.

Thanks

Tanweer

you have to let us know what changes were made to your publication. Do you know if the object does in fact exist?|||

No structure changes were made however our system regenerate sanpshot weekly should I turned it off.

Thanks

Tanweer

|||If this worked previously, but not now, something must have changed. Either that or you should describe your scenario in more detail. Does the object exist in question exist?sql

Invalid Descriptor Index problem

I've successfully set up replication on two servers, but the third one
is giving me problems. Naturally, the first two were development test
servers and the last one is the customer's server. It's a Win2000
server, with SP4. The SQL Server version is 8.00.760 (SP3). I
upgraded MDAC last night to 2.8, so the SQL Server ODBC version is
2000.85.1022.00.
I am trying to set up transactional publications between databases.
When they initialize--before the first transaction can run--I get the
Invalid Descriptor Index error. If anyone can help, please do!
(Please reply to the group, so that all who search may benefit from
your wisdom.)
I ran distrib from the command line with OutputVerboseLevel set to 2.
The command I used was:
>distrib -Publisher SQL1S -PublisherDB B_DB_Copy -Subscriber SQL1S
>-OutputVerboseLevel 2 -DistributorLogin sa -DistributorPassword
><password_removed> -SubscriberDB RC_DB_Copy -SubscriberLogin sa
>-SubscriberPassword <password_removed> -Distributor SQL1S
>-SubscriptionType 0 -SubscriptionTableName reg_geo_cfg
>-Publication B_DB_Copy
Here's the result (sanitized so the customer won't get mad):
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Connecting to Distributor 'SQL1S'
Connecting to Distributor 'SQL1S.'
[6/10/2004 10:14:50 PM]SQL1S.: exec sp_helpdistpublisher N'SQL1S'
[6/10/2004 10:14:50 PM]SQL1S.distribution: select @.@.SERVERNAME
Server: SQL1S
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
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
[6/10/2004 10:14:50 PM]SQL1S.distribution: execute sp_server_info 18
ANSI codepage: 1
[6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'SQL1S')
[6/10/2004 10:14:50 PM]SQL1S.distribution: {?=call
sp_MShelp_subscriber_info (N'SQL1S', N'SQL1S')}
Subscriber security mode: 0, login name: sa.
[6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'SQL1S')
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MShelp_distribution_agentid(0, N'B_DB_Copy', N'B_DB_Copy', 0,
N'RC_DB_Copy', 0)}
Agent message code 20046. Invalid Descriptor Index
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1,
0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 3,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent
SQL1S-BDBCopy-B_DB_Copy-SQL1S-1 failed. Invalid Descriptor
Index[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_repl_alert(3, 1, 3, 14151, ?, -1, N'SQL1S', N'B_DB_Copy',
N'SQL1S', N'RC_DB_Copy', ?)}
ErrorId = 3, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_repl_error(3, 0, 4, ?, N'S1002', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'SQL1S'
The process finished. Use CTRL+C to close this window.
there have been reports that this problem has been solved by reapplying the
latest sp (sp3a).
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"KenRobertson" <google.nospam.roberken@.spamgourmet.com> wrote in message
news:4c25a332.0406110528.5bfd7b97@.posting.google.c om...
> I've successfully set up replication on two servers, but the third one
> is giving me problems. Naturally, the first two were development test
> servers and the last one is the customer's server. It's a Win2000
> server, with SP4. The SQL Server version is 8.00.760 (SP3). I
> upgraded MDAC last night to 2.8, so the SQL Server ODBC version is
> 2000.85.1022.00.
> I am trying to set up transactional publications between databases.
> When they initialize--before the first transaction can run--I get the
> Invalid Descriptor Index error. If anyone can help, please do!
> (Please reply to the group, so that all who search may benefit from
> your wisdom.)
> I ran distrib from the command line with OutputVerboseLevel set to 2.
> The command I used was:
> Here's the result (sanitized so the customer won't get mad):
> Microsoft SQL Server Distribution Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Connecting to Distributor 'SQL1S'
> Connecting to Distributor 'SQL1S.'
> [6/10/2004 10:14:50 PM]SQL1S.: exec sp_helpdistpublisher N'SQL1S'
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select @.@.SERVERNAME
> Server: SQL1S
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> 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
> [6/10/2004 10:14:50 PM]SQL1S.distribution: execute sp_server_info 18
> ANSI codepage: 1
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
> from master..sysservers where upper(srvname) = upper(N'SQL1S')
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {?=call
> sp_MShelp_subscriber_info (N'SQL1S', N'SQL1S')}
> Subscriber security mode: 0, login name: sa.
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
> from master..sysservers where upper(srvname) = upper(N'SQL1S')
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MShelp_distribution_agentid(0, N'B_DB_Copy', N'B_DB_Copy', 0,
> N'RC_DB_Copy', 0)}
> Agent message code 20046. Invalid Descriptor Index
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1,
> 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 3,
> Transaction Seqno = 0000000000000000000000000000, Command ID = -1
> Message: Replication-Replication Distribution Subsystem: agent
> SQL1S-BDBCopy-B_DB_Copy-SQL1S-1 failed. Invalid Descriptor
> Index[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_repl_alert(3, 1, 3, 14151, ?, -1, N'SQL1S', N'B_DB_Copy',
> N'SQL1S', N'RC_DB_Copy', ?)}
> ErrorId = 3, SourceTypeId = 4
> ErrorCode = 'S1002'
> ErrorText = 'Invalid Descriptor Index'
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_repl_error(3, 0, 4, ?, N'S1002', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1002
> Message: Invalid Descriptor Index
> Disconnecting from Distributor History 'SQL1S'
>
> The process finished. Use CTRL+C to close this window.
|||Reapplying the service pack worked. Thank you for your help!!
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<uO#U0aWUEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> there have been reports that this problem has been solved by reapplying the
> latest sp (sp3a).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "KenRobertson" <google.nospam.roberken@.spamgourmet.com> wrote in message
> news:4c25a332.0406110528.5bfd7b97@.posting.google.c om...

Invalid Descriptor Index error

I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )

Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly.

can you cut/paste the entire agent error output?|||

The distribution Job fails with this message " Invalid Descriptor Index. The step failed."

There are no other error messages within any of the logs. Have re-applied replication over 12 times now.

|||

I am having the same issue and wondered if you had found out what was causing the problem. In my case it is a brand new publication but I have built the same one on another server without this error. Thanks

|||

Do you know if the distrib.exe process was able to start at all when you start the SQL Server Agent job? (May be tricky to find out from taskmgr.exe...) If possible, can you manually run the distrib.exe executable using the command-line from msdb..sysjobsteps with -OutputVerboseLevel 2 and post the (sanitized) output here? Thanks.

-Raymond

|||

Ah cool ! I looked at the job and thought "what runs this - I should get to the command line"

Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation

Connecting to Subscriber 'MyServer'
Connecting to Subscriber 'MyServer.ServerAdmin'

Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
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

[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: {?=call sp_helpsubscription_properties (N'MyServer', N'SouthWind', N'')}
Distributor security mode: 1, login name: sa, password: ********.
alternate snapshot folder: .working directory: .use ftp?: 0.
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
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

Connecting to Distributor 'MyServer'
Connecting to Distributor 'MyServer.'
[10/9/2006 9:38:46 AM]MyServer.: exec sp_helpdistpublisher N'MyServer'
[10/9/2006 9:38:46 AM]MyServer.distribution: select @.@.SERVERNAME

Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
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

[10/9/2006 9:38:46 AM]MyServer.distribution: execute sp_server_info 18

ANSI codepage: 1
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MShelp_distribution_agentid(0, N'SouthWind', NULL, 0, N'ServerAdmin', 1)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 2,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent MyServer-SouthWind-MyServer-1 failed. Invalid Descriptor Index[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_repl_alert(3, 1, 2, 14151, ?, -1, N'MyServer', N'SouthWind', N'MyServer', N'ServerAdmin', ?)}
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: exec dbo.sp_MSupdatelastsyncinfo N'MyServer',N'SouthWind', N'', 1, 6, N'Invalid Descriptor Index'
Disconnecting from Subscriber 'MyServer'
Disconnecting from Distributor History 'MyServer'

|||

Here is the results I am getting. I am sorry to say this didn't help me much. I also have someone checking a possible issue with the xprepl.dll file on the publisher - it seems to be an older file. Most of the web searches I have done mention SP3a or SP2 being needed but I have the same replication working on another server with the same SQL Server versions on publisher and subscriber so I don't think that is causing my issue. Thanks for any ideas you have.

Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: USALCOT-DB02-TCSC-subscriber-3

Startup Delay: 3702 (msecs)
Connecting to Distributor 'publisher'
Connecting to Distributor 'publisher.'
[10/9/2006 9:29:23 AM]publisher.: exec sp_helpdistpublisher N'publisher'
[10/9/2006 9:29:23 AM]publisher.distribution: select @.@.SERVERNAME

Server: publisher
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
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

[10/9/2006 9:29:24 AM]publisher.distribution: execute sp_server_info 18

ANSI codepage: 1
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'subscriber')
[10/9/2006 9:29:24 AM]publisher.distribution: {?=call sp_MShelp_subscriber_info (N'publisher', N'subscriber')}
Subscriber security mode: 0, login name: sa.
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'publisher')
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MShelp_distribution_agentid(0, N'TCSC', NULL, 2, N'tcsc', 0)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent publisher-TCSC-subscriber-3 failed. Invalid Descriptor Index[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_alert(3, 3, 9, 14151, ?, -1, N'publisher', N'TCSC', N'subscriber', N'tcsc', ?)}
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_error(9, 0, 4, ?, N'S1002', ?)}

Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'publisher'

|||

This looks like a mismatch of distribution database version (SP, QFE not applied correctly?). Can you do a distribution..sp_helptext 'sp_MSadd_distribution_history' to see if the parameters are lined up properly? If you have a working environment, you may also want to compare the text of the sp_MSadd_distribution_history proc and see if you can find any suspicious differences.

-Raymond

|||

You are my hero !! doh ! how could I be so stupid as to not consider checking the distribution template database create date.

I checked against another box and it appears the templates had never been updated, copied the mdf and ldf from another box and lo it works!!!

Raymond I owe you a beer big time!! ( it's also the first time in around 2 or 3 years I've actually had a problem solved on a forum ) I will store this information as a crucial peice of information. This explains the "re-apply service pack" solution but has never explained the reasoning behind it.

Thanks again!

|||

Its seems like this may also be the problem I am having. What I wondered if there is anyway to do this without rebuilding the replication? I will need to do this work on the weekend again if I have to resend the snapshot.

This is the first time I have ever added information to a forum so the fact that I got the answer so quickly is real impressive to me.

Thanks to both of you for your help!!!

|||you don't have to rebuild anything, just try reapply the last service pack/QFE that was attempted.|||

in my case I couldn't apply the sp so I just moved the files. Thanks for clarifying the point Greg.

Still sort of worrying about the SP though.

Invalid Descriptor Index error

I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )

Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly.

can you cut/paste the entire agent error output?|||

The distribution Job fails with this message " Invalid Descriptor Index. The step failed."

There are no other error messages within any of the logs. Have re-applied replication over 12 times now.

|||

I am having the same issue and wondered if you had found out what was causing the problem. In my case it is a brand new publication but I have built the same one on another server without this error. Thanks

|||

Do you know if the distrib.exe process was able to start at all when you start the SQL Server Agent job? (May be tricky to find out from taskmgr.exe...) If possible, can you manually run the distrib.exe executable using the command-line from msdb..sysjobsteps with -OutputVerboseLevel 2 and post the (sanitized) output here? Thanks.

-Raymond

|||

Ah cool ! I looked at the job and thought "what runs this - I should get to the command line"

Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation

Connecting to Subscriber 'MyServer'
Connecting to Subscriber 'MyServer.ServerAdmin'

Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
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

[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: {?=call sp_helpsubscription_properties (N'MyServer', N'SouthWind', N'')}
Distributor security mode: 1, login name: sa, password: ********.
alternate snapshot folder: .working directory: .use ftp?: 0.
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
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

Connecting to Distributor 'MyServer'
Connecting to Distributor 'MyServer.'
[10/9/2006 9:38:46 AM]MyServer.: exec sp_helpdistpublisher N'MyServer'
[10/9/2006 9:38:46 AM]MyServer.distribution: select @.@.SERVERNAME

Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
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

[10/9/2006 9:38:46 AM]MyServer.distribution: execute sp_server_info 18

ANSI codepage: 1
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MShelp_distribution_agentid(0, N'SouthWind', NULL, 0, N'ServerAdmin', 1)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 2,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent MyServer-SouthWind-MyServer-1 failed. Invalid Descriptor Index[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_repl_alert(3, 1, 2, 14151, ?, -1, N'MyServer', N'SouthWind', N'MyServer', N'ServerAdmin', ?)}
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: exec dbo.sp_MSupdatelastsyncinfo N'MyServer',N'SouthWind', N'', 1, 6, N'Invalid Descriptor Index'
Disconnecting from Subscriber 'MyServer'
Disconnecting from Distributor History 'MyServer'

|||

Here is the results I am getting. I am sorry to say this didn't help me much. I also have someone checking a possible issue with the xprepl.dll file on the publisher - it seems to be an older file. Most of the web searches I have done mention SP3a or SP2 being needed but I have the same replication working on another server with the same SQL Server versions on publisher and subscriber so I don't think that is causing my issue. Thanks for any ideas you have.

Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: USALCOT-DB02-TCSC-subscriber-3

Startup Delay: 3702 (msecs)
Connecting to Distributor 'publisher'
Connecting to Distributor 'publisher.'
[10/9/2006 9:29:23 AM]publisher.: exec sp_helpdistpublisher N'publisher'
[10/9/2006 9:29:23 AM]publisher.distribution: select @.@.SERVERNAME

Server: publisher
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
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

[10/9/2006 9:29:24 AM]publisher.distribution: execute sp_server_info 18

ANSI codepage: 1
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'subscriber')
[10/9/2006 9:29:24 AM]publisher.distribution: {?=call sp_MShelp_subscriber_info (N'publisher', N'subscriber')}
Subscriber security mode: 0, login name: sa.
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'publisher')
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MShelp_distribution_agentid(0, N'TCSC', NULL, 2, N'tcsc', 0)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent publisher-TCSC-subscriber-3 failed. Invalid Descriptor Index[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_alert(3, 3, 9, 14151, ?, -1, N'publisher', N'TCSC', N'subscriber', N'tcsc', ?)}
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_error(9, 0, 4, ?, N'S1002', ?)}

Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'publisher'

|||

This looks like a mismatch of distribution database version (SP, QFE not applied correctly?). Can you do a distribution..sp_helptext 'sp_MSadd_distribution_history' to see if the parameters are lined up properly? If you have a working environment, you may also want to compare the text of the sp_MSadd_distribution_history proc and see if you can find any suspicious differences.

-Raymond

|||

You are my hero !! doh ! how could I be so stupid as to not consider checking the distribution template database create date.

I checked against another box and it appears the templates had never been updated, copied the mdf and ldf from another box and lo it works!!!

Raymond I owe you a beer big time!! ( it's also the first time in around 2 or 3 years I've actually had a problem solved on a forum ) I will store this information as a crucial peice of information. This explains the "re-apply service pack" solution but has never explained the reasoning behind it.

Thanks again!

|||

Its seems like this may also be the problem I am having. What I wondered if there is anyway to do this without rebuilding the replication? I will need to do this work on the weekend again if I have to resend the snapshot.

This is the first time I have ever added information to a forum so the fact that I got the answer so quickly is real impressive to me.

Thanks to both of you for your help!!!

|||you don't have to rebuild anything, just try reapply the last service pack/QFE that was attempted.|||

in my case I couldn't apply the sp so I just moved the files. Thanks for clarifying the point Greg.

Still sort of worrying about the SP though.

Invalid Descriptor Index

I have a Sql Server 2000 SP3 Publisher that performs merge replication with a group of MSDE 2000 SP3 subscribers.
Everything was working fine. I had to make changes to the publication and reinit the subscribers. Some subscribers are working fine. Others are getting an error. The error is
The process could not enumerate changes at the 'Subscriber'.
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
Invalid Descriptor Index
(Source: XXXXXXXX(Data source); Error number: 0)
------
Anyone know what might cause this? And the solution for it?
90 % of the failure to enumerat changes errors can be cleared by restarting the agent.
In your case the invalid descriptor index is unusual and points to an ODBC error. SQL Server replication uses ODBC to communicate between servers.
There are some reports that you can clear this error by rearranging your columns returned in your results set.
It sounds like this might be a bug.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||"There are some reports that you can clear this error by rearranging your columns returned in your results set."
I can't rearrange anything since its setup from replication itself. Its wierd because some subscribers to go through fine, others are failing with the error.
|||Is this error a transient error for these subscribers? Are these subscribers at the same MDAC, sp, hot fix level for that the successful subscribers are at?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Invalid Descriptor Index

Hi,
I have setup snapshot pull replication between SQL2000 (publisher) and
MSDE2000 (subscriber).
The following error "Invalid Descriptor Index" is occuring on the
subscriber. Would someone please tell me what this error means and how to
get around it?
Thanks
reapply sp3 on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mm" <postto@.news.com> wrote in message
news:u8luytqlEHA.2492@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have setup snapshot pull replication between SQL2000 (publisher) and
> MSDE2000 (subscriber).
> The following error "Invalid Descriptor Index" is occuring on the
> subscriber. Would someone please tell me what this error means and how to
> get around it?
> Thanks
>

Invalid descriptor index

Good day
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:

> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks

Invalid Descriptor Index

I'm testing db to db transactional replication on a box ( all on the same box
) and the distribution agent fails with the above error. I know it's
something to do with the physical server as this test works on other servers
fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named
and anonymous. Replication also fails if I use the default snapshot location.
I suspect policy restrictions ( maybe on the sql service accounts ) Any
pointers would be helpful - there are no errors other than above, sadly.
The distribution Job fails with this message " Invalid Descriptor Index.
The step failed."
The snapshot works fine, I can see snapshots created ( as I add articles
through tsql ) the data is produced in the designated folder ( not the
default )
When I used the default snapshot folder the snapshot failed with a
permission error - couldn't write the files ( or similar ) which with the
services in the local admins makes me think this is a policy thing.
The servers are not really on the domain and it's actually quite tricky (
like a collection of workgroups ) but that shouldn't stop local replication
working.
This is a hoary problem with no good solution I know of. Some people have
reported success by
1) remove and re-enabling replication (not an option on a clustered server)
2) applying the sp again
3) rearranging the order of columns so the text column is not the last
column in the table. This would require a recreating of the table.
Can you enable logging to determine which table it is breaking on?
http://support.microsoft.com/default...312292&sd=tech
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
> I'm testing db to db transactional replication on a box ( all on the same
> box
> ) and the distribution agent fails with the above error. I know it's
> something to do with the physical server as this test works on other
> servers
> fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
> Server and Agent accounts are in local admins, tried push and pull, named
> and anonymous. Replication also fails if I use the default snapshot
> location.
> I suspect policy restrictions ( maybe on the sql service accounts ) Any
> pointers would be helpful - there are no errors other than above, sadly.
> The distribution Job fails with this message " Invalid Descriptor Index.
> The step failed."
> The snapshot works fine, I can see snapshots created ( as I add articles
> through tsql ) the data is produced in the designated folder ( not the
> default )
> When I used the default snapshot folder the snapshot failed with a
> permission error - couldn't write the files ( or similar ) which with the
> services in the local admins makes me think this is a policy thing.
> The servers are not really on the domain and it's actually quite tricky (
> like a collection of workgroups ) but that shouldn't stop local
> replication
> working.
>
|||Hah - well there's a point, I'm actually replicating a function, although I
did try a table and a procedure all produced the same result.
have removed and replaced replication about twelve times with no change to
result.
will ask about having the sp re-installed but as it's hosted, not sure. I
could try for 2187 rollup I guess.
"Hilary Cotter" wrote:

> This is a hoary problem with no good solution I know of. Some people have
> reported success by
> 1) remove and re-enabling replication (not an option on a clustered server)
> 2) applying the sp again
> 3) rearranging the order of columns so the text column is not the last
> column in the table. This would require a recreating of the table.
> Can you enable logging to determine which table it is breaking on?
> http://support.microsoft.com/default...312292&sd=tech
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
>
>
|||Two considerations. 1) use snapshot replication for replicating schema only
objects - like functions, views, stored procedures. Snapshot replication is
the only replication type which picks up schema changes.
2) try to use sp_addscriptexec to deploy your function if you deployed your
snapshot through a unc. It does not work using ftp.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...[vbcol=seagreen]
> Hah - well there's a point, I'm actually replicating a function, although
> I
> did try a table and a procedure all produced the same result.
> have removed and replaced replication about twelve times with no change to
> result.
> will ask about having the sp re-installed but as it's hosted, not sure. I
> could try for 2187 rollup I guess.
>
> "Hilary Cotter" wrote:
|||I have to provide a scripted solution for replication, using the GUI is not
an option for a controlled environment. It all works fine on the test boxes,
and yes I'm using the snapshot to move the non table objects. It provides a
simplified solution for the client if everything is within one publication,
less chance of mistakes, and they ( or another DBA ) will have to support my
work after I've gone. There are in truth a number of routes I could take but
a consistant method of implementing changes is important.
Anyway I digress - it works except on the production cluster, if I could
extract a more useful error message or figure out how to run the distributor
command out of the agent job ?
Unless I can get a handle on the problem there is no way to go to the data
centre providers so currently we have an impasse as I figure it's the server
config but without some measure of documented proof I can't approach the data
centre.
"Hilary Cotter" wrote:

> Two considerations. 1) use snapshot replication for replicating schema only
> objects - like functions, views, stored procedures. Snapshot replication is
> the only replication type which picks up schema changes.
> 2) try to use sp_addscriptexec to deploy your function if you deployed your
> snapshot through a unc. It does not work using ftp.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...
>
>
|||Use a pre or post snapshot script to deploy the schema only objects then.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...[vbcol=seagreen]
>I have to provide a scripted solution for replication, using the GUI is not
> an option for a controlled environment. It all works fine on the test
> boxes,
> and yes I'm using the snapshot to move the non table objects. It provides
> a
> simplified solution for the client if everything is within one
> publication,
> less chance of mistakes, and they ( or another DBA ) will have to support
> my
> work after I've gone. There are in truth a number of routes I could take
> but
> a consistant method of implementing changes is important.
> Anyway I digress - it works except on the production cluster, if I could
> extract a more useful error message or figure out how to run the
> distributor
> command out of the agent job ?
> Unless I can get a handle on the problem there is no way to go to the data
> centre providers so currently we have an impasse as I figure it's the
> server
> config but without some measure of documented proof I can't approach the
> data
> centre.
> "Hilary Cotter" wrote:
|||It's interesting that there doesn't seem to be any logical solutions or
pointers to this error message. I searched extensively prior to posting ( on
several forums ) and I haven't seen one solution other than re-installing sp3
- which doesn't apply here. I have asked that the data centre re-patch but I
don't know when that will be.
I have to admit I rarely post problems I encounter, as, like now, I never
seem to find a resolution - it's very frustrating !!!
Such is life I guess.
"Hilary Cotter" wrote:

> Use a pre or post snapshot script to deploy the schema only objects then.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...
>
>
|||You can always open a support incident with PSS.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:44A1572A-A1A8-4DC7-A449-D0418991B110@.microsoft.com...[vbcol=seagreen]
> It's interesting that there doesn't seem to be any logical solutions or
> pointers to this error message. I searched extensively prior to posting
> ( on
> several forums ) and I haven't seen one solution other than re-installing
> sp3
> - which doesn't apply here. I have asked that the data centre re-patch but
> I
> don't know when that will be.
> I have to admit I rarely post problems I encounter, as, like now, I never
> seem to find a resolution - it's very frustrating !!!
> Such is life I guess.
> "Hilary Cotter" wrote:

Invalid cursor state @ Distribution Agent

Hi! When i create a Transactional Replication between two SQL Server 2000 (with no additional Service Pack or Update) with the Enterprise Manager, i get this error (also when i restart the agent):
Invalid cursor state
(Source: ODBC Driver Manager [ODBC]; Error number: 24000)
I think it the MDAC is not the problem, because i never updated the SQL Server 2000 and there the error is from the ODBC SQL Driver. But here it′s from the Driver Manager.
Please help me!! I really despair!
Thanks
Does this apply to your case:
http://support.microsoft.com/default.aspx?kbid=831997?
HTH,
Paul Ibison

Invalid cursor state

Hi! I want to build up a transactional replication between 2 SQL Server 2000. The Publisher is also the distributor.
I used all the wizards and after i made the subscriber, I get an error at the distribution agent:
Invalid cursor state
(Source: ODBC Driver Manager (ODBC); Error number: 24000)
What should i do now?
Please help, thank you!!
Is this error reproducible? For instance if you restart your Distribution
Agent do you get it again?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"oselinge" <oselinge@.discussions.microsoft.com> wrote in message
news:98B5BAD5-2551-4279-9B74-40A6481FF3E1@.microsoft.com...
> Hi! I want to build up a transactional replication between 2 SQL Server
2000. The Publisher is also the distributor.
> I used all the wizards and after i made the subscriber, I get an error at
the distribution agent:
> Invalid cursor state
> (Source: ODBC Driver Manager (ODBC); Error number: 24000)
> What should i do now?
> Please help, thank you!!
|||Yes, I get it again! I often tried to restart, but everytime i got this error.
"Hilary Cotter" wrote:

> Is this error reproducible? For instance if you restart your Distribution
> Agent do you get it again?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "oselinge" <oselinge@.discussions.microsoft.com> wrote in message
> news:98B5BAD5-2551-4279-9B74-40A6481FF3E1@.microsoft.com...
> 2000. The Publisher is also the distributor.
> the distribution agent:
>
>
|||Oselinge,
usually this is an MDAC/ODBC error - hopefully this
article applies to your case:
http://support.microsoft.com/default.aspx?kbid=831997
HTH,
Paul Ibison
|||I think this is not the reason for this error.
Any other ideas?
"Paul Ibison" wrote:

> Oselinge,
> usually this is an MDAC/ODBC error - hopefully this
> article applies to your case:
> http://support.microsoft.com/default.aspx?kbid=831997
> HTH,
> Paul Ibison
>

Invalid column name 'rowguid'.

Hello ,
I scripted the creation of my Replication ,
Then droped the Replication ,
Added 1 or 2 new Fields in 5 existing tables
and then Recreated the Replication using the scripted File.
The Replication is Created Succesfully but I got an Error when running
the
Snapshot Agent
The Error Is
Invalid column name 'rowguid'.
Can Anyone Help Please
Thanks
Savvas
I set the @.keep_partition_changes = N'false' in the Create
Replication Script
then run snapshot and it worked

invalid column name 'rowguid'

Hi all,

I'm new to replication and database management so I will like some understanding and guiding as this is a new task for me.

I was given a copy of a database with merge replication on it and doing allot of reading and using some scripts I cleaned replication (or so Im thinking) and tryed to use it again on the ms-access front end. When I try to update some data I get "invalid column name 'rowguid'" , but no such column exist in my database as it was dropped.

Any help on this?

thanks George.

You refer to rowguid column either with its real column name or ROWGUIDCOL identifier. ie:

create table myTable ( pkid uniqueidentifier rowguid, othercolumn int )

select pkid, othercolumn from myTable

select ROWGUIDCOL, othercolumn from mytable

|||

When you setup merge replication, it adds a guid column to your table article. You scripts may have dropped the column when you clean up the replication and your front end may still have dependency to that column. Perhap, add a dummy column with same name and type to see if that would get your access front end to work.

Regards,

Gary

|||

As Gary mentioned most likely the column is removed when you cleaned up replication.

It is better to update your front end to not reference this non-existant column.

|||

Thanks for the replies.

The database I was given had a pull subscription on it. With the scripts it was cleared. In my access code using breakpoints I did not find any references to this columns in places where the code steps into.

The code I found and used to clean all replication (indexes, rowguid column etc.) in the database follows:

************************************

UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END

CLOSE list_views
DEALLOCATE list_views

GO

DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name
like 'sp_ins_%' or name like 'sp_MSdel_%' or name
like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END

CLOSE list_procs
DEALLOCATE list_procs

GO

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

GO

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
and syscolumns.name='rowguid'

OPEN list_rowguid_constraints

FETCH NEXT FROM list_rowguid_constraints INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name ) + '
DROP CONSTRAINT ' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO
@.name, @.constraint
END

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

GO

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.'
+@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
END

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + '
drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM syssubscriptions
GO
DELETE FROM sysarticleupdates
GO
DELETE FROM systranschemas
GO
DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM sysarticles
GO
DELETE FROM sysschemaarticles
GO
DELETE FROM syspublications
GO
DELETE FROM sysmergeschemachange
GO
DELETE FROM sysmergesubsetfilters
GO
DELETE FROM MSdynamicsnapshotjobs
GO
DELETE FROM MSdynamicsnapshotviews
GO
DELETE FROM MSmerge_altsyncpartners
GO
DELETE FROM MSmerge_contents
GO
DELETE FROM MSmerge_delete_conflicts
GO
DELETE FROM MSmerge_errorlineage
GO
DELETE FROM MSmerge_genhistory
GO
DELETE FROM MSmerge_replinfo
GO
DELETE FROM MSmerge_tombstone
GO
DELETE FROM MSpub_identity_range
GO
DELETE FROM MSrepl_identity_range
GO
DELETE FROM MSreplication_subscriptions
GO
DELETE FROM MSsubscription_agents
GO

****************************************************************************************

DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM sysmergepublications

OPEN list_pubs

FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*
sp_dropmergesubscription [ [ @.publication = ] 'publication' ]
[ , [ @.subscriber = ] 'subscriber'
[ , [ @.subscriber_db = ] 'subscriber_db' ]
[ , [ @.subscription_type = ] 'subscription_type' ]
[ , [ @.ignore_distributor = ] ignore_distributor ]
[ , [ @.reserved = ] reserved
*/
PRINT 'dropping publication ' +@.name
EXEC sp_dropmergesubscription @.publication=@.name,
@.subscriber ='all'
EXEC sp_dropmergepublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END

CLOSE list_pubs
DEALLOCATE list_pubs
GO

DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles

OPEN list_replicated_tables

FETCH NEXT FROM list_replicated_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@.name
--select @.name='drop Table ' + @.name
EXEC sp_msunmarkreplinfo @.name
FETCH NEXT FROM list_replicated_tables INTO @.name
END

CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables

GO

UPDATE syscolumns set colstat = colstat & ~4096 WHERE
colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END

CLOSE list_views
DEALLOCATE list_views

GO

DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name
like 'sp_ins_%' or name like 'sp_MSdel_%' or name
like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END

CLOSE list_procs
DEALLOCATE list_procs

GO

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

GO

UPDATE syscolumns set colstat=2 WHERE name='rowguid'

GO

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
and syscolumns.name='rowguid'

OPEN list_rowguid_constraints

FETCH NEXT FROM list_rowguid_constraints INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name ) + '
DROP CONSTRAINT ' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO
@.name, @.constraint
END

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

GO

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.'
+@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
END

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + '
drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO

DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergeschemachange
GO

DELETE ...

*************************************************************************

These are the two scripts

I'm willing to repeat this with new directions to clean up the database if someone has an understantable way to do this.

My version:

/*
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38 Copyright (c) 1988-2003
Microsoft Corporation Personal Edition on Windows NT 5.1
(Build 2600: Service Pack 2)
SP4

*/

This 2 scripts were run on my database.

Thanks allot

|||

The table does not have the rowguid column.

Your front end is still thinking that the column exists and is either trying to insert/update/delete or access it. Check your front end code.

For eg: originally table was: Employee (Name varchar(20), id int, rowguid uniqueidentifier rowguidcol).

and your front end was doing: insert into Employee (Name, id, rowguid) values ('Bill', 1, newid())

or doing: select * from Employee

After you removed replication: table is Employee (Name varchar(20), id int)

Now with this schema, if you try to insert or access the missing column, you will get the above error.

|||

The issue has nothing to do with the front end. I replicated the issue as follows:

Take a database replicated with a merge publication and a push subscription, and (with DTS) copy the database to a SQL Server that does not have Replication configured.

Run the script listed in a previous reply to this posting.

Run any valid SQL that alters the data and makes no reference to the rowguid column.

This produces the error in question.

The solution lies in the Triggers. It appears that Replication either adds or modifies triggers that the scripts fail to remove or fix.

I came up with this code to add to the script, but the catch is that if you've got your own triggers that start "ins_", "del_" and "upd_", this will delete them as well. So you may want to change the name of some of your triggers, first, if you use this naming convention:

DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='tr' and (name
like 'ins_%' or name
like 'del_%' or name
like 'upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping Triggers ' +@.name
select @.name='drop trigger ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END

CLOSE list_procs
DEALLOCATE list_procs
go

|||So essentially the scripts did not cleanup replication from the database. I believe this is SQL 2000. There is a system stored procedure sp_removedbreplication that will help remove traces of replication from a database. However it is also know that in SQL 2000, this sp may not be completely removing the trace in some cases. It has been improved significantly in SQL 2005. Not that this posting will help resolve the issue, but posting it just for information.|||

Thanks to all.

I managed to go over that problem (it had nothing to do with the front end!) I found some scripts at http://www.replicationanswers.com that were very helpfull.