Does someone know when MS plans to resolve this Bug?

Hi,

I have a problem with merge replication again. This is the replication between sql server 2005 standard edition and express edition. non-filtering. after the merge agent ran normally for a couple of days, the merge replication always stops at the uploading point. It seems like the merge agent can't run the 'MSmakegeneration' sp. Here is the output log. I wish someone could give me hand. Thank you very much!

2007-05-27 21:37:19.656 Microsoft SQL Server Merge Agent 9.00.3042.00
2007-05-27 21:37:19.656 Copyright (c) 2005 Microsoft Corporation
2007-05-27 21:37:19.656
2007-05-27 21:37:19.656 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-05-27 21:37:19.656 User-specified agent parameter values:
-Publication Brown
-Publisher FMSL_SERVER
-Subscriber Brown-WF
-Distributor FMSL_SERVER
-PublisherDB Brown
-SubscriberDB Brown
-PublisherLogin sa
-PublisherPassword **********
-PublisherSecurityMode 0
-OutputVerboseLevel 0
-SubscriberSecurityMode 1
-SubscriptionType 1
-DistributorLogin sa
-DistributorPassword **********
-DistributorSecurityMode 0
-Output C:\OUTPUTFILE.txt
-Outputverboselevel 2
2007-05-27 21:37:19.703 Percent Complete: 0
2007-05-27 21:37:19.703 Connecting to Subscriber 'Brown-WF'
2007-05-27 21:37:19.703 Connecting to OLE DB Subscriber at datasource: 'Brown-WF', location: '', catalog: 'Brown', providerstring: '' using provider 'SQLNCLI'
2007-05-27 21:37:19.812 OLE DB Subscriber: Brown-WF
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: Brown
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-05-27 21:37:19.812 OLE DB Subscriber 'Brown-WF': {call sp_MSgetversion }
2007-05-27 21:37:19.828 OLE DB Subscriber 'Brown-WF': set nocount on declare @.dbname sysname select @.dbname = db_name() declare @.collation nvarchar(255) select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as 'CodePage', collationproperty(@.collation, N'LCID') as 'LCID', collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle',cast(case when convert (int,databasepropertyex (@.dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as DB_CaseSensitive,cast(case when convert (int,serverproperty ('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as Server_CaseSensitive set nocount off
2007-05-27 21:37:19.828 OLE DB Subscriber 'Brown-WF': {?=call sp_helpsubscription_properties (N'FMSL_SERVER', N'Brown', N'Brown')}
2007-05-27 21:37:19.843 Distributor security mode: 0, login name: sa, password: ********.
2007-05-27 21:37:19.843 Percent Complete: 0
2007-05-27 21:37:19.843 Connecting to Distributor 'FMSL_SERVER'
2007-05-27 21:37:19.843 Connecting to OLE DB Distributor at datasource: 'FMSL_SERVER', location: '', catalog: '', providerstring: '' using provider 'SQLNCLI'
2007-05-27 21:37:20.390 OLE DB Distributor: FMSL_SERVER
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name:
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-05-27 21:37:20.718 OLE DB Distributor 'FMSL_SERVER': {call sp_MSgetversion }
2007-05-27 21:37:20.796 OLE DB Distributor 'FMSL_SERVER': {call sp_helpdistpublisher (N'FMSL_SERVER') }
2007-05-27 21:37:20.984 OLE DB Distributor 'FMSL_SERVER': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'FMSL_SERVER')
2007-05-27 21:37:21.078 OLE DB Distributor 'FMSL_SERVER': {call sp_MShelp_merge_agentid (0,N'Brown',N'Brown',null,N'Brown',90,N'Brown-WF')}
2007-05-27 21:37:21.171 OLE DB Subscriber 'Brown-WF': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:21.187 OLE DB Distributor 'FMSL_SERVER': {call sp_MShelp_profile (91, 4, N'')}
2007-05-27 21:37:21.312 Percent Complete: 0
2007-05-27 21:37:21.312 OLE DB Subscriber 'Brown-WF': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:21.312 Initializing
2007-05-27 21:37:21.312 OLE DB Distributor 'FMSL_SERVER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:21.312 Connecting to OLE DB Publisher at datasource: 'FMSL_SERVER', location: '', catalog: 'Brown', providerstring: '' using provider 'SQLNCLI'
2007-05-27 21:37:21.421 Percent Complete: 0
2007-05-27 21:37:21.421 Connecting to Publisher 'FMSL_SERVER'
2007-05-27 21:37:21.421 OLE DB Distributor 'FMSL_SERVER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:21.765 OLE DB Publisher: FMSL_SERVER
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: Brown
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-05-27 21:37:22.109 OLE DB Publisher 'FMSL_SERVER': set nocount on declare @.dbname sysname select @.dbname = db_name() declare @.collation nvarchar(255) select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as 'CodePage', collationproperty(@.collation, N'LCID') as 'LCID', collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle',cast(case when convert (int,databasepropertyex (@.dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as DB_CaseSensitive,cast(case when convert (int,serverproperty ('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as Server_CaseSensitive set nocount off
2007-05-27 21:37:22.234 OLE DB Publisher 'FMSL_SERVER': {call sp_MSgetversion }
2007-05-27 21:37:22.406 Connecting to OLE DB Publisher at datasource: 'FMSL_SERVER', location: '', catalog: 'Brown', providerstring: '' using provider 'SQLNCLI'
2007-05-27 21:37:22.828 OLE DB Publisher: FMSL_SERVER
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: Brown
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-05-27 21:37:23.515 OLE DB Subscriber 'Brown-WF': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:23.515 Percent Complete: 0
2007-05-27 21:37:23.515 OLE DB Subscriber 'Brown-WF': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:23.515 Retrieving publication information
2007-05-27 21:37:23.515 OLE DB Distributor 'FMSL_SERVER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:23.625 Percent Complete: 0
2007-05-27 21:37:23.625 Retrieving subscription information.
2007-05-27 21:37:23.625 OLE DB Distributor 'FMSL_SERVER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:28.203 OLE DB Subscriber 'Brown-WF': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:37:28.203 Percent Complete: 0
2007-05-27 21:37:28.203 Uploading data changes to the Publisher
2007-05-27 21:37:28.203 OLE DB Distributor 'FMSL_SERVER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2007-05-27 21:42:28.312 Percent Complete: 0
2007-05-27 21:42:28.312 The process is waiting for a response from the query '{call sys.sp_MSmakegeneration (?)}'
2007-05-27 21:42:28.312 OLE DB Distributor 'FMSL_SERVER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

In addition, from the replication monitor, I can see the last error message is:

The merge process was unable to create a new generation at the 'Subscriber'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)

And this problem happened on a couple of subscribers on the same day. All the rest are OK so far.
|||now I'm pretty sure that sp_makegeneration doesn't work on the subscriber side properly. I tried to run it manually, it was just keeping running all the time, without any message coming out.

anyone got any idea about this?
|||if the makegeneration proc isn't returning, can you try profiling it to see what's blocking it? Could be a long-running query.|||hi greg,

Glad to hear from you. your reply is always so quick

Actually I did a trace, which shows at that moment, only sp_msmakegeneration was running, nothing else. And the sp was just running, never finish. I have been waitting for about 15 minutes, and tried several times. always got the same situation.

And all the other subscribers, which run properly, just finished the replication within 5 minutes.
|||WII, what I was asking is to do a statement-level trace (add SPtmtStarting event) to see what query inside the proc (or some child proc) is long-running.|||Hi Greg,

I did the statement-level trace as you suggested. But the trace information is too large and unreadable to me.

What I can find is that such kind of queries were keeping running infinitely:

if @.error<>0
update dbo.MSmerge_contents with (rowlock)
set partchangegen = (-@.target_gen )
where partchangegen = (-@.gen) and tablenick = @.art_nick
if @.error<>0
update dbo.MSmerge_metadataaction_request with (rowlock)
set generation = @.target_gen
where generation = @.gen and tablenick = @.art_nick
if @.error<>0
select @.changes_in_target_gen = @.changes_in_target_gen + @.contents_rows_moved
select @.changes_in_gen = @.changes_in_gen - @.contents_rows_moved
update dbo.MSmerge_genhistory with (rowlock) set changecount = @.changes_in_gen where generation = @.gen

if @.@.error<>0
update dbo.MSmerge_genhistory with (rowlock) set changecount = @.changes_in_target_gen where generation = @.target_gen

........

I feel there is some deadlock? Can you see anything from the log? Thx
|||And also, i just learned from some article, that

select tCustomers.* from tCustomers
inner join MSmerge_contents on tCustomers.rowguid = MSmerge_contents.rowguid
inner join MSmerge_genhistory on MSmerge_contents.generation = MSmerge_genhistory.generation
where MSmerge_genhistory.genstatus = 0

I can see all the unuploaded changes on the subscribers that got the problem, which means all the merge triggers were fired normally when any change happened on them.
|||I opened a support case with Microsoft. And this issue is treated as a bug now. So I suppose u guys will see the answer in a future hotfix.

Thanks guys
|||

Hi WII

Just wondered how far your support case had got, we have exactly the same problem and I wondered if you have got a fix?

Thanks,

SMN

|||Same problem here, merge agent gets stuck on sys.sp_MSmakegeneration, which happened after a reinitialization and don't go away even after dropping and creating the subscription.|||Same problem at our system.... Some subsribers do not replicate anymore....

