The SQL Designer allows you to execute SQL scripts (=SQL commands). These scripts can only be executed if the SQL Designer is logged on to a database. The active database is displayed in the status line (=last line). Scripts always refer to this database.
A database can be selected via the File menu and the menu item "Establish data connection". Alternatively, the script command "USE" can be used to log on to another database.
The SQL server only accepts commands that are allowed by the user rights of the logged on user. For example, data backups can only be performed by administrators. The user can also be logged in via the"File" menu and the"Establish data connection" item. The dialog for creating a data source corresponds to the UDL dialog.
In the left area you will find an overview of the available objects. These can be tables, views, functions, etc., including fields and field definitions. In the upper right-hand corner you will find the input area and below it the output area for the results returned by the SQL server.
The last line displays the SQL Designer status line. Here you can find information about the name of the active SQL server, the logged on database and the logged on user.
Objects and templates
An overview of the tables, fields, views, procedures and functions is provided in the left part of the SQL Designer. After selecting the main object group, the elements below it are displayed in a tree view similar to that of the File Explorer.
If user tables have been selected, a complete list of all stored tables is displayed, including the fields for the selected table in the"Columns" folder. The fields are listed with the data types and definitions, such as accuracy, decimal places, and so on.
Fields with the NOT NULL property must be filled with content when saving or updating. These fields usually have default values. These are used if the field is not used when inserting data.
The same options are available for views as for tables. Unlike tables, views are only a different set of data that always refers internally to the actual data of tables.
Procedures are SQL programs that are executed in the SQL server. All procedures always have a return value of type Integer, which specifies whether the procedure was executed without errors or not.
The parameters with the data types are listed for each procedure. This overview is especially important if undocumented procedures have to be used.
A new feature of Microsoft® SQL Server 2000 are functions. These can be inserted directly into a Select command and offer enormous new possibilities via parameters.
Unlike procedures, functions can provide any data type as a result and even return entire tables as a result.
The parameters with the data types are listed for each function. This overview is especially important if you have to use undocumented functions.
The SQL Designer supports any number of independent output series (=recordsets) in the text output area. A query normally only generates one output, which can of course contain several lines (=records). However, it is possible that several independent recordsets are requested from the server. These can also be displayed by the SQL Designer. The results of multiple recordsets are only available in the text output and not in the grid output.
Alternative representation as gittter
As an alternative to text output, the SQL Designer can also display the results as a grid. This display is similar to the browser display within EULANDA®. However, only a single result set (=recordset) can be displayed in this display.
SQL Designer Features
A first guide to creating a small script allows you to create your first script in the shortest time. You will find a description of this in the next chapter under "Creating SQL scripts".
In the toolbar there is an option "Remove Enter". By default, this option is disabled. Long texts, such as memos, index cards, and so on, are then displayed completely in the output area. However, if you would like an overview, select the option "Remove Enter".
All line breaks (Enter and line feed) are then removed and represented by a double star. If the total length is longer than 35 characters, the output field is shortened and... Length=" and the original length specification.
Under "File" you will find all functions related to loading and saving scripts as well as the text output area.
Scripts that are displayed in the Script section can be started via the"Edit" menu.
To simplify the most important database functions, the"Macro" menu contains predefined SQL scripts, such as data backup, data restore, a list of all database fields and other macros.
Functions for managing the SQL server and setting the network protocols, as well as changing UDL files, can be found in the"Extra" menu. There is also an SQL registry editor, which is especially useful if the SQL registry is so destroyed that the program EULANDA® can no longer be started.
You will find a list of the most important help pages for the SQL Designer in the "Help" menu.
The functions in the File menu allow loading and saving SQL scripts. This allows you to call up and execute modified macros or your own command sets at any time.
Some file functions are also available in the right mouse menu of the script and output area and in the toolbar.
Establish data connection
Normally the SQL-Designer automatically takes over the activated EULANDA® database. However, you can change the database using this menu item. This is the standard operating system dialog used for UDL files. Only the SQL server is supported as a database provider. The server, the type of authentication, the user name, the password and the database can be set via the dialog.
UDL operating system dialog
When entering the access data in the UDL dialog, you must always specify the option "Save password", otherwise the SQL Designer cannot log on to the SQL server.
If you have to perform administrative tasks, such as data backup, the authorization of the Eulanda user is not sufficient. In this case, enter"sa" as the user name and also click on the options"No password" and"Allow password to be saved". The password is not set for the first delivery. If you have changed your passwords, ask your administrator for the new password. The new settings should first be checked using the"Test connection" button.
Establish data connection from UDL file
This menu item allows you to select any UDL file. This is evaluated and a connection to the SQL server is established. Only UDL files that control the SQL server as provider can be used.
Select UDL file
Logging on a new SQL user
The SQL Designer can only execute commands that correspond to the authorization level of the logged on user.
For example, administration rights or security rights are required for data backup. To do this, you can log on to the SQL server via this dialog without having to log off from the PC.
Note: The user name for the SQL administrator is"sa"; the password was not assigned during the initial installation and is omitted. However, if the password has been changed, ask the administrator for this password.
This function deletes the script area. When deleting a script, any existing script is saved to the undo memory. This function can also be called up using the key combination Ctrl+N.
This function can be used to load an SQL script via the file system. By default, SQL scripts have the".sql" extension. When loading a script, any existing script is saved to the undo memory. This function can also be called using the key combination Ctrl+O.
If there is an SQL script in the script area, it can be saved using this function. SQL scripts are always ASCII files and have the extension".sql" as file extension. This function can also be called using the key combination Ctrl+S.
Script as e-mail
The SQL script displayed in the script area can be sent by e-mail. Sending via e-mail must always be confirmed in your e-mail system. Support from EULANDA® is suggested as the recipient address. This function makes it easy to have scripts checked by support or to send them to other partners.
The e-mail function is based on the MAPI interface as supported by Outlook® 2016, for example.
If you have support questions about a script, you should ask the question in the EULANDA® ticket system. You can open a new ticket via "My account" as a registered user in our online shop.
The SQL script displayed in the script area can be printed using this program item.
If only the print icon is clicked, the script area is printed by default. Use the arrow to the right of the print symbol to make a different selection via a menu.
The output printer can be selected in dialog.
Save output as text
Each time a script is executed, output is generated in the text area.
These can be saved as text file (ASCII) via this menu item.
Save output as CSV file
If data records are returned due to a SELECT command, they can be saved as CSV files in Delimiter format. The semicolon is used as field separator. The CSV file contains a list of the field names used as the first line.
This output allows you to export data in any combination in a simple way. A CSV file can be opened and processed directly from Microsoft® Excel
An SQL script is executed with Ctrl+E. Alternatively, the command for executing SQL scripts is also available in the"File" menu and via the arrow icon on the toolbar.
Depending on the SQL command, data is retrieved from the server (=recordsets), or status or error messages are returned from the SQL server. All messages are displayed in the text output area. If data is returned, you can also choose table output. All data can be exported as CSV file.
The SQL-Designer is able to receive multiple result sets (=recordsets) in the structure and display them in the text output area. However, with different structures, only one recordset is displayed in the table output.
If data is returned from the SQL server, you can also export it as a CSV file, i.e. in delimiter format, in the"File" menu. This format can then be processed directly by Excel, for example.
Changes that affect the entire script, such as deleting, loading, loading macros, etc., can be undone using this function. The last fully displayed script is displayed. Alternatively, you can also use the key combination Ctrl+Z.
The key combination Ctrl+X can be used to remove selected texts in the script area. Alternatively, this function is also available in the"Edit" menu and in the toolbar.
A marked text is copied to the clipboard using the key combination Ctrl+C.
A text in the clipboard is inserted at the cursor position in the script area using the key combination Ctrl+V.
A selected text is removed using this menu function.
The entire text in the script area is selected using the key combination Ctrl+A. This can then be cut, copied or deleted.
The SQL Designer contains the most important SQL scripts for the administration of the SQL Server. A macro can only be retrieved if a database connection exists. After its call, the macro is written to the script area. It can then be reworked and finally executed.
Backing up the database
This script saves the current database to the default path "C:\Programme\Microsoft SQL Server\MS\MS\BACKUP\" on the SQL server. Every backup and restore is always performed by the SQL server and the location information is always visible from the SQL server. If the folder does not exist, the script must be adapted accordingly.
In the extra menu, you can use the option "Set default directory for backups" to correct the backup path if it is not set to the default value. This option can only be executed on the SQL server itself.
A previously saved database is restored using this macro. If you want to use another path for the backup file or the target database, the script must be adapted before execution.
The script deletes the default database user "Eulanda" and automatically creates it again with the default authorization role "eul_SuperUser". In addition, an SQL user "Eulanda" with the default password "eulanda" is created. If the SQL user already exists, the SQL server ignores the command.
The advantage of creating the database user "Eulanda" is that you can also port a database directly with the script, i.e. copy it to a new SQL server. The new SQL server has its own SID (=Security ID) for each user, so that this user would not be compatible with the database user. Deleting and appending always uses the user with the SID valid from the SQL server.
Restore (port) database
This macro corresponds to the menu item "Create new client" from the backup file. It creates a database with an identical name from a data backup on an SQL server that had never executed the backed up database before. The SQL user "Eulanda" is synchronized with the local account of the database "Eulanda" or is created if it does not yet exist. After executing the macro the UDL file is created in the program folder of EULANDA®.
The new client can then be called immediately.
The SQL Server checks the current database for errors.
Shrinking the database
The SQL server logs all transactions, these are self-contained changes, in log files. These grow over time and can quickly multiply the size of the database if there are intensive changes to the data.
Using this macro, the database (=MDF file) and the log file (=LDF file) can be shrunk to a minimum. No user may use the database for this purpose during the process. The macro checks this and reports if there are other users working on the database. In this case, the output area contains a list of all persons and work center names that prevent the macro from being executed.
Since own access also counts, the SQL-Designer for this function may not be started from the system administration of EULANDA®, but only via the start menu of EULANDA® via the right mouse menu under "Administration".
To shrink the database and the logs, both must be backed up first.
The macro performs all the necessary tasks for this:
- Ensuring that no user is using the database
- Output of the old database and log size
- Switching the active database to the MASTER database
- Switch database to single user mode
- Truncate completed logs
- Backing up the database
- Backup of logs
- Switching the database back to multi-user operation
- Update database statistics
- Switching to the EULANDA® database
- Output of the new database and log size
The size of the database and log, which you can call up using your own macro, tells you whether it makes sense to shrink.
The current database is deleted using this macro. Since a database in use cannot be deleted, the master database is temporarily activated using the SQL command "USE master".
This command cannot be undone after execution!
Space required for database and log
The database size is displayed in a small table. A distinction is made between the data file "*.MDF" and the transaction file "*.LDF". If the LDF file is very large compared to the data file, you should call the macro for shrinking the database.
Space requirements of the individual tables
Outputs a list of all tables with information about the number of data records, the size of the data and index fields, and the unused space in KB. In addition, the average for a data set is displayed in bytes.
Since this function is based on the server function"sp_spaceused", which can only be called by table, this function is called internally for all existing tables. The results are output as macros in a SELECT/UNION command.
Number of data records per table
This macro creates a table with table names and the number of records contained in each listed table.
Field descriptions for all tables
All fields with the field definitions for the currently logged on database are displayed. The field list can be exported from the File menu as a text file or CSV file.
Active database users
This macro lists all active database users, specifying the PC name and the name of the application that opened the database.
SQL server version
The version number of the active SQL server is displayed. In addition to the version number, information on the expansion level is also displayed here.
Open table or view (with Execute)
This function creates a macro in the SQL script area that displays the selected table with the first 100 data records. The macro is displayed in the script area and executed immediately. When the macro is created, any existing script is saved to the undo memory. For this reason, the system does not ask whether a possibly displayed script should be saved.
Via the extra menu you reach special functions for the administration of the EULANDA® database and the Microsoft® SQL Server . Some of the dialogs listed here are program modules of your operating system; these are started here from the extra menu to enable central administration of the most important functions.
The Extra Menu
Set default directory for backups
The backup file normally expects the data backup or data restore in the path "C:\Programme\Microsoft SQL Server\MSSQL.1l\MSSQL\Backup". However, there are programs that set the path to another folder, so that a restore cannot be automated because the backup folder is unknown.
With this menu item you have the possibility to check the default directory and to correct it if necessary. If the default directory does not exist, it is created automatically.
This function works only from the SQL server, since the local registry of the SQL server must be accessed.
If you prefer to set the default directory manually, which is not recommended, enter it in the registry of the SQL server under the key "\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer" in the character string "BackupDirectory". Note, however, that the specified folder must also exist, otherwise no data restore or backup is possible.
Launch file explorer
This function starts the Windows file explorer and opens the path of the active UDL file or the EULANDA® program path.
Edit UDL file
This function is only available if the SQL Designer was started from a UDL file. This is the case if it was opened from the EULANDA® start menu with the right mouse button on a client entry.
The UDL file is an operating system file and contains all the information required to access the SQL server. These include the OLEDB database driver, SQL server name, user name, password, network protocol and other settings. All values can be viewed and, if necessary, changed via this menu item.
Configuring SQL Client Logs
This function is part of the operating system. However, to enable central administration via EULANDA®, the program can be started from the SQL Designer.
Client configuration tool
By default, nothing needs to be changed in the logs. Normally, only the TCP/IP protocol should be on the right active side. The port on which communication is to take place can be set under "Properties". No changes should be made here either. Changes may be necessary here if communication to the SQL server is via a firewall. In this case, servers and all clients must use the same port. By default, the SQL server uses port 1433.
Configuring SQL Server Logs
This dialog can only be called on the SQL server. It is part of the operating system or SQL server and is only provided in the SQL Designer to enable central administration. The active logs are located on the right-hand side and specify to which logs the SQL server can respond. In principle, all protocols can be listed there; however, we recommend that you also only set the TCP/IP protocol here. Under Properties you can change the port, which is set to 1433 by default. This may be necessary, for example, if a firewall is used. If the port is changed, this must also be taken into account for all client protocols.
The SQL Registry is normally accessed via the Quick Launch toolbar and the"System Administration" item. However, since the SQL registry creates program statements and connections between individual modules at runtime, it can happen that the EULANDA® program cannot start if the SQL registry fails.
If the SQL-Designer is called from the start menu, the contents of the SQL-Registry can be accessed via this menu item, even if a start of EULANDA® is no longer possible. A correction of the contents would still be possible at this point.
If the SQL-Designer is called from the start menu of EULANDA®, the window positions and settings cannot be transferred as usual. In this case, the standard positions for the window are used.
Changes to the SQL registry should only be made with the necessary expertise. Otherwise, incorrect entries can prevent the program from starting.
Help (context help)
The SQL Designer has its own help menu with the most important input pages that can be called directly. This menu item calls up the start page of the help system on the topic SQL-Designer.
SQL Server Administration Help
Calls the start page of the SQL Administration help system from the administrator manual.
SQL Query Language Help
Calls the start page of the help system on SQL query language from the developer's manual.
Help for EULANDA®-API
Calls the entry page of the EULANDA®-API help system from the developer's manual.
Help for Help
Calls up a general description for operating the help system. Here is an overview of the search methods, such as wildcard and combination search.
Create SQL script
In the right area the SQL script (=SQL commands) is entered. A script can be inserted via the clipboard, the file dialog or as a macro and of course typed in.
A script always refers to the database specified in the status line. The tables or fields on the left are for information purposes only and are intended to make your work easier. For example, field names can be transferred to the script area by double-clicking.
The SQL Designer is a tool for handling SQL commands and administering the SQL server. A small selection of the most important SQL commands can be found in this online help. In addition, Microsoft® makes the complete documentation of the SQL server and its commands available online on the Internet at http://msdn.microsoft.com