Merge replication sync fails with error code 80040E19 on handheld

Hi;

We are performing merge replication between SQL Server 2005 and SQL Server Mobile with more than 1100 handheld, and recently replications are terminating with eror code 80040E19.
We can only avoid the problem by deleting the related user's partition directory and reruning the related user's dynamic snapshot.

Any advice on this issue would be appreciated

Thanks,
Hakan Gümüs

I feel your pain. We only have 300 handhelds in the field but receive this error after every couple of weeks or so. We have not found much information on it at all. It seems to be a client side error, ie no error shows up in the server logs. What we do to alleviate the error is to right click on the publication in SQL Server and Reinitialize All Subscriptions, selecting the Use a new snapshot option with Generate now checked. Once the snapshot is done (monitor with View Snapshot Agent Status) the device replicates again just fine.

Due to the cyclic affect of when it occurs, it seems it may correlate to Retention period of the publication. This is difficult to say as we don't fully understand what happens when the expiration period is reached. The only thing we do know is that Reinitializing the subscriptions works every time.

It is also a guess that it may have something to do with the number of simultaneous replications occuring. Possibly some data at some point gets corrupted or out of sync. We did not see the error during our pilot when we only had a couple of dozen units in the field. Our users by the way replicate twice a day - morning and night, so there are periods of high activity.

Please, if you find out any additional information to this issue, let me know. It has plagued us now for over 6 months. I'll be happy to share whatever information that I can to help solve this issue for good.

Regards,
Santino Lamberti

|||

Hi Santino;

We have opened a case about this issue, I'll inform you about the result.

Thanks for your reply.

Hakan Gümü?

|||The Investigation about the case is still going on. The only workaround that we could find is dropping the related user's merge partition with below command and resync it.

sp_dropmergepartition 'TESTPUB',NULL,'THOSTNAME'

|||Thanks for sharing this. I'll try it next time we run across the error. It seems that I've tried doing this via the UI once before with no effect, but I'll give this a whirl anyway. I also plan on looking at the snapshot data (schema and bcp files) for the failing user and the master snapshot. The error seems to occur after all the schemas are applied and possibly when the data is being copied in.

Merge replication sync fails with error code 80040E19 on handheld

Hi;

We are performing merge replication between SQL Server 2005 and SQL Server Mobile with more than 1100 handheld, and recently replications are terminating with eror code 80040E19.
We can only avoid the problem by deleting the related user's partition directory and reruning the related user's dynamic snapshot.

Any advice on this issue would be appreciated

Thanks,
Hakan Gümüs

I feel your pain. We only have 300 handhelds in the field but receive this error after every couple of weeks or so. We have not found much information on it at all. It seems to be a client side error, ie no error shows up in the server logs. What we do to alleviate the error is to right click on the publication in SQL Server and Reinitialize All Subscriptions, selecting the Use a new snapshot option with Generate now checked. Once the snapshot is done (monitor with View Snapshot Agent Status) the device replicates again just fine.

Due to the cyclic affect of when it occurs, it seems it may correlate to Retention period of the publication. This is difficult to say as we don't fully understand what happens when the expiration period is reached. The only thing we do know is that Reinitializing the subscriptions works every time.

It is also a guess that it may have something to do with the number of simultaneous replications occuring. Possibly some data at some point gets corrupted or out of sync. We did not see the error during our pilot when we only had a couple of dozen units in the field. Our users by the way replicate twice a day - morning and night, so there are periods of high activity.

Please, if you find out any additional information to this issue, let me know. It has plagued us now for over 6 months. I'll be happy to share whatever information that I can to help solve this issue for good.

Regards,
Santino Lamberti|||

Hi Santino;

We have opened a case about this issue, I'll inform you about the result.

Thanks for your reply.

Hakan Gümü?

|||The Investigation about the case is still going on. The only workaround that we could find is dropping the related user's merge partition with below command and resync it.

sp_dropmergepartition 'TESTPUB',NULL,'THOSTNAME'

|||Thanks for sharing this. I'll try it next time we run across the error. It seems that I've tried doing this via the UI once before with no effect, but I'll give this a whirl anyway. I also plan on looking at the snapshot data (schema and bcp files) for the failing user and the master snapshot. The error seems to occur after all the schemas are applied and possibly when the data is being copied in.

Merge Replication Conditional Filter with UDF Problem

I'm performing Merge Replication between SQL Server 2000/SQL CE using Dynamic Filters.
I would like a conditional filter based on the Host_Name(). I've written and tested a UDF
in Query Analyzer called SELECT_Stores_RepID. In the Dynamic Filters properties for the
Stores Table I've tried many different things such as:
SELECT <published_columns> FROM [dbo].[Stores] WHERE dbo.SELECT_Stores_RepID (HOST_NAME())
All them give me errors. My question is what is the correct syntax to use my UDF? The documentation
on this is very poor.
Cheers
it should be something like this
select <punished_columns> from <<Table>> where au_id in (select au_id from dbo.Authors_filter('CA'))
There may be more efficient ways of doing this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi Hilary,
Thanks for the help. I tried to apply it to my Dynamic Filter...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.StoreID IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
but I'm receiving the following error...
Incorrect Syntax near '('
Once again, I can't see where the problem is with this statment ?
|||Sorry typo in my post ...
(SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
Should be (extra bracket)...
(SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME()))
Still getting the same problem as before.
-- Cube wrote: --
Hi Hilary,
Thanks for the help. I tried to apply it to my Dynamic Filter...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.StoreID IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
but I'm receiving the following error...
Incorrect Syntax near '('
Once again, I can't see where the problem is with this statment ?
|||can you post your UDF.
BTW - I should warn you that UDF's don't always offer the best performance. Also Host_Name() is evaluated where the merge agent is run. If you kick it off on EM runing it on your desktop it will evaluated to your machine name.
If you kick if off on the subscriber as a pull subscription it will resolve correctly.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I'm still getting this error in Replication. Does anyone know if its a known issue?
The error is that when I call my UDF below in my Dynamic Filter and pass in
HOST_NAME() I get an incorrect syntax near '(' error.
Ciaran
-- Cube wrote: --
Hi Hilary,
Please find below my UDF. The RepID in my table is an int, which I CAST so I can
compare it to my Host_Name(). I'm interested to know your opinion. Also, if UDF's
don't offer best performance, do I have other options apart from creating a seperate
publication?
ALTER FUNCTION dbo.SELECT_Stores_RepID
(
@.inRepIDchar(4)
)
RETURNS @.Stores TABLE (StoreID int)
AS
BEGIN
IF (@.inRepID = '8')
INSERT INTO @.Stores
SELECTStoreID FROM Stores
ELSE
INSERT INTO @.Stores
SELECTStoreID
FROM Stores
WHERE CAST(Stores.RepID AS char(4)) = @.inRepID
RETURN
END
BTW - Some history behind all this. I have some HandHeld Devices deployed with my custom app.
The Replication depends on the RepID, which is set through the Host_Name() on the device, pulling
back the users specified data. Now, there is a requirement to pull back all data in some cases
e.g. management, without modifying the software on the client. Henece, the reasons for the
IF RepID = X conditional filter.