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!
>
Showing posts with label transactional. Show all posts
Showing posts with label transactional. Show all posts
Friday, March 16, 2012
Tuesday, March 6, 2012
"On demand" push subscription
Hi everyone,
Here is my problem: I need to implement an "on demand" push subscription. I am currently using a transactional replication with subscription agents running continiously. The connection between the publisher and the subscribers is pretty flaky, plus having the agents run continiously utilizes an ennormous amount of network bandwidth. At the same time the data need to be pushed to the subscribers as soon as they get into the publisher, so scheduling would not work either.
The only choice I have is to implement a solution where the distribution agents would be generally disconnected (i.e. disabled) from the subscribers, and would be enabled only when a data change is processd by the publisher.
Where do I start? Should I use triggers, DTS, or anything else? If yes, how? I am open to any suggestions.
Thank you very, very, very much!For the consistency between database why don't you use LOG SHIPPING which works in similar fashion of replication.|||Thank you for your input, Satya! I've investigated log shipping as a possible alternative, but found that it requires to be scheduled at a cerain time. I need a solution that allows an immediate update of the records.
Thank you, any additional ideas are greatly appreciated!
Originally posted by Satya
For the consistency between database why don't you use LOG SHIPPING which works in similar fashion of replication.|||True, its debatable topic between replication and log shipping.
What I mean is if you log shipping and enable Log transfer every 15 minutes between Primary and secondary server which will have easy effect of backup of live database. LS proved and saved a lot of time of mine than replication.
If you're insist to use immediate updations then better to opt for replication and make sure to suffice all the terms before using.
Refer books online for more information on REPLICATION topic.|||To Satya:
Woohoo! I resolved the problem! I have implemented the "on demand" programmatically using C#. The user decides when to actually replicate by simply pressing a button. The command initializes, runs, and terminates all of the necessary distribution agents. This way the agents are deactivated most of the time, and are activated only when the user actually puts in a batch of records and gives a command to replicate. Since the records are put in only once or twice daily, but need to be available everywhere immediately, it is a perfect solution for us. Of course, if the data were put in more often, it would make more sense to leave the agents run continuously or to use a frequently scheduled log shipping.
P.S. I used a Microsoft Distribution Control 8.0 (SQLDISTXLib)
Here is my problem: I need to implement an "on demand" push subscription. I am currently using a transactional replication with subscription agents running continiously. The connection between the publisher and the subscribers is pretty flaky, plus having the agents run continiously utilizes an ennormous amount of network bandwidth. At the same time the data need to be pushed to the subscribers as soon as they get into the publisher, so scheduling would not work either.
The only choice I have is to implement a solution where the distribution agents would be generally disconnected (i.e. disabled) from the subscribers, and would be enabled only when a data change is processd by the publisher.
Where do I start? Should I use triggers, DTS, or anything else? If yes, how? I am open to any suggestions.
Thank you very, very, very much!For the consistency between database why don't you use LOG SHIPPING which works in similar fashion of replication.|||Thank you for your input, Satya! I've investigated log shipping as a possible alternative, but found that it requires to be scheduled at a cerain time. I need a solution that allows an immediate update of the records.
Thank you, any additional ideas are greatly appreciated!
Originally posted by Satya
For the consistency between database why don't you use LOG SHIPPING which works in similar fashion of replication.|||True, its debatable topic between replication and log shipping.
What I mean is if you log shipping and enable Log transfer every 15 minutes between Primary and secondary server which will have easy effect of backup of live database. LS proved and saved a lot of time of mine than replication.
If you're insist to use immediate updations then better to opt for replication and make sure to suffice all the terms before using.
Refer books online for more information on REPLICATION topic.|||To Satya:
Woohoo! I resolved the problem! I have implemented the "on demand" programmatically using C#. The user decides when to actually replicate by simply pressing a button. The command initializes, runs, and terminates all of the necessary distribution agents. This way the agents are deactivated most of the time, and are activated only when the user actually puts in a batch of records and gives a command to replicate. Since the records are put in only once or twice daily, but need to be available everywhere immediately, it is a perfect solution for us. Of course, if the data were put in more often, it would make more sense to leave the agents run continuously or to use a frequently scheduled log shipping.
P.S. I used a Microsoft Distribution Control 8.0 (SQLDISTXLib)
Saturday, February 25, 2012
"Mark for re-initialization" hangs
Hi, I have a problem with a SQL 2000 transactional replication.
It is a replication with subscriptions on several world wide located
servers.
A WAN connection has been disabled for some time and as result one
subscription has been marked inactive.
Message: "The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be dropped and
recreated."
In the past this problem could been solved by re-initializing the
subscription but in this case
the Enterprise Manager hangs, when I tried to mark the subscription
for re-initialization.
Both SQL servers (publisher and subscriber) have been re-started but
the problem remained.
There are no sessions on the subscriber database which could block the
initialization.
The subscription can not be deleted.
What more can I do?
can you issue this command in qa on the publisher in the publication
database?
sp_reinitsubscription
'publicationname','all','SubscriberName','subscrip tionDatabase'
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185358724.772454.145180@.k79g2000hse.googlegr oups.com...
> Hi, I have a problem with a SQL 2000 transactional replication.
> It is a replication with subscriptions on several world wide located
> servers.
> A WAN connection has been disabled for some time and as result one
> subscription has been marked inactive.
> Message: "The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be dropped and
> recreated."
> In the past this problem could been solved by re-initializing the
> subscription but in this case
> the Enterprise Manager hangs, when I tried to mark the subscription
> for re-initialization.
> Both SQL servers (publisher and subscriber) have been re-started but
> the problem remained.
> There are no sessions on the subscriber database which could block the
> initialization.
> The subscription can not be deleted.
> What more can I do?
>
|||I tried out the stored procedure but the result was a hanging qa
without any status or error message.
|||use sp_who2 to identify locks/deadlocks caused by the replication processes
and selectively kill them,
Then try again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185443077.713064.265760@.r34g2000hsd.googlegr oups.com...
>I tried out the stored procedure but the result was a hanging qa
> without any status or error message.
>
|||Success!
I have killed again all sessions both on the publisher and subscriber
databases and
then the stored procedure sp_reinitsubscription worked.
Many thanks for your support.
Martin
It is a replication with subscriptions on several world wide located
servers.
A WAN connection has been disabled for some time and as result one
subscription has been marked inactive.
Message: "The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be dropped and
recreated."
In the past this problem could been solved by re-initializing the
subscription but in this case
the Enterprise Manager hangs, when I tried to mark the subscription
for re-initialization.
Both SQL servers (publisher and subscriber) have been re-started but
the problem remained.
There are no sessions on the subscriber database which could block the
initialization.
The subscription can not be deleted.
What more can I do?
can you issue this command in qa on the publisher in the publication
database?
sp_reinitsubscription
'publicationname','all','SubscriberName','subscrip tionDatabase'
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185358724.772454.145180@.k79g2000hse.googlegr oups.com...
> Hi, I have a problem with a SQL 2000 transactional replication.
> It is a replication with subscriptions on several world wide located
> servers.
> A WAN connection has been disabled for some time and as result one
> subscription has been marked inactive.
> Message: "The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be dropped and
> recreated."
> In the past this problem could been solved by re-initializing the
> subscription but in this case
> the Enterprise Manager hangs, when I tried to mark the subscription
> for re-initialization.
> Both SQL servers (publisher and subscriber) have been re-started but
> the problem remained.
> There are no sessions on the subscriber database which could block the
> initialization.
> The subscription can not be deleted.
> What more can I do?
>
|||I tried out the stored procedure but the result was a hanging qa
without any status or error message.
|||use sp_who2 to identify locks/deadlocks caused by the replication processes
and selectively kill them,
Then try again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185443077.713064.265760@.r34g2000hsd.googlegr oups.com...
>I tried out the stored procedure but the result was a hanging qa
> without any status or error message.
>
|||Success!
I have killed again all sessions both on the publisher and subscriber
databases and
then the stored procedure sp_reinitsubscription worked.
Many thanks for your support.
Martin
Friday, February 24, 2012
"Know Issue" not in KB. COLID continues to rise.
I use transactional replication on several very "wide" table. One has
254 columns. After finding that was my limit for replication to work I
removed an unused columns and added a new needed column. Since the
COLID in the SYSCOLUMNS did not reset replication failed. I'm down to
renaming columns now.
This was in SQL Server 2000 and is still present in 2005. A call to
Microsoft turned up "Known Issue" statement.
Has anyone found another way around this without creating a new table
with the needed columns and moving the current data in? Keep in mind I
inherited this database and did not create a table this wide.
Thanks.
Mike.Mike,
apart from your suggestion, the only other workaround I am aware of is to
partition the table and to replicate the 2 tables separately, which is
probably more work than your method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
254 columns. After finding that was my limit for replication to work I
removed an unused columns and added a new needed column. Since the
COLID in the SYSCOLUMNS did not reset replication failed. I'm down to
renaming columns now.
This was in SQL Server 2000 and is still present in 2005. A call to
Microsoft turned up "Known Issue" statement.
Has anyone found another way around this without creating a new table
with the needed columns and moving the current data in? Keep in mind I
inherited this database and did not create a table this wide.
Thanks.
Mike.Mike,
apart from your suggestion, the only other workaround I am aware of is to
partition the table and to replicate the 2 tables separately, which is
probably more work than your method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
"Know Issue" not in KB. COLID continues to rise.
I use transactional replication on several very "wide" table. One has
254 columns. After finding that was my limit for replication to work I
removed an unused columns and added a new needed column. Since the
COLID in the SYSCOLUMNS did not reset replication failed. I'm down to
renaming columns now.
This was in SQL Server 2000 and is still present in 2005. A call to
Microsoft turned up "Known Issue" statement.
Has anyone found another way around this without creating a new table
with the needed columns and moving the current data in? Keep in mind I
inherited this database and did not create a table this wide.
Thanks.
Mike.Mike,
apart from your suggestion, the only other workaround I am aware of is to
partition the table and to replicate the 2 tables separately, which is
probably more work than your method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
254 columns. After finding that was my limit for replication to work I
removed an unused columns and added a new needed column. Since the
COLID in the SYSCOLUMNS did not reset replication failed. I'm down to
renaming columns now.
This was in SQL Server 2000 and is still present in 2005. A call to
Microsoft turned up "Known Issue" statement.
Has anyone found another way around this without creating a new table
with the needed columns and moving the current data in? Keep in mind I
inherited this database and did not create a table this wide.
Thanks.
Mike.Mike,
apart from your suggestion, the only other workaround I am aware of is to
partition the table and to replicate the 2 tables separately, which is
probably more work than your method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Subscribe to:
Posts (Atom)
