The process could not enumerate changes at the Publisher

Question

The process could not enumerate changes at the Publisher

MrAkki

SSCrazy

Points: 2574
More actions
January 8, 2004 at 7:04 am

#101836

Hi there,
I have a merge replication. It worked perfectly for weeks. Suddenly it stoped working
with the following message:
Last statement: {call sp_MSenumchanges(?,?,?,?,?,?,?,?,?)}
The process could not enumarate changes at the 'Publisher'
Errornumber: -2147200999
Wrong syntax close to the AND-keyword
Errornumber: 156
The process was successfully stopped
Error: -2147201000
After the error occured onces on one machine it now occures on all machines.
I allready deleted the subscribers and the publication and set up a new publication
but after a few weeks it started again.
Does anyone have any idea?
I'm using SQL Server 2000 SP3 - 8.00.818 on the server and on the clients
MSDE 2000 SP3 8.00.818
Thanks,
Thorsten

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christian-120994 SSC Veteran Points: 259 More actions · Answer 1

Hi, an idea is to maintain the indexes in a defind period. I have the same error on different servers. If i make a reindex of the table esxpacially of the tableas realted to the replication (for exapmle mergecontents etc.) the problem was solve. You must call DBCC DBREINDEX to do this. Please look in the reference of T-SQL.

BR

Chrisitan

MrAkki SSCrazy Points: 2574 More actions · Answer 2

Hi Christian,

thanks a lot for the informationen. I tried it but it did not solve the problem.

So I decided to check with the SQL-Profiler where the error happens. And i figured out

that the problem occurs in not in sp_MSenumchanges. Within the sp_MSenumchanges the

procedure sp_MSenumchanges_direct is started. And this procedure generates that error.

(I found out that this stored procedure is new in the HotFix 818 (KB815495).

I analyzed the procedure and found the problem.

The problem is caused, because

the variable @genlist is declared as varchar(8000). In my case the genlist has a length of 4447 characters.

Later in the function an exec(SELECT .... ) is performed. The select statement contains

many variables which are put together with the '+' Statement. Some of them are declared

as nvarchar. I think during that opperation all variables used in that SELECT-Statement

are transformed to nvarchar, and because nvarchar is only capable of carring 4000 characters

the genlist with 4447 chars is cut of to 4000 chars, and because this happens in

the WHERE-clause I get the error message: Wrong syntax close to the AND-keyword.

Does anyone have any idea what to do? I already opened up a call at microsoft support.

I was wondering if i can change that procedure by myself?

Thanks.

Thorsten

Christian-120994 SSC Veteran Points: 259 More actions · Answer 3

Hi Thorsten, this sounds really intresting. So i will try to investigated more time in this problem and come back with more info later. If you have found out more please post it here. Thnaks.

I have also a problem. Have you ever make experience with the error: Gernerell network error ??

I replicated a sub but everytime at the same process i recieved this error ? Any idea ?

BR

Christian

MrAkki SSCrazy Points: 2574 More actions · Answer 4

Hi Chrisitian,

as I know in 99% the 'General network error' is really a network error.

So I guess you have to check your underlying network configuration.

However I noticed that if the server is under heavy load (too many replications at once,

or bad configured indexes causing table scans) this error also occurs.

Try checking the network config and if erverything is alright. Check the indexes of the tables.

You should have a clustered index on the rowguid-column.

If you are filtering the tables you have to have the clustered index on the filtered column.

Hope this is of any help,

Thorsten

MrAkki SSCrazy Points: 2574 More actions · Answer 5

Hi,

i corrected the error in sp_MSenumchanges_direct and sp_MSenumchanges_pal and now it works. It seems it really

is a bug in the Hotfix of the KB815495 (MS03-031).

Thanks,

Thorsten

Christian-120994 SSC Veteran Points: 259 More actions · Answer 6

Hi Torsten,

thanks for your reply. I will try to come closer to the problem i have.

Can you discrib how you solve your own problem. I`m really interest how you solve it. Thanks alot.

BR

Christian

MrAkki SSCrazy Points: 2574 More actions · Answer 7

Hi,

I patched the stored procedure sp_MSenumChanges_direct

I changed the following things:

@mingenstr nvarchar(21) to @mingenstr varchar(21)

@maxgenstr nvarchar(21) to @maxgenstr varchar(21)

select @mingenstr = convert(nchar, @mingen) to select @mingenstr = convert(varchar, @mingen)

select @maxgenstr = convert(nchar, @maxgen) to select @maxgenstr = convert(varchar, @maxgen

and it worked.

The patch has to be applied to sp_MSenumChanges_pal, too.

Regards,

Thorsten

ramani Koneripalli SSC Journeyman Points: 77 More actions · Answer 8

I'm having the exact same error message. Is there a hotfix available to address this ? How did you go about patching it ?

r_icki SSC Rookie Points: 29 More actions · Answer 9

Hi Thorsten

You mentioned that we need to have a clustered index on the rowguid column, is this mandatory? in our set up the tables have a unique identifier column for the applicaiton which has been defined as the clustered index column, the rowguid column is a nonclustered index.

does this have any implications in terms of performance?

s_kakoli Newbie Points: 3 More actions · Answer 10

Hi, I am having the same problem with my merge replication.

I found the stored procedures you mentioned, but they are system stored procedures. Could you please let me know how you modified them or did you actually apply a patch?

Thanks,

Kakoli