install sql express instance with certain name as install prerequest

[millevlada]

Hi.
We have specific requirements:

1. when setup is started check if SQL Server Instance named XXX already exists at target PC (instances with other names are ignored if any exists).
2. If named instance already exists exit setup with info that instance already exists.
3. If named instance not exists, then install one with name XXX, and after it is installed (and sql service is started) add default DataBase data by execution of SQL script.

Can this be done through InstallShield prerequests and standard actions, or we have to create custom actions to detect if certain instance exists (probably by reading registry keys), to install sql server express in silent mode by passing specific command line parameters to SQLEXPR32.EXE loaded into binary table, and run managed code dll to insert sql into database?

What is the best practise to do such installation?
Also, in case that SQLEXPR32.EXE must be executed from command line, how to detect when (and if) installation is succesfully completed and sql service is started since script can be added only after that.

This should be pretty common scenario except maybe for this 'specific name instance' part. I'm new to Install Shield so please can someone help? I have read pretty much of ug_IS2009_en.pdf but I still missing some answers.

Best regards,
Vladimir

[Megladon]

The way I do it is I look for the registry entry:

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQL$INSTANCE_NAME

[hidenori]

You can modify the built-in Microsoft SQL Server 2005 Express prerequisite to install a specific instance silently as follows:

[LIST=1]

Open the Microsoft SQL Server 2005 Express SP2.prq in the Prerequisite Editor.

Select the Conditions tab. You should see one condition specified.

Highlight the condtion and click the Modify button.

Modify the "Specify the registry key name to check:" setting to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\XXX\MSSQLServer\CurrentVersion

Click the OK button.

Select the Application to Run tab.

Specify "/qb INSTANCENAME=XXX" in the "Specify the command line for the application" setting.

Save the changes to the .prq file.

Please see http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx for more information about the commad line options of Microsoft SQL Sever 2005 setup.

Hope that helps.

[roger_hurst]

is it possible to specify a command-line property in terms of another package that's installed on the machine? For example, I want to use DATADIR=\data.. There may be other properties that you wouldn't want to hard-code.

I've been able to get the prerequisite to install, by hard-coding DATADIR, but the above is a requirement.

[hidenori]

Setup prerequisites expand environment variables for command line paramaters. You will need to write your own .exe that will set your environment variables, and launch the .exe as a prerequisite before the Microsoft SQL Server 2005 Express prerequisite is executed.

[roger_hurst]

Thanks for your help.

In follow-up: can you provide any changes to the above procedure if the instance to be installed is for MSDE 2000(we have to use it).

So far I've not been able to make the SQL Server Install go w/o any operator interaction--always must click "install."

[roger_hurst]

I wrote the .exe to set the environment variable (via SetEnvironmentVariable), and created the Pre-req that is a dependency of the SQL instance Prereq. All that seems to work.

However, the env var doesn't seem to be known inside the project. Just for kicks I did a GetEnvVar inside an Immediate Execution CA and inside a Deferred Execution CA, but the value was not set. This was just a test, because I thought the approach could be a workaround to another problem where a property value (via MsiGetProperty) is unavailable inside a Deferred Execution CA. Why can't I get a property value in the Deferred CA that was set in the Immediate Execution CA?
Why doesn't GetEnv work in either place?

[roger_hurst]

hidenori:

Undertain what you mean here:

> Setup prerequisites expand environment variables for command line paramaters.

I set environment variables via a dependency executable. But I must not understand how to use them: I used prerequsite editor to specify command line for the app (under Application to run) in terms of the Env Var set above, as DATADIR=%JmpsDataDir%. Didn't work, as you could probably guess. How can I make the SQL instance install using a DATADIR property defined by the target environment?

Pls get back as soon as possible.

Thanks.

[hidenori]

hidenori:

Undertain what you mean here:

> Setup prerequisites expand environment variables for command line paramaters.

I set environment variables via a dependency executable. But I must not understand how to use them: I used prerequsite editor to specify command line for the app (under Application to run) in terms of the Env Var set above, as DATADIR=%JmpsDataDir%. Didn't work, as you could probably guess. How can I make the SQL instance install using a DATADIR property defined by the target environment?

Pls get back as soon as possible.

Thanks.

Can you set your environment variable manually before launching setup.exe, and see if it still does not work? I would like to narrow down where the problem is.

Thank you.

[hidenori]

In follow-up: can you provide any changes to the above procedure if the instance to be installed is for MSDE 2000(we have to use it).

So far I've not been able to make the SQL Server Install go w/o any operator interaction--always must click "install."

You only need to check the "The prerequisite should be hidden from the installation list" box in the Behavior tab for the MSDE 2000 prerequisite. You do not need to specify any command line options for the prerequisite.

[roger_hurst]

By setting the env vars manually before executing setup the SQL instance prerequisite install worked. As I thought about it, when the Setenviron.exe (sets them internally) is run, the values should be known only in the context of the .exe, so I'm not sure what we accomplished by it.

However, by setting them manually, I did expect the env var values to be available within the context of the deferred CA, but were not.

Hmmm.

[hidenori]

You are right. The changes of environment variables do not get reflected into the parent process of the Setenviron.exe which is the Setup.exe bootstrapper. Therefore, the subsequent prerequisites do not get the information. What actually you need to do is to write your own executable that will query a folder for the DATADIR= command line parameter, and launch the Microsoft SQL Server 2005 Express redistributable (SQLEXPR32.EXE) with it. You also need to update the Microsoft SQL Server 2005 Express prerequisite to launch your helper executable.

Hope that helps.

[roger_hurst]

Thanks. I did that and it works.

Now, just one other issue. INSTALLDIR property is exported (msisetproperty) in another immediate CA sequenced after AppSearch. msigetproperty() is used within the deferred CA to import INSTALLDIR. However, the function call returns null string.

This worked when it was an immediate execution CA, sequenced above ExecuteAction.

How can my CA get at INSTALLDIR as a deferred CA.

Thanks for your help.

[hidenori]

Deferred CA: please check out this help topic.

[TheTraveler]

Try something like this...

Begin
    nType = REGDB_STRING_MULTI;
    My_GetRegistryEntryEX(HKEY_LOCAL_MACHINE,
                           "SOFTWARE\\Microsoft\\Microsoft SQL Server",
                           "InstalledInstances",
                           strTemp,
                           nType,
                           strValue);
    My_ClearStringList( m_listInstalledInstances );
    StrGetTokens(m_listInstalledInstances, strTemp, '\0');
    dlgCustom_DatabaseConnection.bIsSQLServerInstalled = ListCount( m_listInstalledInstances ) > 0;

    ///////////////////////////////////////////////////////////////
    // Loop through the list of Installed DB Instances           //
    ///////////////////////////////////////////////////////////////
    nResult = ListGetFirstString ( m_listInstalledInstances, strValue );
    while ( nResult != END_OF_LIST)
        StrToUpper(strTemp, strValue);
        if strTemp = "MSSQLSERVER" then
            ///////////////////////////////////////////////////////
            // Remove the Default instance from the list...      //
            ///////////////////////////////////////////////////////
            ListDeleteString(m_listInstalledInstances);
            dlgCustom_DatabaseConnection.bIsDefaultPresent = TRUE;
        endif;
        
        nResult = ListGetNextString (m_listInstalledInstances, strValue );
    endwhile;
End;

function My_GetRegistryEntryEX(nRoot, strKey, strName, strValue, nType, strError)
    BOOL    bResult;
    INT    nvSize;
    NUMBER  nResult;
    NUMBER  nSize;
    STRING  strTemp;
    STRING  strVal;
    LIST    listTemp;
    NUMBER  nIndex;
begin
    bResult = TRUE;
    nResult = 0;
    strError = "";
    strValue = "";
    listTemp = ListCreate( STRINGLIST );
    //RegDBSetDefaultRoot(HKEY_LOCAL_MACHINE);
    RegDBSetDefaultRoot(nRoot);

    ///////////////////////////////////////////////////////////////////////////
    // G e t   t h e   V e r s i o n   N u m b e r   o f   A I M W o r X     //
    ///////////////////////////////////////////////////////////////////////////
    nResult = RegDBKeyExist(strKey);
    if (nResult >= 0) then
        nResult = RegDBGetKeyValueEx (strKey, strName, nType, strValue, nvSize);
        if nResult < 0 then
            Sprintf(strError, "A problem occured while trying to read the registry:\n     KEY  = %s\n     NAME = %\nERROR MESSAGE:\n%s", strKey, strName, FormatMessage ( nResult ));
        else
            /*
            if nType = REGDB_STRING_MULTI then
                StrGetTokens(listTemp, strValue, '\0');
                nSize = ListCount( listTemp ); 
                strVal = "";
                for nIndex = 0 to ( nSize -1)
                    ListSetIndex(listTemp,  nIndex);
                    ListCurrentString(listTemp, strTemp);
                    if strVal = "" then
                        strVal = strTemp;
                    else
                        strVal = strVal +"|"+ strTemp;
                    endif;
                endfor;
                strValue = strVal;
            endif;
            */
            bResult = FALSE;
        endif;
    else
        Sprintf(strError, "Could not find key \"%s.\"", strKey);
    endif;

    #ifdef _My_LOG_H_
        if m_bRegistryVerbosity then
            My_AddToLog("Get Registry Entry");
            My_AddToLog("     KEY   := \""+ strKey   +"\"");
            My_AddToLog("     NAME  := \""+ strName  +"\"");
            My_AddToLog("     VALUE := \""+ strValue +"\"");
            if strError != "" then
                My_AddToLog("     ERROR := \""+ strError  +"\"");
            else
                Sprintf(strTemp, "     RESULT:= %d", nResult);
                My_AddToLog(strTemp);    
            endif;
        endif;
    #endif

    ListDestroy( listTemp );    
    return bResult;
end;