SQL Packager does not package dependencies in correct order?
kconnor@ncu.edu
Posts: 3
I'm trying to package a database with SQL Packager. This database contains objects with dependencies on other objects (i.e. a table created in a schema which must have the schema created prior to the table, or a table with FK constraints which obviously depends on the referenced table being created).
SQL Packager creates the SQL script to create the database, with the objects in absolutely horrid order.
For example... the create order for various objects in the script are as follows:
1. logins (even though I expressly unchecked the box to create these logins)
2. db users (I cleared the checkbox)
3. schemas
4. defaults
5. types
The very next object that gets created... is a Function.
And the function runs SELECT statements against tables in the database. But those tables aren't created yet.
So obviously the CREATE FUNCTION script errors out.
Why does this product not script object creation in the correct order of its dependencies? Looking through this DB creation script, it'll be an absolute nightmare to shuffle everything around in the correct order of dependency. I'll have to cut all the keys away from the tables and just create the tables as heaps, then go through and assign all the PK's, then assign all the FK's, and so on... all manually.
I bought your product so I wouldn't have to do that. I can do that on my own with SSMS.
SQL Packager creates the SQL script to create the database, with the objects in absolutely horrid order.
For example... the create order for various objects in the script are as follows:
1. logins (even though I expressly unchecked the box to create these logins)
2. db users (I cleared the checkbox)
3. schemas
4. defaults
5. types
The very next object that gets created... is a Function.
And the function runs SELECT statements against tables in the database. But those tables aren't created yet.
So obviously the CREATE FUNCTION script errors out.
Why does this product not script object creation in the correct order of its dependencies? Looking through this DB creation script, it'll be an absolute nightmare to shuffle everything around in the correct order of dependency. I'll have to cut all the keys away from the tables and just create the tables as heaps, then go through and assign all the PK's, then assign all the FK's, and so on... all manually.
I bought your product so I wouldn't have to do that. I can do that on my own with SSMS.
Comments
SQL Packager should script the objects in dependency order. If it's not doing that then it's either a bug, or you have a nasty circular dependency that we can't handle.
The logins and users have probably been re-added to your project because some other selected objects have a dependency to them. It's a default option to 'include dependencies'.
If you have SQL Compare, I would suggest setting up the same project and see if the script is correct using that. If you're creating an install package using SQL Packager, you can simulate this by using SQL Compare to a blank database. The reason I ask is because SQL Packager uses the SQL Compare engine; only a much older version. If the problem is still in the latest version of SQL Compare, then it's probably a new issue to us.
Let me know what you find.
I did try SQL Compare against a blank database shortly after my original post, and got the same behavior.
Could this possibly be resolved in a 2-stage process by telling Packager to not include FK's in the 1st step, then telling Packager to only target FK's in a second stage (or using SQL Compare to grab the FK's)? Is this something these tools can do?
You could try using the 'ignore foreign keys' option for the first pass.
I'm not exactly sure why you would need to do a 2 step sync', but I'm happy to look into it if you're able to send me a copy of your schema.