Replication Stops at distributor in some cases

Hi,
I recently had a problem with a merge replication publication. In
some cases when data is changed on a subscriber, the change gets back
to the publisher but does not replicate from the publisher to the rest
of the subscribers. I checked the conflict viewer and there are no
conflicts. I am running SQL Server 2000 and I was wondering if anyone
is familiar with this problem.
Thanks,
Paul
Are you using any sort of filtering? This might account for this behavior.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408091019.79d49cf2@.posting.google.c om...
> Hi,
> I recently had a problem with a merge replication publication. In
> some cases when data is changed on a subscriber, the change gets back
> to the publisher but does not replicate from the publisher to the rest
> of the subscribers. I checked the conflict viewer and there are no
> conflicts. I am running SQL Server 2000 and I was wondering if anyone
> is familiar with this problem.
> Thanks,
> Paul
|||I do have some row filters on but none that comes into play in this
particular situation.
|||Can you give us some more details about this topology? How many subscribers,
etc.
I have tried to repro this on a local server with two subscribing database
and can't get the expected behavior. IE an update on Subscriber1 makes it to
the publisher and Susbcriber2. Same with updates on Susbcriber2.
Also can you query the conflict tables directly on the Publisher and
subcriber just to verify the conflict viewer is correct.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408120524.77f6d0ca@.posting.google.c om...
> I do have some row filters on but none that comes into play in this
> particular situation.
|||This publication has 12 subscribers (push subscriptions) that resync
every 2 hours. They are staggered to run 5 minutes apart. This only
happens with one particular table and I think it is perhaps a bug that
is just not listing the conflict. So far I have determined that this
happens when a personnel record is update on two different
subscribers. The only thing is in this case the fields being updated
on the two different subscribers do not overlap. So there should not
be any conflicts, and none of them are showing up in the conflict
manager. I also queried the conflict table and there were no rows in
there.
For example:
Field a and b are changed on subscriber X
Field d and c are changed on subscriber Y
publisher resyncs with X and gets the changes to field a and b
publisher resyncs with Y and gets changes to field d and c but does
update
Y with the changes from X.
I hope this helps, I am currently trying to reproduce this to have get
a more detailed version of what is going down. Thank you very much
for your help Hilary, it is appreciated.

Replication stops after server reboot??

Hi,

I have transactional replication set up between two dedicated servers. Server A is the PDC and Server B is a BDC (they are both Win2000 boxes). Both the servers are brand new, and replaced the two that were running like clock work (replication wise) for the last 12 months. I never had this problem with the old servers...

When the servers are shut down (as the case was a couple of weeks back with a power failure) or just recently when they were move to another room. Both servers boot up at the same time. Server B (which is the server holding the db being replicated) boots quicker and as a result replication fails and is then 'sucessfully stopped'. Unless I am aware of the server being rebooted and can monitor this potential problem, within 2 days the logfile grows to large and everything comes to a crashing halt.

I just remove replication, truncate and shrink the log, reset replication and we're away... BUT I really need to know why it is happening in the first place. I figure there must be a setting that I have forgotten about or something.

Both servers are Win2000 (SP4) and SQL2000(SP3a).

Any help would be appreciated.

Thanks
CasperHave you tried restarting replication agents before removing replication? Make sure sql agent is set up as auto start. I assume the distribution db are sitting on the publisher (server B). When dist server couldn't find subscriber (box A), it will auto stop all distribution agetns, which caused the distribution db to grow.

You can set up a job monitoring log reader and dist agent to see if they are stopped. Then run sp_start_job to automatically restart them.|||When the log is full everything comes to a crashing halt. That's basically the reason why. You need to either backup the log or truncate it more often, at least once a day. Shrink it if need be. You really don't have to uninstall replicate to dump the log and shrink the log file. You can truncate the log and shrink it with replication running.|||Joejcheng - yes I already do that, the transaction log is backed up and truncated every hour. The problem was when replication stops the transaction log grows (because it is retaining all transactions waiting to be replicated).

Richard - thank you! I had not even thought about just restarting the jobs (doh!). However the 2 times this has happened the transaction log has been around 15GB and the database controls a production system and they cannot afford to be offline for long (I usually just get them up and running and then reset replication the following evening). Can I start the jobs and have them continue work whilst it is pushing all the transactions across - or would it just never catch up because of the size of the transaction log?

And also thank you for mentioning sp_start_job - I will most certainly put that in place.

However I would still really like to know why this is happening in the first place, but I think I may have worked it out...

