August 3, 2005 at 10:44 pm
Hi,
I am trying and thinking very hard to solve a "simple" problem (my knowledge of TSQL is limited):
I need a stored procedure to change all Foreign Key attributes in my database to have the NOT FOR REPLICATION attribute set. This is needed when I use the MsACCESS Upsize Wizard to generate a SQL server database, which I will use as a replicated database. The Upsize Wizard does not set this attribute.
Can you please help?
Thanks, Edwin
August 4, 2005 at 11:06 am
I think you'd need to scan all the tables for the FKs and then somehow alter them. I'm not sure you can just add not for replication, but I could be wrong. The tricky part is idoentifying all the constraints and knowing what needs to changed to build the syntax.
You might be able to use this script (http://www.sqlservercentral.com/scripts/contributions/25.asp) to disable them and then reenable with the NOT FOR REPL parameter.
August 4, 2005 at 7:17 pm
Hi
Thanks for the reply. I have seen this SP and I am still stuck using this SP to loop through all the table's foreign key names. The statement to alter the constraint "NOT FOR REPLICATION" for the column is straightforward. I just don't know how to program a stored procedure to loop through these foreign keys.
Thanks, Edwin
August 6, 2005 at 10:10 pm
Hi
I have been working a bit more on this problem. Using the following call I managed to create and export a list of all tables with their foreign keys:
sp_msforeachtable @command1="sp_MStablekeys @tablename='?'"
I modified the sp_MStablekeys procedure to include the tablename in the output as well and now I am able to import this in an Access database table (using Excel linked table).
The statement I made earlier that the ALTER TABLE is easy is not true. These seem not to be a way to alter the foreign key CONSTRAINT, to add the NOT FOR REPLICATION. The way around this I found is to DROP the constraint and then to ADD is back to the table. A good thing is that the sp_MStablekeys gathers all the needed information to recreate the CONSTRAINT.
Does anybody know that this is indeed the only way to solve this problem? Ultimately I would like to build a Stored Procedure which does all of this (I start with modifying sp_MStablekeys), but I do not have too much time to look into writing SPs unfortunately.
Thanks, Edwin.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply