Additional settings relate to the server connection, timeout, forcing sort order on rows in #_T_ and #_F_ data sets, and the option to run an executable file before or after running the SQL.
Use "order by" or "select" on data set identifier
Data in temporary tables are not necessarily ordered according to a provided ”order by” statement, and the copy of the table into an Excel sheet may not be in the expected sort order. This can be solved by providing an explicit ”order by” specification. This setting will be used when the content of the temporary table is transferred to Excel. If needed, it is also possible to provide a specific "select" specification to use in the transfer to Excel.
An "order by" specification could be as follows: "#_T_RandomTable_A2_H Row asc, Number desc #_F_File 1, 2". Note that the "order by" statement can be provided for multiple tables, and can be constructed in the same way as in Server Management Studio, i.e. with reference to one or more column names or one or more column numbers.
Run executable files
It is possible to run executable files as part of the workflow. This can be done before and after running the SQL.
Modify server connection setting
The server uses Window authentication by default. It is possible to change, but requires the username and password to be stored in the Excel sheet or provided each time the SQL is run.
- There is generally not made any distinction between upper and lower case letters in SQLinExcel. Whether the SQL-execution is case sensitive will depend on the server settings.
- It is possible to duplicate the SQL-sheet in the same Excel workbook and use it separately. For instance a copy of the SQL-sheet can be used to connect to a different server. It is possible to automatically execute multiple SQL-sheets in one batch by changing the “Run another SQL sheet” setting.
- It is possible to run the SQLinExcel workflow defined in a sheet from anywhere in the workbook. Create a button in another Excel sheet and assign the macro 'Run_SQL_Sheetname("SQL")' to the button. Include the leading and trailing >'< in the text field in the Assign Macro form.
- It is possible to save files in a directory that does not already exist. If you specify a path that doesn't already exist, it will be created automatically.