Problem with SQL scripts

[zoobert]

Hi,

I am having a problem executing a SQL Server script as part of my install. I have tried using three different methods:

• 1. Embed the sql scripts into the install in the Project Details -> SQL Scripts section
  2. Launch osql from the command line to execute the script
  3. Launch osql from a VBScript to execute the script

The first method fails and returns an ambiguous error. I correctly set ‘GO’ as the statement separator and I can successfully ran a small subset of the script. However, the script is rather large and runs successfully when executed with osql. I assume the problem is the script requires T-SQL specific tokens and executes as ANSI-SQL inside of Advanced Installer.

The second method works, but briefly displays an undesired command prompt window during the install.

The third method fails at the moment, but I am trying to track down the problem. Here is the VBScript I am using:

Code: Select all

Function runSqlServerScript(database, script)
    Const ERROR_SUCCESS = 0
    Set WshShell = CreateObject("WScript.Shell")
    
    runSqlServerScript = WshShell.Run("osql -S .\SQLEXPRESS -E -d " + database + " -b -i " + script, 1, true)
    Exit Function
End Function

So here are my two questions:

• a. Are my assumptions about the first method correct or should I investigate this further? (I assume this is the preferred method for SQL Scripts)
  b. How can I pass an installed SQL script file to the VBScript function above?

Thanks,
-kevin

[canhuth]

The third method fails at the moment, but I am trying to track down the problem. Here is the VBScript I am using:

Code: Select all

Function runSqlServerScript(database, script)
    Const ERROR_SUCCESS = 0
    Set WshShell = CreateObject("WScript.Shell")
    
    runSqlServerScript = WshShell.Run("osql -S .\SQLEXPRESS -E -d " + database + " -b -i " + script, 1, true)
    Exit Function
End Function

You might want to use the intWindowStyle parameter to hide the http://msdn.microsoft.com/en-us/library ... S.85).aspx console window, should it still pop up using this method.

Also, your code seems to assume a properly set working directory. See http://www.advancedinstaller.com/forums ... ORY#p18000 and note the values of these properties:

Code: Select all

Session.Property("AI_SETUPEXEPATH")
Session.Property("CURRENTDIRECTORY")
Session.Property("SETUPEXEDIR")

(You can output stuff via a popup, for example:

Code: Select all

var shell = new ActiveXObject("WScript.Shell");
shell.Popup(msg);

)

b. How can I pass an installed SQL script file to the VBScript function above?

As far as I know you can pass values via properties, probably via "Action Data" (see Project Details/Custom Actions) and you can call a function that calls another function with the correct parameters:

Code: Select all

function doXYZForFileA()
{
  doXYZ("FileA");
}

function DXYZ(filename)
{
  // ...
}

With best regards

Clemens Anhuth

[Ionut]

Hi Kevin,

Are my assumptions about the first method correct or should I investigate this further? (I assume this is the preferred method for SQL Scripts)

Using the SQL Scripts functionality in AI is indeed the preferred method for executing SQL scripts at install time. However, I am not sure what the problem could be. What is the exact error message you are receiving (a screenshot will help greatly)?

Additionally, please send the AIP file and the SQL script(s) to support at advancedinstaller dot com in order to investigate this issue.

The second method works, but briefly displays an undesired command prompt window during the install.

You can suppress the command prompt window by using the approach discussed in this post. Using "[%ComSpec]" is not required in your case, unless you want to use I/O redirection or pipes.

How can I pass an installed SQL script file to the VBScript function above?

As Clemens mentions above, values to Custom Actions are passed via Properties. You can use a "Property Set with Formatted" Custom Action to set a Property to the full path of your installed SQL file: [#script.sql]. However, I do not recommend that you use this approach. We shall investigate the issue as soon as you send us the SQL script and AIP file to our support email address.

Regards,
Ionut