EMS SQL Manager for SQL Server

EMS SQL Manager for SQL Server is a powerful graphical tool for Microsoft SQL Server development and administration. It makes creating and editing MS SQL Server database objects easy and fast, and allows you to run SQL scripts, manage users and their privileges, build SQL queries visually, extract, print and search metadata, export data to 14 available formats and import them from most popular formats, view and edit BLOB fields, and much more.

These editions of SQL Manager for SQL Server differ in their functionality. Being a light edition, SQL Manager Freeware has certain restrictions, for example, it can handle not more than 5 databases, and some others. You can view the list of all functional differences between full and freeware versions at our Feature Matrix Page.

First of all you must have an opportunity to connect to some local or remote SQL Server. Besides you need your computer to satisfy the system requirements of EMS SQL Manager for SQL Server.

1. If your server is installed as named instance, use server name in form computer_name\instance_name. SQL Server Express Edition has by default SQLEXPRESS instance name.
2. If you connect to the remote server, make sure that the connection is not blocked by firewall. Ask your system administrator to correctly open appropriate ports.
3. Make sure that SQL Server is started and you have correct login to access it.
4. Make sure that client protocols are correctly tuned. Use SQL Server Configuration Manager on connection to SQL Server 2005 or higher (it is installed with Native Client) or SQL Server Client Network Utility (system32\cliconfg.exe) on connection to earlier versions.
5. Make sure that remote connection is enabled on server and server protocols are correctly tuned. Use SQL Server Surface Area Configuration on SQL Server 2005 or higher and SQL Server Network Utility (Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe) on earlier server versions.
6. If you use named instance of server with dynamic TCP/IP port, make sure that SQL Browser service is running.
7. If you connect remotely, try using server IP address instead of its name to ensure that DNS works well.

You can customize all display formats: integer, float, date, time and date/time in the Environment Options window on the Grid | Color & Formats tab.

For your convenience and to speed up your work Data Grid allows customizing many data display parameters. Here are the most important of them (accessible through Grid | Data Options tab of Environment Options dialog):
- Limit options in table and view editors. The “Select all records of a table” option will enable you to see all table records without extra references to the server, yet in case of large tables or low speed connection channel the data may be fetched with huge delays and the incoming traffic might grow considerably. This mode is recommended when working with local databases or in a private network. The “Select only” mode restricts the maximum number of records returned after the query. Man can't process a massive amount of information at once. Hence, we came up with this mode. This mode speeds up table data viewing considerably, prevents hanging and connection timeout. It is recommended to work with large tables, in case of low speed connection channels and when the traffic volume is of importance. This is a default mode. With this mode enabling the “Use SQL sorting in data view” and “Use SQL filter in data view” options comes really helpful.
Default Grid Mode. This option defines whether the requested rows will be loaded in the Grid all at once ("Load all rows"), or in parts ("Load visible rows") as the user scrolls down table data. The first mode increases the query opening time but speeds up scrolling. In the second mode the query opens very fast but there might be delays when navigating the grid.
- Use SQL sorting in data view. With this option enabled, when the user sets data sorting a new request is sent to the server, and the result is shown anew in the Grid. Enabling this option is very helpful with the “Select only” mode enabled, but it increases the traffic. When this option is disabled, the sorting is made on the client computer without any references to the server but only loaded data are sorted. That is, if you set "Select only 1,000 records", then only these records will be sorted.
- Use SQL filter in data view. With this option enabled, the data will be sorted on the server to which a new request is sent, and the result will be shown anew in the Grid. Enabling this option is also helpful when the “Select only” mode is on, but it increases the traffic. When this option is disabled, the filtering is made on the client computer without any references to the server but only loaded data are filtered. That is, if you set "Select only 1,000 records", then only these records will be filtered.
We recommend that you set the following option values to achieve maximum efficiency when working with large tables:
- Select only – On
- Load visible rows – On
- Use SQL sorting in data view – On
- Use SQL filter in data view – On

Database Registration Info dialog contains Log tab where you can enable logging metadata changes that are performed on a database and SQL statements that are executed in SQL Editor.

If you have troubles in connection to a named instances of SQL Server 2000 please try the following:
- Run SQL Server Client Network Utility (system32cliconfg.exe) on client computer where EMS SQL Manager for SQL Server runs.
- Go to the second (Alias) tab of the Utility.
- Add a new server alias with Server Name in form and TCP/IP as Network library.
- Use the server alias name as host name on Database Registration Info dialog of EMS SQL Manager for SQL Server.

To connect to SQL Server 2005 or higher on alternative port, do the following:
- Run SQL Server Configuration Manager (it is installed with Native Client) on client computer where SQL Manager runs.
- Select SQL Native Client Configuration | Aliases node.
- Add a new server alias and set port number, TCP/IP as protocol and server name.
- Use the server alias name as host name on Database Registration Info dialog of SQL Manager.
To connect to SQL Server 2000 or earlier on alternative port, do the following:
- Run SQL Server Client Network Utility (system32cliconfg.exe) on client computer where SQL Manager runs.
- Go to the second (Alias) tab of the Utility.
- Add a new server alias and set server name, port number and TCP/IP as Network library.
- Use the server alias name as host name on Database Registration Info dialog of SQL Manager.

EMS Data Export/Import for SQL Server includes some additional features, which are not available in SQL Manager for SQL Server such as:

• export/import data from/to several tables at once;
• export/import data from/to tables selected from different databases on one host;
• command line utility to export/import data using the configuration file with all the export/import options.

First of all, EMS SQL Query for SQL Server works faster as it is a much lighter product. Besides it provides additional features for query building, e.g.:

• keeping query history, which allows you to rollback to any edited query;
• various interface improvements for more productive and easy work.

The 'DDL' tabs of the Table Editor, View Editor, etc. are read-only. They display the definition of tables. To modify this text you can copy it to the clipboard and modify it using SQL Editor.

You need to switch off the “Convert created object's names to lower case” option in Environment Options dialog on the Tools | Object Editors tab.

You need to set Grid Mode for the table to 'Load Visible Rows'. Please right click on the table's grid and select Grid Mode | Load Visible Rows.

Export As SQL Script is intended to export table data that will be inserted into a database system other than MS SQL Server. Use Extract Database to copy data to a table on MS SQL Server.

Fields of types TEXT, BINARY, IMAGE, etc. are not exported by default. You should select these fields manually on the Fields tab.

Follow the steps below to change directory.
- Right click on the database you need in DB Explorer and click on "Database Registration Info..." item in popup menu (you can also find this item in main menu "Database"). The Database Properties form will be opened.
- Click on the "Save Options" tab.
- In the "Default Directory for Export Data" section you can choose the default directory for export file.

You need to increase timeout values on Tools | Timeouts tab of Environment Options dialog, or set them to 0 - unlimited.

SQL Manager stores all your settings in the Windows registry. It means that the only way to transfer your database data is to get access to the Windows registry. You can do it by loading OS from your old HDD (if possible) or by opening the registry file with a special editor program. If you managed it, you can unload the 'HKEY_CURRENT_USER\Software\EMS\MS SQL Manager' branch to the *.REG file, transfer this file to a new system and add information to the registry by double-clicking the file.