Hello,
I add an article to a live publisher. The table to be added has foreign key
relations. At publisher, under "action if there is already an object": Drop
existing object and create a new one. The subscriber database was restored
from the publisher a while ago.
When I sync them (Replicate Schema Changes=True), I got an error at
subscriber saying that the table can not be dropped because the foreign
relationship.
Is there a way make the replication agent drop the relation of the table and
then create it without changing any other articles? Or anyway to avoid this
failure?
“Just don't drop the table” is an easy solution. However, my problem is that
the table schema has been changed since the database restored. So I still
have to apply the table changes to the subscriber.
Thank you in advance for help.
John
Hi John,
I understand that when you added an article to your live publication under
"action if there is already an object": Drop existing object and create a
new one, the following error came out during the synchronization:
the table can not be dropped because the foreign relationship. The
subscriber was restored from the publisher a while ago and the table schema
has been changed since the database restored.
If I have misunderstood, please let me know.
To let me better understand your issue, I would like to confirm with you
the following questions:
1. What is your replication type, snapshot, merge or transactional?
2. Did you mean that the added table existed in the database originally but
just the schema changed?
3. What is the synchronization type of your replication, web
synchronization or not?
4. Did you know which tables were being dropped and the relationships among
those tables and the added table?
5. Why are there some tables being dropped since the added table was not
replicated before?
I really hope that you could describe your environment more detailed so
that I can perform a test to reproduce your issue at my side. Look forward
to your response.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Paul,
Thank you very much. This is a great. Somehow my pre-publish script doesn't
run.
I think this should apply to merger replication although the link article
applies to SQL Server 2005 transactional and snapshot replication. What am I
missing?
System: SQL Server 2005, Merger replication. Please refer my next replying
post to Charles Wang for detailed infomation.
Thank you for help.
John
"Paul Ibison" wrote:
> You could use 2 sp_addscriptexecs to drop and readd the FK. Also to drop the
> FK you could specify @.pre_creation_cmd. Finally, in SQL Server 2005 there is
> a .pre script which should take care of this automatically:
> http://www.replicationanswers.com/PreSnapshotScripts2005.asp.
> HTH,
> Paul Ibison
>
>
|||Charles,
Thank you for response. Sorry for not offering details.
1.SQL 2005 merge
2.Yes. The table to be added is in both databases, just didn't publish before.
3. Try to use both Windows and web. I haven't pass any of them.
4. Yes. PK of this table is FK of the other's and anothe's PK is FK of this.
5. Business requires to he publish the existing table.
Regards,
John
"Charles Wang[MSFT]" wrote:
> Hi John,
> I understand that when you added an article to your live publication under
> "action if there is already an object": Drop existing object and create a
> new one, the following error came out during the synchronization:
> the table can not be dropped because the foreign relationship. The
> subscriber was restored from the publisher a while ago and the table schema
> has been changed since the database restored.
> If I have misunderstood, please let me know.
> To let me better understand your issue, I would like to confirm with you
> the following questions:
> 1. What is your replication type, snapshot, merge or transactional?
> 2. Did you mean that the added table existed in the database originally but
> just the schema changed?
> 3. What is the synchronization type of your replication, web
> synchronization or not?
> 4. Did you know which tables were being dropped and the relationships among
> those tables and the added table?
> 5. Why are there some tables being dropped since the added table was not
> replicated before?
> I really hope that you could describe your environment more detailed so
> that I can perform a test to reproduce your issue at my side. Look forward
> to your response.
> Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
>
>
|||OK - then I'd use the sp_addscriptexec method. You'd need to use this to
readd the FK anyway, so using it to drop the FK initialially is only a bit
more work.
HTH,
Paul Ibison
|||Hi John,
I am sorry for delaying this response.
Due to the existed foreign key constraint, when you drop the table whose PK
is FK of another table, it could not be dropped. I recommend that you first
run the following statement to drop the existed foreign key relations on
the table:
ALTER TABLE tablename DROP CONSTRAINT <foreign key index name>
After the synchronization, then run ALTER TABLE tablename ADD CONSTRAINT
(column name) REFERENCES <foreign table>(column name) to add the foreign
key.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||It works. Thanks.
I guess my previous pre-sub query failed was because of not enoguh access
right to the snapshot directory.
"Paul Ibison" wrote:
> OK - then I'd use the sp_addscriptexec method. You'd need to use this to
> readd the FK anyway, so using it to drop the FK initialially is only a bit
> more work.
> HTH,
> Paul Ibison
>
|||Hi Charles,
I thought SQL Server should save the relations, drop them, replicate the
schema chages and then restored the relations back from the saved. Please
refer to this:
http://www.replicationanswers.com/PreSnapshotScripts2005.asp.
Thank for response.
John
"Charles Wang[MSFT]" wrote:
> Hi John,
> I am sorry for delaying this response.
> Due to the existed foreign key constraint, when you drop the table whose PK
> is FK of another table, it could not be dropped. I recommend that you first
> run the following statement to drop the existed foreign key relations on
> the table:
> ALTER TABLE tablename DROP CONSTRAINT <foreign key index name>
> After the synchronization, then run ALTER TABLE tablename ADD CONSTRAINT
> (column name) REFERENCES <foreign table>(column name) to add the foreign
> key.
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi John,
Thanks for your feedback.
I understand your concerns. You may submit your suggestions to Microsoft
via the following link:
https://connect.microsoft.com/sql
Your suggestiosn will be routed to SQL product team so that this function
may be included in future.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment