This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- SQL Script Execution Order
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Dec 19, 2008
11:38 AM
SQL Script Execution Order
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?
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?
(6) Replies
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Jan 02, 2009
12:53 PM
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
Tim
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Jan 05, 2009
09:44 AM
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Jan 05, 2009
12:26 PM
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.
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Jan 05, 2009
02:42 PM
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.
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]
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Jan 07, 2009
10:35 AM
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.
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Jan 07, 2009
10:44 AM
Traveler, thanks for the help anyway. Is there anyone who knows how to handle my specific issue?