Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ',null,'

From: Steve Klein (sklein_at_singular.com)
Date: 10/06/04 

Date: Tue, 5 Oct 2004 19:36:44 -0700

Hilary--

You mentioned manipulating the command in the msrepl_commands table. We've
found the correct row. Is the goal to fix the command, or would just
deleting it work? If the goal is to fix it, any recommendations on how to
edit the command field (varbinary)?

Here is the schema for the table involved:

CREATE TABLE [dbo].[ESS] (
        [SERVER_ID] [int] NOT NULL ,
        [ESS_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
        [DESCRIPTION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [DISCOVER_DATE] [datetime] NOT NULL ,
        [SUBMISSION_SOURCE_ID] [int] NULL ,
        [COMPANY_ID] [int] NULL ,
        [TARGET_COMPANY_URL] [varchar] (200) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [EMAIL_FORMAT_ID] [int] NULL ,
        [EMAIL_SUBJECT] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [EMAIL_FROM] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [EMAIL_FINGERPRINT] [bigint] NULL ,
        [EMAIL_FILENAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [VISIBLE_URL] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [MAIN_URL] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
        [SUBMISSION_DATE] [datetime] NULL ,
        [CREATED_DATE] [datetime] NULL ,
        [LAST_UPDATED_DATE] [datetime] NULL ,
        [PROCESSED_DATE] [datetime] NULL ,
        [PROCESSED_NOTE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

And here is the log file from the Push Agent:

Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
PG1-Ph-Ph-PG4-260D498E19AE4507B703BFBBC333F6F0

Startup Delay: 3425 (msecs)
Connecting to Distributor 'PG1'
Connecting to Distributor 'PG1.'
[10/5/2004 6:49:42 PM]PG1.: exec sp_helpdistpublisher N'PG1'
[10/5/2004 6:49:42 PM]PG1.distribution: select @@SERVERNAME

Server: PG1
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/5/2004 6:49:42 PM]PG1.distribution: execute sp_server_info 18

ANSI codepage: 1
[10/5/2004 6:49:42 PM]PG1.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'PG4')

[10/5/2004 6:49:42 PM]PG1.distribution: {?=call sp_MShelp_subscriber_info
(N'PG1', N'PG4')}
Subscriber security mode: 0, login name: sa.
[10/5/2004 6:49:43 PM]PG1.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'PG1')

[10/5/2004 6:49:43 PM]PG1.distribution: {call
sp_MShelp_distribution_agentid(0, N'Ph', N'Ph', 2, N'Ph', 0)}
Initializing
[10/5/2004 6:49:43 PM]PG1.distribution: {call
sp_MSadd_distribution_history(2, 1, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01,
0x01)}

[10/5/2004 6:49:43 PM]PG1.distribution: {call sp_MSsubscription_status(2)}
[10/5/2004 6:49:43 PM]PG1.distribution: execute sp_MShelp_profile 2, 3, N''
Connecting to Subscriber 'PG4'
[10/5/2004 6:49:43 PM]PG1.distribution: {call
sp_MSadd_distribution_history(2, 3, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01,
0x01)}

Connecting to Subscriber 'PG4.Ph'

Server: PG4
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/5/2004 6:49:44 PM]PG1.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'PG4')

[10/5/2004 6:49:44 PM]PG4.Ph: execute sp_server_info 18

ANSI codepage: 1
[10/5/2004 6:49:44 PM]PG1.distribution: exec sp_datatype_info 0, 3
[10/5/2004 6:49:44 PM]PG4.Ph: exec sp_MSreplcheck_subscribe
[10/5/2004 6:49:44 PM]PG4.Ph: exec sp_MScreate_sub_tables @tran_sub_table =
1, @property_table = 0
[10/5/2004 6:49:44 PM]PG4.Ph: exec dbo.sp_MSinit_subscription_agent
@publisher = N'PG1',@publisher_db = N'Ph', @publication = N'Ph',
@subscription_type = 0

