Running an MS SQL Server script from InstallAware

[greenstone]

Hi,

I'm trying to get an MS SQL Server script running from inside the InstallAware installer.

I have not been able to get the script to run and can't figure out what I'm missing.

Here are the steps I've done.

1.) Created a basic installer (running wizard pages function fine from start to end).

2.) In the "Server Configuration"/"Sql Databases" item I created a new "MS Sql Server" item (giving localhost as the server, SQLExpress as the Instance, myDatabase as the existing database, and <empty> for username and password (to use windows authentication).

note: I've tried many variants on these parameters, incase I was interpreting what to enter incorrectly (e.g. "localhost", <machinename> as server, "SQLExpress" or <nothing> as the Instance, correct Sql authentication username/password and null for trying to use Windows authentication. (my normal connection string to this database would be: "Server=<machinename>\\SQLEXPRESS;Database=testDb;User Id=test_user;Password=temp;")

3.) I run the installer (and all the wizard pages seem to happen), but the script I supplied on the InstallAware script page:
INSERT INTO framework.system (Name, Value) Values ('test', 'testvalue')
never seems to happen (this INSERT statement works fine in an sql query).

Is there a setting somewhere I need to set to have the defined MS SQL Server script to run? ...or other settings that need to happen?

What is the best way to debug the problem?

I'm running InstallAware "lonepine" version 071107. MS SqlServer Express SP2

Thanks!

[greenstone]

I found the sample called "SQL Server Connection" in the "InstallAware/InstallAware 7/Samples/Sql Server Connection" directory. It connects to the database fine, and I can change the query to do the INSERT.

It appears what I was missing was a chunk of code in the MSI script to instantiate the a dialog for the SqlServer connection. I speculate there's some type of "glue" between (in this case) the dialog called "sqlwait" and the database connection/query defined the ServerConnections/SQLDatabases window.

Would someone be able to explain this "glue"?

Thanks

[Alex_Ronquillo]

There is no such "glue". However we've had reports of people saying that they need a progress dialog to make their SQL Server Connection work correctly. They say that in order to make sure the MSI script waits for the SQL script to finish execution, they need a progress dialog such as the sqlwait dialog.

[greenstone]

Hi Alex,

I am able to get the "SQL Server Connection" sample project to run, however, I still cannot get a "new" project to work.

So I took the tactic of trying to use the "SQL Server Connection" project as a "start" for making modifications.

Unfortunately, this tactic is failing also.

So I decided to run a simple test:

GOAL OF TEST: To to take the InstallAware "SQL Server Connection" sample project, delete the SQLServer connection, recreate the connection...and have the installer still function.

==> I get a failure (No Success) as described in the steps below.

Could you share thoughts on how I can make a "new" InstallAware Installer function with SQL Server (I also need to do Oracle)...but I suppose one-step-at-a-time?

Thanks


GOAL OF TEST: To to take the InstallAware "SQL Server Connection" sample project, delete the SQLServer connection, recreate the connection...and have the installer still function?

==> No Success


***************************

Following are the steps and the results of the steps. Can you spot what is wrong with what I am doing?


COPY AND RUN THE EXISTING SAMPLE PROJECT
1.) Copy the C:\\Program Files\\InstallAware\\InstallAware 7\\Samples\\SQL Server Connection directory to a temp directory
2.) Open the InstallAware project (in this new temp directory) in InstallAware
3.) Build the project, and run (selecting the SQL Server "server") ==> Get message box "Connection test was successful." {***SUCCESS*** with the unmodified sample project}


DELETE AND RECREATE THE SQL DATABASE "CONNECTION/'GO' SCRIPT"
4.) In the "Design" tab, select to the "server Configuration"/"SQL Databases" item and select the list item called "$SERVERNAME$"
5.) Select and press the "Edit" button (displaying the dialog titled "MS SQL Server"
6.) Record all settings
7.) Hit cancel
8.) Reselect "$SERVERNAME$" and press the "Delete" button (the goal of the test is to recreate this item)
9.) Press the "New" button ==> Get a message box saying:
"Apply Install command not found in setup script. This command must be present in your script before you can visually edit SQL scripts in yoru project."
10.) Press "Ok" from this messagebox

11.) Go into the MSICode tab
12.) To the end of the script, add an "Apply Changes" MSI command to the end of the script (with the "Return Success State in Variable" = SQLTEST, and "Action to Perform" = "Install Project")

13.) Go back to the "Design" tab (list is now empty) and press the "New" button with the option "MS SQL Script"
14.) Reenter the previously-recorded values (from the item that was deleted), and press "OK"

15.) Rebuild ==> Get error messagebox "Variable not defined: SUCCESS"
16.) To the "Initialize Variable" region of the MSI script, add a variable called "SUCCESS" (with a null value)
17.) Rebuild ==> {rebuilds ok}
18.) Run ==>
a.) Displays the "Microsoft SQL Server Instances" dialog {ok}
b.) Select the "Instance Name" for the server and "Windows Authentication"
c.) Press the "Next" button
==> get message box "Connection Failed. Would you like to try again?" {"yes" to retry also fails}
==> then get a message box "The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2728."

19.) Go into the "MSIcode" tab and delete the "Apply Install" section (after which InstallAware appears to have added the following MSI code):

if Variable SUCCESS not Equals ERROR
if Variable SUCCESS not Equals CANCEL
Microsof SQL Server Script : Connnect to database mast on instance $SERVERNAME$\\$INSTANCENAME$ as user $SQLUSER$ (get result into variable SQLTEST)
end
end

20.) Rebuild ==> {rebuilds ok}
21.) Run ==>
a.) Displays the "Microsoft SQL Server Instances" dialog {ok}
b.) Select the "Instance Name" for the server and "Windows Authentication"
c.) Press the "Next" button
==> get message box "Connection Failed. Would you like to try again?" {"yes" to retry also fails}
{but don't get the message box "The installer has encountered an unexpected error..."}

[greenstone]

19.) Go into the "MSIcode" tab and delete the "Apply Install" section (after which InstallAware appears to have added the following MSI code):

if Variable SUCCESS not Equals ERROR
if Variable SUCCESS not Equals CANCEL
Microsof SQL Server Script : Connnect to database mast on instance $SERVERNAME$\\$INSTANCENAME$ as user $SQLUSER$ (get result into variable SQLTEST)
end
end

20.) Rebuild ==> {rebuilds ok}
21.) Run ==>
a.) Displays the "Microsoft SQL Server Instances" dialog {ok}
b.) Select the "Instance Name" for the server and "Windows Authentication"
c.) Press the "Next" button
==> get message box "Connection Failed. Would you like to try again?" {"yes" to retry also fails}
{but don't get the message box "The installer has encountered an unexpected error..."}

[greenstone]

19.) Go into the "MSIcode" tab and delete the "Apply Install" section (after which InstallAware appears to have added the following MSI code):

if Variable SUCCESS not Equals ERROR
if Variable SUCCESS not Equals CANCEL
Microsof SQL Server Script : Connnect to database mast on instance $SERVERNAME$\\$INSTANCENAME$ as user $SQLUSER$ (get result into variable SQLTEST)
end
end

20.) Rebuild ==> {rebuilds ok}
21.) Run ==>
a.) Displays the "Microsoft SQL Server Instances" dialog {ok}
b.) Select the "Instance Name" for the server and "Windows Authentication"
c.) Press the "Next" button
==> get message box "Connection Failed. Would you like to try again?" {"yes" to retry also fails}
{but don't get the message box "The installer has encountered an unexpected error..."}