Error inserting into MSreplication_subscriptions table

Tech-Archive recommends: Fix windows errors by optimizing your registry



Hi,
I have created a pull subscription on sql server 2000. When I tried to
start the synchronization process I encountered a failure in the
distribution agent: ?Agent message code 20046. String data, right
truncation?.
I then attempted to insert a row into the MSreplication_subscriptions
table (using QA) and received a data conversion error (from varchar to
varbinary). Can I modify the sp(which I haven?t identified) that
updates this table converting/casting to the varbinary
transaction_timestamp column ? Thanks in advance.
What follows is the log file using the ?output command on the ?run
agent step? for the distribution job):

Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
IN-REGGIEDBP01.IN.-ReggieProd-ReggieProd-JUPITER-Reggie4Replicated-3AD6E
052-8865-4CAA-9C41-15173148B647

Startup Delay: 6696 (msecs)
Connecting to Subscriber 'JUPITER'
Connecting to Subscriber 'JUPITER.Reggie4Replicated'

Server: JUPITER
DBMS: Microsoft SQL Server
Version: 08.00.2039
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

[11/6/2008 12:35:07 AM]JUPITER.Reggie4Replicated: {?=call
sp_helpsubscription_properties (N'in-reggiedbp01', N'ReggieProd', N'')}

Server: JUPITER
DBMS: Microsoft SQL Server
Version: 08.00.2039
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

Connecting to Distributor 'in-reggiedbp01.in.sfdph.net'
Connecting to Distributor 'in-reggiedbp01.in.sfdph.net.'
[11/6/2008 12:35:07 AM]in-reggiedbp01.in.sfdph.net.: exec
sp_helpdistpublisher N'in-reggiedbp01'
[11/6/2008 12:35:07 AM]in-reggiedbp01.in.sfdph.net.distribution: select
@@SERVERNAME

Server: IN-REGGIEDBP01
DBMS: Microsoft SQL Server
Version: 08.00.2050
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

[11/6/2008 12:35:07 AM]in-reggiedbp01.in.sfdph.net.distribution: execute
sp_server_info 18

ANSI codepage: 1
[11/6/2008 12:35:07 AM]in-reggiedbp01.in.sfdph.net.distribution: select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'JUPITER')
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'in-reggiedbp01')
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: {call
sp_MShelp_distribution_agentid(0, N'ReggieProd', NULL, 4,
N'Reggie4Replicated', 1)}
Initializing
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: {call
sp_MSadd_distribution_history(15, 1, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0,
0x01, 0x01)}
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: {call
sp_MSsubscription_status(15)}
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: execute
sp_MShelp_profile 15, 3, N''
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'JUPITER')
[11/6/2008 12:35:08 AM]JUPITER.Reggie4Replicated: execute sp_server_info
18

ANSI codepage: 1
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: exec
sp_datatype_info 0, 3
[11/6/2008 12:35:08 AM]JUPITER.Reggie4Replicated: exec
sp_MSreplcheck_subscribe
[11/6/2008 12:35:08 AM]JUPITER.Reggie4Replicated: exec
sp_MScreate_sub_tables @tran_sub_table = 1, @property_table = 0
[11/6/2008 12:35:08 AM]JUPITER.Reggie4Replicated: exec
dbo.sp_MSinit_subscription_agent @publisher =
N'in-reggiedbp01',@publisher_db = N'ReggieProd', @publication = N'',
@subscription_type = 1
Connecting to Distributor 'in-reggiedbp01.in.sfdph.net.distribution'
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: {call
master..sp_MScheck_agent_instance(N'IN-REGGIEDBP01-ReggieProd-JUPITER-15
', 10)}
[11/6/2008 12:35:08 AM]JUPITER.Reggie4Replicated: select
transaction_timestamp, subscription_guid from
MSreplication_subscriptions where publisher = ? and publisher_db = ? and
independent_agent = 0 and subscription_type = 1
[11/6/2008 12:35:08 AM]JUPITER.Reggie4Replicated: insert into
MSreplication_subscriptions values (?, ?, ?, 0, 1, ?, {ts '2008-11-06
00:35:08'}, NULL, ?,0, NULL, NULL, NULL, 0)
Agent message code 20046. String data, right truncation
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: {call
sp_MSadd_distribution_history(15, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1,
0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 65,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent
IN-REGGIEDBP01-ReggieProd-JUPITER-15 failed. String data, right
truncation[11/6/2008 12:35:08
AM]in-reggiedbp01.in.sfdph.net.distribution: {call
sp_MSadd_repl_alert(3, 15, 65, 14151, ?, -1, N'in-reggiedbp01',
N'ReggieProd', N'JUPITER', N'Reggie4Replicated', ?)}
ErrorId = 65, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'insert into MSreplication_subscriptions values (?, ?, ?, 0,
1, ?, {ts '2008-11-06 00:35:08'}, NULL, ?,0, NULL, NULL, NULL, 0)'
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: {call
sp_MSadd_repl_error(65, 0, 1, ?, N'', ?)}

Category:COMMAND
Source: Failed Command
Number:
Message: insert into MSreplication_subscriptions values (?, ?, ?, 0, 1,
?, {ts '2008-11-06 00:35:08'}, NULL, ?,0, NULL, NULL, NULL, 0)
ErrorId = 65, SourceTypeId = 4
ErrorCode = '22001'
ErrorText = 'String data, right truncation'
[11/6/2008 12:35:08 AM]in-reggiedbp01.in.sfdph.net.distribution: {call
sp_MSadd_repl_error(65, 0, 4, ?, N'22001', ?)}

Category:ODBC
Source: ODBC SQL Server Driver
Number: 22001
Message: String data, right truncation
Disconnecting from Subscriber 'JUPITER'
Disconnecting from Distributor 'in-reggiedbp01.in.sfdph.net'
Disconnecting from Distributor History 'in-reggiedbp01.in.sfdph.net'
Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
IN-REGGIEDBP01.IN.-ReggieProd-ReggieProd-JUPITER-Reggie4Replicated-3AD6E
052-8865-4CAA-9C41-15173148B647



*** Sent via Developersdex http://www.developersdex.com ***
.

Relevant Pages

  • Re: Invalid cursor state
    ... For instance if you restart your Distribution ... Agent do you get it again? ... Looking for a book on SQL Server replication? ... I want to build up a transactional replication between 2 SQL Server ...
    (microsoft.public.sqlserver.replication)
  • Re: Deadlock between Distribution Agent and Distribution Agent Cle
    ... Another option will be to put your distribution database in bulk copy ... Looking for a SQL Server replication book? ... The Distribution Agent Cleanup job code ... and only reading the transactions committed some ...
    (microsoft.public.sqlserver.replication)
  • Re: Network traffic with transactional replication with immediate updates (SQL 2005)
    ... publisher/distributor running on the main office and a subscriber at the ... This is a new SQL Server 2005 behaviour, ... the agent should execute them (maybe the agent just ... I cannot find a way to explain why Distributor Agents are to ...
    (microsoft.public.sqlserver.replication)
  • Re: Network traffic with transactional replication with immediate updates (SQL 2005)
    ... publisher/distributor running on the main office and a subscriber at the ... the agent should execute them (maybe the agent just launches ... Looking for a SQL Server replication book? ... I cannot find a way to explain why Distributor Agents are to ...
    (microsoft.public.sqlserver.replication)
  • Re: Agent Deadlock Errors
    ... I am not sure about updating the statistics in the distribution database. ... clean up agent is locking with. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)