Connecting to Distributor 'PG1.distribution'
[10/5/2004 6:49:45 PM]PG1.distribution: {call
master..sp_MScheck_agent_instance(N'PG1-Ph-Ph-PG4-260D498E19AE4507B703BFBBC3
33F6F0', 10)}

[10/5/2004 6:49:45 PM]PG4.Ph: select transaction_timestamp,
subscription_guid from MSreplication_subscriptions where publisher = ? and
publisher_db = ? and publication= ? and subscription_type = 0

[10/5/2004 6:49:45 PM]PG4.Ph: exec dbo.sp_MSupdatelastsyncinfo N'PG1',N'Ph',
N'Ph', 0, 1, N'Synchronization in progress'

[10/5/2004 6:49:45 PM]PG1.distribution: {call sp_MSget_subscription_guid(2)}
[10/5/2004 6:49:45 PM]PG1.distribution: {call sp_MSget_repl_commands(2, ?,
0, 7500000)}
sp_MSget_repl_commands timestamp value is: 0x000004810000069f000900000000
Last transaction timestamp: 0x000004810000069f000900000000
Transaction seqno: 0x00000481000006e70001
Command Id: 1
Partial: 0
Type: 12
Command: [PG4][Ph]DELETE [ESS_DETAILS] where [SERVER_ID] = 4and
[ESS_Detail_ID] = 12232

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 2
Partial: 1
Type: 12
Command: [PG4][Ph]DELETE [ESS_DETAILS] where [SERVER_ID] = 4and
[ESS_Detail_ID] = 12232

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 3
Partial: 1
Type: 12
Command: [10/5/2004 6:49:45 PM]PG4.Ph: DELETE [ESS_DETAILS] where
[SERVER_ID] = 4and [ESS_Detail_ID] = 12232

DELETE [ESS_DETAILS] where [SERVER_ID] = 4and [ESS_Detail_ID] = 12232

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 4
Partial: 0
Type: 12
Command: DELETE [ESS_DETAILS] where [SERVER_ID] = 4and [ESS_Detail_ID] =
12232

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 5
Partial: 0
Type: 12
Command: [PG4][Ph]DELETE [ESS] where [SERVER_ID] = 4and [ESS_ID] = 19533

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 6
Partial: 1
Type: 12
Command: [PG4][Ph]EXEC sp_MSins_ESS 4,19533,null,'2004-10-03
22:04:29.417',1,null,null,0,'Notice Wells Fargo update','"Wells Fargo"
<atmservice@wellsfargo.com>',3722469734,'PG4-20041003-19533',null

[10/5/2004 6:49:46 PM]PG4.Ph: DELETE [ESS_DETAILS] where [SERVER_ID] =
4and [ESS_Detail_ID] = 12232DELETE [ESS_DETAILS] where [SERVER_ID] =
4and [ESS_Detail_ID] = 12232DELETE [ESS_DETAILS] where [SERVER_ID] =
4and [ESS_Detail_ID] = 12232

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 7
Partial: 0
Type: 12
Command: ',null,'2004-10-03 22:04:29.417','2004-10-03
22:04:29.417',null,'2004-10-03
22:00:39.240',null,'4541D8DA-CB2A-4AE1-B074-012B7839D6D1'

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 8
Partial: 0
Type: 12
Command: [PG4][Ph]DELETE [ESS_DETAILS] where [SERVER_ID] = 4and
[ESS_Detail_ID] = 12233

[10/5/2004 6:49:46 PM]PG4.Ph: DELETE [ESS] where [SERVER_ID] = 4and
[ESS_ID] = 19533

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 9
Partial: 1
Type: 12
Command: [PG4][Ph]DELETE [ESS_DETAILS] where [SERVER_ID] = 4and
[ESS_Detail_ID] = 12233

[10/5/2004 6:49:46 PM]PG4.Ph: EXEC sp_MSins_ESS 4,19533,null,'2004-10-03
22:04:29.417',1,null,null,0,'Notice Wells Fargo update','"Wells Fargo"
<atmservice@wellsfargo.com>',3722469734,'PG4-20041003-19533',null',null,'200
4-10-03 22:04:29.417','2004-10-03 22:04:29.417',null,'2004-10-03
22:00:39.240',null,'4541D8DA-CB2A-4AE1-B074-012B7839D6D1'

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 10
Partial: 1
Type: 12
Command: DELETE [ESS_DETAILS] where [SERVER_ID] = 4and [ESS_Detail_ID] =
12233

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 11
Partial: 0
Type: 12
Command: DELETE [ESS_DETAILS] where [SERVER_ID] = 4and [ESS_Detail_ID] =
12233

Last transaction timestamp: 0x00000481000006e7000100000000
Transaction seqno: 0x00000481000006e70001
Command Id: 12
Partial: 0
Type: 12
Command: [PG4][Ph]DELETE [ESS] where [SERVER_ID] = 4and [ESS_ID] = 19533

Agent message code 20046. Line 1: Incorrect syntax near ',null,'.
[10/5/2004 6:49:47 PM]PG1.distribution: {call
sp_MSadd_distribution_history(2, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 6, 0x01,
0x01)}

Adding alert to msdb..sysreplicationalerts: ErrorId = 6,
Transaction Seqno = 00000481000006e7000100000000, Command ID = 6
Message: Replication-Replication Distribution Subsystem: agent
PG1-Ph-Ph-PG4-260D498E19AE4507B703BFBBC333F6F0 failed. Line 1: Incorrect
syntax near ',null,'.[10/5/2004 6:49:47 PM]PG1.distribution: {call
sp_MSadd_repl_alert(3, 2, 6, 14151, ?, 6, N'PG1', N'Ph', N'PG4', N'Ph', ?)}

ErrorId = 6, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'EXEC sp_MSins_ESS 4,19533,null,'2004-10-03
22:04:29.417',1,null,null,0,'Notice Wells Fargo update','"Wells Fargo"
<atmservice@wellsfargo.com>',3722469734,'PG4-20041003-19533',null',null,'200
4-10-03 22:04:29.417','2004-10-03 22:04:29.417',null,'2004-10-03
22:00:39.240',null,'4541D8DA-CB2A-4AE1-B074-012B7839D6D1''

[10/5/2004 6:49:47 PM]PG1.distribution: {call sp_MSadd_repl_error(6, 0, 1,
?, N'', ?)}

Category:COMMAND
Source: Failed Command
Number:
Message: EXEC sp_MSins_ESS 4,19533,null,'2004-10-03
22:04:29.417',1,null,null,0,'Notice Wells Fargo update','"Wells Fargo"
<atmservice@wellsfargo.com>',3722469734,'PG4-20041003-19533',null',null,'200
4-10-03 22:04:29.417','2004-10-03 22:04:29.417',null,'2004-10-03
22:00:39.240',null,'4541D8DA-CB2A-4AE1-B074-012B7839D6D1'

ErrorId = 6, SourceTypeId = 5
ErrorCode = '170'
ErrorText = 'Line 1: Incorrect syntax near ',null,'.'
[10/5/2004 6:49:47 PM]PG1.distribution: {call sp_MSadd_repl_error(6, 0, 5,
?, N'170', ?)}

Category:SQLSERVER
Source: PG4
Number: 170
Message: Line 1: Incorrect syntax near ',null,'.
ErrorId = 6, SourceTypeId = 5
ErrorCode = '105'
ErrorText = 'Unclosed quotation mark before the character string ''.'
[10/5/2004 6:49:47 PM]PG1.distribution: {call sp_MSadd_repl_error(6, 0, 5,
?, N'105', ?)}

Category:SQLSERVER
Source: PG4
Number: 105
Message: Unclosed quotation mark before the character string ''.
[10/5/2004 6:49:47 PM]PG4.Ph: exec dbo.sp_MSupdatelastsyncinfo N'PG1',N'Ph',
N'Ph', 0, 6, N'Line 1: Incorrect syntax near '',null,''.'

Disconnecting from Subscriber 'PG4'
Disconnecting from Distributor 'PG1'
Disconnecting from Distributor History 'PG1'

Thanks,

--Steve

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:uC07mh0qEHA.2796@tk2msftngp13.phx.gbl...
> It looks like a bug here. Could you post the schema of the problem table
> here?
>
> To get around this you should open a support incident with Microsoft.
>
> It is possible to manipulate this command in the msrepl_commands table to
> fix it.
>
>
>
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Steve Klein" <sklein@singular.com> wrote in message
> news:10m6abdovj1e519@corp.supernews.com...
> > Greetings--
> >
> > We are running queued updatable transactional replication between one
SQL
> > Server 2000 and one MSDE instance (SP3 on both). Replication died last
> night
> > with the following error --
> >
> > Distribution Agent Line 1: Incorrect syntax near ',null,'
> >
> > Last command:
> >
> > EXEC sp_MSins_<tablename> 4,19533,null,'2004-10-03
> > 22:04:29.417',1,null,null,0,'Notice Wells Fargo update','"Wells Fargo"
> >
>
<atmservice@wellsfargo.com>',3722469734,'PG4-20041003-19533',null',null,'200
> > 4-10-03 22:04:29.417','2004-10-03 22:04:29.417',null,'2004-10-03
> > 22:00:39.240',null,'4541D8DA-CB2A-4AE1-B074-012B7839D6D1'
> > Transaction sequence number and command ID of last execution batch are
> > 0x00000481000006E7000100000000 and 6.
> >
> >
> >
> > You'll notice that the SQL is malformed at the 13th parameter:
> > ...,null',...
> >
> > We are trying to figure out the following:
> >
> > 1. How to re-establish replication
> > 2. How this happened
> > 3. How to keep it from happening again!
> >
> > Any and all help appreciated!
> >
> > --Steve Klein
> >
> >
> >
> >
>
>

Relevant Pages

  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... private void HandleCallback ... SqlCommand command = result.AsyncState; ... the transaction completes I get back to the point just before I clicked "OK" ... par = cm.CreateParameter; ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Transactions without setting OleDbCommand.Transaction property
    ... roundtrip since we will batch the first Begin Transaction command with the ... track of the transaction, so if you leak it we will explicitly roll it back ... "Osvaldo Bisignano" wrote in message ... > On the other hand, I think that when you run an ExecuteNonQuery, an ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ,null,
    ... > You mentioned manipulating the command in the msrepl_commands table. ... > transaction capable: 2 ... > Last transaction timestamp: 0x00000481000006e7000100000000> Transaction seqno: 0x00000481000006e70001> Command Id: 2 ...
    (microsoft.public.sqlserver.replication)
  • Re: C# and SQL - how to insert few lines together
    ... You should dispose of your connection and transaction objects when you are no longer using them. ... In your example you are iterating 2 times and executing the command each time without adjusting the parameter's value. ... >>> unique and can not be sold twice ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: trans repl - distribution agent doesnt deliver transactions
    ... Looking for a SQL Server replication book? ... Last transaction timestamp: 0x000008da0004fa9b002c00000000 ... Transaction seqno: 0x000008da0004fa9b002c ... Command Id: 19 ...
    (microsoft.public.sqlserver.replication)