January 20, 2005 at 8:52 am
Hello,
MSSQL 7 or 2000
When you use DTS to deploy objects (ie: Views or Tables) from DEV to PROD, and these objects are dependent upon other objects, does DTS automatically order the creation of these objects based on dependent objects?
ie: view1 references view2. Does DTS have the intelligence to detect dependencies ie: create/drop view2 before create/drop view1?
Many thanks. Jeff
January 20, 2005 at 9:27 am
If you use the DTS Import/Export wizard and choose to move by "object" (3rd choice), you have the ability to choose that option (include dependent objects). The 1st choice, move tables and views, doesn't allow that.
hope that helps....
January 20, 2005 at 9:45 am
Hi, thanks for the response. The 3rd option is the method I wish to use. I don't want to select the "include dependent objects" as it will drop dependent objects in production db which could corrupt the schema (ie: views that call several tables will be dropped along with table constraints; indexes) ...
(ie: view 1 is dependenent on View 2) ... if i create a script using Query Analyzer, I need to ensure that "CREATE VIEW view2 AS ..." is before CREATE VIEW view1 AS ..." in code definition or else I get "invalid object" error.
Does DTS have the intelligence to do this in the backgroud? ie: in "Copy All Objects" there could be a big list, but it doesn't give option to how to order based on what should be created first, then next, and so on ... ???
Many thanks. Jeff
January 20, 2005 at 2:01 pm
OK, I played with this out of curiosity because I move stuff all the time. So here is what I found:
1st, I decided to script from database->all tasks->generate sql script.
Note: Views to correctly display dependencies need to be bound to the schema (found in properties under design view). And tables are through FK relationships.
2nd, I scripted everything including dependent objects (based on my object selection). This gave me all objects and their dependencies. So if a view references a table, that table is scripted.
Note: This produced my objects in the correct order for creation depending on dependency.
3rd, I scripted only my selections and not their dependents. This gave me a script for only my selected objects.
Note: Again, this produced my objects in the correct order for creation based on dependency.
So, it works correctly if you manage relationships correctly through proper schema binding / FK relationships. Makes sense, it's proper db modeling.
...hope that helps.
January 21, 2005 at 9:00 am
We resolved this issue with a SQL product from Red Gate. It does everything we need it to when moving objects from dev to prod.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply