SQL Script Execution Order

[Joseph_Schrag]

I am creating a simple web install (not installscript) with a sql script to create a database. I used IS to create the db script automatically from a SQL Server 2005 database. I have run into two problems:

First, varchar(max) is created in the script as varchar(-1) which of course causes sql errors. I have fixed this via text replacement, but it seems that this is an issue that should have been fixed a long time ago given the other threads on the subject. Is there something I am missing?

Second, I have several functions that call each other. However, these dependencies are apparently not taken into account when the script is created. I end up with SQL errors because a required function has not been created yet. I really want to avoid manually reordering this script, is there a way to have IS consider these dependencies when creating the script?

[TimStVCS]

Joseph, I cannot speak to the varchar(max) issue but regarding the order of SQL script execution you must take charge of this yourself. You would need to ensure that the SQL scripts which create the functions that are executed later are run first by reordering them in the SQL Scripts view in InstallShield.

Tim

[Joseph_Schrag]

Manually reordering scripts is what I am trying to avoid. For any decent size database, this is just not an option. This forces me to find a third party sql tool to manage my script creation. It would be really handy if IS would make a few improvements to the SQL DB Import functionality, including dependency checking.

[TheTraveler]

I don't know about having to do a third party app to run SQL scripts. In Install Shield (IS) projects, you can create ADO objects to run SQL statments. Here is a link of an example on how to do this.

I should also mention that I created my ADO layer to create the database, create database users and create the product table with default data. If you need assistance in this matter, let me know.

[Joseph_Schrag]

Traveler, I think we may be talking about different issues.

Essentially, this is what I am trying to do: I have a SQL Server 2005 db on the same machine as InstallShield. I want to have IS look at that db and generate create & insert scripts to recreate the db on the target machine. The problem is, when I use the Database Import Wizard in IS to do this, the resulting script does not take dependencies into consideration. For example, some functions are created before prerequisite functions which creates sql errors. This forces me to manually reorder some of the statements in the script to account for dependencies.

What I want is IS to take these dependencies into consideration so the resulting script runs without error. If that cannot be done, I must do one of three things:
[LIST=1]

reorder the statements in the script manually in IS

create the statements manually with SQL Server and paste them into one script in IS while taking dependencies into account when deciding execution order

use a third party tool to create a script that considers dependencies and paste that into IS.


So, the third party tool would not run the sql scripts on the target machine. IS does that nicely. The third party control would create the script. I am just wondering if I can force IS to take dependencies into consideration when creating a SQL script so I don't need a third party tool.

[TheTraveler]

Hello,

Yes, I did misunderstand what you needed done. Then again, the way we handle this situation is by having an SQL file checked into Visual Source Safe which is responsible for creating the database. So all of the dependencies have already been taken care of. The other reason we use an SQL file is simple. We want to keep track of all the changes that happen and who made them. So if there is a problem, we can look at the history of check-ins and find out where and when it broke. Not to mention that we can go and talk to the developer and ask when the changes were made and roll the changes back if we need to as well. So this method works for us. I wish you luck in you method.

[Joseph_Schrag]

Traveler, thanks for the help anyway. Is there anyone who knows how to handle my specific issue?