We found the same cause sp_MSmakegeneration (timeout respectively no return value?)

Our system supports salesmen with an application to create orders. We can not reset the subscribtions, as the data of the salesmen would be lost. Our customer looses every day big money due to this bug. There is no way for me to use the workaround as describe above, as we use partially colum level replication. I don't want to create a mess in system tables. If someone would provide a solution for this as well? (Or a comprehensive script, step by step what to do....?)

Otherwhise it would be good, if an experienced programmer could have a look at this "sp_MSmakegeneration" and complete the work and provide a temporarily substitution for this script. It seems to have a hidden dog.

E.g. Comment of MS Programmer in sp_MSmakegeneration Line 265: " -- TODO: possibly add some code for 0 artnick generations..."

Thanks for any help or advice.
|||http://support.microsoft.com/kb/936305

It seems that one of the Hotfixes apply to this timeout problem|||Has anyone determined that this hotfix corrects this problem?

thanks
jg
|||Did NOT fix problem here.sql

Documented Bug in SQL Server 2005?

I set up a WMI alert in SQL2K5, which invokes a response launching a job.
The alert is AUDIT_LOGIN_FAILED. The response job is to insert the contents
of the token $(WMI(TextData)) into a table.
The job step text looks like:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', REPLACE('$(WMI(TextData))', '''', ''''''))
The error looks like:
Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near 'bubba'.
[SQLSTATE 42000] (Error 102).
The profile trace looks like:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', REPLACE('Login failed for user 'bubba'.
[CLIENT: <local machine>]', '''', ''''''))
I have tried this with and without a REPLACE. The result is the same. Is
this documented? Is there a workaround?
Thanks for all input.
John T (JohnT@.discussions.microsoft.com) writes:
> I set up a WMI alert in SQL2K5, which invokes a response launching a job.
> The alert is AUDIT_LOGIN_FAILED. The response job is to insert the
> contents of the token $(WMI(TextData)) into a table.
> The job step text looks like:
> INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
> VALUES('AUDIT_LOGIN_FAILED', REPLACE('$(WMI(TextData))', '''', ''''''))
> The error looks like:
> Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near
> 'bubba'. [SQLSTATE 42000] (Error 102).
> The profile trace looks like:
> INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
> VALUES('AUDIT_LOGIN_FAILED', REPLACE('Login failed for user 'bubba'.
> [CLIENT: <local machine>]', '''', ''''''))
> I have tried this with and without a REPLACE. The result is the same. Is
> this documented? Is there a workaround?
Documented? Sort of. On
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/105bbb66-0ade-4b46-b8e4-
f849e5fc4d43.htm
it says:
SQL Server Agent replaces an exact value for the token. Your job steps
must take this into account and correctly quote the tokens you use.
Bug? Certainly quite a serious design flaw in my eyes. There is no way you
can save this situation with replace(). As I understand it, SQL Server
Agent pastes in the value of the token, and it's your job to put that
value in a syntactic correct context. Which is quite an uphill battle in
this case. SQL Server Agent would need to provide to means to expand a
token: as-is and as-string-literal. Say that the syntax for the latter
would be $$(WMI(TextData)). That would expand the string to
N'Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near
''bubba''. [SQLSTATE 42000] (Error 102).'
An alternative would be that ' were not expanded as such but as some
innocent character, for instance `.
The workaround you can employ is to say SET QUOTED_IDENTIFIER OFF in
the script. Then you can say:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', "$(WMI(TextData))")
But that would not be waterproof, since you would get the same syntax
error for a message that includs a ".
I would suggest that you submit a bug/suggestion for this on
http://lab.msdn.microsoft.com/productfeedback/. If you submit it as
a bug, it may be closed as "By Design". I would not expect this to
be fixed in SQL 2005, as it would be a feature change. Then again,
since there is a potential source for SQL injection here, maybe there
is cause for alarm. Feel free to include to suggestions I've given here
in your submission on the Feedback Centre.
Disclaimer: I have very little experience of working with tokens in
SQL Server Agent, so I may have missed something obvious. (Which is
why I don't want to submit any bug/suggestion myself.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Document Map Bug

I am findng that when I add a document map and then later remove it that it
is still appearing in my reports...Is the report still in session? When you make a change to remove the
document map, I take it that you are re-deploying the report? Try closing
all the browser windows and then open a new browser window and view the
report. - Does the document map still appear?
I think the design descision was taken that once you render the report that
you are woking with a copy of the report in that browser session - and that
then allows changes to occur to the underlying report RDL - without it
affecting your session...
Peter Blackburn
Author: Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:AA230BE3-8D85-44C3-9E04-05F7A1873FE8@.microsoft.com...
>I am findng that when I add a document map and then later remove it that it
> is still appearing in my reports...