Dear Friends
I am facing the problem in my accounting database
replication there is one table with 1024 column and hence
the replication is failing as the maximum size is 1024
and the rowguide column can not be created.
Please guide if there is any way out to do the replicaion
with 1025 column in the table.
I can not do anything in this table as the applicaion is
third party. ( Can not delete any column)
Your earlier reply would be a great help.
Best regards
Sharad
Hi
SQL Server does not allow more than 1024 columns for a Table.
A system with 1024 columns...especially accounting. I hope you got it for
free and are looking at replacing it soon. No DBA who knows what he is doing
would design such a table. It is totally un-manageable, and with a
limitation of 8k per row, if every column were populated, each column could
be only 8 bytes.
There is no solution for you as you have hit a system limitation.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sharad" <Niitmalad@.yahoo.co.in> wrote in message
news:2a6201c4eb7e$811d3c40$a401280a@.phx.gbl...
> Dear Friends
> I am facing the problem in my accounting database
> replication there is one table with 1024 column and hence
> the replication is failing as the maximum size is 1024
> and the rowguide column can not be created.
> Please guide if there is any way out to do the replicaion
> with 1025 column in the table.
> I can not do anything in this table as the applicaion is
> third party. ( Can not delete any column)
> Your earlier reply would be a great help.
> Best regards
> Sharad
>
|||Hello Sharad,
what about splitting of your article (table) into two publications?
Greetings from Cologne
Thomas
"Sharad" wrote:
> Dear Friends
> I am facing the problem in my accounting database
> replication there is one table with 1024 column and hence
> the replication is failing as the maximum size is 1024
> and the rowguide column can not be created.
> Please guide if there is any way out to do the replicaion
> with 1025 column in the table.
> I can not do anything in this table as the applicaion is
> third party. ( Can not delete any column)
> Your earlier reply would be a great help.
> Best regards
> Sharad
>
>
|||obviously Mike has never heard of PeopleSoft.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:exm6Ea46EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi
> SQL Server does not allow more than 1024 columns for a Table.
> A system with 1024 columns...especially accounting. I hope you got it for
> free and are looking at replacing it soon. No DBA who knows what he is
doing
> would design such a table. It is totally un-manageable, and with a
> limitation of 8k per row, if every column were populated, each column
could
> be only 8 bytes.
> There is no solution for you as you have hit a system limitation.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Sharad" <Niitmalad@.yahoo.co.in> wrote in message
> news:2a6201c4eb7e$811d3c40$a401280a@.phx.gbl...
>
Showing posts with label failing. Show all posts
Showing posts with label failing. Show all posts
Wednesday, March 21, 2012
Saturday, February 25, 2012
Replication keeps failing after one failure
Hi,
We are running Merge Replication on Sql Server 2K with multiples MSDE 2K subsciber.The Replication is getting done through a Visual Basic Application and it works fine as long as it does not fail.Once the replication fails on the Clients machine then we always need to blow the Local Database on the Client's machine and then start the Merge Replication process again.I wonder Why?Any help is really appriciated.. :(It's necessary adjust the retry count for the merge agent, in the event it fails (e.g. remote computer is turned off, network failure etc.). Expand Replication monitor, expand agents, click on merge agents. A list of merge agents, corresponding to each location is listed on the right. To change retry count for a merge agent, highlight the merge agent, right click and select agent properties. Click on tab "Steps", Highlight ID 2 (step 2 - run agent), click on edit. Step name should say "Run agent". Click on Advanced tab, and adjust the retry count and retry interval.
:D|||Thanks for your reply and I will increase the Number of Retry but my question is still unanswered and that's Why the Replication keeps failing after one failure?Is this turning on some flag somewhere in the Sql Server after a failure?|||Ok, I'm confused.
Replication is done via a Visual Basic program?
Replication is a configuration of Sql Server. Has nothing to do with Visual Basic.
What kind of Merge replication is setup? Merge continuosly, or scheduled merge?
If Merge continuously, then the retry count and schedule HAS to be changed. The defaults will cause the agent to fail if the connection is down for just 10 minutes. (10 retries, at 1 minute intervals).
Also, when you say replication fails, there are error messages that can be viewed via replication monitor. The agent may try 10 times, and on failure display 1 message. That doesn't mean there were no retries.|||It's a Merge Replication which runs on Demand through Visual Basic Application.The process works fine 9/10 times but if its fails then it keeps failing and the error log says "The Process was Successfully Stopped".I also don't think that it involves any network issue as the subscriber is connected to the network all the time.
Also once the Synchronization fails then we need to set the Initial database at the subscriber(which involves defining the subscriber,creating the local DB,Copying Roles and creating a dynamic Merge Replication job) and then do the Regular Synchronization,which works fine thereafter.
I don't know Y?:confused:
We are running Merge Replication on Sql Server 2K with multiples MSDE 2K subsciber.The Replication is getting done through a Visual Basic Application and it works fine as long as it does not fail.Once the replication fails on the Clients machine then we always need to blow the Local Database on the Client's machine and then start the Merge Replication process again.I wonder Why?Any help is really appriciated.. :(It's necessary adjust the retry count for the merge agent, in the event it fails (e.g. remote computer is turned off, network failure etc.). Expand Replication monitor, expand agents, click on merge agents. A list of merge agents, corresponding to each location is listed on the right. To change retry count for a merge agent, highlight the merge agent, right click and select agent properties. Click on tab "Steps", Highlight ID 2 (step 2 - run agent), click on edit. Step name should say "Run agent". Click on Advanced tab, and adjust the retry count and retry interval.
:D|||Thanks for your reply and I will increase the Number of Retry but my question is still unanswered and that's Why the Replication keeps failing after one failure?Is this turning on some flag somewhere in the Sql Server after a failure?|||Ok, I'm confused.
Replication is done via a Visual Basic program?
Replication is a configuration of Sql Server. Has nothing to do with Visual Basic.
What kind of Merge replication is setup? Merge continuosly, or scheduled merge?
If Merge continuously, then the retry count and schedule HAS to be changed. The defaults will cause the agent to fail if the connection is down for just 10 minutes. (10 retries, at 1 minute intervals).
Also, when you say replication fails, there are error messages that can be viewed via replication monitor. The agent may try 10 times, and on failure display 1 message. That doesn't mean there were no retries.|||It's a Merge Replication which runs on Demand through Visual Basic Application.The process works fine 9/10 times but if its fails then it keeps failing and the error log says "The Process was Successfully Stopped".I also don't think that it involves any network issue as the subscriber is connected to the network all the time.
Also once the Synchronization fails then we need to set the Initial database at the subscriber(which involves defining the subscriber,creating the local DB,Copying Roles and creating a dynamic Merge Replication job) and then do the Regular Synchronization,which works fine thereafter.
I don't know Y?:confused:
Tuesday, February 21, 2012
Replication is failing due to use of FTS on my subscriber, what can I do?
Hi Daniel,
If you are using push subscriptions from a SQL2000 distributor, you are
essentially using the SQL2000 replication components which, I am sorry to
say, are ill-equipped to handle things like full-text indexes during
snapshot processing. This also means that upgrading the subscriber to
SQL2005 SP2 will probably not do you any good. Alternatively, you can use a
SQL2005 SP2 instance as the distributor of your SQL2000 publisher so you can
enable the FulltextIndex (0x0000000001000000) article schema option via
sp_add|changearticle (both SQL2000 and SQL2005 GUI will not allow you to
specify that for a SQL2000 publisher) and let the SQL2005 replication
components handle the full-text index dependencies for you (ideally using
"drop" as the article pre-creation commands). That said, I can imagine that
moving the distributor can involve a substantial amount of work.
Hope that helps,
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:uAOtEPAjHHA.1272@.TK2MSFTNGP04.phx.gbl...
> I'm in the middle of setting up a non-updating transactional replication
> from SQL Server 2000 SP3 (can't put SP4 on as the main application we use
> hasn't been certified for SP4 yet) to SQL Server 2005 SP1 (I could put SP2
> on if it will fix the issue, but it would mean taking our e-commerce sites
> down while doing so and I'd need to schedule a maintenance window for
> this). At first it was working fine, but snapshots were set to delete the
> tables and so we'd lose the FTS setups. I've now changed the snapshots to
> not do this, and now the replications fails with the error:
> Cannot drop index 'PK_Product' because it enforces the full-text key for
> table or indexed view 'Product'.
> This occurs with both "Delete data. If article has a row filter, delete
> only data that matches the filter.", "Truncate all data in the existing
> object" (which is the preferred option), and "Keep existing object
> unchanged". It also doesn't matter if I have the clustered and/or
> nonclustered index copy set to true or false (the PK is the clustered
> index on this table).
> This is a push subscription, with the distribution agent running on the
> publisher.
> Any ideas how I can retain the table structure (so keeping the FTS index
> and not having to rebuild it each time the snapshot is applied) and have
> transaction replication working? Or is it not possible?
> Dan
>
Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
|||Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
If you are using push subscriptions from a SQL2000 distributor, you are
essentially using the SQL2000 replication components which, I am sorry to
say, are ill-equipped to handle things like full-text indexes during
snapshot processing. This also means that upgrading the subscriber to
SQL2005 SP2 will probably not do you any good. Alternatively, you can use a
SQL2005 SP2 instance as the distributor of your SQL2000 publisher so you can
enable the FulltextIndex (0x0000000001000000) article schema option via
sp_add|changearticle (both SQL2000 and SQL2005 GUI will not allow you to
specify that for a SQL2000 publisher) and let the SQL2005 replication
components handle the full-text index dependencies for you (ideally using
"drop" as the article pre-creation commands). That said, I can imagine that
moving the distributor can involve a substantial amount of work.
Hope that helps,
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:uAOtEPAjHHA.1272@.TK2MSFTNGP04.phx.gbl...
> I'm in the middle of setting up a non-updating transactional replication
> from SQL Server 2000 SP3 (can't put SP4 on as the main application we use
> hasn't been certified for SP4 yet) to SQL Server 2005 SP1 (I could put SP2
> on if it will fix the issue, but it would mean taking our e-commerce sites
> down while doing so and I'd need to schedule a maintenance window for
> this). At first it was working fine, but snapshots were set to delete the
> tables and so we'd lose the FTS setups. I've now changed the snapshots to
> not do this, and now the replications fails with the error:
> Cannot drop index 'PK_Product' because it enforces the full-text key for
> table or indexed view 'Product'.
> This occurs with both "Delete data. If article has a row filter, delete
> only data that matches the filter.", "Truncate all data in the existing
> object" (which is the preferred option), and "Keep existing object
> unchanged". It also doesn't matter if I have the clustered and/or
> nonclustered index copy set to true or false (the PK is the clustered
> index on this table).
> This is a push subscription, with the distribution agent running on the
> publisher.
> Any ideas how I can retain the table structure (so keeping the FTS index
> and not having to rebuild it each time the snapshot is applied) and have
> transaction replication working? Or is it not possible?
> Dan
>
Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
|||Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
Subscribe to:
Posts (Atom)