I set up replication from a script created on the previous system. One of the replication owners is XXXSqlAdmin, however in the new system this user is just SQLAdmin. I have noticed the error raised by this in the past (ie XXXSqlAdmin does not exist in this db), but didn't think it would cause any problems. But now I think I was very wrong. I changed the script before I ran it last night, no errors were returned. I will monitor the DB for the next week and then we will do a test where we shut down the servers and boot server B well before we boot server A.

Thanks again for your help, much appreciated!

Casper :)

Replication of Dynamic SQL Stored Procedures

I'm trying to set up replication on a Db that I just took over. When
replicating the SProcs the Distribution Agent stops with an error on the
Subscriber whenever it encounters an SP that is building a dynamic SQL
statement. I typically get the following error:
are not allowed. Add a name or single space as the alias name.
(Source: EVESTMENTDB6 (Data source); Error number: 1038)
There is nothing before the "are not allowed." Lookinig for error 1038 has
so far been fruitless. I have been rewriting the Dynamic SQl but I know that
at least some of the SProc's are really going to need it.
Jay Croft
Senior Systems Architect
Can you post one of the problem procs here?
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
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:654660AB-4A31-4275-BD3E-A432D815B7E4@.microsoft.com...
> I'm trying to set up replication on a Db that I just took over. When
> replicating the SProcs the Distribution Agent stops with an error on the
> Subscriber whenever it encounters an SP that is building a dynamic SQL
> statement. I typically get the following error:
> are not allowed. Add a name or single space as the alias name.
> (Source: EVESTMENTDB6 (Data source); Error number: 1038)
> There is nothing before the "are not allowed." Lookinig for error 1038
> has
> so far been fruitless. I have been rewriting the Dynamic SQl but I know
> that
> at least some of the SProc's are really going to need it.
> --
> Jay Croft
> Senior Systems Architect
|||These SP's were written by an untrained junior programmer, but they've been
working in a production DB for two months.
/****** Object: StoredProcedure
[dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date: 02/08/2006
18:14:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE procedure [dbo].[eaSP_analytics_select_benchmark_perf_data]
@.benchmark_type varchar(20),
@.view_name varchar(40),
@.risk_benchmark_id int,
@.earliest_date datetime,
@.latest_date datetime,
@.qorm char(1)
as
begin
SET NOCOUNT ON
declare @.strSQL varchar(1000)
if @.benchmark_type = ''Index''
Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
year, value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
if @.benchmark_type = ''DB Product''
Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
product_category_code, vehicle_type, gross_or_net, base_currency, product_id
as benchmark_id, product_name, return_date, month, year, product_performance
as value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
product_performance is not null "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
SET NOCOUNT OFF
end
'
END
GO
Jay Croft
Senior Systems Architect
"Hilary Cotter" wrote:

> Can you post one of the problem procs here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Hi Jay,
Replication of text objects that use " instead of ' for delimiting string
literal is not supported (or simply broken) in SQL2000 although the SQL2005
snapshot agent will automatically convert these "s into 's. If there are not
many procedures that use " for delimiting string literals, you may want to
consider rewriting them to use ' instead. Otherwise you would need to use
the Database Import\Export wizard to transfer these stored procedures
instead.
HTH
-Raymond
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:0D89C509-557E-4DA1-A99F-560BD91688C7@.microsoft.com...
> These SP's were written by an untrained junior programmer, but they've
> been
> working in a production DB for two months.
> /****** Object: StoredProcedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date:
> 02/08/2006
> 18:14:39 ******/
> SET ANSI_NULLS OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
> OBJECTPROPERTY(id,N'IsProcedure') = 1)
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
>
>
> CREATE procedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data]
> @.benchmark_type varchar(20),
> @.view_name varchar(40),
> @.risk_benchmark_id int,
> @.earliest_date datetime,
> @.latest_date datetime,
> @.qorm char(1)
> as
> begin
> SET NOCOUNT ON
> declare @.strSQL varchar(1000)
> if @.benchmark_type = ''Index''
> Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
> year, value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> if @.benchmark_type = ''DB Product''
> Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
> product_category_code, vehicle_type, gross_or_net, base_currency,
> product_id
> as benchmark_id, product_name, return_date, month, year,
> product_performance
> as value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
> product_performance is not null "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> SET NOCOUNT OFF
> end
> '
> END
> GO
> --
> Jay Croft
> Senior Systems Architect
>
> "Hilary Cotter" wrote:
>