sqlcmd not working inside inno setup even with result code equals zero

Question

After googling a bunch and struggling for a day with no result, here I am. I'm developing an installer using Inno Setup, which installs and properly configures MS SQL Server 2008 R2 according some user input from a wizard page containing checkboxes.

I have a procedure to do that SQL Server configuration setted like this:

Source: ".\src\{#ExecName}"; DestDir: "{app}"; Flags: ignoreversion; AfterInstall: runSQLCMD

where #ExecName is my .exe name;

here is the procedure:



    [Code]
    procedure runSQLCMD();
    var
        ResultCode: Integer;
        SQLCMDPath: String;
        SQLParams: String;
        BasicQuery: String;
        FullQuery: String;
    begin
        SQLCMDPath := ExpandConstant('{pf}') + '\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe'

        BasicQuery := 'EXEC sp_configure ''show advanced option'', 1; RECONFIGURE; EXEC sp_configure ''max worker threads'', 256; RECONFIGURE; EXEC sp_configure ''backup compression default'', 1; RECONFIGURE; EXEC sp_configure ''max degree of parallelism'', 4; RECONFIGURE; EXEC sp_configure ''max server memory'', 512; RECONFIGURE;';

        FullQuery := 'EXEC sp_configure ''show advanced option'', 1; RECONFIGURE; EXEC sp_configure ''backup compression default'', 1; RECONFIGURE; EXEC sp_configure ''max degree of parallelism'', 4; RECONFIGURE; EXEC sp_configure ''max server memory'', 1024; RECONFIGURE;';

        if not FileExists(SQLCMDPath) then begin
            SQLCMDPath := ExpandConstant('{pf64}') + '\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe'
        end;
        if (ShouldRunItem(1)) then begin
            SQLParams := '-S .\INSTANCE -U sa -P "password" -Q "' + BasicQuery + '-o ' + ExpandConstant('{tmp}') + '\logsql.txt';
            if (Exec(SQLCMDPath, SQLParams, '',SW_SHOW,ewWaitUntilIdle, ResultCode)) then begin
                MsgBox('Success!', mbInformation, MB_OK);
            end
            else begin
                MsgBox('Error', mbError, MB_OK);
            end;    
        end;
        if (ShouldRunItem(2)) then begin
            SQLParams := '-S .\INSTANCE -U sa -P "password" -Q "' + FullQuery + '-o ' + ExpandConstant('{tmp}') + '\logsql.txt';
            if (Exec(SQLCMDPath, SQLParams, '',SW_SHOW,ewWaitUntilIdle, ResultCode)) then begin
                MsgBox('Success!', mbInformation, MB_OK);
            end
            else begin
                MsgBox('Error', mbError, MB_OK);
            end;
        end;
    end;

When the Exec statement runs, the ResultCode variable returns zero on debug, but when I right-click on server, point to Properties/Memory (on SSMS), the Memory configuration is not set with the user-chosen value. Not even the logsql.txt file is generated (I've opened the directory pointed by {tmp} constant and no .txt files there)

Running sqlcmd from cmd, using the same parameters and query, everything goes fine.

What I'm doing wrong?

Thank you in advance.

kuklei kuklei · Accepted Answer · 2021-10-23T10:38:15

In my case it was not running because I had added "" around the SQLCMDPath variable. Once I removed them the -Q was executed successfully. Also, I used {commonpf} instead of {pf} as such := ExpandConstant('{commonpf}\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe');

Full code

procedure CurStepChanged(CurStep: TSetupStep);
var
sqlCmd: String;
dbCreate: String;
path: String;
ResultCode: Integer;
begin
  path := ExpandConstant('{app}');
  sqlCmd := ExpandConstant('{commonpf}\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe');
  dbCreate := ' -S .\IVA -U sa -P sql -Q "CREATE DATABASE pos ON PRIMARY (NAME = N''pos'', FILENAME = N''' + path + '\pos.mdf'', SIZE = 73728KB, MAXSIZE = UNLIMITED ,FILEGROWTH = 65536KB) LOG ON (NAME = N''pos_log'', FILENAME = N''' + path + '\pos_log.ldf'', SIZE = 73728KB, MAXSIZE = 2048GB,FILEGROWTH = 65536KB)" -o "' + ExpandConstant('{app}\install.log') + '"';
  // CurStep values
  // ssInstall, ssPostInstall, ssDone
  if CurStep = ssPostInstall then begin
    // use log to view and test the string in a command window
    Log('The Value is dbCreate: ' + dbCreate );

    //MsgBox(sqlCmd + dbCreate, mbConfirmation, MB_YESNO)

      if Exec(sqlCmd, dbCreate, '' , SW_HIDE, ewWaitUntilIdle, ResultCode) then
        begin
          MsgBox( 'Database is ready for use' , mbInformation, mb_Ok);
        end
      else begin
        MsgBox(SysErrorMessage(ResultCode) , mbInformation, mb_Ok);
      end;
      
  end;
end;

sqlcmd not working inside inno setup even with result code equals zero

1 Answers