Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Checking for the Existence of Our Variables

Once again, we check the presence of each of our variables that we reference. I have provided you with a sample statement for the check of the CompanyName variable (see Listing 2). By now, I am sure that you will be more than comfortable writing the statements (or copying most of them from the InstallSQLServer.bat script) to check for the existence of the other variables.

Listing 2—Checking for the Existence of the Variables.

if not "%CompanyName%"=="" goto :CompanyNameOK
    (echo ERROR: Missing input parameter CompanyName.)
    (echo ERROR: Missing input parameter CompanyName.) >> %Logfile%
    set RETVAL=10
    goto :end

...other parameter checking statements to follow...


Just be a little careful when you aren't too strict about the parameters that you expect. For example, although we have specified that some parameters are optional in this script, you should still check for them. If they aren't supplied, you should assign a default value rather than error out the script.

For example, see the following snippet of code for the checking of the SQLsaPwd parameter:

     if "%NTAuthentication:~0,1%"=="Y" goto :UseNTAuthentication
        if not "%SQLsapwd%"=="" goto :SQLsapwdOK
           (echo ERROR: Missing input parameter SQLsapwd.)
           (echo ERROR: Missing input parameter SQLsapwd.) >> %Logfile%
           set RETVAL=15
           goto :end

The SQLsaPwd parameter is required only if the NTAuthentication variable is set to "No". In this instance, it wouldn't make sense to error out the script if the SQLsaPwd parameter were not supplied.

After we have ensured that the user of our script has provided all of the parameters, we can then generate the .iss file for the installation of SQL Server 2000 SP2. This is a very similar process to creating the .iss file for the installation of SQL Server. Note, however, that this .iss file (for SP2) is a lot less complex than it is for the installation of SQL Server. Most of the hard work is done by the time an installation of an SP is required, so the .iss file does not require as much detail about your specific SQL Server installation.

Listing 3 shows the code and the sections of the .iss file that we will generate.

Listing 3—Writing Out the .iss File to Install SP2.

    set ISSFile=%ISSDir%\SQL2KSP2.iss
    REM Build the auto-answer file for the setup program.

    (echo Building auto-answer file.) 
    (echo Building auto-answer file.)	>> %LogFile%

    (echo [InstallShield Silent])     > %ISSFile%
    (echo Version=v5.00.000)       >> %ISSFile%
    (echo File=Response File)       >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [File Transfer])        >> %ISSFile%
    (echo OverwriteReadOnly=NoToAll)   >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgOrder])           >> %ISSFile%
    (echo Dlg0=DlgW2kReboot-0)      >> %ISSFile%
    (echo Count=13)            >> %ISSFile%
    (echo Dlg1=SdWelcome-0)        >> %ISSFile%
    (echo Dlg2=DlgMachine-0)       >> %ISSFile%
    (echo Dlg3=DlgInstallMode-0)     >> %ISSFile%
    (echo Dlg4=SdLicense-0)        >> %ISSFile%
    (echo Dlg5=DlgInstanceName-0)     >> %ISSFile%
    (echo Dlg6=DlgMaintainInstall-0)   >> %ISSFile%
    (echo Dlg7=DlgUpgrade-0)       >> %ISSFile%
    (echo Dlg8=ServerConnect-0)      >> %ISSFile%
    (echo Dlg9=DlgCollation-0)      >> %ISSFile%
    (echo Dlg10=DlgServerNetwork-0)    >> %ISSFile%
    (echo Dlg11=SdStartCopy-0)      >> %ISSFile%
    (echo Dlg12=SdFinishReboot-0)     >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgW2kReboot-0])        >> %ISSFile%
    (echo Result=189)           >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [SdWelcome-0])         >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgMachine-0])         >> %ISSFile%
    (echo Type=1)             >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgInstallMode-0])       >> %ISSFile%
    (echo Type=2)             >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [SdLicense-0])         >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgInstanceName-0])      >> %ISSFile%
    (echo InstanceName=%InstanceName%)  >> %ISSFile%
    (echo Result=0)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgMaintainInstall-0])     >> %ISSFile%
    (echo Type=12)            >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgUpgrade-0])         >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [ServerConnect-0])       >> %IISFile%
    if "%NTAuthentication:~0,1%"=="Y" (
        (echo NTAuthentication=1)   >> %IISFile%
        (echo SQLAuthentication=0)  >> %IISFile%
    ) else (
        (echo NTAuthentication=0)   >> %IISFile%
        (echo SQLAuthentication=1)  >> %IISFile%
        (echo svPassword=%SQLsapwd%) >> %IISFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgCollation-0])        >> %ISSFile%
    (echo collation_name=%SQLCollation%) >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [DlgServerNetwork-0])      >> %ISSFile%
    (echo NetworkLibs=255)        >> %ISSFile%
    (echo TCPPort=%TCPPort%)       >> %ISSFile%
    (echo TCPPrxy=Default)        >> %ISSFile%
    (echo NMPPipeName=\\.\pipe\sql\query) >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [SdStartCopy-0])        >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [Application])         >> %ISSFile%
    (echo Name=Microsoft SQL Server)   >> %ISSFile%
    (echo Version=8.00.000)        >> %ISSFile%
    (echo Company=%CompanyName%)     >> %ISSFile%
    (echo Lang=0009)           >> %ISSFile%
    (echo.)                >> %ISSFile%
    (echo [SdFinishReboot-0])       >> %ISSFile%
    (echo Result=1)            >> %ISSFile%
    (echo BootOption=0)          >> %ISSFile%

    REM Copy and rename the auto-answer file for 
    REM the setup program to the Windows directory i.e. C:\WinNT
    (echo Copying answer file to "%WinDir%".)
    (echo Copying answer file to "%WinDir%".) >> %LogFile%
    (copy /A /Y "%ISSFile%" "%WinDir%\setup.iss") >> %LogFile% 2>>&1


You generate an .iss file for an SP the same way you do for a SQL Server installation. In other words, run the installation program with the command-line switches that we explored in the first article in this series.

As you may have noticed, I already replaced the hard-coded values in the .iss file with my parameters (that is, Collation_Name now has a value of %CollationName%).


The NTAuthentication variable that we use in the .iss file simply specifies the way we want to connect to the SQL Server. That is, with SQL Authentication (NTAuthentication=No), which means that we need to supply a valid password for the "sa" account; or with Windows Authentication (NTAuthentication=Yes), which means that the account we are logged on with must have system admin rights in the SQL Instance that we are upgrading.

After the .iss file has been generated, we copy it to the Windows directory and rename the file to setup.iss. At this point, it is a very easy process to ensure that our .iss file contains the information that we want to configure with our install.

Writing the Code to Actually Install SP2Now the fun stuff! We are ready to install our SP. Check out Listing 4.

Listing 4—Calling the Installation Program for SQL Server 2000 SP2.

    REM SP2 requires that the SNMP service is NOT running before installation
    (echo Stopping the SNMP service)
    (echo Stopping the SNMP service.) >> %LogFile%
    (net stop SNMP /y) >> %LogFile% 2>>&1

    REM Installing SQL Server silently specifying the setup.iss file 
    (echo Launching SQL SP2 setup, commands %AppPath%\setupsql.exe -s -m -SMS -f1 
    (echo Launching SQL SP2 setup, commands %AppPath%\setupsql.exe -s -m -SMS -f1 
"%WinDir%\setup.iss".) >> 
    start /wait "%AppPath%\setupsql.exe" -s -SMS -f1 "%WinDir%\setup.iss" 
>> %LogFile% 2>>&1


A requirement of installing SQL Server 2000 SP2 is to ensure that the SNMP service is stopped before installing the SP. If you are modifying this script for other SPs, this is the section that you stop dependant services with. However, be careful of the order in which you stop services; otherwise, errors may be generated. I use the /y switch on the net stop command to automatically stop all dependant services on the SNMP service. (A nice little undocumented feature for you...well, I couldn't find it, at leastJ).

If SNMP is not installed on your machine, you can either comment this line out or ignore the error that will be returned.

There we go. Now that we have launched the installation of SQL Server 2000 SP2, it is a simple case of restarting services, starting with the SQL Server instance, to make sure that everything worked okay. Listing 5 shows you how to restart the services in code.

Listing 5—Restarting the Services to Make Sure Our Installation was Successful.

    REM Make sure the SQL Server service gets started.
    (echo Attempting to start the SQL Server service "%InstanceName%".)
    (echo Attempting to start the SQL Server service "%InstanceName%".) >> %LogFile%
    (net start "%InstanceName%") >> %LogFile% 2>>&1

    REM Set the return value

    (echo Restarting the SNMP service.)
    (echo Restarting the SNMP service.) >> %LogFile%
    (net start SNMP) >> %LogFile% 2>>&1
  • + Share This
  • 🔖 Save To Your Account