I'm doing a transactional replication for the first time between 2
servers.
After 14 minutes of running (according to the logs), the replicaton
crashes. This is what I find in the log file generated by the
distribution agent. It starts bulk copying data from many tables until
it finally crashes on the first copy.
Any ideas why?
What does it mean 'Text column data incomplete'.
Table Parcel has 2 text columns that can be NULL.
Also, at the time the replication was taking place, it is possible
other transactions might have been being added/deleted from
Parcel......could this produce this type of error?
Here goes the erro log:
Bulk copying data into table 'Parcel'
:
Bulk copying data into table 'Item Specific Cost Factor'
:
Bulk copying data into table 'Item Appraisal Details'
:
:
Agent message code 20037. The process could not bulk copy into table
'"Parcel"'.
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
414, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0004158900000066000100000001, Command ID = 414
Message: Replication-Replication Distribution Subsystem: agent
SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
failed.
The process could not bulk copy into table '"Parcel"'.[6/16/2004
10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
N'TestDB', ?)}
ErrorId = 9, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'select * from "Parcel Item" where 1 = 2
'
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: select * from "Parcel Item" where 1 = 2
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: 68.216.171.51
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
[6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
table ''"Parcel"''.'
Disconnecting from Subscriber '......'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
is the database collations on the subscriber and publisher the same?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Rocio" <rocio.katsanis@.softwareservices.net> wrote in message
news:7b914006.0406161119.2936cea7@.posting.google.c om...
> I'm doing a transactional replication for the first time between 2
> servers.
> After 14 minutes of running (according to the logs), the replicaton
> crashes. This is what I find in the log file generated by the
> distribution agent. It starts bulk copying data from many tables until
> it finally crashes on the first copy.
> Any ideas why?
> What does it mean 'Text column data incomplete'.
> Table Parcel has 2 text columns that can be NULL.
> Also, at the time the replication was taking place, it is possible
> other transactions might have been being added/deleted from
> Parcel......could this produce this type of error?
> Here goes the erro log:
> Bulk copying data into table 'Parcel'
> :
> Bulk copying data into table 'Item Specific Cost Factor'
> :
> Bulk copying data into table 'Item Appraisal Details'
> :
> :
> Agent message code 20037. The process could not bulk copy into table
> '"Parcel"'.
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
> 414, 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
> Transaction Seqno = 0004158900000066000100000001, Command ID = 414
> Message: Replication-Replication Distribution Subsystem: agent
> SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
> failed.
> The process could not bulk copy into table '"Parcel"'.[6/16/2004
> 10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
> 14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
> N'TestDB', ?)}
> ErrorId = 9, SourceTypeId = 1
> ErrorCode = ''
> ErrorText = 'select * from "Parcel Item" where 1 = 2
> '
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: select * from "Parcel Item" where 1 = 2
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: 68.216.171.51
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> [6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
> table ''"Parcel"''.'
> Disconnecting from Subscriber '......'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
|||Hi Hillary,
I treid the same replication 3 more times, and the erro I got on those 3
times was different than the one I published here. Now the error is:
Category:SQLSERVER
Source: <IP of subscriber...>
Number: 2812
Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
[6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
datastream processing of parameterized command.'
Disconnecting from Subscriber '<IP of subscriber...>'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
If I looked carefully at this log file, and it seems to me that such
stored procedure is somehow created:
[6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
"[sp_MSupd_Entity Address]"
@.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
int,@.c10 varchar(500),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "Entity Address" set
"Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"Address ID" end
,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "Entity Address" set
"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
however, if I look at TestDB in my subscriber, I can find
[sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
with brackets, the other not.
why 2 sp's anyway (with & without brackets)?
how can I control the creation of this procedure? I looked at the
properties tab of this publictaion at the Publisher, under Article
Entity Address, and make sure all the options are selected (e.g. Replace
INSERT commands with this stored procedure, etc).
The only option I could not access is the one at the very bottom that
causes replication to use the columns name. It was just enabled.
Any ideas?
btw: your book about Transactional & Snapshot replication looks good, I
haven't found a good book on replication yet! Is it out already?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||on your publisher do this
sp_scriptpublicationcustomprocs 'publicationName'
then in your results pan, copy the results and then paste them into another ISQLW window which you open into your subsciption database. then execute this script there.
Restart your distribution agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rocio Katsanis" wrote:
> Hi Hillary,
> I treid the same replication 3 more times, and the erro I got on those 3
> times was different than the one I published here. Now the error is:
> Category:SQLSERVER
> Source: <IP of subscriber...>
> Number: 2812
> Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
> [6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
> datastream processing of parameterized command.'
> Disconnecting from Subscriber '<IP of subscriber...>'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
> If I looked carefully at this log file, and it seems to me that such
> stored procedure is somehow created:
> [6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
> "[sp_MSupd_Entity Address]"
> @.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
> int,@.c10 varchar(500),@.pkc1 int
> ,@.bitmap binary(2)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "Entity Address" set
> "Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "Address ID" end
> ,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "Entity Address" set
> "Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> however, if I look at TestDB in my subscriber, I can find
> [sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
> with brackets, the other not.
> why 2 sp's anyway (with & without brackets)?
> how can I control the creation of this procedure? I looked at the
> properties tab of this publictaion at the Publisher, under Article
> Entity Address, and make sure all the options are selected (e.g. Replace
> INSERT commands with this stored procedure, etc).
> The only option I could not access is the one at the very bottom that
> causes replication to use the columns name. It was just enabled.
> Any ideas?
> btw: your book about Transactional & Snapshot replication looks good, I
> haven't found a good book on replication yet! Is it out already?
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
tobias5: