MS SQL Script Execution Order

[gsmmc]

I'm having some very odd results scripting our MS SQL 2005 database installation into InstallAware.

In a prior version of our trial application we used InstallAware to successfully script the creation of our database and insert some data. Now with the latest version of our trial, we have 6 update scripts to run to bring the prior trials database up-to-speed. The script execution is sometimes successful but more often than not it fails. After testing this for nearly 8 hours in various configurations i've come to the conclusion that it seems like InstallAware is executing scripts out-of-order.

For example,
Looking at the InstallAware MSI Code I am executing this:
SQL Server Script: createdatabase (we know this works from prior version)
SQL Server Script: createtablesandstuff (we know this works from prior version)
SQL Server Script: updatedb1 (we know the script itsself works fine)
SQL Server Script: updatedb2 (we know the script itsself works fine)
SQL Server Script: updatedb3 (we know the script itsself works fine)
SQL Server Script: updatedb4 (we know the script itsself works fine)
SQL Server Script: updatedb5 (we know the script itsself works fine)
SQL Server Script: updatedb6 (we know the script itsself works fine)

Now each of these updatedb scripts has a version number that gets updated in our database, so updatedb1 updates the version to 1, updatedb2 updates the version to 2, and so on. The problem we're running in to is that after the installation is complete the version is not correct because it appears as-though InstallAware is not executing these scripts in order.

Sometimes our database ends up version 2, or 5, or 6, but it's not predictable. I can write an application that executes the scripts (and they work fine) but the reason we're using InstallAware in the first place is so the scripts are somewhat hidden (I don't want to have to include them in the directory we install the application and use them from there if I can avoid that).

Is InstallAware executing each script before the prior script has completely finished execution??

That's the only illogical conclusion i've been able to draw...

(Please note: before you mention putting all the scripts1 through 6 into one script I'll tell you that I can't.)

Thanks in Advance,
GS

[gsmmc]

I'm at a loss here as to what to do...

So far over the past 30+ hours i've tried the following

1. Scripting the database as described in the post above
2. Adding all scripts into one single file (This turned out to be a file about 1MB in size), attempting to paste the file into InstallAware and use the 'Load Script' button (on seperate occasions) to load the file into InstallAware.
3. Using the InstallAware SQL Plug-in to reference the file (without success)

The script never completely executes predictably any way I try this...

All of the most obvious failure-points have been checked 100 times, connection, database referenced, ANSI instead of unicode, etc...

Whether the script is a single file or multiple files, it executes without a single error in SQL Server 2005 Management Studio, and I can create a script.vbs that executes the databasescript.sql using osql.exe at the end of the InstallAware installation and everything works great... but that defeats the purpose of our using InstallAware...

Is their any known issue with long scripts not completing normally? Am I the only one trying to pass a 1MB script through the app?

Unfortunately i'm at a loss as to how the behavior is so unpredictable and I can't spend anymore time attempting to debug it under those circumstances...

If their is anything I can do to help remedy this moving forward (aside from uploading the scripts because the company wouldn't have it), please let me know the steps and I'll take them...

[gsmmc]

Does InstallAware perhaps assume a specific statement seperator? After playing around with other Installers today I found that AdvancedInstaller lets you specify a statement seperator and its default setting was ";". I'm using the conventional "GO" at the end of each batch of statements but no statement seperator.


GS

[gsmmc]

Eh.. still no luck...

FYI, one of the installers I setup and tested today was InstallShield Professional and the scripting worked flawlessly (proving to me that I at least haven't gone insane). I suppose for 1800 bucks it better work right but money-aside, I would like to stick with InstallAware if I can get a resolution for this script execution...

GS

[BrandonK]

Are you running SQL 2005 SP2? Can you try your IA project against SQL 2000?

I'm having problems that may be related. The Microsoft SQL Server Script command fails against 2005 SP2 databases:
- outside of IA, the SQL scripts run fine against 2000 and 2005
- my IA project runs against 2000, but fails against 2005 SP2

Guess there's a bug with the IA script command; still waiting for a reply to my email to IA support.

As to your question about statement separators, my scripts use GO with no problem.

[gsmmc]

Thanks for the reply Brandon,

I am using SQL Server 2005 SP2. I'm half-tempted to downgrade my InstallAware so I can see if the pre-SP2 SQL Server 2005 demonstrates the same behavior but i'm getting strapped for time. I'll try MSDE to let you know if I see a similar result.

GS

[BrandonK]

Just a thought here... are your SQL Script commands before or after the Apply Install command? If they're before, move them after and give it a go.

[gsmmc]

Hi Brandon,
They're after the Apply Install

Apply Install
If success not equal error
If success not equal cancel
execute script 1
execute script 2
...
...
...
end
end

I haven't pursued this any further since the initial posts; too much other stuff going on and I opted to use InstallShield until i have more time or until I receive approval to upload the scripts to InstallAware

Thanks for the input,
GS

[MichaelNesmith]

Please go ahead and email support your scripts! We'll see what we can find out.

[greenstone]

I may have a similar issue...need to research a little more before posting. But can you tell me if this issue satisfactorily resolved?

Thanks!