Home > Articles > Data > SQL Server

  • Print
  • + Share This
From the author of

Parameter Checking within the Script

Now that we have established the header information, let's take a look at the next logical section of the script: parameter checking. Because I am a little paranoid (only a little, mind you), I always explicitly check to see that the user(s) of my scripts supply the parameters that I expect them to. This ensures that if the script fails, the user will at least understand why!

The general format (pseudo-code) for parameter checking is "if variable is not empty check next parameter (use goto), otherwise we either assign a value to the parameter or report to the user that the parameter is missing". For example:

1:   if not "%MyParameter%"=="" goto :MyParameterOK
2:       (echo ERROR: Missing input parameter MyParameter.)
3:       (echo ERROR: Missing input parameter MyParameter.) >> %Logfile%
4:       set RETVAL=10
5:       goto :end
6:   :LogFileOK

In the above code snippet, you can see on line 1 that we check for the existence of our parameter (MyParameter); if it is not empty, we jump to line 6, so that we can check the next parameter.

Otherwise (if the parameter is empty), we go to line 2 and send a message out to the console window, alerting the user to the fact that the parameter did not contain a value. Line 3 writes the error out to our log file, and line 4 sets a return value, so when we are looking through our logs, it is easy to identify where the script errored-out. Finally, line 5 forces us to exit the batch program because it is an error we cannot recover from.

In Listing 2, you can see the parameter-checking in action.

Listing 2—Checking for the Parameters within the Script.

:Main
    REM Check for the input values.
    if not "%LogFile%"=="" goto :LogFileOK
        (echo WARNING: Missing input parameter LogFile. Setting to default "%TEMP%\SQLInstall.log")
        set LogFile="%TEMP%\SQLInstall.log"
    :LogFileOK

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

    if not "%SQLPath%"=="" goto :SQLPathOK
        (echo ERROR: Missing input parameter SQLPath.)
        (echo ERROR: Missing input parameter SQLPath.) >> %Logfile%
        set RETVAL=15
        goto :end
    :SQLPathOK

    if not "%SvcActName%"=="" goto :SvcActNameOK
        (echo WARNING: Missing input parameter SvcActName. Setting to default "LocalSystem")
        (echo WARNING: Missing input parameter SvcActName. Setting to default "LocalSystem") >> 
 %Logfile%
        set SvcActName=LocalSystem
    :SvcActNameOK

    REM If the service account is set to "LocalSystem" then we don't check for the existence of the other 
    REM of the domain and password parameters (not required if running under localsystem)
    if "%SvcActName%"=="LocalSystem" goto :SkipCheck
        if not "%SvcActDom%"=="" goto :SvcActDomOK
           (echo ERROR: Missing input parameter SvcActDom.)
           (echo ERROR: Missing input parameter SvcActDom.) >> %Logfile%
           set RETVAL=20
           goto :end
        :SvcActDomOK

        if not "%SvcActPwd%"=="" goto :SvcActPwdOK
           (echo ERROR: Missing input parameter SvcActPwd.)
           (echo ERROR: Missing input parameter SvcActPwd.) >> %Logfile%
           set RETVAL=25
           goto :end
        :SvcActPwdOK
    :SkipCheck

    if not "%MixedMode%"=="" goto :MixedModeOK
        (echo WARNING: Missing input parameter MixedMode. Setting to default "No")
        (echo WARNING: Missing input parameter MixedMode. Setting to default "No") >> %Logfile%
        set MixedMode=No
    :MixedModeOK

    REM Checks to see if we are using Mixed Mode or Windows only authentication. If Mixed Mode is used
    REM a password MUST be supplied for the "sa" account.
    if "%MixedMode:~0,1%"=="N" goto :NotMixedMode
        if not "%SQLsaPwd%"=="" goto :SQLsaPwdOK
           (echo ERROR: Missing input parameter SQLsaPwd.)
           (echo ERROR: Missing input parameter SQLsaPwd.) >> %Logfile%
           set RETVAL=30
           goto :end
        :SQLsaPwdOK
    :NotMixedMode

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

    if not "%ISSDir%"=="" goto :ISSDirOK
        (echo WARNING: Missing input parameter ISSDir. Setting to default "%TEMP%")
        (echo WARNING: Missing input parameter ISSDir. Setting to default "%TEMP%") >> %Logfile%
        set ISSDir=%TEMP%
    :ISSDirOK

    if not "%SQLDataPath%"=="" goto :SQLDataPathOK
        (echo WARNING: Missing input parameter SQLDataPath. Setting to default "%SQLPath%")
        (echo WARNING: Missing input parameter SQLDataPath. Setting to default "%SQLPath%") >>    
 %Logfile%
        set SQLDataPath=%SQLPath%
    :SQLDataPathOK

    if not "%InstanceName%"=="" goto :InstanceNameOK
        (echo WARNING: Missing input parameter InstanceName. Setting to default "MSSQLSERVER")
        (echo WARNING: Missing input parameter InstanceName. Setting to default "MSSQLSERVER") >>    
 %Logfile%
        set InstanceName=MSSQLSERVER
    :InstanceNameOK

    if not "%SQLCollation%"=="" goto :SQLCollationOK
        (echo WARNING: Missing input parameter SQLCollation. Setting to default
 "SQL_Latin1_General_CP1_CI_AS")
        (echo WARNING: Missing input parameter SQLCollation. Setting to default
 "SQL_Latin1_General_CP1_CI_AS") >> %Logfile%
        set SQLCollation=SQL_Latin1_General_CP1_CI_AS
    :SQLCollationOK

    if not "%TCPPort%"=="" goto :TCPPortOK
        (echo WARNING: Missing input parameter TCPPort. Setting to default "1433")
        (echo WARNING: Missing input parameter TCPPort. Setting to default "1433") >> %Logfile%
        set TCPPort=1433
    :TCPPortOK

    if NOT "%LicenseMode%"=="" goto :LicenseModeOK
        (echo WARNING: Missing input parameter LicenseMode. Setting to default "PERDEVICE")
        (echo WARNING: Missing input parameter LicenseMode. Setting to default "PERDEVICE") >>
 %Logfile%
        set LicenseMode=PERDEVICE
    :LicenseModeOK

    if NOT "%LicenseLimit%"=="" goto :LicenseLimitOK
        (echo WARNING: Missing input parameter LicenseLimit. Setting to default "1")
        (echo WARNING: Missing input parameter LicenseLimit. Setting to default "1") >> %Logfile%
        set LicenseLimit=1
    :LicenseLimitOK

    if "%InstanceName%"=="MSSQLSERVER" goto :DefaultInstance
        (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%")
        (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%") >> %Logfile%
        set InstanceName=MSSQLSERVER$%InstanceName%
    :DefaultInstance

Wow, what a lot of code! Yes, it does look like a lot, but all the code does is check for the existence of values for our variables; that is, it ensures that a value has been supplied for those parameters that require it.

You may have also noticed that with some of the variables, default values have been supplied. This means that if the user does not supply them (overlooked them), we give them a value instead. For example, the first check looks for the existence of the LogFile variable. If this variable does not contain a value, a default value of %Temp%\SQLInstall.log is assigned.

The next interesting part of this code is the check for the value that the SvcActName variable contains. If this contains a value of LocalSystem, we know that SQL Server will be installed to run (as a service) as LocalSystem; thus, we do not check for the existence of the SvcActDom (domain) or SvcActPwd (password) variables. However, if the SvcActName does not equal LocalSystem, the user of the script must provide the domain and password values for the variables.

NOTE

Even if SQL Server uses a local user account, the domain name must be supplied. If using a local user account, it is the machine name.

By building in this type of conditional logic, we can install SQL Server in a number of slightly different configurations, simply by passing in different values for the variables.

The last cunning trick is the instance name (look at the last IF statement in the code). This allows us to install multiple instances of SQL Server on the same machine (a feature available only in SQL Server 2000) by checking to see if the user has supplied the special MSSQLSERVER value (default instance). Using this logic, we can decide whether this installation is for the default instance or for a named instance (not the default instance). If the name is not MSSQLSERVER, we append the value MSSQLSERVER$ to the name. For instance, if we want an installation of SQL Server to be called JIMMY, the .iss file (when written out) will contain MSSQLSERVER$JIMMY in the [DlgInstanceName-0] section.

NOTE

In this script, I am forcing the user to supply a value for the sa password because my script requires that SQL Server be installed in Mixed Mode. You may not actually require a password in your installation, so you can remove this check if you want or modify the code to supply a default value if the user of the script does not supply a value. Or simply remove the capability for the installation to use SQL authentication altogether, thus forcing the server to use only Windows Authentication.

Finally, you may have also noticed the return status (RETVAL) incrementing (by 5) as we check for each variable (see within each IF statement). By using a different return value at each checkpoint, it allows us to pinpoint the problem areas within the script. For example, if the script has a return status of 55, the user has not supplied a value for the parameter CompanyName.

  • + Share This
  • 🔖 Save To Your Account