2005 Log Reader

We are having a problem with a recently upgraded 2005 replication setup. We
didn't actually migrate to the new version, but rather started from a clean
operating system and SQL installation, just upgrading the databases. I took
the precaution of clearing down all the replication before commencing and
things were looking fine until the following error messages started to
appear. These are fairly consistent, in that the reference the same numbers
etc.
records scanned in pass # 1, elapsed time 1531 (ms).'.
2006-12-19 13:04:47.913 Status: 0, code: 20011, text: 'The process could not
execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
2006-12-19 13:04:47.913 The process could not execute 'sp_replcmds' on
'PASASERV\PASLIVE'.
2006-12-19 13:04:47.913 Status: 0, code: 18764, text: 'Execution of filter
stored procedure 1279200203 failed. See the SQL Server errorlog for more
information.'.
2006-12-19 13:04:47.913 Status: 0, code: 18805, text: 'Logreader failed to
construct replicated command from LSN {0000534e:00001a64:0019}.'.
2006-12-19 13:04:47.913 Status: 0, code: 22037, text: 'The process could not
execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
records scanned in pass # 1, elapsed time 1531 (ms).'.
2006-12-19 13:04:47.913 Status: 0, code: 20011, text: 'The process could not
execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
2006-12-19 13:04:47.913 The process could not execute 'sp_replcmds' on
'PASASERV\PASLIVE'.
2006-12-19 13:04:47.913 Status: 0, code: 18764, text: 'Execution of filter
stored procedure 1279200203 failed. See the SQL Server errorlog for more
information.'.
2006-12-19 13:04:47.913 Status: 0, code: 18805, text: 'Logreader failed to
construct replicated command from LSN {0000534e:00001a64:0019}.'.
The SQL Server error log is no more revealing: -
2006-12-19 13:04:47.913 Status: 0, code: 22037, text: 'The process could not
execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
records scanned in pass # 1, elapsed time 1531 (ms).'.
2006-12-19 13:04:47.913 Status: 0, code: 20011, text: 'The process could not
execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
2006-12-19 13:04:47.913 The process could not execute 'sp_replcmds' on
'PASASERV\PASLIVE'.
2006-12-19 13:04:47.913 Status: 0, code: 18764, text: 'Execution of filter
stored procedure 1279200203 failed. See the SQL Server errorlog for more
information.'.
2006-12-19 13:04:47.913 Status: 0, code: 18805, text: 'Logreader failed to
construct replicated command from LSN {0000534e:00001a64:0019}.'.
2006-12-19 13:04:47.913 Status: 0, code: 22037, text: 'The process could not
execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
The first time this happened, I eventually decided to remove all the
replication and start again, but I can't really afford to do this again.
Also, it's a production system and we don't want to have to downgrade as it
worked fine in testing.
The only thing I can think of that might be causing it is some of the later
publications I am adding (maybe the word filter is significant?) as when I
add my initial set of publications it seems to be going fine, only messing up
later.
Any help or suggestions on this would most be gratefully received. I have
only slept about 3 hours for the last couple of nights, trying to get this
resolved. So much for a weekend upgrade ...
It might be a filter problem. Are you using filtering? The last time I ran
into the could not construct replicated command was when I was replicating
text in SQL 7.
Can you try to see if anything works? If it works intermittently, which it
appears to do, you need to isolate the command which is causing the log
reader to fail.
I would strongly recommend opening a support incident with PSS.
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
"Adam Sewell" <Adam Sewell@.discussions.microsoft.com> wrote in message
news:37B4B5B8-D617-4101-BA6D-2FBAAA1DB00F@.microsoft.com...
> We are having a problem with a recently upgraded 2005 replication setup.
> We
> didn't actually migrate to the new version, but rather started from a
> clean
> operating system and SQL installation, just upgrading the databases. I
> took
> the precaution of clearing down all the replication before commencing and
> things were looking fine until the following error messages started to
> appear. These are fairly consistent, in that the reference the same
> numbers
> etc.
> records scanned in pass # 1, elapsed time 1531 (ms).'.
> 2006-12-19 13:04:47.913 Status: 0, code: 20011, text: 'The process could
> not
> execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
> 2006-12-19 13:04:47.913 The process could not execute 'sp_replcmds' on
> 'PASASERV\PASLIVE'.
> 2006-12-19 13:04:47.913 Status: 0, code: 18764, text: 'Execution of filter
> stored procedure 1279200203 failed. See the SQL Server errorlog for more
> information.'.
> 2006-12-19 13:04:47.913 Status: 0, code: 18805, text: 'Logreader failed to
> construct replicated command from LSN {0000534e:00001a64:0019}.'.
> 2006-12-19 13:04:47.913 Status: 0, code: 22037, text: 'The process could
> not
> execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
> records scanned in pass # 1, elapsed time 1531 (ms).'.
> 2006-12-19 13:04:47.913 Status: 0, code: 20011, text: 'The process could
> not
> execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
> 2006-12-19 13:04:47.913 The process could not execute 'sp_replcmds' on
> 'PASASERV\PASLIVE'.
> 2006-12-19 13:04:47.913 Status: 0, code: 18764, text: 'Execution of filter
> stored procedure 1279200203 failed. See the SQL Server errorlog for more
> information.'.
> 2006-12-19 13:04:47.913 Status: 0, code: 18805, text: 'Logreader failed to
> construct replicated command from LSN {0000534e:00001a64:0019}.'.
> The SQL Server error log is no more revealing: -
> 2006-12-19 13:04:47.913 Status: 0, code: 22037, text: 'The process could
> not
> execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
> records scanned in pass # 1, elapsed time 1531 (ms).'.
> 2006-12-19 13:04:47.913 Status: 0, code: 20011, text: 'The process could
> not
> execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
> 2006-12-19 13:04:47.913 The process could not execute 'sp_replcmds' on
> 'PASASERV\PASLIVE'.
> 2006-12-19 13:04:47.913 Status: 0, code: 18764, text: 'Execution of filter
> stored procedure 1279200203 failed. See the SQL Server errorlog for more
> information.'.
> 2006-12-19 13:04:47.913 Status: 0, code: 18805, text: 'Logreader failed to
> construct replicated command from LSN {0000534e:00001a64:0019}.'.
> 2006-12-19 13:04:47.913 Status: 0, code: 22037, text: 'The process could
> not
> execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'.
> The first time this happened, I eventually decided to remove all the
> replication and start again, but I can't really afford to do this again.
> Also, it's a production system and we don't want to have to downgrade as
> it
> worked fine in testing.
> The only thing I can think of that might be causing it is some of the
> later
> publications I am adding (maybe the word filter is significant?) as when I
> add my initial set of publications it seems to be going fine, only messing
> up
> later.
> Any help or suggestions on this would most be gratefully received. I have
> only slept about 3 hours for the last couple of nights, trying to get this
> resolved. So much for a weekend upgrade ...
|||Hi Hilary,
Thanks for responding as usual.
I am thinking 'filter' too, because I am using filters. They used to work
under 2000 with no trouble and I built the publications fresh using the SQL
Management thing for 2005 and then asked it to script them. Although I am
not a massive fan of the scripts it produces, they are better than the
retrospective ones with loads of superfluous and often downright dangerous
parameters
There are a couple of filters I'm using that I'm worried about. On one of
the tables is: -
StockCode IN(SELECT StockCode FROM Holdings WHERE CompanyCode = 'C'
AND(UnconfirmedHoldingsUnits <> 0 OR ConfirmedHoldingUnits <> 0) AND(
StartDate >= '2004-01-01' OR ISNULL(EndDate, '2029-01-01') >= '2004-12-31')
The other is: -
WHERE CompanyCode IN(SELECT CompanyCode FROM vw_PFS_Companies)
vw_PFS_Companies is a really simple subset view.
There's nothing more complicated than those and they used to work ...
Adam
"Hilary Cotter" wrote:

> It might be a filter problem. Are you using filtering? The last time I ran
> into the could not construct replicated command was when I was replicating
> text in SQL 7.
> Can you try to see if anything works? If it works intermittently, which it
> appears to do, you need to isolate the command which is causing the log
> reader to fail.
> I would strongly recommend opening a support incident with PSS.
> --
> 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
>
> "Adam Sewell" <Adam Sewell@.discussions.microsoft.com> wrote in message
> news:37B4B5B8-D617-4101-BA6D-2FBAAA1DB00F@.microsoft.com...
>
>
|||Filters can be tricky - you might be returning more than one row in your
filter condition. Run profiler to see if you can detect the problem command.
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
"Adam Sewell" <AdamSewell@.discussions.microsoft.com> wrote in message
news:0949A8DB-5A43-43ED-B8E6-7EEFB7789683@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> Thanks for responding as usual.
> I am thinking 'filter' too, because I am using filters. They used to work
> under 2000 with no trouble and I built the publications fresh using the
> SQL
> Management thing for 2005 and then asked it to script them. Although I am
> not a massive fan of the scripts it produces, they are better than the
> retrospective ones with loads of superfluous and often downright dangerous
> parameters
> There are a couple of filters I'm using that I'm worried about. On one
> of
> the tables is: -
> StockCode IN(SELECT StockCode FROM Holdings WHERE CompanyCode = 'C'
> AND(UnconfirmedHoldingsUnits <> 0 OR ConfirmedHoldingUnits <> 0) AND(
> StartDate >= '2004-01-01' OR ISNULL(EndDate, '2029-01-01') >=
> '2004-12-31')
> The other is: -
> WHERE CompanyCode IN(SELECT CompanyCode FROM vw_PFS_Companies)
> vw_PFS_Companies is a really simple subset view.
> There's nothing more complicated than those and they used to work ...
>
> Adam
> "Hilary Cotter" wrote: