Relationships 2

[petermeachem]

The chap that set up the database I am using set up some relationships between tables. For instance between Quotes and Customers. The problem is that I need to make the customer email field a bit wider. Enterprise Manager then tells me it needs to save both tables. If I say yes, the whole thing grinds to a halt, staff can't do anything and I get the flack. I can't even break the relationship, get the same save thing. And I am pretty sure I won't be able to export the data, drop the tables and rebuild them either.
There must be a way round this little dilemma.

 

[ptheriault]

Can you make this change off hours? Also, if you post your table structure we can show the syntax to run your table alter from Query analyzer. I find it's better to make your changes with Query analyzer so you can understand what is going on instead of using a wizard from EM. (Which can sometimes time out)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

This change needs to be made after hours.

In order to make the field wider a new Customers table will be created called tmp_Customers and all the data from the Customers table will be loaded into the tmp_Customers table. Then all the relationships between Customers and Quotes are removed. The Customers table is dropped and the tmp_Customers table is renamed. Then the relationships are readded.

This is all done within a transaction to prevent new data from being added to the Customers tables until the change has been made and the forign keys set back up.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[petermeachem]

Thank you both. Excellent advice as always from tek tips.