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

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 10/06/04

Next message: SThompson: "The merge process could not perform retention-based meta data clea"
Previous message: Hilary Cotter: "Re: A typical publishing configuration"
In reply to: Steve Klein: "Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ',null,'"
Next in thread: Steve Klein: "Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ',null,'"
Reply: Steve Klein: "Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ',null,'"
Messages sorted by: [ date ] [ thread ]

Date: Wed, 6 Oct 2004 08:56:25 -0400

I'm able to write to the msrepl_commands tables, but it seems like there is
a checksum value embedded in this column which makes what I write corrupt
the column. Until I figure out how to do this, you are probably better off
contacting Microsoft for help with this.

-- 
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Steve Klein" <sklein@singular.com> wrote in message
news:10m6mi66ja8p031@corp.supernews.com...
> 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
> > >
> > >
> > >
> > >
> >
> >
>
>

Next message: SThompson: "The merge process could not perform retention-based meta data clea"
Previous message: Hilary Cotter: "Re: A typical publishing configuration"
In reply to: Steve Klein: "Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ',null,'"
Next in thread: Steve Klein: "Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ',null,'"
Reply: Steve Klein: "Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ',null,'"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

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: 0x000004810000069f000900000000 ... Transaction seqno: 0x00000481000006e70001 ...
(microsoft.public.sqlserver.replication)
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: 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)