From: Manoj Kumar on

Hi there,

I am facing an issue at the Tx replication side. Pub. & Sub are in the same
server [sql 2005+sp3 standard]



Typical error msg is as follows,

Replication-Replication Distribution Subsystem: agent DEV-DB-DEV-17 failed.
Syntax error, permission violation, or other nonspecific error

In the distributor to subscriber tab of the replication monitor, i can see
that a TRANSACTION SEQUENCE NUMBER AND COMMAND ID.

I have used the following command and i see that there are many commands and
upon investigating each one of them I have noticed that a specific record has
not been replicated from source to destination.


exec sp_browsereplcmds
@xact_seqno_start = '0x000006AB0000035E009800000000',
@xact_seqno_end = '0x000006AB0000035E009800000000' ,
@publisher_database_id = 1,
@command_id = 12



Also inspecting the command, i do not see date fields are not in quotes,
could that be the issue? At this point, i had to reinitialize the replication
to make it work.



{CALL sp_MSins_US_DBTable$Invoice_Line
(0x00000000D3F3C657,'516757',10000,'WWE6XLI',10000,'WE6XLI','','CUST094','N',0,'','AL-F4KT-TT-
JZM',1.00000000000000000000,0.00000000000000000000,1.00000000000000000000,1.00000000000000000000,0.00000000000000000000,0,'DD-L7',2010-01-20
00:00:00.000,2011-01-19 00:00:00.000,1,0,'',2010-02-27
00:00:00.000,'USA',0,'50000507',1753-01-01 00:00:00.000,0)}



Any other areas that I could check to ensure that the issue does not happen
again and if it happens again what would be the ideal trouble shooting and
resolve steps?





-- TIA
Manoj Kumar
From: Manoj Kumar on
Hi Paul,

Yes, i will do that next time when i encounter the issue as I have
reinitialized the repl. and users did not reporduce the issue yet.

Btw, last time when i have encountered this i have used only the parameter
section and replaced the repl. stored proc call with a insert into
tblname..., and it did result in an error as the date data type values were
not in quotes.


-- TIA
Manoj Kumar


"Paul Ibison" wrote:

> Can you run the proc call at the subscriber manually in SSMS? This'd rule
> out the syntax error issue if so.
> HTH,
> Paul Ibison
>