Microsoft SQL Enterprise Manager Help Contents

	What's New in SQL Enterprise Manager? Highlights of new features, enhancements, and changes in SQL Enterprise Manager for SQL Server 6.5
	Servers Registering servers, setting server configurations, viewing server activity, viewing the server error log, managing messages, managing remote servers and logins, managing devices, managing SQL Server logins, configuring SQL Executive
	Databases Managing databases, setting database options, setting statement permissions, backing up and restoring databases, managing database users and groups
	Objects Managing objects, assigning object permissions, renaming objects, viewing object dependencies, generating scripts
	Transfer Transferring all or some objects from one database to another
	Alerts Managing alerts and operators
	Scheduled Tasks Managing scheduled tasks
	Replication Configuring servers for replication, adding publications, subscribing to publications
	Queries Querying a database, analyzing queries, setting query system options
	Other Tools Running a Wizard, adding and running external tools


What's New in SQL Enterprise Manager?

For information about new and enhanced features in SQL Enterprise Manager for SQL Server 6.5, choose one of the following:

Alerts

Backup and Recovery

Database Maintenance Plan Wizard

Display Refresh

Extended Stored Procedures Manager

External Tools

Graph Controls

Menu Changes

Microsoft Distributed Transaction Coordinator

Print Support

Remote Servers and Logins Manager

Remote SQL Enterprise Manager Registration

Replication

SERVERS.BIN File Replaced by Registry Entries

SNMP Support

SQL Executive

SQL Mail

SQL Query Tool

Tasks

Tip of the Day

Transfer


Alerts

The alerts management interface in SQL Enterprise Manager contains several enhancements.

SNMP traps can be raised as part of an alert response. Set this up from the New Alerts or Edit Alerts dialog boxes. For more information, see Raising SNMP Traps from an Alert.

A Send Mail button has been added to the New/Edit Operator dialog box. (The box is opened from the Manage Alerts dialog box, which appears after choosing Alerts from the Server menu.) The Send Mail button is enabled only when the mail client is running.

Choosing the Send Mail button opens the Send Note window from the underlying mail system and uses the value in the Email Name box as the email recipient. This  feature is useful for notifying operators about changes in alert handling responsibilities.

SQLALRTR.EXE has a new parameter, -M, that allows you to supply parameters to errors. For example:

sqlalrtr -E 1105 -M 2,'add',4,'test'

Each error message parameter must be separated with a comma, but no spaces. Strings must be delimited with single quotes. Messages containing the format string %.*s require two parameters: the length (for example, 4) and the string (for example, test).

For example, for a 50001 error defined as your %S is %.*S which is %ld characters long, you would invoke SQLALRTR as follows:

SQLALRTR -E 50001 -M 'name',4,'Rich'4

For more information about SQLALRTR.EXE parameters, see the Microsoft SQL Server Administrator's Companion.

When setting alerts on forwarded events, each server participating in event forwarding must have a nonnull @@servername value, or the forwarding process may not work correctly under all situations and cyclical forwarding may go undetected. The @@servername value can be set by executing the command 'sp_addserver <servername>, local' and then restarting SQL Server. For more information about using sp_addserver, see the Microsoft SQL Server Transact-SQL Reference.

New task parameters support alert response tasks. See Tasks for more information.

See Also

Tasks

Raising SNMP Traps from an Alert


Backup and Recovery

The backup and recovery capabilities managed by SQL Enterprise Manager have been enhanced. Individual tables can be backed up and restored; transaction logs can be recovered through a specified date and time; a history is maintained of backups from a particular database; and the Database Backup/Restore dialog box has been enhanced. For more information, choose one of the following:

Table Dump and Load

Point-in-Time Recovery

Other Backup and Recovery Changes


Table Dump and Load

Individual tables can be backed up and restored. A table load can be performed from a table dump or full database dump. Loading a table is useful in a disaster recovery situation where loading the entire database may be a poor solution. (Full database and transaction log loads remain the basis of a proper backup strategy.)

Note that only a full database or transaction log load ensures that all Primary Key to Foreign Key constraints are correct.

Table dump and load operations have the following characteristics and limitations:

Table dumps cannot be performed for tables that have text or image columns.

Table loads are not permitted for tables that have indexes, text or image columns, or are published for replication.

Triggers, rules, defaults, and declarative referential integrity (DRI) are not enforced for table loads.

A table load is permitted only to user-defined tables. Source tables can be any table. That is, you can load a system table source into a user-defined table, but not into a system table.

Table loads can be performed from full database dumps or individual table dumps.

To allow table load, the Select Into / Bulk Copy database option must be set. (To set this option, use the Options tab of the Edit Database dialog box.)

A table load disables DUMP TRANSACTION until a full database dump is performed.

Source and target table schema must be identical in their null values, length, and base-system types. However, user types can be different.

Source and target tables can have different names.

Using SQL Enterprise Manager, table dump and load is performed from the Database Backup/Restore dialog box. Table dump and load can also be performed using Transact-SQL DUMP and LOAD statements. For more information, see Whats New in Microsoft SQL Server 6.5, in Microsoft SQL Server Books Online.


Point-in-Time Recovery

Transaction logs can be recovered through a specified date and time. Transactions committed after the specified date and time will be rolled back.

Point-in-time recovery applies to transaction log restores only (not to full database restores).

Point-in-time recovery can be accomplished from the Database Backup/Restore dialog box in SQL Enterprise Manager. Point-in-time recovery can also be accomplished using LOAD TRANSACTION with the WITH STOPAT option. For information, see Whats New in Microsoft SQL Server 6.5, in SQL Server Books Online.


Other Backup and Recovery Changes

The Backup and the Restore tabs increase the Database Backup/Restore dialog box usability. Options supporting table dump and load, and point-in-time recovery have been added.

Also, a history is maintained of backups from a particular database, enabling you to use SQL Enterprise Manager to restore a database or table without knowing the dump device the backup was performed to. For backups from SQL Server 6.5 servers, SQL Enterprise Manager automatically presents a list of database dumps, table dumps, and transaction log dumps for you to choose from. Backup history information is maintained in tables in the msdb database.


Database Maintenance Plan Wizard

SQL Enterprise Manager provides a database maintenance plan wizard. A wizard is an online user assistance tool that is simple to use and focuses on results, not technique. Wizards walk you through a series of questions, progressing in an easy, step-by-step manner toward completing a goal.

The Database Maintenance Plan wizard sets up and schedules core maintenance actions for a selected database. The actions include checking data allocation, data and index linkages, and system data; updating optimizer information; reorganizing data and index pages; and performing database and transaction log backups. These actions can be scheduled for preset times using daily or weekly intervals.

See Also

Running the Database Maintenance Plan Wizard


Display Refresh

The information displayed in the Server Manager window can be updated in two ways. Expand the tree for a server and then do one of the following:

Select a folder, select the View menu, and then choose the Refresh command.

Click the right mouse button on a folder, and then choose Refresh from the shortcut menu.


Extended Stored Procedures Manager

SQL Enterprise Manager provides an extended stored procedures manager. Extended stored procedures dynamically load and execute a function within a dynamic link library (DLL) in a manner similar to that of a stored procedure. Actions outside of SQL Server can be triggered, and external information can be returned to SQL Server. The extended stored procedures manager also supports return status codes and output parameters (identical to their counterparts in regular stored procedures).

With the extended stored procedures manager, you can add and drop extended stored procedures to a SQL Server. The Extended Stored Procedures command on the Manage menu starts the extended stored procedures manager. The command can be used only when the master database is the current database.

For more information about extended stored procedures, see the Microsoft SQL Server Transact-SQL Reference.

See Also

Managing Extended Stored Procedures


External Tools

Applications can be added from SQL Enterprise Manager. External applications can be added from the Tools menu, and they can be run from the tools menu or from the floating, dockable Tools toolbar. The toolbar can be displayed or hidden using the View menu Toolbar command.

See Also

Adding an External Tool

Running an External Tool


Graph Controls

Some SQL Enterprise Manager dialog boxes have enhanced charting capabilities and option buttons to allow you to control graph characteristics and print the graphs.

The following dialog boxes and windows have new option buttons and enhanced charting capabilities.

Dialog box/Window	Charting Capability
Edit Database Devices	Device space usage
Expand Database	Available database devices
Manage Databases	Databases
Manage Database Devices	Devices
New Database	Available database devices
New Database Device	Available storage space

To support printing, the default printer can be configured by choosing the Print Setup command from the File menu.

For more information, select one of the following:

Graph Properties

Bar Graph Options

Print Options


Graph Properties

Opens the Graph Properties dialog box. Use the dialog box to set graph label, solid color, legend display, and graph type properties.


Bar Graph Options

Opens the Bar Graph Options dialog box. Use the dialog box to set the graph mode to stretched or selectable, and the orientation to vertical or horizontal.


Print Options

Opens the Print Options dialog box. Use the dialog box to set color, formatting, and font options, and to print the graph.


Menu Changes

Some SQL Enterprise Manager menu commands have been added, moved, or renamed.

Moved and renamed commands are as follows:

Old Menu / Command	New Menu / Command
Tools / Task Scheduling	Server / Tasks
Tools / Query Analyzer	Tools / SQL Query Tool
New commands include:

New Menu / Command	Description
File / (all)	Contains commands from the toolbar of the active window.
File / Print Setup	Used to configure the default printer.
View / Toolbar	Displays or hides the floating, dockable toolbar for external tools.
View / Refresh	For a selected server, updates the information in the currently selected folder in the Server Manager window.
Server / Distributed Transaction Coordinator	Used to Administer MS DTC. Opens the MS DTC Transactions, Trace, Statistics, or Configuration window for servers that have MS DTC installed.
Server / SQL Mail	Used to start, stop, and configure SQLMail.
Server / SQL Executive	Used to start, stop, and configure SQL Executive.
Server / SQL Server	Used to stop, start, pause, continue, and administer SQL Server.
Server / Remote Servers	Manages remote servers and logins, as well as subscribing servers (including ODBC subscribers).
Tools / External Tools	Adds or removes external tools to SQL Enterprise Manager.
Manage / Extended Stored Procedures	Adds or removes extended stored procedures.
Object / Transfer	Transfers objects from one database to another.
Help / Tip of the Day	Displays the Tip of the Day (information about using SQL Enterprise Manager).
Help / Database Maintenance Wizard	Starts the database maintenance plan wizard.


Print Support

SQL Enterprise Manager supports printing from the following windows:

Manage Stored Procedures

Manage Triggers

Manage Views

 To print the information from these windows, from the window's toolbar click the Print button.

Also, the graph controls support printing graphs from several SQL Enterprise Manager Dialog boxes.

To support printing, the default printer can be configured by choosing the Print Setup command from the File menu.

See Also

Graph Controls


Remote Servers and Logins Manager

SQL Enterprise Manager provides an interface for managing remote servers and remote logins, setting replication distribution options, and configuring ODBC subscribers.

A remote server is a SQL Server on the network that can be accessed through a user's local server, allowing the user to execute stored procedures on the remote server. Replication processes also establish remote server relationships.

For information about remote servers and remote logins, see the Microsoft SQL Server Administrators Companion.

See Also

Managing Remote Servers and Logins


Remote SQL Enterprise Manager Registration

Generally, SQL Enterprise Manager uses server registration information that is stored locally (on the same server where SQL Enterprise Manager is installed).

Optionally, you can set SQL Enterprise Manager to use server registration information stored on a remote server. For example, you might configure several computers so that their SQL Enterprise Manager installations have identical configurations (common program groups, common servers registered, and so on).

To set SQL Enterprise Manager to use registration information from a remote server

1.	From the Tools menu, choose Preferences/Configure. The Configure SQL Enterprise Manager dialog box appears.

2.	 Choose the Application tab.

3.	Select the Read From Remote option, then in the Server Name box enter the remote server name.

4.	For the new registration information source to take effect, close and then restart SQL Enterprise Manager.

See Also

SERVERS.BIN File Replaced by Registry Entries


Replication

In SQL Enterprise Manager, text and image datatypes can be replicated, some subscribers other than SQL Server are supported, and several dialog boxes in the replication management interface have been enhanced.

For more information, choose one of the following:

Text and Image Datatype Replication

Replication to ODBC Subscribers

Other Replication Enhancements


Text and Image Datatype Replication

Text and image datatypes can be replicated, with the following considerations:

INSERT, UPDATE, and DELETE statements are supported.

Because the replication processes monitor the transaction log, only logged text operations can be replicated. Therefore, if a text or an image column is published for replication, WRITETEXT and UPDATETEXT operations with the WITH_NO_LOG option are not supported. Use WRITETEXT and UPDATETEXT with the WITH LOG option on tables published for replication.

UPDATETEXT operations can be performed for only SQL Server version 6.0 or later subscribers (not to subscribers other than SQL Server). Also, UPDATETEXT will fail if an ODBC subscriber has a subscription on the text column.

WRITETEXT operations are replicated as UPDATE statements, enabling replication of WRITETEXT to ODBC subscribers as well as to SQL Server. (UPDATETEXT operations are replicated as UPDATETEXT.)

A configurable parameter, max_text_repl_size, controls the maximum size (in kilobytes) of text and image data that can be replicated. This parameter permits support of ODBC drivers and databases other than SQL Server that cannot handle large text and image values, and distribution servers that have system resource (virtual memory) constraints. When a text or image column is published, and an INSERT, UPDATE, WRITETEXT, or UPDATETEXT operation is run that exceeds the configured limit, the operation is rejected.

Set the max_text_repl_size parameter by using the sp_configure system stored procedure or the SQL Enterprise Manager Server Configuration dialog box.

Note: This parameter is not implemented for the beta release, but will be implemented for the final release of Microsoft SQL Server 6.5.

Because SQL Server replication is log-based, to replicate text and image operations the retrieval of the text pointer must be done within the same transaction as the WRITETEXT or UPDATETEXT operation.

Also, once the text pointer has been obtained, you should not perform any operations that may alter the location of the text pointed to by the text pointer (such as updating the primary key). The recommended way of using UPDATETEXT and WRITEXT against data to be replicated is:

1.	Begin the transaction.

2.	Retrieve the text pointer.

3.	Use the text pointer in the UPDATETEXT or WRITETEXT operation.

4.	Commit the transaction.

Replicate a text or image column by publishing the table using SQL Enterprise Manager. Include the selected text or image column in the published article. For more information about setting up replication and publishing tables and columns, see the Microsoft SQL Server Administrators Companion.


Replication to ODBC Subscribers

For this beta release, Microsoft SQL Server 6.5 can replicate to the following Open Database Connectivity (ODBC) subscribers other than SQL Server:

Microsoft Access

Oracle

ODBC subscribers that comply with SQL Server ODBC subscriber requirements

See Also

Using an ODBC Subscriber

About Microsoft Access Subscribers

About Oracle Subscribers

About Other ODBC Subscribers

Replication Restrictions for ODBC Subscribers

Datatype Mappings for ODBC Subscribers


Other Replication Enhancements

The following information describes other enhancements and changes to replication.

The explicit (NOLOCK) optimizer hint is no longer needed in a replication filter, since the lock manager has been updated to ignore table locks on the replicated base table during a replication filter.

NOT FOR REPLICATION can be added to foreign key constraint declarations. For more information, see Whats New in Microsoft SQL Server 6.5.

The Replication Topology window includes New Subscriber and Remove Subscriber buttons to add or remove subscription servers. The window also graphically indicates the type of subscriber (SQL Server, Microsoft Access ODBC, generic ODBC, and so on).

Several replication dialog boxes in SQL Enterprise Manager offer improved usability. For more information, run SQL Enterprise Manager and review its replication interface.

The distribution task -i parameter is no longer supported.

The DBREPL.SQL script has been removed.

The following enhancements have been added to SQL Enterprise Manager to support replication to ODBC subscribers.

In the Replication - Publishing dialog box, an ODBC Subscriber button has been added. This button opens a dialog box that presents a list of system DSNs registered with the publisher.

You can also enter a username and password, if required by the ODBC subscriber, to connect to the ODBC subscriber. In many cases, entering the username or password in this dialog box is not necessary, since they are often entered and stored (depending on the ODBC driver) during the process of registering a system DSN with the ODBC administration application in the Control Panel.

Note that if you enter a username or password here, it overrides any username or password entered in the ODBC administration application for a registered DSN.

In the Distribution Options dialog box, fields are provided for supplying a login name and password for an ODBC subscriber. These fields are colored gray if a SQL Server subscriber is being administered.

The specified username and password are used by the distribution server to connect to the ODBC subscriber. Entering the username or password in this dialog box may not be necessary, since they are often entered and stored (depending on the ODBC driver) during the process of registering a system DSN.

A Subscriptions button has been added to the New Publications and Edit Publications dialog boxes. This button provides the ability to do a push subscription of an entire publication. (In SQL Server 6.0, push subscriptions can be performed only for individual articles.)


SERVERS.BIN File Replaced by Registry Entries

For Microsoft SQL Server 6.0, SQL Enterprise Manager server registration information is maintained in a SERVERS.BIN file. Server registration information includes server groups used in the Server Manager, servers registered with SQL Enterprise Manager, and associated data.

For Microsoft SQL Server 6.5, server registration information is maintained in the operating system Registry. (This applies to both Windows NT- and Windows 95based computers.)

The first time SQL Enterprise Manager is run after upgrading SQL Server 6.0 to SQL Server 6.5, information from the existing SERVERS.BIN file is automatically moved to the Registry.

Using the Preferences/Configure command from the Tools menu, you can configure SQL Enterprise Manager to use server registration information that is stored locally (on the same server that SQL Enterprise Manager is installed on), or that is stored in the Registry of a remote server. Also, you can configure SQL Enterprise Manager to use common registration information for all users, or to maintain separate registration information for each user.

See Also

Remote SQL Enterprise Manager Registration


SNMP Support

SQL Server 6.5 supports Simple Network Management Protocol (SNMP) monitoring of SQL Server. Copies of the Microsoft SQL Management Information Base (MSSQL-MIB) (MSSQL.MIB) and the SQL Server SNMP extension agent (SQLSNMP.DLL) are installed by SQL Server setup.

MSSQL-MIB is a Microsoft MIB based on the proposed IETF SNMP RDBMS-MIB RFC 1697, but has been enhanced specifically for use with SQL Server.

With SQL Server 6.5 and the MSSQL-MIB, SNMP applications can monitor the status of SQL Server installations, monitor performance information, explore defined databases, and view server and database configuration parameters.

In addition, SQL Enterprise Manager alert responses include the option to raise an SNMP trap when an alert occurs. An SNMP trap sends notification to the SNMP agent that some type of status or condition has occurred. The SNMP agent can then forward that notification to pre-specified workstations, which are running SNMP monitoring application(s). For information, see Raising SNMP Traps from an Alert.

For SNMP applications to monitor the status of a SQL Server, a copy of the SQL Server MIB must be placed on the monitoring workstation and loaded into the SNMP application. The MSSQL.MIB file is located in the C:\SQL60\SNMP directory on the computer running SQL Server, and can be copied to the appropriate directory on a monitoring workstation.

For information about loading the SQL Server MIB and monitoring SQL Server, see the documentation for your SNMP application. For more information about SNMP support, see What's New in Microsoft SQL Server 6.5, in Microsoft SQL Server Books Online.

See Also

Raising SNMP Traps from an Alert


SQL Executive

SQL Executive configuration parameters can be set from SQL Enterprise Manager. To configure SQL Executive, in the Server Manager window click the right mouse button on a servers SQL Executive icon, choose Configure from the shortcut menu, and complete the Configure SQL Executive dialog box.

Also, a Windows NT user account, SQLExecutiveCmdExec, has been added to support running CmdExec tasks owned by users other than system administrators.

See Also

Configuring SQL Executive

SQLExecutiveCmdExec User Account


SQLMail

SQL Server 6.5 provides several enhancements to SQLMail. For more information, choose one of the following:

New SQLMail Icon in the Server Manager Tree

SQLMail Can Impersonate a Client for Security Purposes

SQLMail is Compatible with Microsoft Exchange


New SQLMail Icon in the Server Manager Tree

SQLMail can be started and stopped from the Server Manager window of SQL Enterprise Manager. You can also configure SQLMail (assign it a mail login name and password) from the Server Manager window.

To start or stop SQLMail

1.	In the Server Manager window, expand the tree for a server.

2.	Click the right mouse button on the server's SQLMail icon.

3.	From the shortcut menu that appears, choose Start or Stop.

To configure SQLMail

1.	In the Server Manager window, click the right mouse button on the SQLMail icon.

2.	From the shortcut menu that appears, choose Configure.

3.	Complete the SQLMail Configuration dialog box.


SQLMail Can Impersonate a Client for Security Purposes

By default, SQLMail executes in the servers security context. For security purposes, you can set SQLMail to impersonate the logged on client and execute in the clients security context.

Use the new sp_xpoption system stored procedure to set SQLMail to impersonate clients. For information on using sp_xpoption, see Whats New in Microsoft SQL Server 6.5, in Microsoft SQL Server Books Online.


SQLMail is Compatible with Microsoft Exchange

To set up SQLMail to work with Microsoft Exchange, do the following:

1.	On the SQL Server computer, configure Microsoft Exchange to connect to the Microsoft Exchange server.

2.	Open the Control Panel, open the Mail and Fax application, click the Show Profiles button, and copy the current profile to a new name.

3.	From the Control Panel, open the Services application, and configure the SQL Server service to run under the same Windows NT user account that Microsoft Exchange is set to use.

Step 3 enables SQL Server to get the correct Microsoft Exchange user information from the Registry. (The Copy SQLMail option in the SQL Server setup does not copy Microsoft Exchange information.)

4.	Run xp_startmail. Supply the profile name as the mail account name and NULL as the password.


SQL Query Tool

The query interface in SQL Enterprise Manager has been renamed. For Microsoft SQL Server 6.0, the command on the Tools menu and the icon on the toolbar are called Query Analyzer. For Microsoft SQL Enterprise Manager 6.5, they are called SQL Query Tool.

See Also

Setting Query System Options


Tasks

The task scheduling interface in SQL Enterprise Manager contains several enhancements, which are summarized as follows.

The Task Scheduling command on the Tools menu has been replaced by the Tasks command on the Server menu.

CmdExec tasks owned by users other than SAs execute in the security context of a Windows NT user account called SQLExecutiveCmdExec. For more information, see SQLExecutiveCmdExec User Account.

The Command value for TransactSQL tasks supports the go command terminator. For example:

sp_who go sp_help go

This change makes TransactSQL tasks more useful, since you can now perform operations that must be done as the first or only thing in a batch.

A user can view tasks and task history for only those tasks the user created and owns. However, the SA can view all tasks.

Tasks return actual error messages (if encountered). Also, the last 230 bytes (approximately) of a CmdExec tasks output can be viewed as part of the Message shown in the Task History dialog box.

Scheduling support has been added for the following long-running operations: create database, expand database, create device, and expand device.

The Task History log can be cleared from the Task Engine Options dialog box by using the Clear Task History Log button.

A stored procedure, sp_runtask, has been added. This stored procedure allows a user to invoke a task from Transact-SQL. For more information about sp_runtask, see Whats New in Microsoft SQL Server 6.5, in Microsoft SQL Server Books Online.

Commands such as COPY and RENAME do not work as CmdExec tasks, unless you carefully phrase the task. For example:

cmd /C copy d:\out.txt d:\outmore.txt

SQL Executive assumes the first white space-delimited token of the command specifies an EXE or CMD module name.

Programs that require user input as CmdExec tasks cannot be run, since by default they run on a virtual desktop and cannot be seen.

New task parameters support alert response tasks. These parameters can be used when creating a task that will be launched when an alert occurs. The parameters are:

	Parameter	Represents
	[SVR]	Server name
	[DBN]	Database name
	[ERR]	Error number
	[SEV]	Severity
	[MSG]	Message text
For example, if five servers forward alerts to a master "alert handler" server, then a single task on that server (that was created using these parameters) could be launched by an alert, could log back into the correct server, use the correct database, and (depending on the error number, severity, or message text) address the problem.

See Also

SQLExecutiveCmdExec User Account


Tip of the Day

The Tip of the Day presents information about using SQL Enterprise Manager. By default, the Tip of the Day is displayed each time you start SQL Enterprise Manager. You can cancel this option.

You can display the Tip of the Day by choosing Tip of the Day from the Help menu.


Transfer

SQL Enterprise Manager includes a transfer management interface that can transfer all or some of the objects from one database to another. The destination server must be Microsoft SQL Server version 6.5. The source server can be Microsoft SQL Server version 6.x or 4.x.

Using the transfer management interface you can:

Copy all or some types of objects.

Copy all or some objects of a specific type.

Transfer schema and data, only schema, or only data.

Append or replace existing data.

Drop destination objects before copying schema.

Include dependent objects of a transferred object.

Use default scripting options, or customize the scripts that are used to copy objects and security information.

Execute a transfer immediately, or schedule it for execution on a one time or recurring basis.

The SQL Transfer Manager application is obsolete and is not provided in SQL Server 6.5.

See Also

Transfer


Manage Remote Servers Dialog Box

Use this dialog box to manage remote servers and remote logins. A remote server is a SQL Server on the network that can be accessed through a user's local server, allowing the user to execute stored procedures on the remote server. Replication processes also establish remote server relationships. For information about remote servers and remote logins, see the Microsoft SQL Server Administrators Companion or choose one of the following:

 Server Name

 RPC

 Subscriber

 Publisher

 Distributor

 Distribution Publisher

 Distribution Options

 Translate All Remote Login Names to

 Trusted (checkbox)

 Remote Server Login Name

 Mapped Login Name

 Trusted (column)

 Add

 Drop

 Drop Login

See Also

Managing Remote Servers and Logins


Server Name

Specifies the name of the server to be configured as a remote server (to the server being administered by SQL Enterprise Manager).


RPC

Specifies the server named in the Server Name box as a remote server (to the server being administered by SQL Enterprise Manager).

If the remote server will not participate in replication, RPC is the only option required.


Subscriber

Specifies the remote server will have subscription server status for replication.


Publisher

Specifies the remote server will have publication server status for replication.


Distributor

Specifies the remote server will have distribution server status for replication.


Distribution Publisher

Specifies the remote server as a publication server and a distribution server for replication.


Distribution Options

Sets the replication distribution options. This button opens the Distribution Options dialog box, and is enabled only when the Subscriber option is selected.


Translate All Remote Login Names to

Maps login IDs from the remote server to local login names selected from the list box: either to a specific login ID, or to the same name.


Trusted (checkbox)

Specifies whether all remote logins will be validated by Windows NT security or by a password. If this box is checked, the security will be handled by Windows NT and a password is not required. If this box is not checked, standard security is used and a login ID and a password are required.


Remote Server Login Name

Specifies a login ID from the remote server.


Mapped Login Name

Specifies a local login ID that the remote login ID is mapped to.


Trusted (column)

Specifies whether the remote login ID will be validated by Windows NT security or by a password. If this box is checked, the security will be handled by Windows NT and a password is not required. If this box is not checked, standard security is used and a login ID and a password are required.


Add

Adds the server selected in the Server Name box as a remote server (to the server administered by SQL Enterprise Manager).

When editing an existing remote server, the Add button is named Modify. Choosing Modify accepts changes made to the remote server configuration.


Drop

Removes the remote server status of the server selected in the Server Name box.


Drop Login

Drops the login(s) selected from the Remote Server Login Name list.


Tip of the Day Dialog Box

This dialog box presents information about using SQL Enterprise Manager.

To view additional tips, choose the Next Tip button.

To display the Tip of the Day each time SQL Enterprise Manager starts, select the Show Tips at Startup option. This is the default setting.

To prevent the Tip of the Day from displaying each time SQL Enterprise Manager starts, clear the Show Tips at Startup option.

To view tips at any time, choose Tip of the Day from the Help menu.


Query System Tab

Use this tab to set configuration preferences for the SQL Query Tool. For more information, choose one of the following:

 Undo Buffer Operations

 Font

 Display Message Boxes

 Default To Save

 Default To Discard

 Query File Extension

 Results File Extension

 Prompt On Closure

 Switch To Result Tab On Execute

 Defaults


Undo Buffer Operations

Specifies the number of text actions to store in the buffer. SQL Enterprise Manager maintains a record of text actions in the Query window, such as typing, deleting, or pasting text. These actions can be reversed by typing CTRL+Z, or from the Edit menu, by choosing Undo in the Query or Result tab. You can keep a record of 2 to 32,000 text actions, but note that the text is stored in memory--the larger the number of text actions, the more memory used. The default number of stored text actions is 20.


Font

Displays the Font dialog box. Use this dialog box to change the font in which the Query window displays queries and results.


Display Message Boxes

Specify how the data in the undo buffer during a large paste or delete is handled. Displays a message before the action takes place.


Default To Save

Saves data in the undo buffer during a large paste or delete.


Default To Discard

Discards the data in the undo buffer during a large paste or delete. Does not remember the operation about to be done and discards all previous undo operations.


Query File Extension

Specifies the default file extensions to be used when saving queries. Type the file extension for queries. The default is .SQL.


Results File Extension

Specifies the default file extensions to be used when saving results. Type the file extension for results. The default is .RPT.


Prompt On Closure

Asks you to confirm the action, when you close the Query window.


Switch To Result Tab On Execute

Automatically displays the Result tab when a query is executed.


Defaults

Sets all options to their default values.


External Tools Dialog Box

Use this dialog box to add, change, or remove an external tool or utility on the Tools menu. For more information, choose one of the following:

 Current Tools

 Menu Text

 Command

 Parameters

 Add

 Change

 Remove

See Also

Adding an External Tool

Running an External Tool


Current Tools

Lists the currently installed external tools.


Menu Text

Specifies the name of the selected tool. To change the entry, edit the Menu Text entry and click the Change button.


Command

Specifies the command to be executed when the selected tool is run from the Tools menu or the floating, dockable toolbar. The command is usually a fully qualified path (including filename) of an application. To change this entry, edit the Command entry and click the Change button.


Parameters

Specifies parameters to be passed to the application each time it is launched. This is an optional entry. To change this entry, edit the Parameters entry and click the Change button.


Add

Opens the Add External Tool dialog box.


Change

Revises the selected tool by applying the current entries in the Menu Text and Command boxes.


Remove

Drops the selected tool.


Add External Tool Dialog Box

Use this dialog box to add an external tool or utility to the Tools menu. For more information, choose one of the following:

 Command

 Locate

See Also

Adding an External Tool

Running an External Tool


Command

Specifies the command to be executed when a selected tool is run from the Tools menu or the floating, dockable toolbar. Usually, the command is a fully qualified path (including filename) of an application. You can type the command, or click the Locate button to find the application.


Locate

Locates the application that will become an external tool.


Open Dialog Box

Use this dialog box to select a tool or utility to add to the Tools menu. For more information, choose one of the following:

 File Name

 List Files of Type

 Directories

 Drives

 Network


File Name

Specifies the type of file that is displayed in the list box, or the selected file.


List Files of Type

Displays the type of file to be displayed in the list box. To change the selection, choose a file type from the list.


Directories

Displays the currently selected directory. To change the selection, click in the folder tree.


Drives

Displays the currently selected drive. To change the selection, choose a drive from the list.


Network

Opens the Connect Network Drive dialog box. Click the Network button to connect to a shared network directory.


Manage Extended Stored Procedures Dialog Box

Use this dialog box to add or remove extended stored procedures. For more information, choose one of the following:

 Procedure

 DLL Name

 Impersonate Client

 Add

 Drop

See Also

Managing Extended Stored Procedures


Procedure

Specifies an extended stored procedure to add or remove.  To add a procedure, type the name in the Procedure box. To remove a procedure, select the name from the list in the Procedure box.


DLL Name

Specifies the dynamic link library (DLL) name of an added extended stored procedure.


Impersonate Client

Specifies that the extended stored procedure will impersonate the logged on client and execute in that client's security context. If this option is cleared, the extended stored procedure executes in the server's security context. For more information, see "sp_xpoption" in What's New in Microsoft SQL Server 6.5.

This feature is only available for Microsoft SQL Server version 6.5 servers.


Add

Adds an extended stored procedure, as defined in the Procedure and DLL Name boxes.


Drop

Removes the selected extended stored procedure.


SQL Server Manager Window

Use this window to stop, pause, start, or continue a server's SQL Server service.

To…	Double click the…
Stop a server	Red light or Stop
Pause a server	Yellow light or Pause
Start a server	Green light or Start/Continue
Continue a paused server	Green light or Start/Continue
When this light is…	The server is…
Red	Off
Yellow	Paused
Green	Running
See Also

Starting or Stopping a Server


Configure SQL Executive Dialog Box

Use this dialog box to set configurable SQL Executive parameters. For more information, choose one of the following:

 Auto-Start SQLMail When SQL Executive Starts

 Auto-Restart Server After Unexpected Server-Down

 Unexpected Server-Down Detection Polling Interval (minutes)

 Local Host Server

 Error Log File

 Locate

 View

 System Account

 This Account

 Password

 Confirm Password

 Reset CmdExec Account

 Reset CmdExec Password

See Also

Configuring SQL Executive


Auto-Start SQLMail When SQL Executive Starts

Automatically starts SQLMail when SQL Executive starts.


Auto-Restart Server After Unexpected Server-Down

Automatically restarts SQL Server after an unexpected stop (if a stoppage is not due to a shutdown or a net stop mssqlserver command).


Unexpected Server-Down Detection Polling Interval (minutes)

Sets the interval, in minutes, that SQL Executive uses between its checks to see whether SQL Server has stopped unexpectedly.


Local Host Server

Specifies the name of the local SQL Server. Usually, it is set to (default). However, if the server is set to listen on an alternate named pipe, do not use (default). Instead, select the listen on name from the Local Host Server list.


Error Log File

Specifies the path and the name of a file to write SQL Executive debug information to.

By default, this field is empty and information is not logged. To log SQL Executive information, type a drive, path, and filename in this box. For example:

C:\SQL60\LOG\EXEC.OUT


Locate

Specifies a location for the SQL Executive error log file.


View

Opens the contents of the SQL Executive error log. This button is dimmed (unavailable) if the Error Log File box is empty.


System Account

Assigns SQL Executive to run in the servers LocalSystem account.

A service running under the LocalSystem account has no user account information associated with it. This prevents certain network server software from allowing the Windows NT service to access resources. Selecting the System Account parameter prevents using several features, such as replication and scheduled task runs involving other servers.


This Account

Specifies that SQL Executive will run in the security context of the specified Windows NT user account. The account will usually be a domain user account (although it can also be a user account on the local server). This account should be reserved for use by SQL Executive (users should not use it to log on).

Usually, the account is assigned through the SQL setup program when SQL Server is installed. If you must change the user account name or update the password, always use the Configure SQL Executive dialog box to do so. Do not use the Services application in the Control Panel.


Password

Specifies the password of the Windows NT user account assigned to SQL Executive.


Confirm Password

Confirms the password of the Windows NT user account assigned to SQL Executive.


Reset CmdExec Account

Reinstalls the SQLExecutiveCmdExec account, resetting the account to its default (minimum) level of access rights. This removes any additional rights that may have been granted to the account.

SQLExecutiveCmdExec is a local Windows NT account on the server that is being administered. CmdExec tasks owned by users other than system administrators run in the security context of this account.


Reset CmdExec Password

Automatically changes the Windows NT password used by the SQLExecutiveCmdExec account.

Do not use User Manager to change the CmdExec password. If you do, then CmdExec tasks owned by users other than system administrators cannot run successfully.


Managing Remote Servers and Logins

A remote server is a SQL Server on the network that can be accessed through a user's local server, allowing the user to execute stored procedures on the remote server. Replication processes also establish remote server relationships. For more information about remote servers and remote logins, see the Microsoft SQL Server Administrators Companion.

To manage remote servers and logins

1.	From the Server Manager window, select a server.

2.	From the Server menu, choose Remote Servers.

3.	Complete the Manage Remote Servers dialog box.

For more information about the Manage Remote Servers dialog box, choose one of the following:

 Server Name

 RPC

 Subscriber

 Publisher

 Distributor

 Distribution Publisher

 Distribution Options

 Translate All Remote Login Names to

 Trusted

 Remote Server Login Name

 Mapped Login Name

 Trusted

 Add

 Drop

 Drop Login


Setting Query System Options

To configure the SQL Query Tool

1.	From the Tools menu, choose Preferences/Configure.

2.	From the Configure SQL Enterprise Manager dialog box, choose the Query System Tab.

3.	Adjust the configuration options as appropriate, then choose OK.

For more information about SQL Query Tool configuration options, choose one of the following:

 Undo Buffer Operations

 Font

 Display Message Boxes

 Default To Save

 Default To Discard

 Query File Extension

 Results File Extension

 Prompt On Closure

 Switch To Result Tab On Execute

 Defaults

See Also

Query System Tab

Setting Query Flag and Format Options


Managing Extended Stored Procedures

With the extended stored procedures manager, you can add and drop extended stored procedures to and from a SQL Server.

To open the extended stored procedure manager

1.	In the Server Manager window, select a server, open its Databases folder, and select the master database.

2.	From the Manage menu, choose Extended Stored Procedures.

To add an extended stored procedure

1.	In the Manage Extended Stored Procedures dialog box, in the Procedure box type the name of the procedure.

2.	In the DLL Name box, type the name of the procedure's dynamic link library (DLL).

3.	Choose Add.

To remove an extended stored procedure

1.	In the Manage Extended Stored Procedures dialog box, select a procedure from the list in the Procedure box.

2.	Choose Drop.


Starting or Stopping a Server

You can start or stop a server directly from the Server Manager window, or by using the Stop/Pause/Start button on the toolbar.

To start or stop a server directly from the Server Manager window

1.	In the Server Manager window, click the right mouse button on the server name.

2.	From the shortcut menu, choose (as appropriate) Start, Stop, Pause, or Continue.

To start or stop a server using the toolbar button

1.	In the Server Manager window, select a server.

2.	From the toolbar, click the Stop/Pause/Start button. (The button displays a traffic light.)

3.	In the SQL Server Manager window, double-click (as appropriate) the red light (to stop the server), the yellow light (to pause the server), or the green light (to start or continue the server).


Other Tools

Running the Database Maintenance Plan Wizard

Adding an External Tool

Running an External Tool


Running the Database Maintenance Plan Wizard

A wizard is an online user assistance tool that is simple to use and focuses on results, not technique. Wizards walk you through a series of questions by progressing in an easy, step-by-step manner toward completing a goal.

To run the Database Maintenance Plan Wizard

1.	From the toolbar, choose the Wizards icon. Or from the Help menu, choose Database Maintenance Wizard.

2. 	Follow the on-screen instructions.

The database maintenance plan wizard, SQLMAINT.EXE, can also be run from the command line. To view a list of command line parameters, type:

C:\SQL60\BINN\SQLMAINT.EXE /?


Adding an External Tool

To add an external tool

1.	From the Tools menu, choose External Tools.

2.	In the External Tools dialog box, click the Add button.

3.	In the Command box, type the path and the filename of the application. Or, click the Locate button and complete the Open dialog box.

4.	To pass parameters to the application each time it is launched, type the parameters in the Parameters box. (This is an optional entry.)

5.	Choose OK.

To change an external tool

1.	In the External Tools dialog box, select a tool from the Current Tools list.

2.	Type a tool name in the Menu Text box. This name will appear on the Tools menu.

3.	Type a command in the Command box. Usually, the command is a fully qualified path and includes the filename of an application.

4.	To pass parameters to the application each time it is launched, type the parameters in the Parameters box. (This is an optional entry.)

5.	Choose Change.

To drop an external tool

1.	In the External Tools dialog box, select a tool from the Current Tools list.

2.	Choose Remove.

See Also

Running an External Tool


Running an External Tool

Tools added to SQL Enterprise Manager are listed at the bottom of the Tools menu. They also appear on a floating, dockable toolbar. The toolbar can be placed anywhere on your screen, or can be docked to the SQL Enterprise Manager toolbar. The floating, dockable toolbar can be displayed or hidden using the View menu Toolbar command.

To run an external tool from SQL Enterprise Manager

From the bottom of the Tools menu, choose the command for the selected tool.

Or, from the floating, dockable toolbar, click the icon for that tool.

See Also

Adding an External Tool


Raising SNMP Traps from an Alert

An SNMP trap notifies the SNMP agent that a status or condition has occurred. The SNMP agent can then forward that notification to specified workstations, which are running SNMP monitoring application(s). Alert responses can include the option Raise an SNMP Trap When the Alert Occurs. Set up this option from the New Alerts or Edit Alerts dialog box.

To configure an alert to raise an SNMP trap

1.	From the Server menu, choose Alerts.

2.	From the Manage Alerts dialog box, click the New Alert or Edit Alert icon.

3.	Select the Raise an SNMP Trap When the Alert Occurs option.

Important: If you will be raising SNMP traps in alert responses, you must define the workstations that will receive SNMP trap notifications. To do this, use the Networks application in the Control Panel. For more information, see What's New in Microsoft SQL Server 6.5.

See Also

SNMP Support


Configuring SQL Executive

SQL Executive configuration parameters can be set from SQL Enterprise Manager.

To configure SQL Executive

1.	From the Server Manager window, click the right mouse button on a servers SQL Executive icon.

2.	Choose Configure from the shortcut menu.

3.	Complete the Configure SQL Executive dialog box.

From the Configure SQL Executive dialog box, you can set the following SQL Executive parameters:

 Auto-Start SQLMail When SQL Executive Starts

 Auto-Restart Server After Unexpected Server-Down

 Unexpected Server-Down Detection Polling Interval (minutes)

 Local Host Server

 Debug Output File

 System Account

 This Account

 Password

 Confirm Password

 Reset CmdExec Account

 Reset CmdExec Password


SQLExecutiveCmdExec User Account

A Windows NT user account, SQLExecutiveCmdExec, is added by the SQL Server setup program when SQL Server 6.5 is installed. CmdExec tasks owned by users other than system administrators (SAs) run in the security context of this account.

Windows NT SAs can control the operating system privileges of tasks owned by other users by controlling the rights, permissions, and group memberships of the SQLExecutiveCmdExec account. By default, this account is a member of the Users local group on the server.

To configure the rights, permissions, and group memberships of the SQLExecutiveCmdExec account, use the User Manager application. For more information on administering Windows NT user accounts, see Windows NT documentation.

Note that SA-owned CmdExec tasks run in the security context of the user account assigned to SQL Executive. Usually, this user account belongs to the Administrators local group on the server.


Graph Properties Dialog Box

Use this dialog box to set the graph properties. For more information, choose one of the following:

 Title Label

 Units Label

 Display Solid Colors

 Display Legend Window

 Graph Types


Title Label

Displays the title of the graph.


Units Label

Displays text labels for the graph's numerical gradations.


Display Solid Colors

Displays the graph in solid colors. If cleared, displays the graph in patterns and colors.


Display Legend Window

Displays a legend window near the graph that explains the graph elements.


Graph Types

Lists available graph types. The default is Bar Graph. You can select an alternate graph type from the list (if alternate types are present).


Bar Graph Options Dialog Box

Use this dialog box to set the bar graph options. For more information, choose one of the following:

 Graph Mode

 Orientation


Graph Mode

Specifies the graph's mode. Selectable allows you to select bar or legend text. You can then edit or delete the entity represented by the selected graph element. (For example, from the Manage Databases window, you can select a database and then open the Edit Database dialog box.)

Stretched prevents you from selecting graph elements, and optimizes the proportion of bar sizes to the available screen area.


Orientation

Specifies the graph's orientation. Vertical displays vertical bars. Horizontal displays horizontal bars.


Print Options Dialog Box

Use this dialog box to print the graph using the server's default printer, and to configure the printing options. For more information, choose one of the following:

 Use Colors/Shading

 Use Patterns

 Print Date/Time

 Print Legend

 Legend Font Size

 Date/Time Font Size

 Print


Use Colors/Shading

Specifies using colors in the graph printout. If a monochrome printer is used, this option uses shading to represent screen colors.


Use Patterns

Specifies using patterns to represent screen colors in the graph printout.


Print Date/Time

Includes the printing date and time on the graph printout.


Print Legend

Includes legend text in the graph printout.


Legend Font Size

Specifies the legend font size: small, normal, or large. This option is not available if the Print Legend option has been cleared.


Date/Time Font Size

Specifies the date and time font size: small, normal, or large. This option is not available if the Print Date/Time option has been cleared.


Print

Prints the graph on the server's default printer using the settings selected in the Print Options dialog box.


Transfer

SQL Enterprise Manager includes a transfer management interface that can transfer all or some objects from one database to another. The destination server must be Microsoft SQL Server version 6.5. The source server can be Microsoft SQL Server version 6.x or 4.x. For more information, choose one of the following:

About Transfer

Transferring Objects


About Transfer

SQL Enterprise Manager includes a transfer management interface that can transfer all or some objects from one database to another. Transfers can be performed immediately, or can be scheduled for immediate, one-time, or recurring execution.

The destination server must be Microsoft SQL Server version 6.5. The source server can be Microsoft SQL Server version 6.x or 4.x. If the source server is version 4.x, some stored procedures or views may contain Transact-SQL statements that are no longer accepted in version 6.x, and some object names may be reserved words in version 6.x. A transfer will fail for these objects, and will not complete correctly for objects that depend on them.

You can use the transfer management interface, for example, to transfer data from SQL Server running on one processor architecture (for example, Intel) to SQL Server running on a different processor architecture (for example, Power PC).

You can transfer data from a server with one sort order to a server with a different sort order. Note that extended characters are not converted, so you cannot use the transfer management interface to convert from one code page to another.

Also note that when transferring data between servers with different case sensitivities, errors will occur if case is applied inconsistently when you create objects and add data. For example, on a case-insensitive server, if you create a table called ABC and then create a view called xyz as SELECT * FROM abc, the CREATE VIEW statement will work. However, if you transfer the information to a case-sensitive server, the CREATE VIEW statement will fail.

Before using the transfer management database tool to transfer data, the destination database must exist and be large enough to contain all objects and data that you transfer. You must have SELECT permission in the source database, and you must own the destination database.


Transferring Objects

To transfer objects between databases

1.	From the Server Manager window, select a source server.

2.	Open the source server's Databases folder, and select a source database.

You must have SELECT permission in the source database.

3.	From the Object menu, choose Transfer.

The Transfer Objects dialog box appears. The title bar displays the source server and database.

4.	From the list in the Destination Server box, select a destination server.

If the required server name is not included in the list, add it to the list by clicking the New Server button and completing the Register Server dialog box.

Note that the source server and the destination server can be the same server.

5.	From the list in the Destination Database box, select a destination database.

Note that the database must already exist and have enough space to contain the objects and data you are transferring. The destination database must be a different database than the source database.

6.	Select or clear the Transfer Schema options. For more information, choose one of the following:

Copy Schema
Drop Destination Objects First
Include Dependency Objects

7.	Select or clear the Copy Data options. For more information, choose one of the following:

Copy Data
Replace Existing Data

8.	To transfer all objects in the database, select the Transfer All Objects check box. To transfer selected objects, click the Choose Objects button and complete the dialog box.

Objects that can be transferred include tables, views, stored procedures, defaults, rules, and user-defined datatypes.

9.	To use default object and security scripting options, select the Default Scripting Options check box. To change the scripting selections, click the Scripting Options button and complete the dialog box.

The object scripts transfer table triggers, table DRIs, table bindings, nonclustered and clustered indexes, and owner qualified objects. The security scripts transfer users and groups, logins, and object and statement permissions.

10. To change the transfer scripts storage location, type a new path in the Save Transfer Files In Directory box.

The default is C:\SQL60\BINN. If the directory does not already exist, SQL Enterprise Manager will create it.

11. Choose Start Transfer to execute the transfer, or click the Schedule button to schedule the transfer.

When the transfer is executed, several files are created in the transfer files directory, and the specified data and objects are transferred.

A scheduled transfer can be set to occur immediately, one time, or on a recurring basis. Scheduling an immediate transfer (instead of choosing Start Transfer) provides the advantage of executing the transfer as a scheduled (background) task, instead of interactively. Executing the transfer interactively allows you to interrupt the transfer by clicking the Terminate button.

12. After the transfer has executed, you can view the transfer log by clicking the View Errors button.


Transfer Objects Dialog Box

Use this dialog box to transfer some or all objects from one database to another. The destination server must be a Microsoft SQL Server version 6.5 server. The source server can be Microsoft SQL Server version 6.x or 4.x. Transfers can be performed immediately, or can be scheduled for immediate, one-time, or recurring execution. For more information, choose one of the following:

 Destination Server

 New Server

 Destination Database

 Copy Schema

 Drop Destination Objects First

 Include Dependency Objects

 Copy Data

 Replace Existing Data

 Save Transfer Files in Directory

 Transfer All Objects

 Choose Objects

 Default Scripting Options

 Scripting Options

 Start Transfer

 Schedule

 View Errors

See Also

Transferring Objects


Destination Server

Specifies a server to transfer data to. Note that the source server and the destination server can be the same server.

If the required server name is not included in the list, add it to the list by clicking the New Server button and completing the Register Server dialog box that appears.


New Server

Adds a server name to the Destination Server list. Click this button and complete the Register Server dialog box that appears.


Destination Database

Specifies the database to transfer data to. Note that the database must already exist and have enough space to contain the objects and data you are transferring. The destination database must be a different database than the source database.


Copy Schema

Copies table schema from the source database to the destination database.


Drop Destination Objects First

Drops objects from the destination database before objects are transferred from the source database.


Include Dependency Objects

Includes all objects that depend on the object(s) or object type(s) selected for transfer.


Copy Data

Copies table data from the source database to the destination database.


Replace Existing Data

Replaces existing data in the destination tables with transferred data. Clear this option to retain existing data and append transferred data.


Save Transfer Files in Directory

Specifies where transfer scripts are stored. The default is C:\SQL60\BINN. To change the location, type a new path in this box.

If the directory does not already exist, SQL Enterprise Manager will create it.


Transfer All Objects

Transfers all objects in the database, including tables, views, stored procedures, defaults, rules, and user-defined datatypes.


Choose Objects

Transfers only selected objects, including tables, views, stored procedures, defaults, rules, and user-defined datatypes. You can transfer all, some, or no objects of a particular type.


Default Scripting Options

Uses default object and security scripting options. The default object options automatically create scripts that re-create in the destination database table triggers, table DRI, table bindings, nonclustered and clustered indexes, and owner-qualified objects from the source database. The default security options re-create users and groups, logins, and object and statement permissions.


Scripting Options

Specifies which scripting options to use. This is an advanced option.

The default object options can automatically create scripts that re-create table triggers, table DRI, table bindings, nonclustered and clustered indexes, and owner qualified objects from the source database in the destination database. The default security options can re-create users and groups, logins, and object and statement permissions.


Start Transfer

Executes the transfer immediately, as defined by your selections in this dialog box.


Schedule

Schedules the transfer to occur immediately, one time, or on a recurring basis.

Scheduling an immediate transfer (instead of choosing Start Transfer) provides the advantage of executing the transfer as a scheduled (background) task instead of interactively.


View Errors

Displays the transfer log after the transfer has executed.


Choose Transfer Objects Dialog Box

Use this dialog box to choose objects to transfer. For more information, choose one of the following:

 All Tables

 All Views

 All Stored Procedures

 All Defaults

 All Rules

 All User Defined Datatypes

 Add/Remove Objects


All Tables

Transfers all tables from the source database to the destination database.


All Views

Transfers all views from the source database to the destination database.


All Stored Procedures

Transfers all stored procedures from the source database to the destination database.


All Defaults

Transfers all defaults from the source database to the destination database.


All Rules

Transfers all rules from the source database to the destination database.


All User Defined Datatypes

Transfers all user-defined datatypes from the source database to the destination database.


Add/Remove Objects

Specifies whether to transfer or prevent transferring a specific object. To transfer an object, select it from the list on the left and choose Add. To prevent transferring an object, select it from the list on the right and choose Remove.

Note that after you select a check box in the Object Types frame, you can click the Remove button to remove one or more objects of the selected type from the transfer list.

Also, after you clear a check box in the Object Types frame, you can click the Add button to add one or more objects of the selected type to the transfer list.


Transfer Scripting Options Dialog Box

Use this dialog box to configure transfer preferences. Note that these preferences are advanced options. Use them only if you completely understand their effects.

For more information, choose one of the following:

 Table Triggers

 Table DRI

 Table Bindings

 Owner Qualified Objects

 All Users/Groups

 All Logins

 Object Permissions

 Statement Permissions

 Nonclustered Indexes

 Clustered Indexes

 Create Clustered Indexes/Keys After Data Transfer with 'Sorted Data'

 Preserve Fill Factors


Table Triggers

Re-creates in the destination database all table triggers from the source database. SQL Enterprise Manager automatically generates a script to create the triggers in the destination database.


Table DRI

Re-creates in the destination database all table constraints (declarative referential integrity, or DRI) from the source database. SQL Enterprise Manager automatically generates a script that creates the constraints in the destination database.


Table Bindings

Re-creates in the destination database all table bindings from the source database. SQL Enterprise Manager automatically generates a script that creates the table bindings in the destination database.


Nonclustered Indexes

Re-creates in the destination database all nonclustered indexes from the source database. SQL Enterprise Manager automatically generates a script that creates the nonclustered indexes in the destination database.


Clustered Indexes

Re-creates in the destination database all clustered indexes from the source database. SQL Enterprise Manager automatically generates a script that creates the clustered indexes in the destination database.


Owner-qualified Objects

Transfers a source database object that is not owned by the dbo to the destination database. The original owner retains ownership. If you clear this option, the object is transferred, but the user performing the transfer becomes the owner of such objects in the destination database.


All Users/Groups

Re-creates in the destination database all users and groups from the source database. SQL Enterprise Manager automatically generates a script that creates the users and groups in the destination database.


All Logins

Re-creates on the destination server all login IDs from the source server. SQL Enterprise Manager automatically generates a script that creates the login IDs on the destination server.


Object Permissions

Re-creates in the destination database all object permissions (of transferred objects) from the source database. SQL Enterprise Manager automatically generates a script that creates the object permissions in the destination database.


Statement Permissions

Re-creates in the destination database all statement permissions (of transferred objects) from the source database. SQL Enterprise Manager automatically generates a script that creates the statement permissions in the destination database.


Create Clustered Indexes/Keys After Data Transfer with 'Sorted Data'

Specifies that clustered indexes and keys will be created after data transfer using the SORTED_DATA option.

If this option is not selected, clustered indexes and keys are created before data transfer, and nonclustered indexes are created after data transfer.


Preserve Fill Factors

Specifies that clustered indexes and keys are created after data transfer using the SORTED_DATA_REORG option. The Create Clustered Indexes/Keys After Data Transfer with 'Sorted Data' option must be selected.


SQL Executive Error Log Window

Displays the contents of the SQL Executive error log.


File Location

Use this dialog box to select the path for the file (the location on disk where the file will be stored). The path will become part of the physical name of the file.


Mirrori Database Device Dialog Box
Mirror/Re-Mirror Database Device Dialog Box

Use this dialog box to mirror or remirror a database device.

Mirror Device Name

Displays the full qualified path where the device will be mirrored. To change the mirror name and path, type a different filename or path in this box.

Mirror Now

Mirrors the database device to the specified location.

Re-Mirror Now

Remirrors a database device to the specified location.


Unmirror Device Dialog Box

Use this dialog box to unmirror a database device.

Switch to Mirror Device - Retain Original Device

Switches to the mirror device, but retains the other database device.

Switch to Mirror Device - Replace Original Device

Switches to the mirror device and replaces the other database device with the mirrored device.

Turn Off Mirroring - Retain Mirror Device

Pauses the mirroring and allows you to remirror at a later time.

Turn Off Mirroring - Remove Mirror Device

Permanently removes the mirror device.

Mirror Device Filename

Displays the fully qualified path of the mirrored device.

Unmirror

Unmirrors the device according the unmirror option you have selected.


Manage Triggers Window

Use this window to create, modify, or drop triggers.

Tables

Specifies the table to manage a trigger for.

Triggers

Displays the triggers for that table. Each table can have only one insert, one update, and one delete trigger. To create a trigger, select <New>, and then type the text of the trigger in the window using valid Transact-SQL syntax. To modify a trigger, select the trigger and modify the text in the window. New triggers automatically replace old triggers of the same type.

For help with Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE TRIGGER.

Represents an INSERT trigger.

Represents an UPDATE trigger.

Represents a DELETE trigger.

 Execute

Saves the trigger to the SQL Server.

 Print

Prints the information about the selected trigger.

Object menu-Drop Command

Drops the trigger selected in the Triggers box.


Manage Stored Procedures Window

Use this window to create, modify, and drop stored procedures.

Procedures

Specifies whether to create, modify, or drop a stored procedure. To create a stored procedure, select <New>, and type the text of the stored procedure in the window using valid Transact-SQL syntax. To modify or drop a stored procedure, select the name of the procedure. To modify the selected procedure, edit the text in the window.

For help with Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE PROCEDURE.

 Execute

Saves the stored procedure to the SQL Server.

 Print

Prints the information about the selected stored procedure.

Object menu-Drop Command

Drops the stored procedure selected in the Procedures box.


Manage Views Window

Use this window to create, modify, and drop views.

Views

Specifies whether to create, modify, or drop a view. To create a view, select <New>, and type the text of the view in the window using valid Transact-SQL syntax. To modify or drop a view, select the name of the view.  To modify the selected view, edit the text in the window.

For help with Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE VIEW.

 Execute

Saves the view to the SQL Server.

 Print

Prints the information about the selected view.

Object menu-Drop Command

Drops the view selected in the Views box.


Application Tab

Use this tab to set application preferences for SQL Enterprise Manager. For more information, choose one of the following:

 Server State Poll Interval

 Server Registration Information

 Store Locally

 Store User Independent

 Read From Remote

 Server Name

 Defaults


Server State Poll Interval

Specifies the interval set in the Server Manager window for polling the server status. Polling uses the Windows NT Service Control Manager.

Because the Service Control Manager is not available with Windows 95, no polling information is available on a client running Windows 95. Also, when an aliased server (using the SQL Client Configuration utility) is registered with SQL Enterprise Manager, server state polling is not supported. Server status information (such as started, stopped, or paused) is not displayed in the Server Manager window.


Server Registration Information

Sets SQL Enterprise Manager to use server registration information stored on a remote server. For example, you might configure several computers so that their SQL Enterprise Manager installations have identical configurations (common program groups, common servers registered, and so on).

SQL Enterprise Manager generally uses server registration information that is stored locally (on the same server that SQL Enterprise Manager is installed on).


Store Locally

Uses server registration information that is stored locally (on the same server that SQL Enterprise Manager is installed on).


Store User Independent

Uses common registration information for all SQL Enterprise Manager users. If cleared, maintains separate registration information for each user (based on the user's Windows NT logon).


Read From Remote

Uses server registration information that is stored in the remote server Registry.


Server Name

Specifies the name of a remote server to provide server registration information for SQL Enterprise Manager.


Defaults

Sets the options in this dialog box to their default settings. The Server State Poll Interval is set to 10, and the Store Locally and Store User Independent options are selected.


Manage Tables Window

Use this window to create and modify tables; to create identity columns; and to specify primary and foreign keys, UNIQUE constraints, and CHECK constraints.

Tables

Specifies whether to create or modify a table. To create a table, select <New>. To modify a table, select that table name.

 Save Table

Saves a new table or changes to an existing table. If you are creating a new table, in the Specify Table Name dialog box, specify a table name.

 Advanced Features

Displays Primary Key/Identity, Foreign Keys, UNIQUE Constraints, and CHECK Constraints tabs.

Key

Specifies whether the column is part of a primary key. The Key field is read only. To create a primary key, click the Advanced Features button and then supply the appropriate information in the Primary Key/Identity tab.

Identity

Specifies whether the column is an identity column. The Identity field is read only. To create an identity column, click the Advanced Features button and then supply the appropriate information in the Primary Key/Identity tab.

Column Name

Specifies the name of the column. If you are creating a table, type a name for the column. If you are modifying a table, change the name of the column by typing a new name in the box. If you rename a table column, you must rebuild all views, stored procedures, or triggers that reference that table column.

Datatype

Specifies the datatype for the column. If you are creating a table, select a datatype in the box. If you are modifying a table, you cannot change the datatype.

Size

Specifies the datatype size when the datatype is char, varchar, binary, varbinary, numeric, or decimal. For numeric and decimal datatypes, you must specify precision and scale separated by a comma. If you are modifying the table, you cannot change the datatype size.

Nulls

Specifies whether the column allows null values. If you are creating a table and the column you are adding will allow null values, select the Nulls box. If you are modifying a table, you cannot change an existing column's null values and all new columns must allow null values.

Default

Specifies a default for the column (to be used if a value is not provided when data is inserted into the table). To create a default, type the default text in the box. For example, to provide the current date for a datetime column as a default, type getdate( ) in the Default box.

See Also

Primary Key/Identity Tab

Foreign Keys Tab

Unique Constraints Tab

Check Constraints Tab


Primary Key/Identity Tab

To display this tab, click the Advanced Features button from the Manage Tables toolbar.

Use this tab to specify primary key information for a table and identity column information. If you are creating a table, you can create a primary key or an identity column. If you are modifying a table, you can create or drop a primary key. You cannot modify identity column information in an existing table.

Column Name

Specifies the column(s) (up to 16) to associate with the primary key. The Column Name box lists only those columns that are valid primary key candidates. For example, columns that allow null values cannot be associated with a primary key.

Type

When you create a primary key, a unique index is automatically created. Specifies whether to create the index as clustered or nonclustered. Only one clustered index can be created per table.

Fill Factor

Specifies a fill factor when you are creating a primary key on an existing table.

Add

Associates entered primary key information with the table. Information is not saved to SQL Server until you click the Save button.

Remove

Removes the primary key. Information is not saved to SQL Server until you click the Save button.

Identity Column

Selects the column to create as an identity column. You can add an identity column only when creating a table.

Seed Value

Specifies the initial (seed) value for the identity column.

Increment

Specifies a value to add to the last identity value to get the new identity value. For example, if you specify a seed value of 5 and an increment of 5, the first row will contain 5, the second 10, and so on.


Foreign Keys Tab

Use this tab to specify foreign key information for a table. To display this tab, click the Advanced Features button from the Manage Tables toolbar. You can add foreign keys when you create or modify a table.

Foreign Keys

Specifies whether to create or drop a foreign key. To create a foreign key, select <new> and type a name for the key. A name is optional. If you do not provide a name, SQL Server automatically generates one. To drop a foreign key, select the key name in this box.

Referenced Table

Specifies the table that contains the primary key or UNIQUE constraint the foreign key is associated with.

Key Columns

Displays the columns in the primary key or UNIQUE constraint of the referenced table.

Foreign Key Columns

Specifies columns of the current table to associate with key columns of the referenced table. Foreign key column datatype and size must match key column datatype and size.

Add

Associates foreign key information with the table. Information is not saved to SQL Server until you click the Save button.

Remove

Removes the foreign key selected in the Foreign Keys box. Information is not saved to SQL Server until you click the Save button.


Unique Constraints Tab

Use this tab to specify UNIQUE constraints for a table. To display this tab, click the Advanced Features button from the Manage Tables toolbar.

Unique Constraints

Specifies whether to create or drop a unique constraint. To create a constraint, select <new> and provide a name for the constraint. A name is optional. If you do not provide one, SQL Server automatically generates one. To drop a constraint, select the name of the constraint to drop.

#

Specifies an automatically generated number for each constraint column.

Column Name

Specifies the column(s) to create a UNIQUE constraint for.

Type

Specifies whether to create a clustered or nonclustered index. Only one clustered index can be created per table.

Fill Factor

Specifies a fill factor when you are creating a UNIQUE constraint on an existing table.

Add

Adds the constraint information. Information is not saved to SQL Server until you click the Save button.

Remove

Removes the selected constraint in the Unique Constraints box. Information is not saved to SQL Server until you click the Save button.


Check Constraints Tab

Use this tab to specify CHECK constraints for a table. To display this tab, click the Advanced Features button from the Manage Tables toolbar. You can specify CHECK constraints when you create or modify a table.

Check Constraint

Specifies whether to create or drop a check constraint. To create a constraint, select <new> and type a name for the constraint. A name is optional. If you do not provide a name, SQL Server automatically generates one. To drop a constraint, select the name of the constraint to drop.

Not for Replication

Specifies that the CHECK constraint will not be replicated.

Constraint

Specifies text for the constraint.

Add

Adds the constraint. Information is not saved to SQL Server until you click the Save button.

Remove

Removes the constraint selected in the Check Constraint box. Information is not saved to SQL Server until you click the Save button.


Manage Databases Window

Use this window to view information about the space used by databases, and to add, modify, and delete databases. You can view databases you have access to.

 New Database

Displays the New Database dialog box, which is used to create new databases.

 Edit Database

Displays the Edit Database dialog box, which is used to expand or shrink databases and to set database options and statement permissions. Select a database from the Databases graph before clicking this button.

 Delete Database

Deletes a database. Select a database from the Databases graph before clicking this button.

Databases graph

Shows available server databases, the size of each, and the free space on each.

Graph Controls

Displays three buttons in the lower left of the dialog box to be used to configure and print information displayed in the graph. For more information, select one of the following:

Graph Properties

Bar Graph Options

Print Options


New Database Dialog Box

Use this dialog box to create a database immediately, or to schedule creating a database.

Name

Specifies the name of the database.

Data Device

Specifies the device on which to allocate space for the data. To create a device, select <New> and complete the information in the New Database Device dialog box.

Size (MB)

Specifies the amount of space, in megabytes (MB), to allocate for the database on the selected device.

Log Device

Specifies the device on which to allocate space for the transaction log. To create a device, select <new> and complete the information in the New Database Device dialog box.

Size (MB)

Specifies the amount of space, in megabytes, to allocate for the transaction log on the selected device.

Create for Load

Creates the database more quickly by not zero-initializing pages when the database is created. Select this option if you will be loading a backup into this database before using it. However, users other than DBOs cannot access the database until a backup is loaded and the database option DBO Use Only is turned off.

Available Database Devices graph

Shows available server database devices, the size of each, and the free space on each.

Graph Controls

Displays three buttons in the lower left of the dialog box to be used to configure and print information displayed in the graph. For more information, select one of the following:

Graph Properties

Bar Graph Options

Print Options

Create Now

Creates the new database, as you have defined it in the New Database dialog box.

Schedule

Opens the Schedule Database Creation dialog box. Click this button to schedule creating the database (instead of creating it immediately).


Expand Database Dialog Box

Use this dialog box to allocate more space for a database or transaction log. You can schedule the expansion to be performed immediately, or in the future.

Data Device

Specifies the device on which to allocate additional space for the database. To create a device, select <new>.

Size (MB)

Specifies the amount of space, in megabytes (MB), to allocate on the selected device for the database.

Log Device

Specifies the device on which to allocate additional space for the transaction log. To create a device, select <new>.

Size (MB)

Specifies the amount of space, in MB, to allocate on the specified device for the transaction log.

Available Database Devices

Shows available server database devices, the size of each, and the free space on each.

Graph Controls

Displays three buttons in the lower left of the dialog box used to configure and print information displayed in the graph. For more information, select one of the following:

Graph Properties

Bar Graph Options

Print Options

Expand Now

Expands the database or transaction log immediately, as specified in this dialog box.

Schedule

Opens the Schedule Database Expansion dialog box. Click this button to schedule the expansion for the future (instead of immediately).


Manage Database Devices Window

Use this window to view the logical names of all database devices, and the size of and the space used on each device. You can add, modify, and remove devices.

 New Device

Displays the New Database Device dialog box, which is used to create database devices.

 Edit Device

Displays the Edit Database Device dialog box, which is used to modify database devices. You must select a device from the graph before clicking this button.

 Delete Device

Deletes a device. You must select a device from the graph before clicking this button.

Devices graph

Shows the available server devices, the size of each, and the free space on each.

Graph Controls

Displays three buttons in the lower left of the dialog box to be used to configure and print information displayed in the graph. For more information, select one of the following:

Graph Properties

Bar Graph Options

Print Options


New Database Device Dialog Box

Use this dialog box to add a new database device. The device can be created immediately, or in the future.

Name

Specifies a name for the device.

Default Device

Sets the device as a default device (to accept a database when no device is specified).

Location

Specifies a drive on which to place the device.

Path

Specifies a path for the new device. The path is set automatically based on the device name. The path defaults to the location of the MASTER device. To change the path, type a new path or click the Locate button.

 Locate

Displays the Locate Database Device dialog box, which is used to select a different path for the device.

Size (MB)

Specifies the size for the device, in megabytes (MB). To change the size, type a new number or move the slider bar control.

Available Storage Space graph

Shows available storage space, in megabytes, on the server disk drives.

Graph Controls

Displays three buttons in the lower left of the dialog box to be used to configure and print information displayed in the graph. For more information, select one of the following:

Graph Properties

Bar Graph Options

Print Options

Create Now

Creates the device immediately, as you have specified in the New Database Device dialog box.

Schedule

Opens the Schedule Database Device Creation dialog box. Click this button to schedule creating the device (instead of creating it immediately).

Mirroring

Displays the Database Device Mirror dialog box, which is used to mirror the device.


Edit Database Devices Dialog Box

Use this dialog box to set a device as the default device, or to handle mirroring for a device. You can also expand the size of a device. The changes can be made immediately, or scheduled for later.

Name

Displays the database device name. You cannot change a device name.

Location

Displays the database device path. You cannot change the location of the device.

Size (MB)

Displays the device size, in megabytes. You can enlarge the device size by typing a new number in this box. You cannot reduce the size of a device.

Default Device

Sets the device as a default device to accept a database when no device is specified.

Device Space Usage graph

Shows the device size, and the amount of space currently used on the device.

Graph Controls

Displays three buttons in the bottom left of the dialog box to be used to configure and print information displayed in the graph. For more information, select one of the following:

Graph Properties

Bar Graph Options

Print Options

Change Now

Modifies the device immediately, as specified in this dialog box.

Schedule

Opens the Schedule Database Device Modification dialog box. Click this button to schedule modifying the device (instead of modifying immediately).

Mirroring

Displays dialog boxes that are used to mirror, unmirror, or remirror a database device.


Managing SQL Server Messages

You can view a list of available messages on SQL Server; search for messages by message text, severity, and/or error number; search for messages that are always written to the Windows NT application event log; select an option to always write a message to the Windows NT application event log; and create or edit userdefined messages.

To select a message to set an alert on

1.	While creating or editing an alert, under Alert Definition, select Error Number and then click the Locate button. The Manage SQL Server Messages dialog box appears.

2.	To view all available messages, leave the default setting as is and click the Find button. A list of all messages appears.

To view a subset of all available messages, choose the appropriate options and then click the Find button. A list of all messages that fit the profile appears.

To sort the list of messages by a column attribute, click the heading for that column. The sort option is not available if the list contains more than 750 rows. The default sort order is by error number.

3.	Select the message to define an alert for, and then click the Select button. The New or Edit Alert dialog box returns. The selected message number is automatically entered in the Error Number box.

To create a userdefined message

1.	From the Server menu, choose Messages. The Manage SQL Server Messages dialog box appears.

2.	Click the New button. The New Message dialog box appears.

3.	In the Error Number box, type an error number for the userdefined message. Error numbers for userdefined messages begin at 50001.

4.	In the Severity box, select a severity level for the message.

5.	In the Message Text box, type the message text.

6.	In the Language box, select a language to display the message in.

7.	To always write the message to the Windows NT application event log, select the Always Write to Windows NT application event log box.

8.	Choose OK.

To edit a message

1.	From the Server menu, choose Messages. The Manage SQL Server Messages dialog box appears.

2.	Define the search criteria for the message and then click the Find button.

3.	Select the message from the list and then click the Edit button. The Edit Message dialog box appears.

4.	Edit the appropriate information.

For userdefined messages, all options except Error Number can be edited. For other messages, only the option Always Write to Windows NT application event log can be changed.

To delete a message

1.	From the Server menu, choose Messages. The Manage SQL Server Messages dialog box appears.

2.	Define the search criteria for the message and then click the Find button.

3.	Select the message from the list and then click the Delete button. Only userdefined messages can be deleted.


Task Scheduling Window

Use this window to view the scheduled tasks list and the currently running tasks list for a server. You can add, modify, or remove a task and view task history information from this window.

A user can view tasks and task histories for only those tasks the user created and owns. The SA can view all tasks.

 New Task

Displays the New Task dialog box.

 Edit Task

Displays the Edit Task dialog box.

 Delete Task

Removes the selected task.

 Run Task

Immediately starts running the task selected in the Task List tab. The task must be enabled and the SQL Executive service must be running before selecting this option.

 Task History

Displays the Task History dialog box, which is used to provide a selected task history.

 Refresh

Refreshes the Task List and Running Tasks tabs.

 Task Engine Options

Displays the Task Engine Options dialog box, which is used to limit the size of or clear the task history log.

Task List Tab

Displays a list of scheduled tasks for the server.

Name

Is the task name.

Enabled

Indicates whether the task is enabled or disabled.

Owner

Is the task owner.

Type

Specifies the type of task. CmdExec is an operating system command or executable file. TSQL is a Transact-SQL statement. Distribution, LogReader, and Sync are replication tasks.

Frequency

Is the task's schedule type.

Last Run Date

Is the day the task last ran.

Last Run Time

Is the time of day the task last ran.

Running Tasks Tab

Displays a list of tasks currently running or about to run. Queued tasks are also shown in the Running Tasks tab. For subsystems, task types (other than TSQL) a task can be queued if it is scheduled to run but the subsystem has no free threads available.

Name

Is the task name.

Owner

Is the task owner (usually, the task creator).

Type

Is the type of task. CmdExec is an operating system command or executable file. TSQL is a Transact-SQL statement. Distribution, LogReader, and Sync are replication tasks.

Start Date

Is the run start time and the day.

Status

Displays status information about the task.


Task Engine Options Dialog Box

Use this dialog box to limit the size of the task history log. The task history log contains the information that is displayed when you select a task from the Task List tab and click the Task History button. By managing the size of the log, you control how long task history information is retained.

Limit Size of Task History Log

Specifies whether to limit the size of the task history log. If you clear this option, the size of the history log is not limited and the number in the Maximum Task History Log Size box has no effect. Depending on the server activity level, clearing this option can cause the size of the msdb.dbo.syshistory table to grow rapidly. In some cases, it can grow until the msdb database is full and subsequent tasks and alerts fail. If you need to retain large amounts of task history information, expand the msdb database size accommodate the growth of the syshistory table.

Maximum Task History Log Size

Specifies the maximum number of rows kept in the task history. The task history log will retain the most recent rows entered, up to the number specified. When the number is exceeded, the oldest row in the log is purged to make room for the newest row. The default number of rows is 1,000. This option applies only if the Limit Size of Task History Log box is selected.

Current Task History Log Size (rows)

Displays the size, in rows (entries), of the task history log.

Clear Task History Log

Empties the task history log.

Process Exit Code of a Successful CmdExec Task

Specifies the process exit code the CmdExec task will supply (if successful).


Task History Dialog Box

Use this dialog box to view a task history.

Run Date

Shows the date the task ran.

Run Time

Shows the time the task ran.

Status

Shows whether the task completed successfully.

Emailed

Indicates whether email has been sent after running a task if task scheduling was configured for email notification.

Run Duration

Shows how long the task ran.

Message

Shows messages that resulted from the task run.

For a CmdExec task, the process exit code plus the last 230 bytes (approximately) of that CmdExec task's output is included as part of the message. The information can assist in determining the cause of a task failure, or be useful in confirming that the task functioned correctly. If output is more than can be displayed, the displayed output is preceded by an ellipses (...).

Clear All

Clears the current task's history information. (Only the task owner can do this.)


New/Edit Operator Dialog Box

Use this dialog box to add an operator or to modify operator information. Operators are SQL Server users who are notified when an alert occurs.

ID

Is the automatically generated ID number of the operator.

Name

Is the name of the operator. If you are creating an operator, type the name of the operator.

Enabled

Enables an operator to receive alerts by default. To create or modify the operator information without enabling the operator to receive notifications, clear this check box.

Email Notifications

Specifies the operator's email information.

Email Name

Specifies the email name of the operator. For example, Fred.

Test

Sends a test message to the email name specified in the Email Name box. If the Mail client is running, SQL Enterprise Manager will attempt to resolve the email name or pager email name to the closest matching friendly email name. Note that the Test option is available only on computers running the 32-bit version of the Microsoft Mail client.

 Locate

Browses the address book of a Microsoft Mail client. This option is available if SQL Enterprise Manager is running on a client computer (a computer where SQL Server has not been installed) and Microsoft Mail is running. Note that the Browse option is available only on computers running the 32-bit version of the Microsoft Mail client.

Pager Notifications

Specifies a pager email name and schedule for the operator. To specify the pager schedule, select the appropriate check boxes and workday begin and end times.

Pager Email Name

Specifies a valid pager email name for the operator.

Test

Sends a test message to the pager email name specified in the Pager Address box. Note that you must define a template for the paging information in the Alert Engine Options dialog box before a test message can be sent to the pager.

Alerts Assigned To This Operator

Specifies the alerts assigned to an operator. To assign an alert to the operator, select the alert and then select the notification method: email and/or pager. A solid envelope or telephone icon indicates that the alert already has the selected notification defined on it. An outline envelope or telephone icon indicates that the alert does not have the selected notification defined for it. You can create an operator even if you have not yet created alerts.

Most Recent Notifications

Provides information about the most recent notifications the operator has received.

Send Mail

Opens the Send Note window from the underlying mail system and uses the value in the Email Name box as the email recipient. This feature is useful for notifying operators about changes in their alert-handling responsibilities.

This button is enabled only when the mail client is running.


New/Edit Alert Dialog Box

Use this dialog box to create or modify an alert.

ID

Is the automatically generated ID number of the alert.

Name

Is the alert name. If you are creating an alert, type a name for the alert.

Enabled

Enables the alert is by default. To create an alert without enabling it, clear this check box.

Alert Definition

Defines the conditions that will cause an alert. Specify an error number or a severity level.

Error Number

Raises an alert when a specific error message is entered in the Windows NT application event log. Type the error message number. To select from a list of error messages or to create a custom error message, click the Locate button.

 Locate

Displays the Manage SQL Server Messages dialog box, which is used to find error messages.

Severity

Raises an alert for any error of the specified severity that is entered in the Windows NT application event log.

Database Name

Specifies that an alert will occur only when an error is received from a specific database, and is optional.

Error Message Contains String

Specifies that an alert will occur only when the error message contains a particular string, and is optional.

Response Definition

Specifies the action the system will take when the defined alert occurs.

Task To Execute

Runs a task when the defined alert occurs. To create a task, select New Task. The task must be a non-replication task, and it must be scheduled to occur on demand. Running a task is optional.

Raise an SNMP Trap When the Alert Occurs

Raises an SNMP trap when the defined alert occurs. The SNMP trap notifies the Microsoft SQL Server SNMP agent that a status or condition has occurred.

Alert Notification Message To Send To Operator

Sends additional information to the operator when the defined alert occurs. This information is optional and is separate from the error message text, which can also be sent in the alert notification message by selecting the Include Error Message Text In check box. You can enter up to 255 characters. You do not need to type the error message text.

Include Error Message Text In

Includes the error message text as part of the notification message.

Delay Between Responses (in seconds) For Recurring Alerts

Specifies the time to wait between notifying an operator of recurring alerts. If you specify a delay, the operator will be notified of the first alert and if a subsequent alert is the same, then the operator only will be notified if the specified delay has elapsed. For example, if you type 8 in this box, and later 5 alerts that meet the alert definition occur within a period of 8 seconds or less, only 1 alert will be sent to the operator.

Operators To Notify

Specifies which operator(s) to notify when a defined alert occurs. Select Email and/or Pager for each operator to notify. A solid envelope or telephone icon indicates that the operator already has address information of that type defined. An outline envelope or telephone icon indicates that the operator does not have address information of that type defined. You can create an alert even if you have not yet added operators.

Most Recent Occurrences

Provides information about the most recent occurrences for the defined alert.


Schedule Backup Dialog Box
Schedule Dialog Box

Use this dialog box to schedule backups or index operations.

Task Name

Displays the task name.

Command

Displays the task text.

Immediately

Runs the task immediately as a background task in SQL Enterprise Manager. (The backup will run when you click the OK button.)

One Time

Schedules the task to run once at the date and the time specified in the On Date and At Time boxes. Note that the time is based on the time at the server, not at the client.

Recurring

Schedules the task to run on a recurring schedule. To set the recurring schedule, choose Change and select the appropriate schedule.

Change

Displays the Task Schedule dialog box which is used to set the recurring schedule.

Options

Displays the TSQL Task Options dialog box, which is used to set task options for sending email notification about the task; to write an entry to the Windows NT application event log, and to specify the number of retry attempts if the task fails.


TSQL Task Options Dialog Box

Use this dialog box to specify where to route notification of task completion, and to set the number of retries for a given task.

Task Name

Displays the name of the task you are setting options for.

Notifications

Specifies whether to notify an operator by email of the task run status and whether to record information about the task run in the Windows NT application event log.

Email Operator

Specifies whether to notify a user on task success and/or failure. In the Email Operator box, select the name of the operator to notify, and then select the appropriate check boxes. You can specify a new operator by selecting <New Operator> in the Operator box. You can remove an assigned operator by selecting <No Operator>.

Write to Windows NT Application Event Log

Specifies whether to record task success and/or failure information in the Windows NT application event log. Note that all event executions are logged in the SQL Executive Task History table.

On Success

Performs the specified action (send email to an operator, write to the Windows NT application event log) when the task successfully runs.

On Failure

Performs the specified action (send email to an operator, write to the Windows NT application event log) when the task fails.

Retries

Sets the number of retry attempts for a failed task. Note that you cannot set retry attempts for Transact-SQL tasks.

Retry Attempts

Specifies the number of retry attempts. Can be from 0 to 256.

Retry Delay (Minutes)

Specifies the amount of time, in minutes, to wait before retrying the task.

Replication

Specifies a remote target server and database for LogReader and Distribution tasks only.

Server

Is the remote target server.

Database

Is the remote target database.


New Subscriber Dialog Box

Use this dialog box to register a subscriber with SQL Enterprise Manager, or to add an ODBC subscriber. For more information, choose one of the following:

 Name

 New Server

 ODBC Subscriber


Name

Specifies the name of the new subscriber to add. The new subscriber name will not appear in this box until you click the New Server or ODBC Subscriber button and complete the dialog box that appears.


New Server

Specifies a Microsoft SQL Server to register with SQL Enterprise Manager. Click this button to open the Register Server dialog box. The registered server can then become a new subscriber.


ODBC Subscriber

Specifies an ODBC subscriber to add. Click this button to open the New ODBC Subscriber dialog box.


Install Replication - Publishing Dialog Box

Use this dialog box to install a distribution database for server replication.

Local - Install New Local Distribution Database

Installs the distribution database on the local server.

Database Name

Specifies the name of the distribution database on the local (publication) server. The default is distribution.

Data Device

Specifies the device on which to store the distribution database. You can select an existing device, or <New> to create a device. If you choose <New>, you will be prompted to specify a drive and a device size.

Size (MB)

Specifies the amount of space to allocate for the distribution database on the specified device. The recommended minimum size is 30 megabytes (MB).

Log Device (MB)

Specifies the device on which to store the transaction log for the distribution database. You can select an existing device, or <New> to create a device. If you choose <New>, you will be prompted to specify a drive and a device size.

Size (MB)

Specifies the amount of space to allocate for the transaction log on the specified device. The recommended minimum size is 15 MB.

Remote - Use Existing Remote Distribution Server

Installs the distribution database on the selected remote server. Note that the remote distribution server must already be created.

OK

Installs the distribution database as defined. Before you choose OK, verify that distribution database options are set appropriately. Once a distribution database is selected, a significant reconfiguration is required to change to a different distribution database.


Replication - Publishing Dialog Box

Use this dialog box to specify which servers are authorized to subscribe to the specified server's publications and the replication schedule, and whether the local server will be used by remote servers as a distribution server.

Enable Publishing to these Servers

Specifies which servers can subscribe to the specified server's publications. To allow a server access, select Enable. Only servers registered in SQL Enterprise Manager are listed. To register a server, choose New Server.

New Subscriber

Displays the New Subscriber dialog box, which is used to register a subscriber with SQL Enterprise Manager or to add an ODBC subscriber.

Distribution Options

Displays the Distribution Options dialog box, which is used to set the distribution schedule and the transaction batch size for the selected server, and the number of hours after distribution that transactions are to be retained in the distribution database.

Publishing Databases

Specifies which databases will publish data. To allow a database to be published, select Enable.

Distribution Configuration

Displays the name of the distribution database and the working directory of the database.  The working directory is used to store the schema scripts and data synchronization files. Providing a path that directly maps to the root of the appropriate hard drive results in significant performance enhancements during initial synchronization. Provide a path to \\servername\<drive letter>$. If the drive letter cannot be determined, then synchronization will use the network share for writing files on the local distribution server. The default is \\servername\C$\SQL60\REPLDATA, which maps to C:\SQL60\REPLDATA on the local computer. To change the working directory, type a new UNC path in the Working Directory box.

Distribution Publishers

Displays the Replication - Distribution dialog box, which is used to specify which remote servers can use the local server as their distribution server.


Replication - Distribution Dialog Box

Use this dialog box to specify whether the local server is allowed to act as a remote distribution server for other publication servers.

Distribution Publishers

Specifies which servers are permitted to use the local server as their remote distribution server. To allow a server to use the local server as a distribution server, select Enable.

Only servers registered with SQL Enterprise Manager are listed. To register a server, choose New Server.

New Server

Displays the Register Server dialog box, which is used to register a server in SQL Enterprise Manager.


Distribution Options Dialog Box

Use this dialog box to change a subscribing server's distribution schedule information.

Commit Every <x> Transactions to Subscriber

Sends multiple transactions to a subscriber as one unit. Type the maximum number of transactions to send to the subscriber as one number. If the number of transactions marked for replication exceeds this number, several units will be sent. Adjusting this number can improve your distribution throughput.

Distribution Schedule

Specifies the distribution schedule, which is the interval at which transactions will be replicated to the subscriber.

Continuous

Specifies that transactions are distributed continuously.

Scheduled

Specifies that transactions are distributed based on a schedule. To change the schedule, choose Change.

Change

Displays the Task Schedule dialog box, which is used to change the distribution schedule for a subscriber.

Retention Period after Distribution (hours)

Specifies how long transaction information is retained in the distribution database after the information is applied to the subscriber.

ODBC Options

Specifies login information for ODBC subscribers, if required by the specified ODBC driver.

SQL Server

Specifies whether the subscriber is a Microsoft SQL Server. ODBC login information is not required.

Other

Specifies whether the subscriber is not a SQL Server ODBC subscriber. Login information may be required.

Login Name

Specifies the login name used to connect to the specified ODBC subscriber. Enter this information only if required by the ODBC subscriber. The username and the password are used by the distribution server to connect to the ODBC subscriber. In many cases, entering a username or password is not necessary, since they are often entered and stored (depending on the ODBC driver) during the process of registering a system data source name (DSN) with the ODBC administration application in the Control Panel.

Password

Specifies the password for the login name. Enter this information only if required by the ODBC subscriber. The username and the password are used by the distribution server to connect to the ODBC subscriber. In many cases, entering a username or password is not necessary, since they are often entered and stored (depending on the ODBC driver) during the process of registering a system DSN with the ODBC administration application in the Control Panel.

Description

Is a read-only display of the description of the selected ODBC subscriber. The description matches the description entered in the New ODBC Subscriber dialog box.


New ODBC Subscriber Dialog Box

Use this dialog box to choose an ODBC data source name (DSN) to become a subscriber of the specified publisher. For more information, choose one of the following:

 Distributor's DSNs

 Description

 User Name

 Password

See Also

Using an ODBC Subscriber

About Microsoft Access Subscribers

About Oracle Subscribers

About Other ODBC Subscribers

Replication Restrictions for ODBC Subscribers

Datatype Mappings for ODBC Subscribers


Distributor's DSNs

Selects a DSN from the list to become a subscriber. The list includes DSNs known to the distribution server. Use the ODBC administration application in the distributor's Control Panel to add a DSN to the list.


Description

Provides a description of the selected DSN.


User Name

Specifies the login name used to connect to the selected ODBC subscriber. Enter this information only if required by the ODBC subscriber. The username and the password are used by the distribution server to connect to the ODBC subscriber. In many cases, entering a username or password it is not necessary, since they are often entered and stored (depending on the ODBC driver) during the process of registering a system DSN with the ODBC administration application in the Control Panel.


Password

Specifies the password associated with the selected user name. Enter this information only if required by the ODBC subscriber. The username and the password are used by the distribution server to connect to the ODBC subscriber. In many cases, entering a username or password is not necessary, since they are often entered and stored (depending on the ODBC driver) during the process of registering a system DSN with the ODBC administration application in the Control Panel.


Replication - Subscribing Dialog Box

Use this dialog box to specify which publication servers are permitted to replicate data to the local server, and which databases on the local server are permitted to subscribe to publications.

Enable Subscribing from these Servers

Specifies which servers are permitted to publish data to the local server. To allow the server to publish data, select Enable. Only servers that are registered in SQL Enterprise Manager or included in sysservers are listed. If a server is not listed, choose New Server to add the server.

New Server

Displays the Register Server dialog box, which is used to register a server in SQL Enterprise Manager.

Subscribing Databases

Specifies which databases on the local server are permitted to subscribe to publications. To allow the database to subscribe to publications, select Enable.


Manage Publications Dialog Box

Use this dialog box to set up or modify publications for a server.

Publications

Displays a list of current publications and articles for databases on the server. Also displays current subscribers. To expand the list, click the plus sign (+) next to the database name. A publication with a key on its icon is restricted.

New

Displays the Edit Publications dialog box, which is used to create a new publication or article.

Change

Displays the Edit Publications dialog box, which is used to modify the existing publication or article selected in the Publications box.

You cannot modify a publication or article that is subscribed to. Before modifying a publication or an article, you must drop all subscriptions. After modifying a publication or article, re-subscribe appropriate subscribers.

Remove

Displays the Remove Article or Remove Publication dialog box, which is used to remove the publication or article selected in the Publications box.

More Info

Displays the Publication Info dialog box, which is used to provide more information about the publication, including the title, description, synchronization schedule, and replication frequency, and the articles.


Article/Publication Subscribers Dialog Box

Use this dialog box to subscribe a server to an article or publication.

Subscription Servers

Displays the servers available for subscribing, each server's subscription status, the database the article will be placed in, and the type of synchronization event.

Subscribe

Subscribes the selected server to the article or publication.

Unsubscribe

Removes the selected server's subscription from the article or publication.


Edit Publications Dialog Box

Use this dialog box to create or modify a publication for replication.

Publication Title

Specifies a name for the publication. The name must be unique for the database it's to be placed in.

Description

Describes the publication. The description can be up to 255 characters.

Replication Frequency

Selects the method to use to replicate data.

Transaction Based

Reads and applies each transaction marked for replication from the transaction log of the source database to the destination databases.

Scheduled Table Refresh

Specifies that at scheduled intervals all articles in the publication will be refreshed. Each time a refresh occurs, all table data (and optionally, table schemas) will be applied to all destination tables. No data changes are sent between scheduled refresh intervals. Transaction-based replication does not occur.

Articles tab

Defines which database tables will be published as articles in the publication. To publish a table, select the table in the Database Tables box and choose Add, or double-click the table name to move the table name to the Articles in Publication box.

Edit

Displays the Manage Article dialog box, which is used to customize data to be included as part of an article. For each article, you can set the article name, the article description, and the destination name; exclude rows and columns from published articles (horizontal and vertical partitioning); customize the synchronization schema script; and customize the data replication mechanism.

Synchronization Tab

Defines the initial synchronization method and the schedule for the selected publication.

Initial synchronization determines the method by which output data files are created for automatically or manually synchronizing the destination database. The method can be bcp, native format (the default), or bcp, character format.

The schedule determines how frequently new subscriptions will be automatically synchronized. Since automatically synchronizing databases or tables requires an appreciable amount of system overhead, scheduling automatic synchronization allows you to set an interval between the time a server subscribes to a publication and the time a server is synchronized to that publication. Scheduling allows synchronization to occur during low activity periods on the publication and subscription servers.

Security Tab

Marks the publication as Unrestricted or Restricted. Subscribers can see and subscribe to publications marked Unrestricted. Publications marked Restricted are visible only to subscribers that have been marked for access.

Add

Creates the publication as defined.

Modify

Modifies the publication as specified.

Subscribers

Adds or removes subscribers of the publication. The Subscribers button is available only when editing an existing publication.


Synchronization Tab

Use this tab to specify the synchronization method and the schedule for replication.

Method

Indicates the method by which the destination database will be synchronized.

Bulk Data Copy - Native Format

Specifies to use the native format of bcp to transfer the data for synchronization. The bcp native format is the default synchronization method for data replication.

Bulk Data Copy - Character Format

Specifies to use the character format of bcp to transfer the data for synchronization. Use this option when replicating to ODBC subscribers.

Schedule

Specifies how frequently new subscriptions will be synchronized. Since synchronizing individual tables requires a fairly high amount of system overhead, scheduling synchronization allows an interval between the time a server subscribes to a publication and the time a server is synchronized to that publication. Scheduling allows synchronization to occur during low activity periods on the publication and subscription servers. To change the schedule, choose Change.

Change

Displays the Task Schedule dialog box, which is used to change the schedule for automatic synchronization.


Security Tab

Use this tab to restrict replication to selected servers.

Unrestricted

Specifies that the publication can be seen and subscribed to by any subscription server.

Restricted To

Specifies that the publication is visible only to subscription servers that have been marked for access. To mark a server for access, select the Allow Access check box next to the server name.


Manage Article Dialog Box

Use this dialog box to configure information for an article. For each article, you can set the article name, description, and destination table name; exclude rows and columns from published articles (horizontal and vertical partitioning); customize the synchronization schema script; and customize the data replication mechanism.

Name

Displays the default name of the article. To change the default name, type a new name. Names must be unique to each publication.

Description

Describes the article. The description can be up to 255 characters.

Destination Table

Specifies the table to which data should be replicated. The destination table name defaults to the publication table name.

Filters Tab

Filters the data that is replicated.

Replicated Columns

Partitions the article vertically (only selected columns are replicated). Select the Replicate check box for each column that will be replicated. Clear the Replicate check box for each column that will not be replicated. Primary key columns (indicated in the Key Column) must be replicated.

Restriction Clause

Partitions the article horizontally (only selected rows are replicated). Type a valid WHERE clause in the Restricted Clause box. For example, if the table you are replicating data from contains information for all states, but you want to publish California information only, type state = 'CA'.

Scripts Tab

Specifies the data replication mechanism and the synchronization schema script used to insert data into the destination table.

Subscribers

Displays the Article Subscribers dialog box, which sets up subscribers for this article. The Subscribers button is available only when editing an existing article.

Advanced

Displays the Manage Article - Advanced dialog box, which is used to specify custom filtering.


Scripts Tab

Use this tab to customize the data replication mechanism or the synchronization schema script.

Data Replication Mechanism

Determines the action SQL Server takes when the log reader process encounters INSERT, UPDATE, or DELETE transactions that are marked for replication in the transaction log of the publication database.

To use the default, do not change selected options for the commands. When the log reader process encounters an INSERT, UPDATE, or DELETE transaction that is marked for replication in the transaction log of the publication database, the SQL statement will be reconstructed and applied to the destination database.

To specify custom processing for one or more of the command types, select the Custom option and enter one of the two possible custom commands in the Custom box. If you enter NONE, transactions of the selected command type will not be replicated. If you enter CALL stored_procedure_name, when the transaction log reader encounters the selected command in a transaction marked for replication, it will pass column parameters from that transaction to the referenced stored procedure. The referenced stored procedure must exist in the destination database of each subscriber.

Initial Table Synchronization Script

Specifies the initial table synchronization schema script. When editing a new article, the Creation Script box is blank. When editing an existing article, the box contains the full path to the schema script in the distribution working directory. Schema scripts are automatically generated when a publication is created or modified.

Edit

Displays the Manage Article - Creation Script dialog box, which is used to view and/or edit an existing schema creation script.

Generate

Displays the Auto - Generate Sync Scripts dialog box, which is used to generate a schema script immediately.


Manage Article - Creation Script Dialog Box

Use this dialog box to edit the creation script used to synchronize a table for use with replication.

Creation Script

Specifies the path and the contents of the creation script. To change the script, edit the appropriate information in the box.


Auto - Generate Sync Scripts Dialog Box

Use this dialog box to generate a schema script immediately.

If Existing Table Detected in Subscriber

Specifies how to handle an existing table of the same name in a subscribing database before applying synchronization.

Do Nothing

Does not drop, truncate, or delete rows from the destination table before synchronization is applied.

DROP Table First

Drops the existing table.

Truncate Table Only

Deletes the rows of the table, but leaves the table structure in place.

DELETE DATA Using Restriction Clause

Removes only the rows that match the restriction clause defined in the Filters Tab. For example, if the replicated data is restricted to rows containing the state "CA", then data in the existing table that contain rows where state is "CA" will be deleted.

Transfer Clustered Index

Creates a clustered index in the destination table identical to that for the published table.

Transfer non-Clustered Index

Creates nonclustered indexes in the destination table identical to those for the published table.

Convert UDDT to base datatype

Converts user-defined datatypes (UDDT) from the published table to their base system datatypes in the destination table.

Include DRI-PK

Copies the declared primary key (PK) to the destination table.

Auto-Generate

Creates the initial table synchronization schema script.


Manage ArticleAdvanced Dialog Box

Use this dialog box to specify a filter stored procedure and a synchronization object.

Filter Stored Procedure

Specifies a stored procedure that was previously created in the publication database. Point to a stored procedure to use as a user-defined filter.

Synchronization Object

Specifies a database object to be used as the source of the schema and the data for the initial synchronization of the destination table. By default, the synchronization object is the article's base table. If the article has been horizontally and/or vertically partitioned, SQL Server automatically creates a view of the partitioned table, which becomes the synchronization object. Generally, the synchronization object is specified automatically and does not need to be changed from the default.


Subscription Options Dialog Box

Use this dialog box to set up subscription option information.

Destination Database

Select the subscription server database in which to place replicated tables. The database must already exist; SQL Enterprise Manager will not create it. The default assigns the publication database name to the destination database.

Sync Method

Determines whether the table schema and the data will be initially synchronized automatically (by SQL Enterprise Manager) or manually (by a user).

Data Synchronization Automatically Applied

Specifies that SQL Server replication automatically synchronizes following the distribution schedule set on the publication server for the selected subscriber.

Data Synchronization Manually Applied by Operator

Specifies that a user manually synchronize outside of the administrative interface for replication. The user must obtain schema and data files from the distribution server, manually synchronize the table, and then inform SQL Server that the synchronization is complete.

If you choose manual synchronization for a subscription, you must manually synchronize the published tables on the publication server with the destination tables in the destination database before replication can begin for that subscription. The synchronization file will be automatically created as part of the synchronization event, but the file will not be distributed to the subscriber.

Selecting manual synchronization can affect replicated data being sent to a destination database. When the synchronization becomes the next task in the distribution database queue, replication to the entire subscription database is paused until the manual synchronization is carried out and SQL Server is informed that the synchronization is complete.

No Data Synchronization

Specifies that SQL Server will immediately begin sending published data modifications to the destination database. SQL Server assumes that the publication and destination tables are already synchronized. The No Data Synchronization option is intended for advanced users who will be implementing a custom replication.

More Info

Displays the Publication Info dialog box, which is used to provide more information about the publication, including the title, description, synchronization schedule, and replication frequency, and the articles.

The More information button appears only if you have accessed the Subscription Options dialog box from the Manage Subscriptions dialog box.


Remove Publication Dialog Box

Use this dialog box to confirm a publication removal.

Yes

Removes the publication.

No

Closes the dialog box without removing the publication.


Remove Article Dialog Box

Use this dialog box to confirm the removal of a published article. This box lists subscribers to the article.

Yes

Removes the article from a publication.

No

Closes the dialog box without removing the article.


Unpublish Database Dialog Box

Use this dialog box to confirm disabling publishing for a selected database.

Publications

Lists the publications and articles that are being published for the selected database.

Yes

Disables publishing for that database.

No

Closes the dialog box without disabling publishing for that database.


Publication Info Dialog Box

Use this dialog box to view information about a selected publication. You cannot change information in this dialog box.

Publication Title

Specifies the title of the publication.

Description

Describes the publication, as specified during publication creation.

Sync Schedule

Specifies the schedule of synchronization events.

Replication Frequency

Specifies the replication frequency, either Continuous or Periodic Snapshot.

Sync Method

Specifies the synchronization method to be used to initially set up the subscribing server with data from the publishing server.

Articles

Displays a list of articles within the publication.


Manage Subscriptions Dialog Box

Use this dialog box to subscribe to publications, to unsubscribe from publications, and to indicate that a manual synchronization event has occurred.

Publications

Displays a list of publishing servers, and publications and articles available for publication. To expand the list, click the plus sign (+) next to a list item.

Subscribe

Displays the Subscription Options dialog box, which is used to subscribe the server to the selected publication or article.

Unsubscribe

Removes a subscription to the selected article or publication. Displays the Un-Subscribe Publication Article dialog box, which asks you to confirm the removal.

Sync Completed

Indicates that you have manually synchronized the selected article or publication. Replication can then resume.

This button is only enabled when the distribution process is waiting for the manual synchronization job within the distribution database that corresponds to the selected publication or article.

More Info

Provides more information about a selected publication or article.


Un-Subscribe Publication Article Dialog Box

Use this dialog box to confirm that you want to unsubscribe from an article.

Yes

Unsubscribes the server from the article.

No

Closes the dialog box without unsubscribing the server from the article.


Remove Subscribers Dialog Box

Use this dialog box to confirm the removal of subscribers and subscriptions from a publisher before removing the publication server.

Yes

Removes subscribers and/or subscriptions.

No

Closes the dialog box without removing subscribers and/or subscriptions.


Replication Overview

Replication is the act of duplicating table schema and data from a source database to a destination database. It allows you to automatically distribute copies of transactional data from a single source server to one or more destination servers at one or more remote locations. SQL Server 6.x replication is transaction-log based: transactions marked for replication are read from the transaction log of the source database and applied to the destination databases.

Replication uses terminology based on a publishing/subscribing metaphor. It employs servers in three different roles: publication, distribution, and subscription. Replication uses a dedicated SQL Server database as a reliable queue for replicated data. Replication offers great flexibility in selecting data to be replicated to each destination database. And, it enhances security by limiting which users can set up and administer replication, and which destination servers can view and receive tables available for replication.

Replicated data moves in one directionfrom the publication server to the subscription server(s). The log reader process moves transactions marked for replication from the publication server transaction log into the distribution database, where the transactions wait for distribution to subscription servers. The synchronization process prepares initial synchronization files containing published table schema and data, stores the files in the SQL Server working directory on the distribution server, and records synchronization jobs in the distribution database. Synchronization affects new subscribers only.

The distribution database is a store-and-forward database that holds transactions waiting to be distributed to subscription servers. The log reader process sends publication server transactions to the distribution database, where the transactions are held until the distribution process moves them to the subscription servers. The distribution process moves the transactions and initial synchronization jobs held in the distribution database tables to the subscription servers.

For a detailed overview of the replication process, see the Microsoft SQL Server Administrator's Companion.

See Also

Text and Image Datatype Replication

Replication to ODBC Subscribers


Setting up DBOs to Manage Publications and Subscriptions

When you set up publication, distribution, and subscription servers for replication, permissions are automatically set that allow the SAs to manage publications and subscriptions. However, before the DBOs of the publication and subscription databases can manage publications or subscriptions, permissions must be set.

Permissions are set by creating necessary SQL Server login IDs, database users, and remote login mappings. Permissions are set separately for the DBOs of publication and subscription databases. For the DBO of a publication database, permissions are set up one of two ways depending on whether that publication server uses a remote distribution server or acts as its own combined publisher/distributor. For more information, select one of the following:

A DBO on a Combined Publisher/Distributor

A DBO on a Publication Server that Uses a Remote Distributor

A DBO on a Subscription Server


A DBO on a Combined Publisher/Distributor

When a publication server is configured as a combined publisher/distributor, to allow the DBO of a publication database to manage publications, you must add a database user to the publisher's msdb and distribution databases specifying the login ID of the user who is the DBO in the publication database.

If several users in the publication database are aliased to the DBO, you must repeat the process for each aliased user. All users can be aliased to one user in the msdb database, and to one user in the distribution database.


A DBO on a Publication Server that Uses a Remote Distributor

When a publication server is configured to use a remote distribution server, to allow the DBO of a publication database to manage publications, you must:

1. On the distribution server, add a login ID for the user who is the publication database DBO. If that user already has a login ID on the remote distribution server, skip this step.

2. On the distribution server, to the msdb database, add a database user for the login ID that was added in step 1.

3. On the distribution server, to the distribution database, add a database user for the login ID that was added in step 1.

4. On the distribution server, map the login ID from the publication server to the login ID on the remote distributor using sp_addremotelogin.

If several users in the publication database are aliased to DBO, repeat these steps for each aliased user. You can alias all the users to one user in the msdb database, and to one user in the distribution database.


A DBO on a Subscription Server

To allow the DBO of a destination database to manage subscriptions, you must use sp_addremotelogin to map the login ID of the DBO on the subscriber to the repl_subscriber login ID on the publisher. To use integrated security, the trusted remote login option must be set using sp_remoteoption.

Repeat this step for each publication server that the destination database will subscribe from. If several users in that destination database are aliased to DBO, repeat the step for each aliased user.


Setting Up the Replication Distribution Database

If a server will participate in replication as a publisher, before any other replication options are set, you must define the distribution database information. You can set up replication distribution so that the publication server will act as its own distribution server or will use a remote distribution server. If the publication server will use a remote distribution server, that server must be set up first.

You must make certain the distribution database options are set appropriately. Once a distribution database is selected, significant reconfiguration is required to change to a different distribution database.

To set up the replication distribution database

1. In the Server Manager window, select the server to set up the distribution database for.

2. From the Server menu, choose Replication Configuration Install Publishing.

The Install Replication - Publishing dialog box appears.

3. To configure the server to be its own distribution server or to act as a remote distribution server for other publication servers, select Local, type the name of the distribution database, and then select the devices to place the database and its log on.

Replicating published data requires extensive use of the distribution database. A minimum of 30 megabytes (MB) for the database and 15 MB for the log is recommended for initial implementation.

4. To configure the server to use a remote distribution server, select Remote and then select the remote server from the server list. Selecting the remote option requires that the remote distribution server already has a distribution database installed.

5. Choose OK.

After the distribution database is installed, you are prompted to add publishing databases and subscribers. If you choose Yes, the Replication - Publishing dialog box appears to enable publishing. If you choose No, you must set up publishing to enable replication. You can set up publishing at any time by selecting Replication Configuration Publishing from the Server menu.

See Also

Replication Overview


Setting Replication Publishing Information

Replication publishing information determines the servers that are authorized to subscribe to the local server's publications, the replication schedule, databases that are authorized to publish data, and the directory used as the distribution working directory. You can also configure the local server to act as a remote distribution server for other publication servers.

To set replication publishing information

1.	In the Server Manager window, select the server to set publishing information for.

2.	From the Server menu, choose Replication Configuration Publishing.

The Replication Publishing dialog box appears.

3.	Under Enable Publishing to these Servers, select Enable to specify the servers to be allowed to subscribe to the data published by the local server.

Only servers that are registered in SQL Enterprise Manager or included in sysservers are listed. To register a server or an ODBC subscriber, choose New Server and complete the dialog box.

4.	To change information about a subscribing server's distribution schedule, choose Distribution Options. Define the transaction batch size that is sent to the subscriber, and change the distribution schedule for the subscribing server.

5.	Under Publishing Databases, select Enable to set the databases that will publish data.

6.	To change the distribution working directory, type a new UNC path in the Working Directory box. The directory is used to store schema scripts and data synchronization files. The default is \\servername\C$\SQL60\REPLDATA, where servername is the name of the distribution server.

7.	To enable the local server to be the distribution server, select Distribution Publishers and in the Replication - Distribution dialog box, select servers that can publish to the local server for distribution and choose OK.

8.	In the Replication - Publishing dialog box, choose OK.

See Also

Using an ODBC Subscriber

About Microsoft Access Subscribers

About Oracle Subscribers

About Other ODBC Subscribers

Replication Restrictions for ODBC Subscribers

Datatype Mappings for ODBC Subscribers


Using an ODBC Subscriber

To publish to a subscriber other than a SQL Server ODBC subscriber

1.	From the Control Panel, run the ODBC administration application and add the subscribers data source name (DSN) as a system DSN.

For more information about adding system DSNs, see the online Help for the ODBC administration application.

2.	Using SQL Enterprise Manager, configure the new system DSN as a subscribing server to the selected publishing server by clicking the New Subscriber button from the Replication - Publishing dialog box, then clicking the ODBC Subscriber button from the New Subscriber dialog box. Complete the New ODBC Subscriber dialog box, which lists only system DSNs.

3.	While using SQL Enterprise Manager to create or edit a publication, perform a push subscription to the selected ODBC subscriber. Click the Subscription button from the Edit Publications or Edit Article dialog boxes, and complete the dialog box that appears.

ODBC subscriptions must be set up by administering the publishing server (using the push subscription model). You cannot set up ODBC subscriptions by administering a subscriber (the pull subscription model). For subscribers other than SQL Server ODBC subscribers, the subscribing database has no administrative capabilities regarding the replication being performed.

Also, while administering a publication that will be replicated to ODBC subscribers, be sure to select the Bulk Data Copy - Character Format option in the Synchronization tab of the Edit Publications dialog box.

For more information about push subscribing, see the Microsoft SQL Server Administrators Companion.

Note that replication creates tables on the ODBC subscribers using quoted identifiers (for example, "authors" rather than authors).

See Also

About Microsoft Access Subscribers

About Oracle Subscribers

About Other ODBC Subscribers

Replication Restrictions for ODBC Subscribers

Datatype Mappings for ODBC Subscribers


About Microsoft Access Subscribers

An ODBC driver that supports Microsoft Access subscriptions to SQL Server replication is included with Microsoft SQL Server 6.5. This driver is only supported for Intel-based computers. To install the driver, run SETUP.EXE from the \SQL60\ODBC directory on the SQL Server CD.

When using the ODBC administration application in the Control Panel to register a Microsoft Access data source name (DSN), you are required to enter a username and a password. The username and the password are stored by the system, and are used by the distribution server to connect to the ODBC subscriber.

Note that when registering a Microsoft Access DSN on a remote server, supply a UNC path (not a redirected drive letter).


About Oracle Subscribers

An ODBC driver that supports Oracle subscriptions to SQL Server replication is included with Microsoft SQL Server 6.5. This driver is only supported for Intel-based computers. To install the driver, run SETUP.EXE from the \SQL60\ODBC directory on the SQL Server CD.

Note that to replicate to ODBC subscribers, you must also obtain the Oracle SQL*Net driver from your vendor.

When using the ODBC administration application in the Control Panel to register an Oracle data source name (DSN), you are required to enter a username. The username is stored by the system, and is used by the distribution server to connect to the ODBC subscriber.

The ODBC driver for Oracle also creates a password key in the Registry. You can edit the Registry to provide a value for the password.

If the Registry password key is edited, the Oracle ODBC driver will connect to Oracle without requesting a password.

If the Registry key is not edited, while setting up subscriptions for Oracle you must enter a username and a password for the ODBC data source when specifying the DSN in the New ODBC Subscriber dialog box of SQL Enterprise Manager.

The Registry key for the ODBC driver password is:

HKEY_CURRENT_USER\ODBC\ODBC.INI\ORACLE\PW:REG_SZ:password

Note that the SQL Server 6.5 ODBC driver for Oracle is not double-byte character set (DBCS)-enabled.

Replication Restrictions for Oracle

Following are known restrictions when replicating to an Oracle ODBC subscriber.

The year datatype is a small datetime (the range is -4713 <> 47113). If you are replicating to Oracle, be sure that SQL Server datetime entries in a replicated column are within this range.

Only one text or image column can exist per replicated table.

The datetime datatype is mapped to CHAR 4.

The SQL Server ranges for float and real datatypes are different than the ranges for Oracle.

For restrictions applying to all ODBC subscribers, see Replication Restrictions for ODBC Subscribers.


About Other ODBC Subscribers

Drivers for other ODBC subscriber types must conform to the following Microsoft SQL Server replication requirements:

The ODBC driver must be ODBC Level 1 compliant.

The ODBC driver must be 32-bit, thread-safe.

The ODBC driver cannot be read-only.

The ODBC driver must be transaction capable.

The ODBC driver must support DDL.


Replication Restrictions for ODBC Subscribers

The following known restrictions apply to replication to all ODBC subscriber types.

Batched statements to ODBC subcribers are not supported. (The distribution task -c option is ignored.)

The ODBC DSN name must conform to SQL Server naming conventions (because it is stored in the sysservers table).

If the ODBC DSN is not a SQL Server DSN, the publication option to truncate an option before synchronization is not supported and will cause the synchronization to fail.

The target source's quoted identifier character (as reported by the ODBC driver) is used.

For restrictions specific to Oracle subscribers, see About Oracle Subscribers.


Datatype Mappings for ODBC Subscribers

When replicating to ODBC subscribers, the distribution task maps SQL Server datatypes to the closest datatype on the target database. The following table lists the datatype mappings for replication to Microsoft Access subscribers.

Microsoft  SQL Server  Datatype	Maps to  Microsoft ACCESS  Datatype
bit	BIT 0
tinyint	BYTE 0
image	LONGBINARY 0
varbinary	VARBINARY 4
binary	BINARY 4
timestamp	BINARY 4
text	LONGTEXT 0
char	CHAR 4
numeric	CHAR 4
decimal	CHAR 4
money	CHAR 4
smallmoney	DOUBLE 0
int	LONG 0
smallint	SHORT 0
float	DOUBLE 0
real	SINGLE 0
datetime	CHAR 4
smalldatetime	DATETIME 0
varchar	TEXT 4
The following table lists the datatype mappings for replication to Oracle subscribers.

Microsoft  SQL Server  Datatype	Maps to  Oracle  Datatype
bit	NUMBER 3
tinyint	NUMBER 3
image	LONG RAW 0
varbinary	RAW 4
binary	RAW 4
timestamp	RAW 4
text	LONG 0
char	CHAR 4
numeric	NUMBER 3
decimal	NUMBER 3
money	NUMBER 3
smallmoney	NUMBER 3
int	NUMBER 3
smallint	NUMBER 3
float	FLOAT 0
real	FLOAT 0
datetime	CHAR 4
smalldatetime	DATE 0
varchar	CHAR 4


Replication Topology Window

Use this window to view the replication relationships of a server, and to perform replication tasks for the servers in the replication relationship. Each subscriber type is indicated by its icon (SQL Server, Microsoft Access, ODBC, and so on).

To manage replication for a publisher or a subscriber, click the right mouse button on the publisher or the subscriber's icon, and select a command from the shortcut menu.

The following toolbar icons are available:

 New Subscriber

 Remove Subscriber

 Refresh

 Zoom In

 Zoom Out

See Also

Viewing Replication Topology


Zoom In

Enlarges the relationship diagram. Displays less information, but shows each element at a larger size.


Zoom Out

Shrinks the relationship diagram. Displays more information, but shows each element at a smaller size.


Refresh

Updates the view with current information.


New Subscriber

Opens the New Subscriber dialog box, from which you can add a SQL Server subscriber or another type of ODBC subscriber. (You can also add a server by selecting one from the Server Manager window and dragging it into the Replication Topology window.)


Remove Subscriber

Removes the subscriber configuration of the currently selected subscriber.


Setting Server Subscribing Information

The server subscribing information determines publication servers to be permitted to replicate to the local server and destination databases on the local server to be permitted to subscribe to publications.

To set server subscribing information

1. In the Server Manager window, select the server to set subscribing information for.

2. From the Server menu, choose Replication Configuration Subscribing.

The Replication - Subscribing dialog box appears.

3. Under Enable Subscribing from these Servers, select Enable for each server that will be permitted to publish data to the local server. Only servers that are registered in SQL Enterprise Manager or included in sysservers appear in the list. If a server is not listed, choose New Server to add that server.

4. Under Subscribing Databases, select Enable for each database to be allowed to subscribe to publications.

If you have not yet created the destination databases on the local server, you can enable subscribing at the server level and go back and edit the appropriate information when the databases are created.

5. Choose OK.

See Also

Replication Overview


Viewing Replication Topology

The Replication Topology window provides a convenient way to view the replication relationships of a server, to add subscription servers, and to set other replication information.

To view replication topology

1. In the Server Manager window, select the server to view replication topology for.

2. From the toolbar, choose the Replication Topology icon.

The Replication Topology window appears, with the selected server in the center. Other servers participating in a replication relationship with the selected server are also displayed. Each subscriber type is indicated by its icon (SQL Server, Microsoft Access, ODBC, and so on).

3. To enlarge the view, from the toolbar click the Zoom In icon. To shrink the view, from the toolbar click the Zoom Out icon.

You can rearrange the servers displayed by dragging a server to the desired location.

4. To add a subscriber, from the toolbar click the New Subscriber button and complete the new Subscriber dialog box.

5. To remove a subscriber, select its icon and from the toolbar click the Remove Subscriber icon.

6. To manage replication for a server, click the server icon with the right mouse button and select the task to perform from the shortcut menu.

Choose Replication Configuration to install or select a distribution database, or to set server publication and subscription options.

Choose Publications to publish data. This command is unavailable if the selected server's publication options are not set up to allow publishing.

Choose Subscriptions to subscribe to published data. This command is unavailable if the selected server's subscription options are not set up to allow subscribing.


Creating Publications

A publication contains one or more articles, and is made available to subscribing servers. An article is created by publishing all data or a subset of data from a table. You can create one or more publications for a database, but each publication can contain data from only one database. Before you create publications, you must set the server publishing information.

To create a publication

1. In the Server Manager window, select the server to create a publication for.

2. From the Manage menu, choose Replication Publications.

The Manage Publications dialog box appears.

3. Under Publications, select a database to create a publication for and then choose New.

The Edit Publications dialog box appears.

4. In the Publication Title box, type a publication name that is unique for the database.

5. In the Description box, type a description of the publication.

6. Under Replication Frequency, select a replication frequency. Replication frequency determines the basic method to be used to replicate the selected publication.

Transaction Based specifies that each transaction marked for replication will be read from the transaction log of the source database and applied to the destination databases.

Scheduled Table Refresh specifies that all articles in the publication will be refreshed at scheduled intervals. Each time the articles are refreshed, all table data (and optionally, table schemas) will be applied to all destination tables. No data changes are sent between scheduled refresh intervals. Transaction-based replication does not occur.

7. Under Articles in Publication, select tables to include in the publication. Choose Add.

8. To customize an article, after you move a table to the Articles in Publication box, choose Edit. The Manage Article dialog box appears. You can specify the article name, description, and destination name. You can exclude rows and columns from published articles (horizontal and vertical partitioning), and customize the synchronization schema script and the data replication mechanism. When you are through modifying information, choose OK.

9. In the Edit Publications dialog box, to define the initial synchronization method and schedule for the selected publication, select the Synchronization tab.

Initial synchronization determines the method by which output data files are created for synchronizing the destination database. The selected method applies to automatic and manual synchronization, and can be bcp, native format (the default); or bcp, character format.

The schedule determines how frequently new subscriptions will be automatically synchronized. Since automatically synchronizating databases or tables requires a fairly high amount of system overhead, scheduling automatic synchronization allows you to set an interval from the time a server subscribes to a publication to when the server is synchronized to that publication. Scheduling allows synchronization to be scheduled for low activity periods on the publication and the subscription servers.

10. To mark the publication as restricted or unrestricted, select the Security tab. A publication marked unrestricted can be seen and subscribed to by any subscription server. A publication marked restricted is visible only to subscription servers that have been marked for access.

11. Choose Add.

The Manage Publications dialog box reappears. The new publication and its articles are listed under the published database. To create additional publications, repeat steps 3 through 10. When you are finished creating publications, choose Close.

See Also

Replication Overview


Setting Up a Push Subscription
Setting Up Push Subscriptions

A push subscription is performed from the publication server as part of creating or managing a publication. Subscriptions are created by pushing out a publication or an article to one or more subscription servers. A push subscription simplifies and centralizes subscription administration: the need to separately administer each subscriber is eliminated, and for each article many subscribers can be set up at once. However, a push subscription is only possible after a user with privileges on the subscription server has enabled the user's publication server (in the Replication - Subscribing dialog box).

Before subscribing to publications or articles, destination databases must be created on the subscription server and authorized to subscribe to a publication.

To set up a push subscription

1. In the Server Manager window, select the publishing server.

2. From the Manage menu, choose Replication Publications.

The Manage Publications dialog box appears.

3. Under Publications, select the publication to add subscribers to and then choose Change.

The Edit Publications dialog box appears.

4. To perform a push subscription for the selected publication, click the Subscribers button. To perform a push subscription for an article, choose an article from the Articles in Publication box, choose Edit, and then choose Subscribers.

The Publication Subscribers or Article Subscribers dialog box appears.

5. Under Subscription Servers, select a server and then choose Subscribe.

6. In the Subscription Options dialog box, select a destination database and the synchronization method, and choose OK.

See Also

Replication Overview


Setting Up Pull Subscriptions

A pull subscription is performed from the subscription server. One or more publications are pulled from publication servers to the subscription server. A pull subscription allows the subscription server SA or DBO to decide which publications will be received. With a pull subscription, the act of publishing is separate from the act of subscribing, and the user administering the subscription server need not be the same user administering the publication server.

Before subscribing to publications or articles, destination databases must be created on the subscription server and authorized to subscribe to publications.

To set up a pull subscription

1. In the Server Manager window, select the subscribing server.

2. From the Manage menu, choose Replication Subscriptions.

The Manage Subscriptions dialog box appears.

3. Under Publications, select a server (click the + next to the server name).

A list of the databases that contain publications appears.

4. Select a database (click the + next to the database name).

A list of the database's publications appears.

5. To subscribe to a publication, select the publication name and then choose Subscribe.

You can also subscribe to an article within a publication. Select the publication (click the + next to the publication name), and a list of articles appears. Select an article and then choose Subscribe.

6. In the Subscription Options dialog box, select a destination database and a synchronization method, and then choose OK.

See Also

Replication Overview


Manually Synchronizing a Database for Replication
Manually Synchronizing Database Tables for Replication

Before a subscription server can receive replicated transactions from an article or a publication, the publication and destination tables must contain the same schema and data. The process of accomplishing this is called synchronization. When you are setting up subscriptions, if you choose automatic synchronization, synchronization occurs automatically and without special action. If you choose manual synchronization, before replication can begin for the selected subscription, you must manually synchronize the destination tables on the subscription server with the published tables.

Note that when a destination database is waiting on a manual synchronization, it cannot receive updates to publications it is subscribed to (not just the publication that is waiting for a manual synchronization).

To manually synchronize the data, you can use the synchronization files SQL Server automatically creates. Apply the .SCH and .TMP to create the destination table and then populate the table with data. Inform SQL Server that the manual synchronization is complete.

To acknowledge completion of a manual synchronization

1. In the Server Manager window, select the subscribing server.

2. From the Manage menu, choose Replication Subscriptions.

The Manage Subscriptions dialog box appears.

3. Under Publications, select the publication server and then choose the appropriate database, publication, and/or article.

The Sync Completed button will be enabled when the distribution process encounters the manual synchronization job within the distribution database.

4. Choose Sync Completed.

A confirmation dialog box appears. Choose Yes. SQL Server has been informed that the subscription server's destination database is synchronized with the publication or article(s). The replication processes can now send replication transactions (changes to data in the synchronized articles) to the destination database.

See Also

Replication Overview


Stopping Replication from a Publisher

While managing a server's publications, you can remove a publication to cancel replication of that publication's tables; or you can remove an article from a publication to cancel replication of that article. However, you cannot remove a publication or an article that has an existing subscription. Before removing a publication or an article, you must drop all subscriptions. While managing publications on the publication server, you can un-subscribe one or more subscription servers from a publication or article.

To remove a publication

1. In the Server Manager window, select the publication server.

2. From the Manage menu, choose Replication Publications.

The Manage Publications dialog box appears.

3. Select a database to remove a publication from (click the + next to the database name).

A list of that database's publications appears.

4. Select a publication and then choose Remove.

See Also

Replication Overview


Stopping Replication from a Subscriber

You can un-subscribe from selected publications or articles. You can also remove the permission that allows a publication server to send publications to the subscription server.

To stop replication from a subscriber

1. In the Server Manager window, select the subscription server.

2. From the Manage menu, choose Replication Subscriptions.

The Manage Subscriptions dialog box appears.

3. Under Publications, select a server and then choose the database that contains the publication to cancel.

4. Select the publication or article name, and then choose Un-subscribe.

5. Choose Yes.

To remove permissions that allow a publication server to send publications to the subscriber

1. In the Server Manager window, select the subscription server.

2. From the Server menu, choose Replication Configuration Subscribing.

The Replication Subscribing dialog box appears.

3. To remove permissions for a server(s), under Enable Subscribing from these Servers, clear the Enable check box for that server(s).

Removing permissions does not stop transactions from being placed in the distribution database for the selected server. To stop transactions, first un-subscribe from the publication. This action should only be taken as an emergency measure to stop unwanted data from being replicated to a subscription server.

4. Choose OK.

See Also

Replication Overview


Advanced Restore Options Dialog Box

Use this dialog box to restore from selected dump device(s) to a selected database.

 Restore To

 Devices

 Backup Information

 Add Device

 Single Table

 Until Time

 Restore Now


Restore To

A list of the server's databases. Select the database you will restore to.

Note that if you are restoring the msdb database, you must first stop the SQL Executive service.


Devices

A list of this server's dump devices.


Backup Information

The Backup Information list displays dumps available from the selected dump device. Select a database, transaction log, or table dump for restoration.

Selecting a database dump restores the entire database. Selecting a transaction log dump restores the transaction log information, re-executes the changes it contains, and rolls back any transactions that were uncommitted when the transaction log was backed up. Selecting a table dump restores that table.


Add Device

To add a new dump device or specify a dump path to restore from, choose the Add Device button. Opens the New Dump Device dialog box.


Single Table

To restore only one table from a full database dump, select the database dump from the Backup Information list, select the Single Table option, and then select a table from the Single Table list.


Until Time

To restore up through a specific point in time, select a transaction log dump from the Backup Information list, then select the Until Time option and adjust the displayed date and time appropriately. Any transactions committed after that time will be rolled back.

Point-in-time recovery applies to transaction log restores only (not to full database restores).


Restore Now

Choose this button to perform the restore immediately.


Database Backup Tab

Use this tab to back up databases, tables, or transaction logs.

For more information, select one of the following:

Database Backup

From the list in this box, select the database to back up.

Entire Database

Backs up the entire database.

Transaction Log

Backs up the transaction log. To use this option the transaction log must be on a separate device from the database and the database must have the Truncate Log on Checkpoint option enabled. (To enable the Truncate Log on Checkpoint option, click the Edit Database button in the Manage Databases window and then select the Options tab.)

Single Table

Backs up a single table.

Append to Device

Adds the backup to the device, instead of overwriting what is on the device with the backup. To overwrite existing backups on the device, clear this option.

For SQL Server version 6.0 or later, backups can be appended to any disk or tape dump device, which allows you to keep backups of a database and its transaction logs in one physical location. If you are using a tape that contains data in a foreign format (not SQL Server backup files) you must clear Append to Device option or the tape will be rejected.

Unload Tape at End

Specifies that the tape is automatically rewound and unloaded when the backup completes. If you will be performing multiple backups to a single tape, clear this option. If you are performing a single backup or the last backup of several to a tape, select this option.

Skip Tape Header

Specifies that the server should not read the ANSI header on a tape. For example, if the ANSI label of a tape warns that it has not expired or that you don't have permission to write to it, selecting this option causes SQL Server to ignore the warning. Or if the tape you are writing to is new (and has no ANSI label), SQL Server writes a new label. Selecting this option prevents unnecessary retries as SQL Server searches for a label. To tell SQL Server to read any existing ANSI tape labels on the tape(s) you are writing, clear this option.

Setting this option for a specified backup overrides the setting defined for the tape dump device when it was created.

No expiration date

Specifies that the backup has no expiration data and can be overwritten at any time.

Expires after

Specifies that the backup expires after a set elapsed number of days. If you select this option, you must also type the number of days in the adjacent box.

An existing backup cannot be overwritten until it expires. However, when Append to Device is selected, the Expires After option is informational only.

Expires on

Specifies that the backup expires on a specific date. If you select this option, you must type the number of days in the adjacent box.

An existing backup cannot be overwritten until it expires. However, when Append to Device is selected, the Expires on option is informational only.

Backup Devices

Lists all available backup devices. You can select one or more devices to backup to. You can also add or drop devices.

Note that you cannot back up to floppy disk devices using SQL Enterprise Manager.

New

To add a new backup device or specify a backup path, click the New button. This button opens the New Dump Device dialog box.

Delete

To delete a backup device, select a device from the list and then click the Delete button. This button opens the Delete Dump Device dialog box.

Info

Click this button to view information for a selected backup device. Displays the Header Information for Dump Device dialog box.

Backup Now

Click this button to perform the backup immediately. The backup runs as a foreground task.

Schedule

Click this button to schedule the backup for immediate, one time, or recurring execution.

Scheduling an immediate backup (instead of choosing Backup Now) provides the advantage of running the backup as a scheduled (background) task, instead of interactively.

See Also

Backing Up a Database or a Transaction Log

Table Dump and Load


Database Restore Tab

Use this tab to restore databases, tables, or transaction logs. For more information, select one of the following:

Restore From

Lists source databases that have been dumped and can be restored from. Select one from the list.

Only dumps from SQL Server version 6.5 or later appear in this list. To select another type of dump (such as from SQL Server 4.x), click the Advanced button.

To

Specifies the database you will restore to.

Note that if you are restoring the msdb database, you must first stop the SQL Executive service.

Database and Transaction Logs

Specifies whether to restore a transaction log or full database dump.

Until Time

Restores transaction logs or databases through a specific point in time. Select this option and adjust the displayed date and time appropriately. Transactions committed after that time will be rolled back.

Point-in-time recovery applies to transaction log restores only.

Single Table

Restores a single table. A table load can be performed from a table dump or a full database dump.

If you select this option, select the destination table from the Single Table list.

History List

Displays dumps available for restoration from the selected source database.

You can also select a database dump plus one or more of its transaction log dumps for restoration.

Selecting a database dump restores the entire database. Selecting a transaction log dump restores the transaction log information, re-executes the changes it contains, and rolls back transactions that were uncommitted when the transaction log was backed up. Selecting a table dump restores that table.

Restore Now

Restores the selected database, transaction log, or table dump immediately.

Advanced

Opens the Advanced Restore Options dialog box. Use this dialog box to restore from sources that do not appear in the Backup From list. (Only sources from SQL Server version 6.5 or later appear in that list.)

See Also

Restoring a Database or a Transaction Log

Table Dump and Load

Point-in-Time Recovery


New Dump Device Dialog Box
New/Delete Dump Device Dialog Box

Use this dialog box to add or delete a dump device. For more information, select one of the following:

Name

Specifies the logical name for the new dump device. This name will be used when you back up to or restore from the device. The name must follow the rules for identifiers and can be up to 30 characters. If you specify a name longer than 30 characters, the name is truncated.

If you are deleting a dump device, this field cannot be edited.

Location

Specifies the physical name for the dump device. The physical name is an operating system filename that includes the full path. Physical names must follow the rules for operating system filenames, or UNC (names for network disk devices). Names containing nonalphanumeric characters must be enclosed in quotation marks.

If you are deleting a dump device, this field cannot be edited.

 Locate

Displays a directory tree. From the tree, select a path for location.

If you are deleting a dump device, the Locate button cannot be selected.

Type

Specifies whether the dump device is a disk or tape.

Disk Dump Device

Creates a dump device file on a hard disk or other disk media. Note that you cannot back up to floppy disk devices using SQL Enterprise Manager.

If you are deleting a dump device, this option cannot be changed.

Tape Dump Device

Creates a dump device for a tape drive.

If you are deleting a dump device, this option cannot be changed.

Skip Headers

Specifies whether to read or ignore ANSI tape labels. Select this option if you selected Tape Dump Device and you don't want the ANSI tape label on the tape you are writing to read. Clear this option to tell SQL Enterprise Manager to recognize all existing ANSI tape labels on the tape you are writing to.

If you are deleting a dump device, this option cannot be changed.

Create

Creates the dump device. Backups to that device can then be performed.

Note that a disk dump device will not appear as a file in the specified directory until a backup has been performed to that device.

Delete

Drops the selected dump device.


Creating a Dump Device

When you create a dump device, you provide a logical and a physical name. The logical name must follow SQL Server rules for identifiers and can be up to 30 characters. The physical name is an operating system filename that includes the full path and that follows the rules for operating-system filenames or the universal naming convention (UNC) for network devices.

SQL Enterprise Manager supports local and network disk dump devices. A local disk dump device is created on a disk drive attached to the local computer. A network disk dump device is created on a shared network directory. When creating a network disk dump device, specify a UNC name for the physical name.

SQL Enterprise Manager supports local tape dump devices (on tape drives attached to the local computer). Network tape dump devices are not supported. To create a tape dump device, you must first install a tape drive using Windows NT. When creating a tape dump device for SQL Server, you must use the physical name assigned to the tape drive by Windows NT. For example, \\.\TAPE0.

You cannot use SQL Enterprise Manager to create, edit, delete, back up to, or restore from disk dump devices, or to create named pipe dump devices.

To create a dump device

1.	In the Server Manager window, select the server to create a dump device for.

2.	From the Tools menu, choose Database Backup/Restore.

The Database Backup/Restore dialog box appears.

3.	Click the New button.

The New Dump Device dialog box appears.

4.	In the Name box, type the logical name for the dump device.

This name will be used when you back up to or restore from the dump device. The name must follow the rules for identifiers and can be as many as 30 characters. (If you specify a name longer than 30 characters, the name is truncated.)

5.	In the Location box, type the physical name for the dump device.

The physical name is an operating-system filename that includes the full path. Physical names must follow the rules for operating-system filenames, or UNC names. Names containing nonalphanumeric characters must be enclosed in quotation marks.

If you do not remember the path you want to use, you can click the Locate button and select a path from the displayed directory tree.

6.	Select a device type. Disk Dump Device creates a dump device file on a hard disk (or other disk media). Tape Dump Device creates a dump device for a tape drive.

7.	If you selected Tape Dump Device in step 6, select or clear the Skip Headers check box.

This option indicates whether ANSI tape labels are read or ignored. Clearing this option tells SQL Server to read any existing ANSI tape labels on the tape you are writing to.

8.	Choose Create.

See Also

Viewing Dump Devices

Dropping a Dump Device


Viewing Dump Devices

You can view a list of dump devices, information about backups stored on a device, and the physical name of a device.

To view dump devices

1. In the Server Manager window, select a server to view dump devices for.

2. To display the list of server dump devices, click the plus (+) sign that is next to the Dump Devices folder for the selected server.

3. To view information about backups stored on that device, click the plus (+) sign next to that device. Full database dumps are indicated by a database icon. Transaction log dumps are indicated by a page icon.

To view the physical name of a dump device

1. In the Server Manager window, select the server to view dump devices for.

2.	From the Tools menu, choose Database Backup/Restore.

The Database Backup/Restore dialog box appears.

3.	In the Dump Devices box, select the dump device and choose Delete. In the Delete Dump Device dialog box that appears, the Device Path box displays the physical name of the dump device.

See Also

Creating a Dump Device

Dropping a Dump Device


Dropping a Dump Device

When you drop a dump device, data backed up to that device is also dropped.

To drop a dump device

1. In the Server Manager window, select the server to drop a dump device from.

2. From the Tools menu, choose Database Backup/Restore.

The Database Backup/Restore dialog box appears.

3. In the Dump Devices box, select the dump device to drop and choose Delete.

The Delete Dump Device dialog box appears.

4. Choose Delete.

After deleting a disk dump device, delete the device's operating system file.

See Also

Creating a Dump Device

Viewing Dump Devices


Header Information for Dump Device Dialog Box

Use this dialog box to view header information for a dump device.

Back Device

Specifies the name of the dump device. You can select a different name from the list.

Device Type

Specifies whether the dump device is Disk or Tape.

Device Size

Specifies the size, in Kilobytes, of the dump device file.

Volume Label

Specifies the volume label of the dump device.

Expiration Date

Specifies the expiration date, if any. A dump device with an expiration date cannot be overwritten until that date expires. A dump device without an expiration date can be overwritten at any time.

Striped

Indicates whether the dump device is a member of a stripe set and contains a portion of a striped dump.

Striped Name

Specifies the name of the stripe set (if applicable).

Members

Lists members (if any) of the stripe set. You can select a device from list to display information about that device.

The backup list

Contains a row for each backup performed to the device.

ID

Specifies the volume ID.

Database

Specifies the name of the source database.

Backup Type

Specifies whether the backup type is Table, Database, or Transaction Log.

Table

Specifies the name of the table, if a table dump.

Backup Start

Specifies the date the backup began.

Backup Finish

Specifies the hour and the minute the backup finished.

Dump Size

Specifies the size of the dump, in pages.

Current Sequence

Specifies the current or old sequence number. The sequence number indicates the position of the dump in the volume.

New Sequence

Specifies the new sequence number.


Backup Volume Labels Dialog Box

Use this dialog box to confirm or change the volume label for a dump device or for dump devices in a stripe set. Volume labels can be up to six characters long.

The function of this dialog box differs slightly, depending on the following situations.

When you select the Append to Device option in the Backup tab and click the Backup Now button, review the volume labels of removable media dump devices to verify they are correct. If not, choose Cancel, insert the appropriate tape (or other removable storage media) in the drive, and initiate the backup again.

When you do not select the Append to Device option in the Backup tab and click the Backup Now button, you can accept the default labels for the new backups or supply new ones. Volume labels can be up to six characters long.

When setting up a scheduled backup, you can leave the volume column entries blank, or enter volume labels to be checked during the scheduled backup.


Backup Progress Dialog Box

Use this dialog box to monitor a backup. To cancel the backup, click the Cancel button.


Backing Up a Database or a Transaction Log

Backing up a database, a transaction log, or a table copies system tables and user-defined objects to a specified dump device. You can back up a database to multiple devices and specify a variable path name to back up to. You can also back up multiple databases or transaction logs to a single file or tape. You cannot back up to floppy disk dump devices using SQL Enterprise Manager. Back up your database on a set schedule. If you store the transaction log on a different disk device from the database, back up the log frequently to record changes made since the last database backup.

The SQL Server dump is a dynamic dump that can occur while the database is active. The dump captures the data as it is at the moment the statement is executed; no partial transactions are reflected in the dumped database. Performing a dump can slow the system, so you may want to run it when the database is not being updated.

To back up a database, a transaction log, or an individual table

1.	In the Server Manager window, select the server to back up from.

2.	From the Tools menu, choose Database Backup/Restore.

3.	In the Backup tab, select a database.

4.	Select the type of dump.

Entire Database backs up the database.

Transaction Log backs up only the transaction log. To use this option, the transaction log must be on a separate device from the database, and the database must have the Truncate Log on Checkpoint option enabled. (To enable the Truncate Log on Checkpoint option, click the Edit Database button in the Manage Databases window and select the Options tab.)

Single Table backs up the table selected from the list.

5.	Select the backup options.

Append to Device appends the dump to the device, instead of overwriting what is on the device with the new dump. To overwrite existing backups on the device, clear the Append to Device option.

For Microsoft SQL Server version 6.0 or later, backups can be appended to any disk or tape dump device, which allows you to keep backups of a database and its transaction logs in one physical location. If you are using a tape that contains data in a foreign format (not SQL Server backup files), you must clear the Append to Device option or the tape will be rejected.

Unload Tape at End specifies that the tape is to automatically rewind and unload when the back up completes. If you will be performing multiple dumps to a single tape, clear the Unload Tape at End option. If you will be performing a single backup or the last backup of several to a tape, select this option.

Skip Tape Header specifies that the server should not read the ANSI header on a tape. To tell SQL Server to read existing ANSI tape labels on the tape(s) you are writing to, clear the Skip Tape Header option. For example, if the ANSI label of a tape warns that the tape has not expired or that you don't have permission to write to it, selecting the Skip Tape Header option causes SQL Server to ignore the warning. Or if the tape you are writing to is new (and therefore has no ANSI label), SQL Server writes a new label on the first try. Selecting the Skip Tape Header option prevents unnecessary retries as SQL Server works to find a label. Setting this option for a particular backup overrides the setting defined for the tape dump device when it was created.

6.	Specify expiration options.

No expiration date specifies that the backup, has no expiration data and can be overwritten at any time.

Expires after specifies that the backup expires after a specified elapsed number of days. If you select this option, you must also type the corresponding number of days in the adjacent box.  An existing dump cannot be overwritten until it expires. However, when Append to Device is selected, the Expires After option is informational only.

Expires on specifies that the backup expires on a specific date. If you select this option, you must also type the number of days in the adjacent box. An existing dump cannot be overwritten until it expires. However, when Append to Device is selected, the Expires on option is informational only.

7.	From the Backup Devices list, select the device(s) to back up to. If you select more than one device, a striped dump (a multiple device backup) is performed. If necessary, add a new dump device by choosing New and completing the dialog box.

8.	Execute or schedule the backup.

Backup Now performs the backup, which runs as a foreground task, immediately.

Schedule opens a dialog box that lets you schedule an immediate, a one time, or a recurring backup. Scheduling an immediate backup instead of choosing Backup Now runs the backup as a scheduled (background) task, instead of interactively.

See Also

Restoring a Database or a Transaction Log

Table Dump and Load


Restoring a Database or a Transaction Log

You can restore a database by loading the most recent database backup and its subsequent transaction log backups to recover as much of a database as possible in case of system failure or database damage. If you are restoring a database because of media failure, run DBCC, drop the damaged database, re-create the database, and load it. When a database is restored from a backup, that database must not be in use. All data in the database to be restored is replaced by the backup data.

Loading a transaction log results in re-executing the changes it contains and rolling back transactions that were uncommitted when the transaction log was backed up. Transaction log backups must be loaded in the sequence in which they were made. SQL Server checks the timestamps on each dumped database and each dumped transaction log to verify that the sequence is correct. When the entire sequence of transaction log dumps has been loaded, the database is restored to its state at the time of the last transaction log dump (minus active transactions).

You can restore a database, transaction log, or table.

To restore a database, a transaction log, or a table

1.	In the Server Manager window, select the server to restore to.

2.	From the Tools menu, choose Database Backup/Restore.

The Database Backup/Restore dialog box appears.

3.	Select the Restore tab.

4.	Select a source database from the Backup From list.

Only databases from SQL Server version 6.5 or later appear in the list. To restore from another database dump (such as a SQL Server 4.x), click the Advanced button and complete the dialog box.

5.	In the To box, select a database from the list. The information will be restored to the selected database.

6.	Set the Restore options.

Database and Transaction Logs restores a transaction log or a full database dump, as selected in the restore list.

Until Time restores through a specific point in time. If you select this option, adjust the displayed time appropriately. Transactions committed after that time will be rolled back.  Point-in-time recovery applies to transaction log restores only.

Single Table restores to the selected table only. A table load can be performed from a table dump or a full database dump.

7.	Select a dump from the restore list, which displays dumps available for the selected source database.

8.	Choose Restore Now.

See Also

Backing Up a Database or a Transaction Log

Table Dump and Load

Point-in-Time Recovery


Microsoft Distributed Transaction Coordinator

The Microsoft Distributed Transaction Coordinator (MS DTC) manages transactions across a network of Windows NT and Windows 95based systems. MS DTC does the following:

Provides distributed transaction support for Microsoft SQL Server, making it easy to reliably update data that resides on two or more Microsoft SQL Server systems.

Permits Microsoft SQL Server to participate in transactions controlled by X/Open DTP XA-compliant transaction processing monitors such as Encina, TopEnd, and Tuxedo.

Note: XA support is not available for this beta release.

Provides an easy-to-use graphical user interface for distributed transactions.

MS DTC can be started, stopped, and administered using Microsoft SQL Enterprise Manager. In the Server Manager window, a Distributed Transaction Coordinator icon appears in the expanded tree under the name of each server that has MS DTC installed. To start, stop, or administer MS DTC, expand the tree for the appropriate server, click the right mouse button on the Distributed Transaction Coordinator icon, then select the appropriate command from the menu.

You can also administer MS DTC using the Distributed Transaction Coordinator command from the Server menu.

Using the MS DTC interface in Microsoft SQL Enterprise Manager, you can:

Start and stop MS DTC (as a Windows NT service).

Open the Transactions window, where you can view all the currently running transactions, the status of each transaction, and force the outcome of transactions. You can force prepared transactions to resolve as being committed or to terminate. You can force active transactions to terminate.You can also force a coordinating MS DTC server to remove the transaction from its log.

Open the Trace window, where you can view MS DTC trace information. You can filter the displayed information by severity level.

Open the Statistics window, where you can view statistics about MS DTC counters, including: the current number of active transactions; the peak number of concurrent transactions; the numbers of committed, terminated, in-doubt, and administratively-resolved transactions; total transactions processed; transaction response times; and the date and time that the MS DTC session started.

Open the Configuration dialog box, where you can set the MS DTC update frequency, the MS DTC transaction display retention, the log file location, and checkpoint and flush timers.

When Microsoft SQL Server 6.5 is installed, the MS DTC service is added to the Services application in the Control Panel.

For more information about configuring and using MS DTC, see the Guide to Microsoft Distributed Transaction Coordinator.

See Also

Using MS DTC from SQL Enterprise Manager


Using MS DTC from SQL Enterprise Manager

The Microsoft Distributed Transaction Coordinator (MS DTC) can be started, stopped, and administered through SQL Enterprise Manager. A Distributed Transaction Coordinator icon appears in the expanded tree under the name of each server that has MS DTC installed.

To start, stop, or administer MS DTC, select a server and expand its tree, click the right mouse button on the Distributed Transaction Coordinator icon, and select the appropriate command from the menu. For more information about a command, choose one of the following command topics:

 Start

 Stop

 Transactions

 Trace

 Statistics

 Configuration

You can also administer MS DTC using the Distributed Transaction Coordinator command from the Server menu.

See Also

Microsoft Distributed Transaction Coordinator


Start

Starts MS DTC as a Windows NT service.


Stop

Stops MS DTC as a Windows NT service.


Transactions

Opens the MS DTC Transactions window. Use this window to display all transactions currently running and the status of each transaction.

You can force the outcome of transactions to resolve as being committed, or to terminate. You can also force a coordinating MS DTC server to remove a transaction from its log.


Trace

Opens the MS DTC Trace window, which displays event log information and MS DTC trace information. You can filter the displayed information by severity level.


Statistics

Opens the MS DTC Statistics window. Use this window to display statistics about MS DTC counters, including: the current number of active transactions; the peak number of concurrent transactions; the numbers of committed, terminated, in-doubt, and administratively-resolved transactions, total transactions processed; transaction response times; and the date and time that the MS DTC session started.


Configuration

Opens the MS DTC Configuration dialog box. Use this dialog box to set the MS DTC update frequency, the MS DTC transaction display retention, the log file location, and checkpoint and flush timers.


MS DTC Transactions Window

Use this window to monitor the status of a transaction or to manually resolve a transaction.

Monitoring a Transaction

Transactions are displayed in the window by icons that represent the current state of each transaction. For a list of icons and the transaction states they represent, see the Microsoft Distributed Transaction Coordinator Administrator's Guide and Programmer's Reference.

Changing the View Options

You can right-click any transaction's icon to display a menu that allows you to change the way that icons are displayed in the window:

Large Icon displays transactions in large icons that are arranged sequentially in rows across the screen.

Small Icon displays transactions in small icons arranged sequentially in rows across the screen.

Details lists transactions in a single column and provides an additional two columns of information:

The network node on which the transaction originated.

The unit of work ID associated with each transaction. This is the transaction's global unique identifier, which is generated by the MS DTC when the transaction begins.

List displays transactions sequentially in columns down the screen.

Clear removes all currently displayed icons from the screen.

Small Icon and List display the most transactions at any one time. Details provides the most information about the transactions, and Large Icon displays transactions in the most readable format.

Manually Resolving a Transaction

You can right-click a transaction's icon to display a menu that allows you to manually resolve the transaction.

Commit commits the transaction on the local MS DTC.

Abort aborts the transaction on the local MS DTC.

Forget deletes the transaction record from the log file of the local MS DTC.

See Also

Microsoft Distributed Transaction Coordinator

Using MS DTC from SQL Enterprise Manager


MS DTC Trace Window

Use this window to list current trace messages issued by the MS DTC. Tracing allows the system administrator to view the current status of various MS DTC activities, such as startup and shutdown, and to track potential problems by viewing additional debugging information. You can filter the displayed information by severity level by adjusting the Trace slider in the MS DTC Configuration dialog box.

Trace Severity Levels

MS DTC tracing supports the following levels of severity:

Errors	Cause MS DTC to malfunction or need a restart. An example would be a corrupt log file detected.

Warnings	Information about something that might go wrong soon.

Information	Useful information about infrequent events, such as startup and shutdown.

Debugging information, such as new connections being established with clients or resource manager enlistments on a given transaction.

All error, warning, and informational traces are routed to the Windows NT event logger regardless of whether SQL Enterprise Manager is running and regardless of the trace view slider setting.

Trace Sources

The tracer and the Windows NT event logger allow each message to be tagged with its source.

Source	Description

SVC	The MS DTC Service

LOG	The MS DTC log

CM	The MS DTC network connection manager

See Also

Microsoft Distributed Transaction Coordinator

Using MS DTC from SQL Enterprise Manager


MS DTC Statistics Window

Use this window to view statistical information about the transactions in which a server participated. Some of the statistics are cumulative, others reflect current performance. It is important to remember that should you ever stop the MS DTC, the values of all cumulative statistics will reset to zero when you restart the MS DTC.

Current Statistics

The Current group shows:

Active: The current number of transactions that have not yet completed the two-phase commit protocol.

Max. Active: The highest number of active transactions at any time during the period in which the MS DTC was operational.

Indoubt: The current number of in-doubt transactions. In-doubt transactions might require administrator intervention. In-doubt transactions can be viewed and managed in the Transactions window.

The size of the MS DTC log file determines the maximum allowable number of concurrent transactions. Once a transaction has committed or aborted, it is deleted from the log file. However, should the log file at any point become full, the MS DTC on the local server will refuse to accept any new transaction until sufficient space has been cleared in the log file to record the new transaction.

Together the Active and Max.Active statistics allow you to measure the transaction load on a particular DTC. If, for example, the number of current transactions approaches the historical maximum, the system administrator may decide to increase the size of the log file to allow for more transactions. If on the other hand, the number of current transactions is well below the maximum, the administrator may decrease the size of the log file to conserve resources such as memory.

You can increase or decrease the size of the log file using the DTC Configuration dialog box.

Cumulative Statistics

The Aggregrate group shows:

Committed: The cumulative total of committed transactions. This number does not include forced (manually resolved) commits.

Aborted: The cumulative total of aborted transactions. This number does not include forced (manually resolved) aborts.

Forced Commit: The cumulative total of committed transactions that were manually resolved.

Forced Abort: The cumulative total of aborted transactions that were manually resolved.

Total: The cumulative total of all transactions.

The Response Times group shows the minimum, average, and maximum transaction response times in milliseconds. Response time refers specifically to the duration of a transaction from the point that it began to the point that its outcome is known--that is, the point at which it is definitively committed by the commit coordinator (aborted transactions and transactions whose outcomes were forced are not taken into account).

The DTC Started group shows the date and time that the current MS DTC started.

See Also

Microsoft Distributed Transaction Coordinator

Using MS DTC from SQL Enterprise Manager


MS DTC Configuration Dialog Box

Use this dialog box to change MS DTC display parameters, reset the MS DTC log and timers, or start and stop the MS DTC.

The View group

Display Refresh controls how often trace, statistical information, and the transaction list is sent from the MS DTC process to the graphical interface. More frequent updates increase administrative overhead on the running transactions, but also provide more accurate information on the running MS DTC. The following values are used in the slider to indicate the update timing (left to right):

0 	Update the display every 20 seconds.

1 	Update the display every 10 seconds.

2 	Update the display every 5 seconds (default).

3 	Update the display every 3 seconds.

4 	Update the display every 1 second.

Transactions Shown controls how long a transaction must be active before being displayed on the graphical interface. The following values are used in the slider to indicate the lengths of time (left to right):

0 	Show 5 minute-old transactions.

1 	Show 1 minute-old transactions.

2 	Show 30 second-old transactions (default).

3 	Show 10 second-old transactions.

4 	Show 1 second-old transactions.

Trace controls the level of traces sent to the graphical interface. The slider positions and descriptions are as follows:

0	Send no tracing.

1	Send only error traces to.

2	Send error and warning traces.

3	Send error, warning, and informational traces.

4	Send all traces.

The Log group

The MS DTC log file is always called MSDTC.LOG. Before changing the size or location of the log file, you must use the Stop button to stop the MS DTC. After changing the size or location of the log file, click the Reset Log and Timers button. You can then use the Start button to restart the MS DTC.

Location: The location of the MS DTC log file.

Drive: The drive on which the MS DTC log file is located.

Directory: The directory on which the log file is located.

Size: The slider can be used to change the size of the MS DTC log file.

Capacity: The current size of the MS DTC log file and the number of allowable concurrent transactions.

The Timers group

For this beta release of MS DTC, do not use.

See Also

Microsoft Distributed Transaction Coordinator

Using MS DTC from SQL Enterprise Manager


Servers

Getting Started with SQL Enterprise Manager

Registering a Server

Creating a Server Group

Renaming A Server Group

Removing a Server Group

Removing a Server

Modifying Server Information

Starting or Stopping a Server

Setting Server Options

Setting Security Options

Setting Server Configuration Information

Managing SQL Server Messages

Viewing Server Attributes Information

Viewing Server Current Activity

Viewing Detail Activity

Viewing Object Locks

Viewing the Error Log

Changing Servers or Databases

Setting SQL Enterprise Manager Configuration Information

Configuring SQL Executive

Adding a SQL Server Login

Modifying a Login

Dropping a Login

Managing Remote Servers and Logins

Adding a Database Device

Dropping a Database Device

Modifying a Database Device

Creating a Dump Device

Viewing Dump Devices

Dropping a Dump Device


Databases

Creating a Database

Modifying a Database

Setting Database Options

Setting Statement Permissions

Dropping a Database

Adding Database Users

Using Aliases

Dropping a Database User

Creating Groups

Modifying Groups

Dropping a Group

Backing Up a Database or a Transaction Log

Restoring a Database or a Transaction Log


Objects

Granting Object Permissions

Revoking Object Permissions

Viewing Object Permissions

Creating a Table

Modifying a Table

Dropping a Table

Creating an Index

Modifying an Index

Renaming an Index

Updating Index Statistics

Viewing Index Distribution Information

Checking the Size of an Index

Dropping an Index

Creating a Trigger

Modifying a Trigger

Dropping a Trigger

Creating a Stored Procedure

Modifying a Stored Procedure

Dropping a Stored Procedure

Managing Extended Stored Procedures

Creating a View

Modifying a View

Dropping a View

Creating a Rule

Binding a Rule to a Column

Binding a Rule to a Datatype

Unbinding a Rule from a Column

Unbinding a Rule from a Datatype

Dropping a Rule

Creating a Default

Binding a Default to a Column

Binding a Default to a Datatype

Unbinding a Default from a Column

Unbinding a Default from a Datatype

Dropping a Default

Creating User-defined Datatypes

Viewing User-defined Datatype Information

Dropping a User-defined Datatype

Generating SQL Scripts

Viewing Object Dependencies

Dropping Objects

Renaming Objects


Alerts

Alerts Overview

Viewing Alerts

Creating an Alert

Editing an Alert

Dropping an Alert

Raising SNMP Traps from an Alert

Viewing Operators

Creating an Operator

Editing Operator Information

Dropping an Operator

Setting Alert Engine Options


Scheduled Tasks

Scheduling a Task

Editing a Scheduled Task

Removing a Scheduled Task

Running a Task Immediately

Viewing Task History

Viewing a List of Scheduled Tasks

Viewing a List of the Scheduled Tasks Currently Running


Replication

Replication Overview

Setting Up the Replication Distribution Database

Setting Replication Publishing Information

Setting Server Subscribing Information

Setting Up DBOs to Manage Publications and Subscriptions

Viewing Replication Topology

Using an ODBC Subscriber

About Access Subscribers

About Oracle Subscribers

About Other ODBC Subscribers

Replication Restrictions for ODBC Subscribers

Datatype Mappings for ODBC Subscribers

Creating Publications

Setting Up Push Subscriptions

Setting Up Pull Subscriptions

Manually Synchronizing Database Tables for Replication

Stopping Replication from a Publisher

Stopping Replication from a Subscriber


Queries

Querying a Database

Editing Query Results

Saving a Query to a File

Saving Query Results

Starting a New Query

Using a Saved Query

Removing a Query/Result Set

Setting Query Flag and Format Options

Setting Query System Options

Searching and Replacing Text

Using Graphical Showplan

Using Graphical Statistics I/O


Getting Started with SQL Enterprise Manager

This Help topic briefly describes SQL Enterprise Manager. For detailed information about how to perform tasks using SQL Enterprise Manager, see the Help Contents or the Online Help available in each window and dialog box.

What is SQL Enterprise Manager?

SQL Enterprise Manager provides powerful administration tools that let you manage multiple servers. With SQL Enterprise Manager you can configure, start, pause, and stop SQL Servers, as well as monitor current server activity and view the SQL Server error log. You can create and manage devices, databases, and database objects. You can manage security, including logins, database users, and permissions. SQL Enterprise Manager and the SQL Executive service provide a way to set alerts for various server events and to schedule server tasks. SQL Enterprise Manager also provides a graphical way to set up and manage replication, and enables you to execute and analyze queries, back up and restore databases, and generate SQL scripts.

Who can use SQL Enterprise Manager?

Anyone who can access SQL Server can use SQL Enterprise Manager; no permission is needed. Access to objects and tasks within SQL Enterprise Manager is controlled by permissions of the login identification. If you log in as the system administrator (SA), you can access all tasks and objects. If you log in as the database owner (DBO), you can access tasks and objects permitted to the DBO. If you log in as a user, you can access all objects you have created and perform tasks you have permission for.

How do I start using SQL Enterprise Manager?

First you must register the server(s) you will be managing with SQL Enterprise Manager. While you are registering servers, you may want to create groups to better organize how your servers appear in the Server Manager window. (Once you have registered a server, that server appears in the Server Manager window each time you start SQL Enterprise Manager.) You can immediately begin to perform tasks for a registered server. Initially, you may want to verify that SQL Server configuration options are set appropriately, create devices and databases, create login IDs, and configure the SQL Server for replication. For more information about these tasks, choose a Help topic from the following:

Registering a Server

Creating a Server Group

Server Manager Window

Setting Server Configuration Information

Adding a Database Device

Creating a Database

Adding a SQL Server Login

Replication Overview


Registering a Server

To manage a server with SQL Enterprise Manager, you must first register the server. Registering a server includes providing the server name, and your login ID and password for that server. You must also assign the server to a server group.

SQL Enterprise Manager works with SQL Server version 4.2x, 6.0, and 6.5. However, to use SQL Enterprise Manager with SQL Server version 4.2x, you must first load the SQL script SQLOLE42.SQL into the 4.2x server. You can find a copy of the script in the \INSTALL directory of the computer that contains SQL Server 6.0 or 6.5. Note that not all features of SQL Enterprise Manager are enabled for use with SQL Server version 4.2x.

To register a server

1. From the Server menu, choose Register Server.

The Register Server dialog box appears.

2. In the Server box, type the name of the server.

The Server box lists all registered servers. To view a list of the servers on the network, click the List Servers button. (Note that you cannot list servers on Windows 95.)

If you are running both 4.2x and 6.x SQL Servers at the same time on the same machine, the servers must use different names to be supported by SQL Enterprise Manager.

3. Select the type of security you will use to log in to the server.

If you select Use Trusted Connection, Windows NT security validates the user and a password does not need to be stored on the computer. This option requires SQL Server to be set with integrated or mixed security.

If you select Use Standard Security, you must provide a login ID and a password in the Login ID and Password boxes. SQL Enterprise Manager stores that information. You need not provide the login ID and the password each time you connect to the server.

4. To not view the status (started, paused, stopped, or indeterminate) of the registered servers in the Server Manager window, clear the Display Server Status in the Server Manager check box.

To set the polling interval for the status information, after you close the Register Server dialog box, from the Tools menu, choose Preferences/Configure.

Note that polling uses the Windows NT Service Control, which is unavailable on Windows 95, making the server status unavailable on Windows 95.

5. In the Server Group box, select a group in which to include SQL Server. To create a new group, click the Server Groups button. A default group, SQL 6.5, is provided with SQL Enterprise Manager.

Server groups provide a convenient way to organize your SQL Servers in the Server Manager window.

6. When you are through adding information about a server, choose Register.

If you receive the message "Unable to Connect to Server. Register anyway?", the server you are registering is not started or the login information you provided is invalid.

See Also

Removing a Server

Modifying Server Information


Creating a Server Group

Server groups provide a way to organize SQL Servers in the Server Manager window. For example, you can create a server group based on the servers' use (Development Servers) or locations (North America Servers). By default, SQL Enterprise Manager is installed with a server group named SQL 6.5.

SQL Enterprise Manager groups are private and are created only at the local computer. If you have several computers with SQL Enterprise Manager installed on them and you want those computers to display identical server groups, go to each computer and create those groups or duplicate the SQL Enterprise Manager server registration configuration by copying the \SQL60\BINN\SERVER.BIN file from one computer to another.

To create a server group

1. From the Server menu, choose Server Groups.

The Manage Server Groups dialog box appears.

2. In the Name box, type a name for the server group.

3. Under Level, select TopLevel Group or Sub-Group Of. If you select Sub-Group Of, select a group.

4. Choose Add Group.

See Also

Renaming a Server Group

Removing a Server Group


Renaming a Server Group

You can change the name of a server to reflect changes in how you want to organize your servers (for example, change North American Servers to U.S. Only Servers).

To rename a server group

1. From the Server menu, choose Server Groups.

The Manage Server Groups dialog box appears.

2. Under Level, select the group to rename.

3. In the Name box, type a new name for the server group.

4. Choose Change Name.

See Also

Creating a Server Group

Removing a Server Group


Removing a Server Group

You can remove a server group. First, you must move all affected servers and subgroups to other groups.

To remove a server group

1. From the Server menu, choose Server Groups.

The Manage Server Groups dialog box appears.

2. Under Level, select the group to remove.

3. Choose Remove Group.

See Also

Creating a Server Group

Renaming a Server Group


Removing a Server

You can remove a server you no longer need to manage in SQL Enterprise Manager. Removing a server removes it only from SQL Enterprise Manager. The server remains installed.

To remove a server

1. From the Server menu, choose Register Server.

The Register Server dialog box appears.

2. In the Server box, select the server to remove.

3. Choose Remove.

See Also

Registering a Server

Modifying Server Information


Modifying Server Information

You can change information about a registered server, including the login information, the group the server belongs to, and whether to display server status in the Server Manager window.

To modify server information

1. From the Server menu, choose Register Server.

The Register Server dialog box appears.

2. In the Server box, select the server to modify.

3. Modify the appropriate information.

Note that server status cannot be displayed on Windows 95based computers because polling uses Windows NT Service Control APIs, which are unavailable on Windows 95.

4. Choose Modify.

See Also

Registering a Server

Removing a Server


Setting Server Options

Once you have installed SQL Server 6.5, you can configure several server options. You can configure SQL Server file paths, the master database, and the error log. You can set options to automatically start SQL Server or SQL Executive whenever you boot the server computer. You can view whether SQL Server events are being registered in the Windows NT application event log, and whether SQL Server performance statistics are being integrated with the Windows NT Performance Monitor. You can set startup parameters, tape support, and mail client information.

To set server options

1. In the Server Manager window, select the server to set options for.

2. From the Server menu, choose Configurations.

The Server Configuration/Options dialog box appears.

3. Select the Server Options tab.

4. Set the appropriate options.

5. Choose Apply Now.

See Also

Setting Security Options

Setting Server Configuration Information


Setting Security Options

SQL Server provides a way to integrate SQL Server login security with Windows NT security. Integrated security lets SQL Server applications take advantage of Windows NT security features, including encrypted passwords, password aging, domain-wide user accounts, and Windows NT-based user administration. With integrated security, authorized users can bypass the SQL Server login process.

To set security options

1. In the Server Manager window, select the server to set security options for.

2. From the Server menu, choose Configurations.

The Server Configuration/Options dialog box appears.

3. Select the Security Options tab.

4. Set the appropriate options.

5. Choose Apply Now.

See Also

Setting Server Options

Setting Server Configuration Information


Setting Server Configuration Information

SQL Server configuration options control various aspects of SQL Server performance and memory allocation. You can configure many options to help optimize SQL Server performance.

Some SQL Server configuration options are advanced options. To view these options, set the value for Show Advanced Option to "1" and choose OK. The next time you open the Configuration/Options dialog box and select the Configuration tab, the advanced options will be displayed.

To set server configuration information

1. In the Server Manager window, select the server to set configuration options for.

2. From the Server menu, choose Configurations.

The Server Configuration/Options dialog box appears.

3. Select the Configuration tab.

4. Select a configuration option, and in the Current box, type a new value for that option.

5. Repeat step 4 for each option to change.

6. Choose Apply Now.

Except where otherwise noted in the Description box, you must stop and restart the server for new configuration values to take effect.

See Also

Setting Server Options

Setting Security Options


Viewing Server Attribute Information
Viewing Server Attributes Information

You can view (but not change) information about a SQL Server, including the server version and various environment variables, and information about the database gateway and underlying data source.

To view server attribute information

1. In the Server Manager window, select the server to view attributes for.

2. From the Server menu, choose Configurations.

The Server Configuration/Options dialog box appears.

3. Select the Attributes tab.

See Also

Setting Server Options

Setting Security Options

Setting Server Configuration Information


Viewing Server Current Activity

You can view information about current server activity, including current user connections and locks. You can see the process number, status, locks, and commands the active users are running. You can also determine which objects are locked and the kinds of locks that are present. Only the SA can view server activity.

To view server connection activity

1. In the Server Manager window, select the server to view connection activity for.

2. From the Server menu, choose Current Activity.

The Current Activity window appears. To view a legend for the icons in the tab, click the Display Legend button.

To view more information about a process

1. Select the process to view more information for.

2. Click the More Info button.

The Process Information for dialog box appears.Information is displayed about the last statement that the process ran and the resource usage for that process. You can send a message to the user or terminate the user's process.

To send a message to a connection

1. Choose Send Message.

The Send Message dialog box appears.

2. In the Message box, type the message to send.

3. Under Destination, specify where the message will be sent.

To send a message to all currently logged on users, type an asterisk (*) in the Using Hostname box.

4. Choose Send.

To terminate a process

1.In the User Activity tab, select the process.

2. Click the Kill Process button.

To refresh connection information

Click the Refresh on-screen information button.

See Also

Viewing Detail Activity

Viewing Object Locks

Viewing the Error Log


Viewing Object Locks

You can view information about object lock for a selected SQL Server, processes that hold the locks, and statements that are being executed. You can also view more information about a process, terminate a process, or send a message to a connection. Only the SA can view object lock information.

To view object locks

1. In the Server Manager window, select the server to view object locks for.

2. From the Server menu, choose Current Activity.

The Current Activity window appears.

3. Select the Object Locks tab.

To view a legend for the icons in the tab, click the Display Legend button.

To view more information about a process

1. Select the process to view more information for.

2. Click the More Info button.

The Process Information dialog box appears. Information is displayed about the last statement the process ran and the resource usage for that process. You can send a message to the user or terminate the user's process.

To send a message to a connection that has an active lock

1. Click the Send Message button.

The Send Message dialog box appears.

2. In the Message box, type the message to send.

3. Under Destination, specify where the message will be sent.

To send a message to currently logged on users, type an asterisk (*) in the Using Hostname box.

4. Choose Send.

To terminate a process

1. Select the process in the Object Locks tab.

2. Click the Kill Process button.

To refresh lock information

Click the Refresh on-screen information button.

See Also

Viewing Server Current Activity

Viewing Detail Activity

Viewing the Error Log


Viewing Detail Activity

You can view information about the process number, status, blocks, and commands the active users are running. You can also view more information about a process, terminate a process, or send a message to a connection. Only the SA can view detail activity.

To view detail activity

1. In the Server Manager window, select the server to view detail activity for.

2. From the Server menu, choose Current Activity.

The Current Activity window appears.

3. Select the Detail Activity tab.

To view a legend for the icons in the tab, click the Display Legend button.

To view more information about a process

1. Select the process to view more information for.

2. Click the More Info button.

The Process Information dialog box appears. Information is displayed about the last statement the process ran and the resource usage for that process. You can send a message to the user or terminate the user's process.

To send a message to a connection

1. Choose Send Message.

The Send Message dialog box appears.

2. In the Message box, type the message to send.

3. Under Destination, specify where the message will be sent.

To send a message to all currently logged on users, type an asterisk (*) in the Using Hostname box.

4. Choose Send.

To terminate a process

1. Select the process in the Detail Activity tab.

2. Click the Kill Process button.

To refresh activity information

Click the Refresh on-screen information button.

See Also

Viewing Server Current Activity

Viewing Object Locks

Viewing the Error Log


Viewing the Error Log

You can view the error log for a selected SQL Server.

To view the error log

1. In the Server Manager window, select the server to view the error log for.

2. From the Server menu, choose Error Log.

The Server Error Log window appears.

A new log is created each time SQL Server is started. The current log is labeled Current. The previous six logs are maintained and are labeled Archive followed by a number. The lowest number is the most recently archived log.

See Also

Viewing Server Current Activity

Viewing Detail Activity

Viewing Object Locks


Setting SQL Enterprise Workbench Configuration Information
Setting SQL Enterprise Manager Configuration Information

You can set time-outs and application preferences for SQL Enterprise Manager.

To set SQL Enterprise Manager configuration information

1. From the Tools menu, choose Preferences/Configure.

The Configure SQL Enterprise Manager dialog box appears.

2. To set application-related preferences, choose the Application tab and set the appropriate preferences.

To reset all application-related preferences to their defaults, choose Defaults.

3. To set connection-related preferences, choose the Connection tab and set the appropriate preferences.

To reset all connection-related preferences to their defaults, choose Defaults.

4. Choose OK.


Changing Servers or Databases

Using the Server Manager window or the Select Server/Database dialog box, you can change to a different server and/or database, or disconnect from a server.

To change to or disconnect from servers or databases in the Server Manager Window

   To change to a server or database, select the server or database.

To disconnect from a server, click the server with the right mouse button and choose Disconnect.

To change to or disconnect from servers or databases in the Select Server/Database dialog box

1. From the Tools menu, choose Set Server/Database.

The Select Server/Database dialog box appears.

2. In the Server box, select the server to change to.

If you are not yet connected to that server, click the Connect button to establish a connection.

If you are connected to that server and want to disconnect, click the Disconnect button.

3. In the Database box, select the database to change to.

4. Choose OK.


Adding a SQL Server Logins
Adding a SQL Server Login

Before a user can access a SQL Server, you must add the user's login ID to the server. You can also assign the user a password, a username, a default database, and a default language. If you don't assign a default database, the master database is used as the default. If you don't assign the user a username in the default database, the user's login ID is used as the username. Only the SA can add SQL Server logins.

If the SQL Server is configured for integrated security, a login ID for each user is not necessary. Instead, use the SQL Security Manager utility to automatically map Windows NT-based usernames to SQL Server login IDs.

When you add a SQL Server login, you can also select databases to permit access to.

To add a SQL Server login

1. In the Server Manager window, select the server to add logins to.

2. From the Manage menu, choose Logins.

The Manage Logins dialog box appears.

3. In the Login Name box, type the login name for the user.

4. In the Password box, type a password for the user. Note that a password is optional.

5. In the Default Language box, select a default language for the user.

6. Under Database Access, select the databases the login can access. To allow the user access, select the Permit box. To set a selected database as the user's default database, select the Default box.

7. Use the User box to change the name the login will use when accessing the database. The default is the user's login name. To change the name, type a different one in the User box.

Use the Alias box to set up aliases for the login in the database. To set an alias, select an alias name from the box.

Use the Group box to select a group for a selected login in the database. To choose a group, select a group name from the box. You cannot specify a group if an alias is chosen for the login ID.

8. Choose Add.

See Also

Modifying a Login

Dropping a Login


To Modify a Login
Modifying a Login

If you are the SA, you can change the password, the default language, and the database access for any login. If you are not the SA, you can modify only your own password.

To modify a login

1. In the Server Manager window, select the server to modify a login for.

2. From the Manage menu, choose Logins.

The Manage Logins dialog box appears.

3. In the Login Name box, select the login to modify.

4. Change the appropriate information.

5. Choose Modify.

See Also

Adding a SQL Server Login

Dropping a Login


Dropping a Login

If you no longer want a user to access a SQL Server, you must remove the user's login ID from the server. Only the SA can drop a login.

You cannot drop the login ID of a user who owns a database or database objects in the current database. You must transfer ownership of a database owned by the login ID and drop owned objects from the database before dropping the owner's login ID.

To drop a login

1. In the Server Manager window, select the server to drop a login for.

2. From the Manage menu, choose Logins.

The Manage Logins dialog box appears.

3. In the Login Name box, select the login to drop.

4. Choose Drop.

See Also

Adding a SQL Server Login

Modifying a Login


Adding a Database Device

Databases and transaction logs are stored in files called database devices. Before you can create a database, you must first create a device in which to store the database. A device can store many databases, and a database can be stored in several devices. Only the SA can create devices.

In addition to database devices, you can create dump devices in which to store a database and transaction log backups. For information on creating dump devices, see Creating a Dump Device.

To add a database device

1. In the Server Manager window, select the server to add a device to.

2. From the Manage menu, choose Devices.

The Manage Database Devices window appears.

3. Click the New Device button.

The New Database Device dialog box appears.

4. In the Name box, type a name for the device.

5. In the Drive box, select a drive to place the device on.

The device location automatically appears in the Path box. To change the location, click the Locate button and select a different location.

6. In the Size (MB) box, type a size, in megabytes, for the device.

Under Available Storage Space, a graph of available drive space is displayed.

7. To specify the device as the default device, select the Default Device check box.

8. To mirror the device, click the Mirroring button. A mirror name is automatically provided. To change the name, type a new name in the Mirror box. Choose Mirror.

9. Choose OK.

See Also

Dropping a Database Device

Modifying a Database Device

Creating a Dump Device


Dropping a Database Device

Dropping a database device frees up storage space on the server. When you drop a device, all databases on it are also dropped. After you drop a device, the file handle and the device number are freed. You can then delete the physical file from the hard disk without shutting down SQL Server. You can also immediately create a new device with the same device number and logical name as the dropped device.

To drop a database device

1. In the Server Manager window, select the server to remove a device from.

2. From the Manage menu, choose Devices.

The Manage Database Devices window appears.

3. From the graph, select the database device to drop.

4. Click the Delete Device button.

A confirmation box appears and lists all databases on the selected device. To confirm deletion, choose Drop. If the device contains no databases, the Confirm Device Delete dialog box appears. Choose Yes.

See Also

Adding a Database Device

Modifying a Database Device

Dropping a Dump Device


Modifying a Database Device

You can specify a database device as the default device; mirror, unmirror, or remirror a device; and increase the device size. Only the SA can modify database devices.

To modify a database device

1. In the Server Manager window, double-click the device to modify.

The Edit Database Devices dialog box appears.

2. To expand the database device size, type a larger number in the Size (MB) box. Note that the device size can be enlarged, but not reduced.

3. To specify the device as the default device, select the Default Device check box.

4. To modify the device mirroring, choose Mirroring.

The Database Device Mirroring dialog box appears. Modify as appropriate.

5. In the Edit Database Devices dialog box, choose OK.


Creating a Database

Creating a database allocates storage space for the database on a device. (You must create a device on which to store a database before you can create a database.) When you create a database, a transaction log is also created to record all transactions performed on that database. Store the transaction log on a different device from the database to simplify recovery and improve performance. If you do not specify a different device, the transaction log is created as part of the database.

To create a database

1. In the Server Manager window, select the server to create a database for.

2. From the Manage menu, choose Databases.

The Manage Databases window appears.

3. Click the New Database button.

The New Database dialog box appears.

4. In the Name box, type a name for the database.

5. In the Data Device box, select the device on which to place the database.

You can create a device for the selected database by choosing <new> and completing the information in the New Database Device dialog box.

6. In the Size (MB) box, type the amount of space, in megabytes (MB), to allocate on that device for the database.

7. In the Log Device box, select the device on which to place the transaction log.

You can create a device for the transaction log by selecting <new> and completing the information in the New Database Device dialog box.

8. In the Size (MB) box, type the amount of space, in MB, to allocate on that device for the transaction log.

The Devices On Server graph shows all available devices and the current space allocated.

9. If you plan to load a backup into the selected database before using it, choose the Create For Load option, which will create the database more rapidly. With this option the pages are not zero-initialized when the database is created. However, users other than the DBO cannot access the database until a backup is loaded into the database and the database option DBO Use Only is turned off. (This option can be turned off in the Options tab of the Edit Database dialog box.)

10. Choose OK.


Modifying a Database

You can increase the space allocated for a database or its transaction log by allocating more space on its current device or on a different device. You must increase the size by at least 1 megabyte. You can shrink the size of the database to reclaim unused space.

You can also set database options and statement permissions for the database. You can only view databases that you have access to.

To modify a database

1. In the Server Manager window, double-click the database to modify.

The Edit Database dialog box appears.

2. To expand the database, click the Expand button.

The Expand Database dialog box appears. Select a data device or log device on which to expand the database. In the Size (MB) box, type the size to allocate for the database or transaction log on the selected device, and choose OK.

3. To shrink the database, click the Shrink button.

The Shrink Database dialog box appears and displays the minimum size the database can shrink to. In the Database Size box, select the size to shrink the database to and choose OK.

4. To recalculate the space available on a database, click the Recalculate button. To ensure accurate numbers, recalculating is advised before shrinking a database.

5. To set database options, select the Options tab, change the appropriate information, and then choose OK.

6. To set statement permissions for the database, select the Permissions tab, change the appropriate information, and then choose OK.


Setting Database Options

You can set options to customize a database. You can set database options for user databases, and the Truncate Log on Checkpoint option for the master database.

To set database options

1. In the Server Manager window, double-click the database to modify.

The Edit Database dialog box appears.

2. Select the Options tab.

3. Set the appropriate options.

4. Choose OK.


Setting Statement Permissions

You can assign permissions that allow individual users or groups to perform various database tasks, such as creating databases, rules, defaults, tables, views, stored procedures, and backing up the database.

To set statement permissions

1. In the Server Manager window, double-click the database to modify.

The Edit Database dialog box appears.

2. Select the Permissions tab.

Note that CREATE DATABASE permissions can only be set from the master database.

3. Set the appropriate options.

4. Choose OK.


Dropping a Database

When you drop a database, that database and its objects are removed. Dropping a database frees the storage space that was allocated for it. After you drop a database, the login IDs that used that database as the default database will use master as the default database instead.

You cannot drop the master, model, or tempdb databases. You cannot drop a database that is open for reading or writing by any user. You cannot drop a database that is participating in replication by publishing some or all of its tables.

Only the DBO and the SA have permission to drop databases. This permission cannot be transferred.

To drop a database

1. From the Manage menu, choose Databases.

The Manage Databases window appears.

2. From the graph, select the database to drop.

3. Click Delete Database.


Adding Database Users

Before a user can use a database, you must add the user's login ID to the SQL Server and assign the user a username for that database. You can also add a user to a group or alias the user to another user in the database. If you don't specify a group name, the user is added only to the public group, which includes all users. If you did not add the database user when you added a login ID to SQL Server, you can use the Manage Users dialog box to add the user.

To add a database user

1. In the Server Manager window, select the server and the database to add the user to.

2. From the Manage menu, choose Users.

The Manage Users dialog box appears.

3. In the User Name box, type a name for that user in the database. The name can be the same as the login ID.

4. In the Login box, select the login name to map to the database username.

5. Optionally, to add the user to a group, in the Group box, select a group for the user.

All users belong to the public group. You have the option of adding the user to one other group.

6. Optionally, to map (alias) other logins to that username, under Available Logins, select the login and choose Add to move them to the Aliased Logins box.

7. Choose Add.

8. Choose Close.

See Also

Using Aliases

Creating Groups


Using Aliases

You can create an alias for a user in a database. An alias allows a user to access the database through an already existing username. For example, aliasing several logins to the DBO username allows those users access to the DBO's permissions.

To create an alias

1. In the Server Manager window, select the server and the database to create an alias for.

2. From the Manage menu, choose Users.

The Manage Users dialog box appears.

3. In the User Name box, select a username to create an alias for.

4. Under Aliases, select a login to map to the username.

5. Choose Add.

6. Repeat steps 4 and 5 for each login that you want to associate with the selected username.

7. Choose Modify.

To remove an alias

1. In the Server Manager window, select the database to remove an alias from.

2. From the Manage menu, choose Users.

The Manage Users dialog box appears.

3. In the User Name box, select the username that you want to remove aliases from.

4. Under Aliased Logins, select the login(s) to remove from the alias.

5. Choose Remove.

6. Choose Modify.

See Also

Adding Database Users


Dropping a Database User

Dropping a user removes the user from a database. Before dropping a user, you must drop all database objects the user owns.

To drop a database user

1. In the Server Manager window, select the server and the database to remove a user from.

2. From the Manage menu, choose Users.

The Manage Users dialog box appears.

3. In the User Name box, select the user to drop.

4. Choose Drop.

See Also

Adding Database Users


Creating Groups

Groups provide a convenient way to grant and revoke permissions to more than one user at the same time. Each user belongs to the public group, and can be placed in only one additional group at any time.

If you are using integrated security and have created groups with Windows NT, those groups are not automatically tied to database groups. If you want all members of a Windows NT group to be assigned to a database group, you must first assign explicit login IDs to all users in the Windows NT group, add those login IDs as users in the database, and then assign those users to the database group you create. Use SQL Security Manager to automatically create login IDs and a SQL Server group for a selected Windows NT group.

To create a group

1. In the Server Manager window, select the server and the database to add a group to.

2. From the Manage menu, choose Groups.

The Manage Groups dialog box appears.

3. In the Group box, type a name for the group.

4. Under Database Users, select users to add to the group, and then click the Add button under Database Users. If you make a mistake, select the user in the Users in Group box, and then choose Remove.

5. When you are finished, choose Add and then choose Close.


Modifying Groups

Groups can be modified by adding or removing users. Each user belongs to the public group, and can be placed in only one additional group at any time. When you remove a username from a group, that user no longer has permissions assigned to the group. You cannot remove a user from the public group.

To modify a group

1. In the Server Manager window, select the server and that database that contain the group to modify.

2. From the Manage menu, choose Groups.

The Manage Groups dialog box appears.

3. In the Group box, select the group to modify.

4. To add users, under Database Users, select users from the Users box, and then choose Add. To remove users, select users from the Users in Group box, and then choose Remove.

5. Choose Modify and then choose Close.


Dropping a Group

When you drop a group, all members explicitly become members of the public group.

To drop a group

1. In the Server Manager window, select the server and the database that contain the group to drop.

2. From the Manage menu, choose Groups.

The Manage Groups dialog box appears.

3. In the Group box, select the group to drop.

4. Choose Drop and then choose Close.


Querying a Database

Using Transact-SQL statements, you can query a database while performing other administrative tasks. When query results are ready, if you have selected the Switch to Results tab on Execute option in the Application tab of the Configure SQL Enterprise Manager dialog box, the results tab appears and becomes active. Or, when the query is completed, choose the Results tab to see the results.

To query a database

1. In the Server Manager window, select the server to query.

2. From the Tools menu, choose SQL Query Tool.

The Query window appears.

3. In the DB box, select the database to query.

4. In the Query tab, type the query using Transact-SQL syntax.

For help on Transact-SQL syntax, select the syntax and press SHIFT+F1, or from the Help menu, choose Transact-SQL Help. For help on specific objects, select the object name and press ALT+F1.

5. To execute the query, click the Execute Query button or press ALT+X.


Editing Query Results

You can cut, copy, paste, and delete the text displayed in the Results tab.

To edit query results

1. In the Server Manager window, select the server and the database to query.

2. From the Tools menu, choose SQL Query Tool.

The Query window appears.

3. In the Query tab, type a query, and then execute the query.

4. In the Results tab, select the text to edit. Then, from the Edit menu, choose the appropriate editing task.


Saving a Query to a File

You can save a query to a file for later use. Before you save the query, verify that the Query tab is active.

To save a query

1. In the Server Manager window, select the server and the database to query.

2. From the Tools menu, choose SQL Query Tool.

The Query window appears.

3. In the Query tab, type the query.

4. Click the Save Query/Result button. Or, from the File menu, choose Save As.

Queries are saved with the default file extension .SQL. To change the file extension for the query you are saving, type a new extension in the File Name box of the Save File dialog box. To change the default file extension for all queries, in the Application tab of the Configure SQL Enterprise Manager dialog box, under File Extensions, change the extension.


Saving Query Results

You can save query results to a file. You can edit the text before saving. Before you save query results, verify that the Results tab is active.

To save query results

1. In the Server Manager window, select the server and the database to query.

2. From the Tools menu, choose SQL Query Tool.

The Query window appears.

3. In the Query tab, type the query and then execute it.

4. When the Results tab is active, click the Save Query/Result button. Or, from the File menu, choose Save As.

Queries are saved with the default file extension .RPT. To change the file extension for the query results you are saving, type a new extension in the File Name box of the Save File dialog box. To change the default file extension for all query results, in the Application tab of the Configure SQL Enterprise Manager dialog box, under File Extensions, change the extension.


Starting a New Query

After you have executed a query, you can start a new query. Queries are listed in the Queries box. You can switch among the query windows at any time.

To start a new query

 Click the New Query button. Or, from the File menu, choose New. Or type CTRL+N.


Using a Saved Query

You can execute saved queries. Verify that the Query tab is active.

To use a saved query

1. In the Server Manager window, select the server and the database to query.

2. From the Tools menu, choose SQL Query Tool.

The Query window appears.

3. Click the Load SQL Script button. Or, from the File menu, choose Open.

4. Select the file and choose OK.

5. To execute the query, click the Execute Query button or press ALT+X.


Removing a Query/Result Set

You can remove a query that is listed in the Queries box of the Query window.

To remove a query and its results

1. In the Queries box, select the query to remove.

2. Click the Remove button.


Setting Query Flag and Format Options

You can set options to control how a query is processed each time you execute the query.

To set query options

1. In the Server Manager window, select the server and the database to query.

2. From the Tools menu, choose SQL Query Tool.

The Query window appears.

3.	Click the Query Options button.

The Query Options dialog box appears.

4.	In the Query Flags tab, under Query Options, select the appropriate options.

5.	Under Limits, in the Row Count box, type the number of rows the server should return before ending a query.

The default (0) returns all rows.

6.	Select the Format Options tab.

7.	In the Result Output Format box, select the format for results.

If you choose Other Delimiter, in the Delimiter box, specify the delimiter.

If you choose Column aligned, to align numeric output to the right, select the Right Align Numerics check box.

8. To display the PRINT statements output in the Results tab, select the Verbose Prints box. This option is only useful if you are running graphical Statistics I/O and/or Showplan.

9. If you do not want column headers to appear in the query results, clear the Print Headers check box.

10. To display the first three lines of the query in the Results tab, select Output Query.

11. Choose OK.

See Also

Setting Query System Options


Searching and Replacing Text

You can use the SQL Query Tool to search for text in the Query or Results tabs. You can also repeat the last search you made, and search and replace text.

To search for text

1. In the Server Manager window, select the server and the database to query.

2. From the Tools menu, choose SQL Query Tool.

The Query window appears.

3. Type and execute the query.

4.	Select the appropriate tab.

5.	From the Edit menu, choose Find.

The Find dialog box appears.

6.	Type the text to find, select whether to match the case, and choose a search direction.

7.	Choose Find Next.

To repeat the last search

1.	Select the appropriate tab.

2.	From the Edit menu, choose Repeat Last Find or press F3.

To replace text

1.	Select the appropriate tab.

2.	From the Edit menu, choose Replace.

The Replace dialog box appears.

3.	Type the text to find and the replacement text. Select whether to match the case, and then choose a search direction.

4. Choose Find Next.

5. To replace text, choose Replace. To replace all specified text without prompting, choose Replace All.


Using Graphical Showplan

Graphical Showplan shows the execution plan for a query. You can generate a query plan without executing the query. You can also generate a textual execution plan for a query.

To use Graphical Showplan

1.	In the Server Manager window, select the server and the database to query.

2.	From the Tools menu, choose SQL Query Tool.

The Query window appears.

3.	Type a query in the Query tab.

4.	Click the Display Showplan button.

To generate Showplan results without executing the query, click the No Execute button.

5.	Click the Execute Query button or press ALT+X.

6.	Select the Showplan tab.

See Also

Details About the Query Chart

Details About the Query Plan Steps


Details About the Query Chart

The query plan chart shows the name of the table(s) used, the method used to access the table(s), and the join path between tables. SQL Server uses one of three methods to access table(s):

The clustered index (indicated by a green border)

A specified index (indicated by a yellow border)

A table scan, in which no indexes are used and the table is scanned sequentially (indicated by a red border)


Details About the Query Plan Steps

The query plan contains three parts: the result number, the action, and steps representing subprocesses that occur when the query is executed. The steps indicate the path chosen by the optimizer to most efficiently process the query. Multistep processes usually occur when data must first be gathered and then sorted into a worktable. The action is the process occurring on tables listed in the query chart.

Action	Result
SELECT	Retrieves data from the tables listed.
SELECT (into worktable)	Retrieves data from the tables listed and puts the data into a worktable for further processing.
INSERT	Inserts data into the listed tables.
DELETE	Deletes data from the listed tables.
UPDATE	Updates data in the listed tables.
/Sorting	Sorts data in the listed tables.


Using Graphcial Statistics I/O
Using Graphical Statistics I/O

Graphical Statistics I/O depicts the amount of disk I/O required to access data for a query. A query must be executed to get Statistics I/O information, which includes Scan Count, Logical Reads, and Physical Reads (the No Execute option must be turned off). You can generate a textual description of Statistics I/O by choosing Show Stats I/O in the Query Options dialog box.

To use Graphical Statistics I/O

1.	In the Server Manager window, select the server and the database to query.

2.	From the Tools menu, choose SQL Query Tool.

The Query window appears.

3.	Type a query in the Query tab.

4.	Click the Display Statistics I/O button.

5.	Click the Execute button or press ALT+X.

6.	Select the Statistics I/O tab.


Scan Count

Indicates the number of times a table is accessed for information during a query. Generally, for single table queries, the table is scanned once. For "lookup tables" (tables that are joined during processing to provide more information), the scan count indicates the number of times the table was accessed.


Logical Reads

Indicates the number of index and data pages that were required to obtain data for the query. This number is not equal to the number of rows retrieved for a table, because a page of data can contain many rows.


Physical Reads

Indicates the number of logical reads that actually resulted in data access on the disk. The difference between logical and physical reads indicates the amount of data that was cached. Tables and worktables can appear multiple times to process all the data required for the query.


Scheduling a Task

SQL Enterprise Manager and the SQL Executive service enable you to schedule various server tasks, including replication, command execution, and Transact-SQL statement execution. You can schedule a task to occur one time, on a recurring schedule, or when initiated by an alert. A task can also be run immediately.

SQL Server processes also automatically create and schedule tasks. The system administrator can use SQL Enterprise Manager to modify, disable, delete, and immediately run user-created and SQL Server-created tasks. Other users can manage only the tasks they own.

To schedule a task

1. In the Server Manager window, select the server to schedule a task for.

2. From the Server menu, choose Tasks.

The Task Scheduling window appears.

3. Click the New Task button.

The New Task dialog box appears.

4. In the Name box, type a unique name for the task.

5. To create a task, but not schedule it, clear the Enabled check box.

6. In the Type box, select the type of task you are creating.

Note that if you choose Transact-SQL, the statement is limited to 255 characters and cannot contain embedded "go" commands.

7. In the Database box, select the database the task will be run against.

8. In the Command box, type the text of the task. The text can be a valid operating system command, the filename (including path) of an .EXE file, or a Transact-SQL statement.

9. Schedule the task.

To create the task but not schedule it for a specific time, under Schedule, select On Demand.

To run a task once, select One-Time and in the When box, specify the time.

To schedule a task to recur, select Recurring.

The Auto Start option is used by replication processes to have SQL Server automatically start a task when the SQL Executive service starts.

10. If you chose Recurring in step 8, choose Change and select a schedule.

The Task Schedule dialog box appears. Set the appropriate information, and then choose OK.

11. To set options for the task, including email notification, Windows NT application event log messages, and retry attempts, choose Options.

The TSQL Task Options dialog box appears.

12. Set the appropriate information, and then choose OK.

Note that you cannot set retry attempts for Transact-SQL tasks.

13. Choose Add.


Editing a Scheduled Task

You can modify information about a scheduled task, including the task name, the task type, command information for the task, the task schedule, and task options.

SQL Server processes created automatically can also be modified by the system administrator. Other users can modify only the tasks they own.

To edit a scheduled task

1. In the Server Manager window, select the server to edit a task for.

2. From the Server menu, choose Tasks.

The Task Scheduling window appears.

3. Select the task in the Task List, and then click the Edit Task button.

4. Change the appropriate information.

5. Choose Modify.


Removing a Scheduled Task

You can remove or temporarily disable a scheduled task.

To remove a scheduled task

1. In the Server Manager window, select the server to remove a task from.

2. From the Server menu, choose Tasks.

The Task Scheduling window appears.

3. Select the task in the Task List.

4. Click the Delete Task button.

To disable a scheduled task

1. In the Server Manager window, select the server to disable a task for.

2. From the Server menu, choose Tasks.

The Task Scheduling window appears.

3. Select the task in the Task List.

4. Click the Edit Task button.

The Edit Task dialog box appears.

5. Clear the Enabled check box.

Task information will be retained, but SQL Executive will not execute the task.

6. Choose Modify.


Viewing Task History

You can view information about a task's last run date, the duration of the last run, its status, whether email notification occurred, and messages encountered while running the task.

To view task history

1. In the Server Manager window, select the server to view task history for.

2. From the Server menu, choose Tasks.

The Task Scheduling window appears.

3. In the Task List, select the task to view history for.

4. Click the Task History button.

The Task History dialog box appears.

5. To clear the task history information, choose Clear All. (Only the task owner can do this.)


Viewing a List of Scheduled Tasks

You can view a list of tasks that are defined for a particular SQL Server.

To view a list of scheduled tasks

1. In the Server Manager window, select the server to view scheduled tasks for.

2. From the Server menu, choose Tasks.

The Task Scheduling window appears. The Task List tab displays a list of scheduled tasks.


Viewing a List of the Scheduled Tasks Currently Running

You can view a list of tasks that are currently running.

To view a list of currently running scheduled tasks

1. In the Server Manager window, select the server to view tasks for.

2. From the Server menu, choose Tasks.

The Task Scheduling window appears.

3. Select the Running Tasks tab.

Queued tasks are also shown in the Running Tasks tab. For subsystems (task types) other than TransactSQL, a task may be queued if it is scheduled to run but no free threads are available in the subsystem.


Creating a Trigger

A trigger is a stored procedure that is automatically invoked when data in a database changes. Triggers are frequently used to enforce the integrity of the database. A trigger executes automatically when a user attempts to modify data on a table specified in the trigger. Only one insert, one update, and one delete trigger can exist per table. If a trigger exists for the table and you create another trigger, the existing trigger is replaced.

To create a trigger

1. In the Server Manager window, select the server and the database to create a trigger for.

2.	From the Manage menu, choose Triggers.

The Manage Triggers window appears.

3.	In the Tables box, select the table to create the trigger for.

4. In the Triggers box, select <New>.

5.	Modify the Transact-SQL syntax in the window as appropriate, adding a trigger name and the text.

For help on the Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE TRIGGER.

6.	Click the Execute button to save the new trigger.

See Also

Modifying a Trigger

Dropping a Trigger


Modifying a Trigger

You can change the contents of an existing trigger.

To modify a trigger

1. In the Server Manager window, select the server and the database to modify a trigger for.

2. From the Manage menu, choose Triggers.

The Manage Triggers window appears.

3. In the Tables box, select the table that contains the trigger to modify.

4. In the Triggers box, select the trigger to modify.

5. Modify the Transact-SQL syntax in the window as appropriate.

For help on Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE TRIGGER.

6. Click the Execute button to save the modified trigger.

See Also

Creating a Trigger

Dropping a Trigger


Dropping a Trigger

You can remove an existing trigger.

To drop a trigger

1.	In the Server Manager window, select the server and the database to drop a trigger from.

2.	From the Manage menu, choose Triggers.

The Manage Triggers window appears.

3.	In the Tables box, select the table from which to drop the trigger.

4.	In the Triggers box, select the trigger to drop.

5.	From the Object menu, choose Drop and then choose Drop All.

See Also

Creating a Trigger

Modifying a Trigger


Creating a Stored Procedure

A stored procedure is a collection of SQL statements and optional control-of-flow language. Stored procedures are compiled the first time they are executed; their execution plans are stored so that subsequent execution is fast. Once defined, a stored procedure can be executed by name.

To create a stored procedure

1. In the Server Manager window, select the server and the database to create a stored procedure for.

2.	From the Manage menu, choose Stored Procedures.

The Manage Stored Procedures window appears.

3.	In the Procedures box, select <New>.

4.	Modify the Transact-SQL syntax in the window as appropriate, adding a stored procedure name and text.

For help on the Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE PROCEDURE.

5.	Click the Execute button to save the stored procedure.

See Also

Modifying a Stored Procedure

Dropping a Stored Procedure

Managing Extended Stored Procedure


Modifying a Stored Procedure

You can change the text of a stored procedure.

To modify a stored procedure

1. In the Server Manager window, select the server and the database to modify a stored procedure for.

2.	From the Manage menu, choose Stored Procedures.

The Manage Stored Procedures window appears.

3.	In the Procedures box, select the stored procedure to modify.

4.	Modify Transact-SQL syntax in the window as appropriate.

For help on the Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE PROCEDURE.

5.	Click the Execute button to save the stored procedure.

See Also

Creating a Stored Procedure

Dropping a Stored Procedure


Dropping a Stored Procedure

You can drop a stored procedure.

To drop a stored procedure

1. In the Server Manager window, select the server and the database to drop a stored procedure from.

2.	From the Manage menu, choose Stored Procedures.

The Manage Stored Procedures window appears.

3.	In the Procedures box, select the stored procedure to drop.

4. From the Object menu, choose Drop and then choose Drop All.

See Also

Creating a Stored Procedure

Modifying a Stored Procedure


Creating a View

A view is another way of looking at data in one or more tables. A view is usually a subset of columns from one or more tables in a database.

To create a view

1. In the Server Manager window, select the server and the database to create a view for.

2.	From the Manage menu, choose Views.

The Manage Views window appears.

3.	In the Views box, select <New>.

4.	Modify the Transact-SQL syntax in the window as appropriate, adding a view name and text.

For help on Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE VIEW.

5.	Click the Execute button to save the view.

See Also

Modifying a View

Dropping a View


Modifying a View

You can add or remove columns, or change the SELECT statement on which an existing view is based.

To modify a view

1.	In the Server Manager window, select the server and the database to modify a view for.

2.	From the Manage menu, choose Views.

The Manage Views window appears.

3.	In the Views box, select the name of the view to modify.

4.	Modify the Transact-SQL syntax in the window as appropriate.

For help on Transact-SQL syntax, from the Help menu, choose Transact-SQL Help and then search for CREATE VIEW.

5.	Click the Execute button to save the view information.

See Also

Creating a View

Dropping a View


Dropping a View

You can drop a view.

To drop a view

1. In the Server Manager window, select the server and the database to drop a view from.

2.	From the Manage menu, choose Views.

The Manage Views window appears.

3.	In the Views box, select the name of the view to drop.

4.	From the Object menu, choose Drop and then choose Drop All.

See Also

Creating a View

Modifying a View


Creating an Index

Indexes are created on columns to speed data retrieval and to impose uniqueness constraints. Two types of indexes can be created: clustered and nonclustered. You can create indexes while creating or modifying a table (by creating a primary key or a unique constraint), or by using the Manage Indexes dialog box. You must be the table owner to create an index.

This Help topic provides information on how to create an index using the Manage Indexes dialog box. For help on how to create a primary key or a unique constraint, see Creating a Table.

You can manage indexes for SQL Server 4.2x, but some options are unavailable.

To create an index using the Manage Indexes dialog box

1. In the Server Manager window, select the server and the database to create an index for.

2.	From the Manage menu, choose Indexes.

The Manage Indexes dialog box appears.

3.	In the Table box, select the table to create an index for.

4.	In the Index box, select (New Index) and then type a name for the index.

5.	Under Available Columns In Table, select the table column(s) to create the index for and then choose Add.

You can remove a column added by mistake by selecting it under Columns in Index [Key], and choosing Remove.

6.	Under Index Attributes, select the appropriate attributes for the index.

Note that each table can have only one clustered index.

You can adjust the Original Fill Factor size to see the estimated size of the index (shown under Index Size).

7.	Choose Build.

You can choose to build the index immediately, or to schedule the task.


Modifying an Index

You can change columns that are indexed or the index options. You can also rebuild an index at any time. When an index is rebuilt, SQL Server drops the existing index and creates a new one.

You must be the table owner to modify an index. You cannot modify primary key or unique constraints in the Manage Indexes dialog box.

To modify an index

1. In the Server Manager window, select the server and the database to modify an index for.

2.	From the Manage menu, choose Indexes.

The Manage Indexes dialog box appears.

3.	In the Table box, select the table that contains the index to modify.

4.	In the Index box, select the index to modify.

5.	Make the appropriate modifications.

You can adjust the Original Fill Factor to see the estimated size of the index (shown under Index Size).

6.	Choose Rebuild.

You can choose to rebuild the index immediately, or to schedule the task.


Renaming an Index

You can rename an index that you have created.

To rename an index

1. In the Server Manager window, select the server and the database to rename an index for.

2.	From the Manage menu, choose Indexes.

The Manage Indexes dialog box appears.

3.	In the Table box, select the table to rename an index for.

4.	In the Index box, select the index to rename.

5.	Choose Rename.

The Rename Index dialog box appears.

6. In the New Index Name box, type a new name for the index.

7. Choose OK.


Updating Index Statistics

You can update information about the distribution of key values in specified indexes. You can update information for indexes created in the Manage Indexes dialog box, and for primary key and unique constraints created while creating or modifying a table.

To update index statistics

1. In the Server Manager window, select the server and the database to update index statistics for.

2.	From the Manage menu, choose Indexes.

The Manage Indexes dialog box appears.

3.	In the Table box, select the table that contains the index to update statistics for.

4.	In the Index box, select the index to update statistics for.

5.	Choose Distribution.

The Index Distribution Statistics dialog box appears.

6. Choose Update.

You can choose to update the index statistics immediately, or to schedule the task.


Viewing Index Distribution Information

You can view information about the number of rows in a table, the number of steps in an index, the average row hits, and the date of the last update. You can also view the distribution steps for the index, the update index distribution statistics, and distribution information for primary key and UNIQUE constraints.

To view index distribution information

1. In the Server Manager window, select the server and the database to view index distribution information for.

2. From the Manage menu, choose Indexes.

The Manage Indexes dialog box appears.

3. In the Table box, select the table that contains the index to view distribution information for.

4. In the Index box, select the index to view distribution information for.

5. Choose Distribution.

The Index Distribution Statistics dialog box appears. To update statistics, choose Update.


Checking the Size of an Index

You can update information about the size of an index for more accurate numbers.

To check the size of an index

1. In the Server Manager window, select the server and the database to check the size of an index for.

2. From the Manage menu, choose Indexes.

The Manage Indexes dialog box appears.

3. In the Table box, select the table that contains the index to check the size for.

4. In the Index box, select the index.

5. Choose Check Size.

You can check the size immediately, or schedule the task.


Dropping an Index

You can drop an index using the Manage Indexes dialog box. When you drop an index, SQL Server removes that index from the database and reclaims the associated storage space. If you have created a primary key or unique constraint while creating or modifying a table, you can drop the constraint using the Manage Tables window.

To drop an index

1. In the Server Manager window, select the server and the database to drop an index from.

2.	From the Manage menu, choose Indexes.

The Manage Indexes dialog box appears.

3.	In the Table box, select the table that contains the index to drop.

4.	In the Index box, select the index to drop.

5.	Choose Remove.

You can choose to drop the index immediately, or to schedule the task.


Granting Object Permissions

Object permissions regulate the use of certain statements on certain database objects. You can grant permissions to users and groups for tables, views, and stored procedures. If the user or group already has permissions on those objects, granted new permissions will be added to the existing permissions. You can grant object permissions by user and group, and by object. You can grant permissions only for objects you own.

To grant object permissions by user and group

1. In the Server Manager window, select the server and the database to grant object permissions for.

2.	From the Object menu, choose Permissions.

The Object Permissions dialog box appears.

3. Select the By User tab.

4.	In the User/Group box, select a user or group.

If you choose a user, in the Member Of box, the group the user belongs to is displayed. If you choose a group, members of the group are listed in the Group Members box. Note that the DBO is not displayed, because you cannot grant or revoke permission from the DBO.

5.	Under Object Filters, to narrow the list of objects to assign permissions for, select the appropriate filters.

To grant permissions for all objects of the type selected, choose Grant All.

6.	Under Object, select the object to assign permission for and then select permission(s) to grant for that object.

To set column-level permissions for SELECT and UPDATE, select the Column Level Permissions check box and then, under Column, select the column and permissions to be granted for that column. Repeat for each column affected.

7.	Repeat steps 4 through 6 for each user or group to grant permission for.

8.	Choose Set.

To grant object permissions by object

1. In the Server Manager window, select the server and the database to grant object permissions for.

2.	From the Object menu, choose Permissions.

The Object Permissions dialog box appears.

3.	Select the By Object tab.

4.	In the Object box, select the object to grant permissions for.

To grant all permissions for this object to all users and groups of the selected database, choose Grant All.

5.	Under Group or User Name, select the name of the user or group to grant permission for and then select permissions to grant.

To set column-level permissions for SELECT and UPDATE, select the Column Level Permissions check box and then, under Column, select the column and the permissions to be granted for that column. Repeat for each column affected.

6.	Repeat steps 4 through 6 for each object to grant permissions for.

7.	Choose Set.

See Also

Revoking Object Permissions

Viewing Object Permissions


Revoking Object Permissions

You can prevent a user from having certain object permissions. You can revoke those permissions by user and group, or by object.

To revoke object permissions by user and group

1. In the Server Manager window, select the server and the database to revoke object permissions from.

2.	From the Object menu, choose Permissions.

The Object Permissions dialog box appears.

3. Select the By User tab.

4.	In the User/Group box, select a user or group.

To revoke all permissions from the selected user or group for all objects in the selected database, choose Revoke All.

5.	Under Object Filters, to narrow the list of objects to revoke permissions for, select the appropriate filters.

6.	Under Object, select an object name, then select the permissions to revoke. When you select a permission to revoke, a red circle and slash appear on top of its check box.

To revoke column-level SELECT and UPDATE permissions, select the Column Level Permissions check box. Under Column, select a column name, and then select the permissions to revoke. Repeat for each column that you will revoke permissions for. To revoke all column-level permissions, revoke the SELECT or UPDATE permissions associated with the object.

7.	Repeat steps 4 through 6 for each user/group to revoke permissions for.

8.	Choose Set.

To revoke object permissions by object

1. In the Server Manager window, select the server and the database to revoke object permissions from.

2.	From the Object menu, choose Permissions.

The Object Permissions dialog box appears.

3.	Select the By Object tab.

4.	In the Object box, select the object to revoke permissions for.

To revoke all permissions granted all users and groups for this object, choose Revoke All.

5.	Under Group or Username, select a name and then select the permissions to revoke. When you select a permission to revoke, a red circle and slash appear on top of its check box.

To revoke column-level SELECT and UPDATE permissions for that user or group, select the Column Level Permissions check box. Under Column, select a column name, and select the permissions to revoke. Repeat for each column to revoke permissions for. To revoke all column-level permissions, revoke the SELECT or UPDATE permissions associated with the selected object.

6.	Repeat steps 4 through 5 for each object to revoke permissions for.

7.	Choose Set.

See Also

Granting Object Permissions

Viewing Object Permissions


Viewing Object Permissions

You can view existing object permissions by user or group, or by object.

To view object permissions by user or group

1. In the Server Manager window, select the server and the database to view object permissions for.

2.	From the Object menu, choose Permissions.

The Object Permissions dialog box appears.

3. Select the By User tab.

4.	In the User/Group box, select a user or group to view permissions for.

Existing object permissions for this user or group are displayed.

5.	Repeat step 4 for each user or group to view permissions for.

To review object permissions by object

1. In the Server Manager window, select the server and the database to view object permissions for.

2.	From the Object menu, choose Permissions.

The Object Permissions dialog box appears.

3.	Select the By Object tab.

4.	In the Object box, select the object to view permissions for.

Permissions granted to users or groups are displayed.

5.	Repeat step 4 for each object to view permissions for.

See Also

Granting Object Permissions

Revoking Object Permissions


Generating SQL Scripts

You can generate SQL scripts for an entire database or for individual database objects. SQL scripts are the text of statements used to create database objects. Once you have generated scripts, you can run the scripts against another database to re-create the database objects. You can also generate scripts to create a record of how a database was created.

To generate SQL scripts

1. In the Server Manager window, select the server and the database to generate SQL scripts for.

2.	From the Object menu, choose Generate SQL Scripts.

The Generate SQL Scripts dialog box appears.

3.	To generate a script for the entire database, under Scripting Objects, choose All Objects.

To generate objects of a particular type, under Scripting Objects, select the objects to generate a script for.

To select individual objects to generate a script for, select the object from the list at the bottom of the dialog box, and then choose Add.

4.	Under Scripting Options, select options for the script.

5.	Under Security, select whether to generate the statements that add users, groups, logins, and permissions. Note that the SA login, and the repl_subscriber/publisher logins and users are not scripted.

6.	Select a script file option. To create one script, select Single File. To create one script for each object, select Per Object.

7.	To preview the script before generating it, choose Preview.

You can make changes to the text in the Object Scripting Preview dialog box.

8.	To generate the script, choose Script.

The Save As dialog box appears.

9.	Name the script and specify a directory to store the script in.

10.	Choose OK or Save.


Viewing Object Dependencies

You can view the objects that a selected object depends on, and the objects that depend on the selected object.

To view object dependencies

1. In the Server Manager window, select the object to view dependencies for.

2. From the Object menu, choose Dependencies.

The Object Dependencies dialog box appears. To view another object's dependencies, select that object in the Subject Object box.

See Also

Dropping Objects

Renaming Objects


Dropping Objects

You can remove an object if you have the appropriate permission for that object. You can drop multiple objects at one time by selecting them in the Server Manager window.

To drop an object

1. In the Server Manager window, select the object(s) to drop. You can select multiple objects by using SHIFT+Click or CTRL+Click.

2. From the Object menu, choose Drop.

The Drop Objects dialog box appears and lists objects, their owners, and their types.

3. To view the object's dependencies, select the object and choose Show Dependencies.

4. To confirm the object(s) is deleted, choose Drop All.

See Also

Viewing Object Dependencies

Renaming Objects


Renaming Objects

You can change the name of any object, except system tables and system objects. When you change the name of an object, you must rebuild views, stored procedures, and triggers that reference the object.

To rename an object

1. In the Server Manager window, select the object to rename.

2. From the Object menu, choose Rename.

The Rename Object dialog box appears.

3. In the New Name box, type the new name for the object.

4. Choose OK.

See Also

Viewing Object Dependencies

Dropping Objects


Creating a Rule

You can determine what data can be entered in a particular column of a table by creating a rule. You can bind the rule to a user-defined datatype so that columns that use the datatype automatically have the rule. In addition to rules, you can create constraints while creating and modifying a table that will enforce data rules for a column. The following procedure explains how to create a rule using the Manage Rules dialog box. For details about creating constraints, see Creating a Table.

To create a rule

1. In the Server Manager window, select the server and the database to create a rule for.

2. From the Manage menu, choose Rules.

The Manage Rules dialog box appears.

3. Select the Rules tab.

4. In the Rule box, type a name for the rule (for example, Age).

5. In the Description box, type the condition for the rule (for example, @age > 0).

6. Choose Add.

To bind the rule, select the Column Bindings or Datatype Bindings tab.


Binding a Rule to a Column

You can bind a rule to a table column so that data entered into the column adheres to that rule or is not added.

To bind a rule to a column

1. In the Server Manager window, select the server and the database that contain the rule to bind.

2. From the Manage menu, choose Rules.

The Manage Rules dialog box appears.

3. Select the Rule Column Bindings tab.

4. In the Tables box, select the table that contains the column to bind the rule to.

5. Under Column, select the column to bind the rule to.

6. Under Binding, select the rule to bind to that column.

7. Choose Bind.


Binding a Rule to a Datatype

You can bind a rule to a user-defined datatype. You can also bind the rule only to future columns the user-defined datatype will be associated with.

To bind a rule to a datatype

1. In the Server Manager window, select the server and the database that contain the rule to bind.

2. From the Manage menu, choose Rules.

The Manage Rules dialog box appears.

3. Select the Rule Datatype Bindings tab.

4. Under User-Defined Datatype, select the datatype to bind the rule to.

5. Under Binding, select the rule to bind.

6. To bind the rule only to future columns the datatype will be associated with, select Future Only.

7. Choose Bind.


Unbinding a Rule from a Column

You can unbind the rule from a table column.

To unbind a rule from a column

1. In the Server Manager window, select the server and the database that contain the rule to unbind.

2. From the Manage menu, choose Rules.

The Manage Rules dialog box appears.

3. Select the Column Bindings tab.

4. In the Tables box, select the table that contains the column to unbind the rule from.

5. Under Column, select the column to unbind the rule from.

6. Under Binding, select (none).

7. Choose Bind.


Unbinding a Rule from a Datatype

You can unbind a rule from a user-defined datatype. If you unbind the rule with Future Only selected, the rule will not be bound to future columns created with that user-defined datatype, but the rule will continue to be bound to existing columns.

To unbind a rule from a datatype

1. In the Server Manager window, select the server and the database that contain the rule to unbind.

2. From the Manage menu, choose Rules.

The Manage Rules dialog box appears.

3. Select the Column Bindings tab.

4. Under User-Defined Datatype, select the datatype to unbind the rule from.

5. Under Binding, select (none).

6. Choose Bind.


Dropping a Rule

You can drop a rule after you unbind it from all columns and user-defined datatypes.

To drop a rule

1. In the Server Manager window, select the server and the database that contain the rule to drop.

2. From the Manage menu, choose Rules.

The Manage Rules dialog box appears.

3. Select the Rules tab.

4. In the Rule box, select the rule to drop.

5. Choose Drop.


Creating a Default

You can create a default for a column or a user-defined datatype that causes SQL Server to automatically supply a specified value if the user does not explicitly provide a value for the column or the user-defined datatype. You can create a default while creating or modifying a table using the Manage Tables window or the Manage Defaults dialog box. The following procedure explains how to create a default using the Manage Defaults dialog box. For more information about creating a default while creating or modifying a table, see Creating a Table.

To create a default using the Manage Defaults dialog box

1. In the Server Manager window, select the server and the database to create a default for.

2. From the Manage menu, choose Defaults.

The Manage Defaults dialog box appears.

3. Select the Defaults tab.

4. In the Defaults box, type a name for the default (for example, datedefault).

5. In the Description box, type the condition for the default (for example, getdate( )).

6. Choose Add.

You can bind a default to a column or a datatype. To bind the default you have created, select the Column Bindings or the Datatype Bindings tab.


Binding a Default to a Column

You can bind a default to a table column. When no data is entered in that column, the default value is supplied.

To bind a default to a column

1. In the Server Manager window, select the server and the database that contain the default to bind.

2. From the Manage menu, choose Defaults.

The Manage Defaults dialog box appears.

3. Select the Column Bindings tab.

4. In the Tables box, select the table that contains the column to bind the default to.

5. Under Column, select the column to bind the default to.

6. Under Binding, select the default to bind to that column.

7. Choose Bind.


Binding a Default to a Datatype

You can bind a default to a user-defined datatype. You can also bind the default only to future columns the user-defined datatype is associated with.

To bind a default to a datatype

1. In the Server Manager window, select the server and the database that contain the default to bind.

2. From the Manage menu, choose Defaults.

The Manage Defaults dialog box appears.

3. Select the Datatype Bindings tab.

4. Under User-Defined Datatype, select the datatype to bind the default to.

5. Under Binding, select the default to bind.

6. If you want to bind the default only to future columns the datatype is associated with, select Future Only.

7. Choose Bind.


Unbinding a Default from a Column

You can unbind a default from a table column.

To unbind a default from a column

1. In the Server Manager window, select the server and the database that contain the default to unbind.

2. From the Manage menu, choose Defaults.

The Manage Defaults dialog box appears.

3. Select the Column Bindings tab.

4. In the Tables box, select the table that contains the column to unbind the default from.

5. Under Column, select the column to unbind the default from.

6. Under Binding, select (none).

7. Choose Bind.


Unbinding a Default from a Datatype

You can unbind a default from a user-defined datatype. If you unbind the default with Future Only selected, the default will not be bound to future columns created with that user-defined datatype, but will continue to be bound to existing columns.

To unbind a default from a datatype

1. In the Server Manager window, select the server and the database that contain the default to unbind.

2. From the Manage menu, choose Defaults.

The Manage Defaults dialog box appears.

3. Select the Datatype Bindings tab.

4. Under User-Defined Datatype, select the datatype to unbind the default from.

5. Under Binding, select (none).

6. Choose Bind.


Dropping a Default

You can drop a default after you unbind it from all columns and user-defined datatypes.

To drop a default

1. In the Server Manager window, double-click the default to drop.

The Manage Defaults dialog box appears.

2. In the Defaults box, select the default to drop.

3. Choose Drop.


Creating User Defined Datatypes
Creating User-defined Datatypes

You can create a user-defined datatype to specify what type of data a column can contain. You can then bind rules and defaults to the datatype.

To create a user-defined datatype

1. In the Server Manager window, select the server and the database to create a user-defined datatype for.

2. From the Manage menu, choose User-Defined Datatypes.

The Manage User-Defined Datatypes dialog box appears.

3. In an empty Name row, type a name for the user-defined datatype.

4. Under Datatype, select a system datatype on which to base the user-defined datatype.

5. Under Length, specify the length, if necessary, for the datatype.

6. If the datatype will allow null values, select Allow Nulls.

7. Under Default, if appropriate, select a default to bind to the user-defined datatype.

8. Under Rule, if appropriate, select a rule to bind to the user-defined datatype.

9. Repeat steps 3 through 8 for each new datatype.

10. Choose Apply Now to create the datatype(s) and leave the dialog box open, or choose OK to create the datatype(s) and close the dialog box.

See Also

Viewing User-defined Datatype Information

Dropping a User-defined Datatype


Viewing User Defined Datatype Information
Viewing User-defined Datatype Information

You can view information about the columns currently associated with the user-defined datatype.

To view user-defined datatype information

1. In the Server Manager window, double-click the user-defined datatype to view information for.

The Manage User-Defined Datatypes dialog box appears.

2. Select the datatype to view information for, and then choose Info.

The User Datatype Info dialog box appears.

See Also

Creating User-defined Datatypes

Dropping a User-defined Datatype


Dropping a User Defined Datatype
Dropping a User-defined Datatype

You can drop a user-defined datatype. However, you cannot drop a user-defined datatype already in use in a table.

To drop a user-defined datatype

1. In the Server Manager window, double-click the user-defined datatype to drop.

The Manage User-Defined Datatypes dialog box appears.

2. Select the datatype to drop.

3. Choose Drop.

See Also

Creating User-defined Datatypes

Viewing User-defined Datatype Information


Viewing Alerts

You can view information about alerts, including the alert name, whether the alert is enabled, whether the alert is based on an error number or a severity level, when the alert last occurred, how alert notification is to be handled, the task associated with the alert, and the number of times the alert occurred.

To view alert information

1. In the Server Manager window, select the server to view alerts for.

2. From the Server menu, choose Alerts.

The Manage Alerts window appears. The Alerts tab displays a list of alerts for the server.

See Also

Alerts Overview


Creating an Alert

SQL Enterprise Manager and SQL Executive provide a way to set alerts on errors that occur in SQL Server. If a selected error occurs, SQL Enterprise Manager will respond automatically to that error by running a defined task, and/or by sending an email and/or pager message to a specified operator. The alerts capability is supported for Microsoft SQL Server version 6.0 or later. You must be the system administrator to manage alerts.

To create an alert

1. In the Server Manager window, select the server to create an alert for.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	Click the New Alert button.

The New Alert dialog box appears.

4.	In the Name box, type a name for the alert.

Enabled is selected by default. To create the alert but not have it take effect immediately, clear the Enabled check box.

5.	Under Alert Definition, select Error Number or Severity and fill in or type the appropriate information.

To select from a list of error messages or to create a custom error message, select Error Number, select the Locate button, and complete the Manage SQL Server Messages dialog box that appears.

6.	To narrow the alert notification criteria further, select a Database Name and/or type in a character string that the error message should contain to raise the alert.

7.	Under Response Definition, specify the action the system will take when the defined alert occurs.

You can choose to have a task run when the alert is raised. The task must be a nonreplication task scheduled for on-demand execution. You can also specify the notification message to be sent to an operator when the alert is raised, and choose to include the actual text of the error message in the notification. You can also set a delay for recurring alert responses, so that an operator is notified only once during a specified period, even if the alert occurs more than once.

8.	Under Operators To Notify, select the operator(s) to receive the alert and the method of notification, either email or pager.

You can create an alert before you add operators. Once you have created the alert, in the Manage Alerts window, select the Operators tab and then choose New Operator. In the New Operator dialog box, assign the alert to the operator you create.

9.	Choose OK.

See Also

Alerts Overview


Editing an Alert

You can change information about an alert, including whether it is enabled, which operators to notify, and the response notification method. You can also redefine the alert, or view information about the most recent alert occurrences.

To edit an alert

1. In the Server Manager window, select the server to edit an alert for.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	In the Alerts tab, select the alert to edit.

4.	Click the Edit Alert button.

The Edit Alert dialog box appears.

5.	Change the appropriate information.

6.	Choose OK.

See Also

Alerts Overview


Dropping an Alert

You can remove an alert, or you can disable an alert without deleting it.

To drop an alert

1. In the Server Manager window, select the server to remove an alert from.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	In the Alerts tab, select the alert to remove.

4.	Click the Delete Alert button.

To temporarily stop an alert from occurring

1. In the Server Manager window, select the server to disable an alert for.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3. In the Alerts tab, select the alert to drop.

4. Click the Edit Alert button.

The Edit Alert dialog box appears.

5. Clear the Enabled check box.

6. Choose OK.

See Also

Alerts Overview


Viewing Operators

You can view information about the operators (the people designated to receive an alert notification) defined for a server. You can view the operator name, whether the operator is enabled for alert notifications, the operator email address and pager number, and the last email and/or pager notification the operator received.

To view operator information

1. In the Server Manager window, select the server to view operator information for.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	Select the Operators tab.

A list of operators and detailed information is displayed.


Creating an Operator

An operator is a person to be notified, through email and/or a pager (via email), that an alert has occurred. When you create an operator, you add information about the operator, including an email address, a pager email name, and the schedule of times the operator is available to respond to a page. In addition, you can assign specific alerts to the operator.

To create an operator

1. In the Server Manager window, select the server to create an operator for.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	Select the Operators tab.

4.	Click the New Operator button.

The New Operator dialog box appears.

5.	In the Name box, type the name of the operator.

Enabled is selected by default. To create the operator but not activate the operator to receive alerts, clear the Enabled check box.

6.	Under Email Notifications, type the email name for the operator.

If SQL Enterprise Manager is running on a client computer (a computer where SQL Server has not been installed) and if the Microsoft Mail client has been started, you can use the mail integration features of SQL Enterprise Manager to browse the address book of Microsoft Mail. Additionally, when you click the Test button, SQL Enterprise Manager will attempt to resolve the entered email name or (as appropriate) pager email name to the closest matching "friendly" email name. Note that this is available only on computers running the 32-bit version of the Microsoft Mail client.

To send a test email, choose Test.

7.	Under Pager Notifications, type a pager email name, and then select the appropriate pager schedule information.

To send a test page, choose Test.

8.	To assign alerts to the operator, select an alert under Alerts Assigned To This Operator, and then select the type of notification.

9.	Choose OK.


Editing Operator Information

You can modify information for an established operator (a person to be notified when an alert occurs), including the operator name, whether the operator is enabled to receive alert information, the operator's email address and pager email name, operator schedule information, and the alerts assigned to that operator. You can also view information about the most recent notifications sent to the operator.

To edit operator information

1. In the Server Manager window, select the server to edit operator information for.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	Select the Operators tab.

4.	Select the operator to modify information about.

5.	Click the Edit Operator button.

The Edit Operator dialog box appears.

6.	Modify the appropriate information.

7.	Choose OK.


Dropping an Operator

You can remove an operator, or you can disable an operator from receiving further notifications without deleting the operator.

To drop an operator

1. In the Server Manager window, select the server to remove an operator from.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	Select the Operators tab.

4.	Select the operator to drop.

5.	Click the Delete Operator button.

To temporarily stop an operator from receiving alerts

1. In the Server Manager window, select the server to remove an operator from.

2.	From the Server menu, choose Alerts.

The Manage Alerts window appears.

3.	Select the Operators tab.

4.	Select the operator.

5.	Click the Edit Operator button.

The Edit Operator dialog box appears.

6.	Clear the Enabled check box.

7.	Choose OK.


Setting Alert Engine Options

Alert engine defaults include designating a "fail-safe operator" and specifying an unhandled event-forwarding server. A fail-safe operator is notified if all attempts to notify other operators via pager fail. An unhandled event-forwarding server allows you to set alerts on one main server to receive SQL Server Windows NT application event logs forwarded by other servers. You can also specify the format of the To, CC, and Subject lines of pager email messages by defining templates.

To set alert engine options

1. In the Server Manager window, select the server to set alert engine options for.

2. From the Server menu, choose Alerts.

The Manage Alerts window appears.

3. Click the Alert Engine Options button.

The Alert Engine Options dialog box appears.

4. To designate a fail-safe operator, select the Fail-Safe tab, select the operator to notify, and then specify the method by which to notify the operator.

The pager fail-safe operator will be notified if all pager notifications for an alert fail. Reasons for such a failure can include (but are not limited to) incorrect pager addresses, a problem with the mail system, or a "hole" in the pager schedules of the intended page recipients.

The fail-safe operator can be notified via email or pager (subject to necessary address information being defined for the operator). Emails or pagers sent to the fail-safe operator have "(Fail-Safe)" at the beginning of the Subject line, to distinguish them from normal notifications.

5. To specify an unhandled event-forwarding server, select the server to receive forwarded events, and specify the type of events to forward to that server. If a desired server does not appear in the list, choose New Forwarding Server from the list and complete the Register Server dialog box.

The Unhandled SQL Server Event Forwarding Server is a remote server designated to receive unhandled SQL Server events on the local server (events for which there are no locally defined alerts). Once the event is placed in the remote server's Windows NT application event log, SQL Executive on the remote server's computer can process the events as if they had been generated by the local SQL Server.

6. To restrict events that are forwarded to the unhandled SQL Server event forwarding server, select a severity level in the Forward Events For Errors With Severity Of or Above box.

7. To specify the format of the To, CC, and Subject lines of a pager email message, select the Pager Email tab and fill in the appropriate information.

8. To send only the subject line in a page, clear the Include Body of Email in Notification Page box.

9.	Choose OK.


Creating a Table

A table contains your database data. When you create a table, you name its columns and supply a datatype for each column. You also specify a primary key for the table, any foreign keys for the table, whether the table has an identity column, and any UNIQUE or CHECK constraints.

SQL Server can have as many as 2 billion tables per database and 250 columns per table. The number of rows and total size of the table are limited only by the available storage.

To create a table

1. In the Server Manager window, select the server and the database to create a table for.

2. From the Manage menu, choose Tables.

The Manage Tables window appears.

3. In the Tables box, select <New>.

4. In the Column Name box, type the name for the column.

The column name must follow the rules for identifiers and must be unique to a table.

5. In the Datatype box, select the datatype for the column.

If appropriate for the datatype, type a size in the Size box.

6. To allow null values for the column you are defining, select the Nulls box.

7. To provide a default value for the column, in the Default box, type the value.

8. To add primary and foreign keys, specify an identity column, and create constraints, click the Advanced Features button.

9. In the Primary Key/Identity tab, select the column(s) to act as the table's primary key. Then choose Add.

When you create a primary key, a unique index is created on the column(s). By default, a clustered index is created. To create a nonclustered index instead, select Non-Clustered.

10. To specify an identity column, in the Identity Column box, select the column. In the Seed Value box, type the value to be assigned to the first row in the table. In the Increment box, type the value to add to the previous value to generate the next value. For example, if you specify a seed value of 5 and an increment of 5, the first row contains 5, the second 10, and so on.

11. To create a foreign key for the table, select the Foreign Keys tab.

In the Foreign Keys box, select <new> and type a name for the foreign key. (The name is optional. If you do not provide a name, SQL Server will automatically generate one.) In the Referenced Table box, select the table that contains the primary key or the UNIQUE constraint the foreign key is associated with. The Key Columns box lists the columns in the referenced table that make up the primary key or UNIQUE constraint. In the Foreign Key Columns box, select the table column(s) associated with the foreign key columns. Then choose Add.

12. To create a UNIQUE constraint for the table, select the Unique Constraints tab.

In the Unique Constraint box, select <new> and then type a name for the constraint. (The name is optional. If you do not provide a name, SQL Server will automatically generate one.) In the Column Name box, select the column(s) to create a UNIQUE constraint for, and then select whether to create a clustered or nonclustered index. Then choose Add.

13. To create a CHECK constraint for the table, select the Check Constraints tab.

In the Check Constraint box, select <new> and then type a name for the constraint. (The name is optional. If you do not provide a name, SQL Server will automatically generate one.) In the Constraint box, type the text of the constraint. CHECK constraints adhere to the same policies as rules; however, CHECK constraints are automatically bound to the columns on which they are defined and can reference another column(s). To prevent the specified CHECK constraint from being replicated, select the Not for Replication box. Then choose Add.

14. To save the new table, click the Save Table button.

The Specify Table Name dialog box appears. In the New Name box, type a name for the table.

15. Choose OK.

See Also

Modifying a Table

Dropping a Table


Modifying a Table

You can add new columns to an existing table, rename a table column, add a column default, or create unique or check constraints. You can also add or remove primary and foreign keys.

To modify a table

1. In the Server Manager window, select the server and the database to modify a table for.

2. From the Manage menu, choose Tables.

The Manage Tables dialog box appears.

3. In the Tables box, select the name of the table to modify.

4. Modify the appropriate information.

5. Click the Save Table button.

See Also

Creating a Table

Dropping a Table


Dropping a Table

Dropping a table removes the table, its contents, and all indexes and permissions associated with the table. You must be the table or database owner to drop it. You cannot drop a table while it is being read or written to by a user or a front-end program.

To drop a table

1. In the Server Manager window, select the server and the database to drop a table for.

2. Select the table to drop.

3. From the Object menu, choose Drop.

See Also

Creating a Table

Modifying a Table


Running a Task Immediately

A task in the task list can be run immediately by the task owner. For the task to run, the SQL Executive service must be running on the target server.

To run a task immediately

1. In the Task List, select the task to run.

2. Click the Run Task button.

See Also

Scheduling a Task

Editing a Scheduled Task

Removing a Scheduled Task


Alerts Overview

SQL Enterprise Manager and SQL Executive provide an easy way to set alerts on SQL Server events. If a selected event occurs, SQL Executive will respond automatically, either by running a task that you have defined or by sending an email and/or a pager message to an operator you have specified.

If the proper SQL Server options are set, SQL Server events are written to the Windows NT application event log. SQL Executive constantly monitors the Windows NT application event log, waiting for events from SQL Server. When an event occurs, SQL Executive compares the event details against the alerts previously defined by the SQL Server administrator. If it finds a match, SQL Executive carries out the defined response.

For each alert, you can have SQL Executive respond by sending email and/or pager notifications to one or more operators. Additionally, you can have SQL Executive run a task, if appropriate. The task can be a Transact-SQL command, a batch file, or a Windows NT program.

If you use paging as a notification method, Microsoft Mail (provided with Windows NT) must be configured to send an email that results in a page being sent. Email interfaces to pager services are provided by many telecommunications companies, and generally require only the ability to route email messages (for example, using Microsoft Mail's External Mail program) to a post office at the pager provider's site. For more information, contact your telecommunications provider.

Whether you are using email or pager-via-email notifications, you must be running Microsoft Mail on the same computer as SQL Server. For information on setting up Microsoft Mail, see your manuals for Microsoft Windows NT and the Microsoft SQL Server Administrator's Companion in Microsoft SQL Server Books Online.

SQL Server events can be processed by the local SQL Executive service or can be forwarded (by the local SQL Executive service) to the Windows NT application event log on another computer that is running SQL Server version 6.0 or later. SQL Server events can also be processed by a combination of the two: for example, some events could be processed locally and some processed remotely.

For more information on alerts, and on tips and troubleshooting, see the Microsoft SQL Server Administrator's Companion.


Register Server Dialog Box

Use this dialog box to register a server in SQL Enterprise Manager. Once a server is registered, its name appears in the Server Manager window and you can perform tasks for that server.

Server

Specifies the server name. The server must already be installed, and you must have permission to access the server. All servers you have registered are listed in the Server box. If you are running both 4.2x and 6.x SQL Servers at the same time on the same machine, the servers must use different names to be supported by SQL Enterprise Manager.

SQL Enterprise Manager works with SQL Server versions 4.2x and 6.0 or later. However, to use SQL Enterprise Manager with SQL Server version 4.2x, you must first load the SQL script SQLOLE42.SQL into the 4.2x server. You can find a copy of this script in the \INSTALL directory of the computer that contains SQL Server version 6.0 or later. Note that not all features of SQL Enterprise Manager are enabled for use with SQL Server version 4.2x.

Login Information

Specifies the login information SQL Enterprise Manager should use when logging in to the server.

Use Trusted Connection

Uses Windows NT security to validate the user; a password does not need to be provided. This option requires SQL Server to be configured for integrated or mixed security.

Use Standard Security

Uses standard security for the server, requiring a login ID and a password to connect to the server in the Server Manager window. SQL Enterprise Manager stores the login ID and the password. You need not provide them each time you connect to the server.

Server Group

Specifies a group for the server. If you do not select a group, the server becomes a default group member (SQL Server 6.5). You can create a new group by clicking the Server Groups button.

Server Groups

Displays the Manage Server Groups dialog box, which is used to add a new server group.

Display Server Status in Server Manager

Displays the status of the server (started, paused, stopped, or indeterminate) in the Server Manager window. To not display the status, clear the check box. To set the polling interval for the status information, from the Tools menu, choose Preferences/Configure. Note that polling uses the Windows NT Service Control Manager, which is unavailable on Windows 95, making server status information unavailable.

Register

Registers the SQL Server with SQL Enterprise Manager. The server is displayed in the Server Manager window.

Remove

Removes the SQL Server from the list of servers in the Server Manager window. When you remove a server, you cannot use SQL Enterprise Manager to administer that server.

List Servers

Displays the Select Server dialog box, which lists the servers in your domain. (Note that you cannot list servers on Windows 95.)


Manage Server Groups Dialog Box

Use this dialog box to add, rename, or remove a server group. Server groups provide a convenient way to organize SQL Servers in the Server Manager window.

Name

Specifies the name of the server group. To create a server group, type the name of the new server group.

Level

Specifies whether the group will appear at the top level of the tree in the Server Manager window, or as a subgroup of another server group. If you are creating a server group, select Top-Level Group or Sub-Group Of and then select the subgroup your server group will belong to. If you are changing the name of a group or removing a group, first select the group in the Level box.

Add Group

Adds the group.

Remove Group

Removes the group. You cannot remove a group that contains subgroups or servers.

Change Name

Changes the name of the group selected in the Level box to the name you type in the Name box.


Server Options Tab

Use this tab to modify server options.

Root Directory

Displays the path for the root directory containing SQL Server files.

Master Database Path

Displays the directory path and the filename for the MASTER database device (MASTER.DAT).

Error Log Path

Displays the path and the filename of the SQL Server error log file. To use the SQL Server error log instead of or in addition to the Windows NT application event logging service, type the directory path for the error log file. If this entry is blank, a SQL Server error log is not written.

Auto Start Server At Boot Time

Starts SQL Server automatically whenever you boot the server computer.

Auto Start Executive At Boot Time

Starts SQL Executive automatically whenever you boot the server computer.

Auto Start Mail Client

Displays whether a mail client session starts automatically whenever SQL Server starts. This option is set using the SQL setup program and cannot be changed in SQL Enterprise Manager.

Auto Start License Logging

Turns on logging information for licensing purposes.

Windows NT Event Logging

Displays whether SQL Server and SQL Executive events are logged in the Windows NT application event log. This option is set using the SQL setup program and cannot be changed in SQL Enterprise Manager.

Performance Monitor Integration

Displays whether SQL Server performance statistics are available to the Windows NT Performance Monitor. This option is set using the SQL setup program and cannot be changed in SQL Enterprise Manager. However, you can change the method of integration. When using either mode, make the Performance Monitor Refresh Interval long enough to avoid overloading SQL Server with requests.

Direct Response Mode

Specifies that SQL Server statistics are gathered separately from the SQL Server Statistics display. When this option is selected, data is available immediately to the Performance Monitor and response time is optimized; however, the statistics display is one period behind the statistics retrieved.

On Demand Mode

Specifies that the Performance Monitor requests and waits for the latest data from SQL Server during each refresh period. When this option is selected, you receive the latest data, but response time for the Performance Monitor is slower than with Direct Response Mode.

xp_cmdshell Impersonates Client

Restricts use of the xp_cmdshell extended stored procedure to users with administrator privileges on the Windows NT-based computer where SQL Server is running. When this option is selected, only users who have connected to SQL Server via a trusted connection and are members of the local Administrators group on that computer are allowed to use xp_cmdshell. By default, xp_cmdshell runs in the security context of the server. When this option is selected, the commands run by xp_cmdshell continue to execute in the server's security context.

Parameters

Displays the Server Parameters dialog box, which is used to set SQL Server startup options.

Tape Support

Displays the Server Options dialog box, which is used to set the waiting interval used when trying to read a tape.

Mail Login

Displays the Server Options dialog box, which is used to provide a mail login name and a mail login password to allow the system to auto start the mail client.

Apply Now

Applies the information you have specified.


Server Options Dialog Box

Use this dialog box to set tape support options for SQL Server.

Wait Indefinitely

Specifies to wait indefinitely when trying to read a tape that has not been loaded in the drive.

Try Once and Quit

Specifies to try once and then quit when trying to read a tape that has not been loaded in the drive.

Try for x Minutes

Specifies how many minutes to wait when reading a tape that has not been loaded in the drive.


Server Parameters Dialog Box

Use this dialog box to set startup parameters for SQL Server.

Parameter

Specifies the parameter to add.

Existing Parameters

Lists the startup parameters already set.

Add

Adds the parameter typed in the Parameter box.

Remove

Removes the parameter selected in the Existing Parameters box.


SQLMail Configuration Dialog Box

Use this dialog box to specify your Windows NT mail client login name and password so that you can use SQL Server's SQLMail. To better identify the origin of SQL Server mail messages, you can set up a mail account for the SQL Server with a descriptive name such as SQL Server Mail.

If you plan to use SQLMail, first set up your Windows NT-based mail client and verify that it works. Then you can configure SQL Server to start a SQLMail session automatically, or you can use extended stored procedures to start SQLMail from the command line. If you are configuring SQL Server to start a mail client session automatically, you must supply a mail login name and password. If you are starting a mail client session from the command line, you can supply your login name and password in this dialog box to avoid having to type them at the command line. Note that SQLMail will "piggyback" an existing mail client session, if one is running.

Use this dialog box to change your saved password whenever you change your password on the mail client.

Mail Login Name

Specifies the name used to log in to the Windows NT-based mail client.

Mail Password

Specifies the password used to log in to the Windows NT-based mail client. Note that if you change your Windows NT-based mail client password, you must also change your password here.

Confirm Password

Verifies the password you typed in the Mail Password box.


Security Options Tab

Use this tab to change login security configuration options.

Login Security Mode

Specifies the security mode for the SQL Server.

Standard

Sets the login security mode to standard. In this mode, SQL Server manages its own login validation process for all connections (except client applications that explicitly request integrated security over the trusted connections).

Windows NT Integrated

Sets the login security mode to Windows NT integrated. In this mode, SQL Server uses Windows NT authentication mechanisms for all connections. Only trusted connections are allowed into SQL Server. The login name and the SQL Server password submitted in the login request from a DB-Library or Open Database Connectivity (ODBC) client application are always ignored by SQL Server. Network users assigned user-level privileges to SQL Server log in using their network username or the default login ID (if the network username is not found in syslogins). Network usernames assigned system administrator-level privilege log in as SA. With this option, only named pipes or multi-protocol clients are supported. If you have installed additional Net-Libraries, this option is not available.

Mixed

Sets the Login Security Mode to Mixed. In this mode, SQL Server allows both trusted and nontrusted connections. For trusted (named pipes or multi-protocol) connections, SQL Server examines the requested login name as specified by the client DB-Library or ODBC application. If the login name matches the user's network username, or if the login name is null or blank spaces, SQL Server first tries the Windows NT Integrated login rules. If this fails, SQL Server uses the Standard rules. If the requested login name is any other value, the user must supply the correct SQL Server password, and SQL Server handles the login using the Standard rules. All login requests from nontrusted connections are handled using the Standard security rules.

Default Login

Specifies the SQL Server login name for an authorized user on trusted connections when the user's network username does not appear in syslogins. If this box is left blank, users with no network usernames in syslogins will be denied access to the server, even if they have user privileges. Note that the SA must add a login ID of the name specified as the default login; a login ID is not automatically created.

Default Domain

Specifies the Windows NT domain name that is the default for matching network usernames to SQL Server login names. Because the same network username can be defined in two different domains for two different users, and both can be authorized to access SQL Server, SQL Server must distinguish between the two names in the login process for a trusted connection. For network usernames defined in domains other than the specified default, SQL Server adds the domain name and a map character such as an underscore (_) to the network username before checking in syslogins.

Set HostName to UserName

Replaces the host name from the client login record with the Windows NT network username for users under trusted connections. If this box is selected, for example, the network username will appear in the output of the sp_who system stored procedure.

Audit Level

Specifies the audit trail for successful or failed logins, or both.

Successful Login

Records successful login attempts along with other SQL Server information in the Windows NT application event log, the SQL Server error log, or both, depending on how you configure logging for your SQL Server.

Failed Login

Records failed login attempts along with other SQL Server information in the Windows NT application event log, the SQL Server error log, or both, depending on how you configure logging for your SQL Server.

Mappings

Specifies how to map special characters to valid SQL Server characters. When you use any of the integrated security options or client requested trusted connections, mappings let you indicate how to map these characters in SQL Server.

Map _

Specifies which special Windows NT character will be mapped to the valid SQL Server character underscore (_). (Windows NT usernames include certain characters that are not valid in SQL Server login IDs, such as hyphens, spaces, and periods.)

Map #

Specifies which special Windows NT character will be mapped to the valid SQL Server character pound sign (#). (Windows NT usernames include certain characters that are not valid in SQL Server login IDs, such as hyphens, spaces, and periods.)

Map $

Specifies which special Windows NT character will be mapped to the valid SQL Server character dollar sign ($). (Windows NT usernames include certain characters that are not valid in SQL Server login IDs, such as hyphens, spaces, and periods.)

Apply Now

Applies the specified information.


Configuration Tab

Use this tab to set configuration options (sp_configure) for a selected server.

Configuration

Lists configuration options that can be set.

Minimum

Displays the minimum value for the configuration option.

Maximum

Displays the maximum value for the configuration option.

Running

Displays the value currently in use for the configuration option.

Current

Displays the current value for the configuration option. To change the value, type a new value in the box.

Description

Describes how the configuration option is used. Also notes whether the option takes effect immediately when the dialog box is closed, or if you must restart the server to set the new value.

Apply Now

Applies the information you have specified. For values that take effect immediately, you must close the Server Configuration/Options dialog box to set the new value.


Attributes Tab

Use this tab to view the attributes of a selected SQL Server. You cannot change this information.

Attribute

Lists SQL Server attributes.

Value

Displays the attribute value set.


Current Activity Window

Use this window to view information about current connections and object locks for a selected SQL Server. You can get detailed information about the statement a process is running, send a message to the process owner, or end a process.

User Activity Tab

Displays information about current connections and locks. The items in the User Activity tab are grouped by login ID. Each item displays an icon indicating the type of activity, the process ID, the user, the status, the name of the connected computer, and (if present) locks. A red background indicates a blocking lock.

Detail Activity Tab

Displays more detailed information about the process number, status, locks, and commands the active users are running.

Status

Specifies the status of the activity.

Process ID

Specifies the process identification number (spid) of the process.

Login ID

Specifies the login ID of the user executing the command. System indicates SQL Server.

Database

Displays the name of the database the process is running against.

User Name

Displays the database username of the user executing the command.

Command

Specifies the command currently being executed.

Host

Specifies the computer from which the command was initiated.

Application

Names the application program.

Blocked By

Specifies the process ID of a blocking process, if any. A blocking process (which can have an exclusive lock) holds resources that another process needs.

Blocking

Shows whether or not the process is blocking another. Zero indicates that it is not blocking; any other number indicates that it is blocking.

Lock Type

Specifies the type of lock. "No locks" indicates no locks are present.

Locked Object

Names the locked object.

Group Name

Names the database group the user belongs to.

CPU Usage

Specifies cumulative CPU time for the process.

Physical I/O

Specifies cumulative disk reads and writes.

Host Process

Specifies the workstation process ID number.

Object Locks Tab

Displays information about which objects are locked, the types of locks, the process ID of the process holding the lock, the user, the type of statement that is being executed against the locked object, and the name of the connected computer. A red background indicates a blocking lock.

 More Info

Displays the Process Information dialog box, which provides more information about a selected process.

 Send Message

Displays the Send Message dialog box, which is used to send a message to the selected user.

 Kill Process

Ends the selected process.

 Refresh on-screen information

Refreshes all lists in the Current Activity window.

 Display Legend

Displays or hides a legend for the icons in the lists.


Send Message Dialog Box

Use this dialog box to send a message to a selected user.

Message

Displays the message text.

Destination

Specifies where the message will be sent. Note that this option is available only for Microsoft networks. It will use the Net Send command to send the message.

Using Username

Specifies the message will be sent to the computer with the username specified.

Using Hostname

Specifies the message will be sent to the computer with the hostname specified. You can send the message to a different computer by typing a computer name in the Hostname box.

To send a message to all currently logged on users, type an asterisk (*). The message will be sent to any computer that has a network connection to the current server.

Note that Hostnames are limited to 10 characters. If the connection you selected supplied its computer name as its hostname, only the first 10 characters of the computer name will be shown. As a result the message may not be sent or be sent to the wrong computer.

Send

Sends the message.


Process Information Dialog Box

Use this dialog box to view detailed process information about a selected process.

Last Statement

Displays the first 255 bytes of the text of the statement run by the selected process.

Resource Usage

Displays the cumulative CPU time for the process, and cumulative disk reads and writes.

Kill Process

Terminates the process.

Send Message

Displays the Send Message dialog box, which is used to send a message to the selected user.

Refresh

Refreshes the information in the dialog box.


Configure Tab
Connection Tab

Use this tab to set connection preferences for SQL Enterprise Manager.

Timeouts (Seconds)

Specifies the login and query time-outs.

Login Timeout

Specifies the time, in seconds, the server should wait before terminating a login attempt.

Query Timeout

Specifies the time, in seconds, to wait when retrieving query results. If you exceed the time-out, you will be prompted to wait for another period.

ANSI -> OEM

Activates the automatic ANSI-to-OEM conversion.

Disconnect After Exec

Disconnects the SQL Query Tool server connection after each query.

Packet Size

Sets the network packet size for the client connection to the server used by SQL Enterprise Manager.

Defaults

Sets all options to their default values.


Select Server/Database Dialog Box

Use this dialog box to change to a different server or database.

Server

Specifies the SQL Server to change to.

Connect

Connects to the selected SQL Server.

Disconnect

Disconnects from the selected SQL Server.

Database

Specifies the database to change to. You must first select a server in the Server box.


Manage Logins Dialog Box

Use this dialog box to manage SQL Server logins. If you are the sytem administrator (SA), you can add, modify, and remove logins. If you are not the SA, you can modify only your password.

Login Name

Specifies the login name of the login ID to add to SQL Server. To add a login ID, type the login name in this box. To modify information about a login, select the login from the Login Name box. A login name can have up to 30 characters and must be unique for that server.

Password

Specifies the password for the login ID. To change the password, choose OK and in the confirmation box that appears, type the password. If you are not the SA, to modify your own password type your old password.

Default Language

Specifies a default language for the login ID. To change the default language, choose a different default language in the box. Only languages available to the server are available to the user. If you do not specify a language, the server default language is assigned.

Database Access

Specifies the login ID's access to databases.

Permit

Grants access to a selected database.

Default

Sets the database as the login ID's default database (the database of focus when the user first logs in to SQL Server). To discourage users from creating database objects in the master database, assign a default database other than master.

User

Specifies the username for a selected database. The default username is the login name. To modify the default username, type another name in the User box.

Alias

Links the login ID to another user within a database. Select an alias in the Alias box. If you select an alias, you cannot provide a username or group.

Group

Specifies the group in the database for the user to belong to. You can place users into groups for easier management. To add the user to a group within the database, select the group in the Group box.

Add

Adds the login.

Modify

Makes changes to the selected login.

Drop

Drops a selected login.


Confirm Password Dialog Box

Use this dialog box to confirm the password typed in the Manage Logins dialog box.

Old Password

Specifies the old password for the login ID. If you are the SA, you do not need to type the old password.

Confirm New Password

Specifies the new password. This password must match the password typed in the Manage Logins dialog box.


Database Tab

Use this tab to view the database creation date, the database owner, and size and space available for a database. You can also expand or shrink a database, and recalculate the space available.

Information

Displays information about the database.

Create Date

Displays the database creation date.

Owner

Names the database owner.

Data Size

Displays the allocated space for the database.

Data Space Available

Displays the remaining available space.

Log Size

Displays the size of the transaction log, in megabytes. If the log is not on a separate device, "Shared with data" is displayed.

Log Space Available

Displays the remaining available space, in megabytes.  If the log is not on a separate device, "Shared with data" is displayed.

Version

Displays the version of SQL Server code used to create the database.

Expand

Displays the Expand Database dialog box, which is used to expand the space allocated for a selected database.

Shrink

Displays the Shrink Database dialog box, which is used to shrink the space allocated for a selected database.

Recalculate

Recalculates the remaining space available for the database. You should recalculate before you shrink the database.


Options Tab

Use this tab to set database options.

Select Into/Bulk Copy

Allows nonlogged operations to be performed.

DBO Use Only

Allows only the database owner to access a database.

No Checkpoint on Recovery

Prevents a checkpoint from being recorded on recovery.

Read Only

Allows users to view but not change the database contents.

Single User

Allows only one user at a time to access a database.

Columns Null by Default

Specifies whether database columns are defined as NULL or NOT NULL by default.

Truncate Log on Checkpoint

Truncates the transaction log every time the CHECKPOINT process occurs. Note that this is the only option you can set for the master database; however, you should leave this value on to avoid filling up the transaction log for the master database.

Description

Describes each option.


Permissions Tab

Use this tab to grant and revoke statement permissions for database users and groups.

Group/User

Specifies the group or user to add or remove permissions for.

Create and Dump Statements

Add or remove permissions for each statement. You can add or remove permissions only for the CREATE DATABASE statement from within the master database.

Create DB

Specifies that the user can create a database. Only the SA can grant this permission.

Create Table

Specifies that the user can create a table.

Create View

Specifies that the user can create a view.

Create SP

Specifies that the user can create a stored procedure.

Create Default

Specifies that the user can create a default.

Create Rule

Specifies that the user can create a rule.

Dump DB

Specifies that the user can back up the database.

Dump Trans

Specifies that the user can back up the transaction log.

OK

Sets permissions.


Shrink Database Dialog Box

Use this dialog box to reduce the database size.

Minimum Size (MB)

Specifies the minimum size the database can be reduced to.

Database Size (MB)

Specifies the size, in megabytes, to reduce the database to.


Manage Users Dialog Box

Use this dialog box to add or drop database users, and to create or drop user aliases.

User Name

Specifies the name of the user to add, drop, or alias other logins to.

Login

Specifies the login name to map to the username.

Group

Specifies a group for the user. Each user belongs to the public group by default,and can belong to only one other group.

Aliases

Associates other logins with the specified user.

Available Logins

Specifies the login(s) to map to the username selected in the User Name box.

Aliased Logins

Creates and removes an aliased login. Choose add to create an aliased login. Choose Remove to remove the login from an alias.

Add

Adds the user to the specified database.

Modify

Makes the specified changes.

Drop

Drops the username selected in the User Name box.


Manage Groups Dialog Box

Use this dialog box to add and drop groups, and to add and remove users from groups.

Group

Specifies the name of the group to add or modify.

Database Users

Specifies users to add or remove from the group.

Users

Lists current database users who are not in the group specified in the Group box. To add users, choose Add.

Users In Group

Lists database users who are currently in the group specified in the Group box. To remove users, choose Remove.

Add

Adds the group you have created.

Modify

Makes specified changes to the group selected in the Group box.

Drop

Drops the group selected in the Group box.


Query Window

Use this window to query a database, and to view query results, Statistics I/O results, and Showplan results. You can also save queries and results, and set query options.

 New Query

Starts a new query.

 Load SQL Script

Displays the Open File dialog box, which is used to open a saved query from disk.

 Save Query/Result

Displays the Save File dialog box, which is used to save a query or result to disk.

 Query Options

Displays the Query Options dialog box, which is used to set query options.

DB

Specifies which database to query.

Queries

Contains one entry for each query. You can switch between queries displayed in the list.

 Remove Current Query Set

Removes the current query/result set from the Queries box.

 Execute Query

Executes the query.

 Cancel Executing Query

Cancels the query execution.

Query Tab

Species the Transact-SQL text for the query. For help on Transact-SQL syntax, select the syntax and press SHIFT+F1, or from the Help menu, choose Transact-SQL Help. For help on specific objects, select the object name and press ALT+F1.

Results Tab

Displays the query results.

Statistics I/O Tab

Displays the results of the Statistics I/O option (if you set that option).

Showplan Tab

Displays the results of the Showplan option (if you set that option).

 No Execute

Generates a query plan without executing the query.

 Display Showplan

Generates a graphical version of the execution plan for a query.

 Display Statistics I/O

Generates the statistics information for a query.


Query Options Dialog Box

Use this dialog box to set query options and format options.

For more information, choose one of the following:

Query Flags Tab

Format Options Tab


Query Flags Tab

Use this tab to set query options.

Query Options

Specifies query options.

Abort on Arithmetic Error

Terminates a query when an overflow or divide-by-zero error occurs during a query. You cannot set both Abort on Arithmetic Error and Ignore Arithmetic Error options.

Ignore Arithmetic Error

Returns NULL when an overflow or divide-by-zero error occurs during a query. You cannot set both Abort on Arithmetic Error and Ignore Arithmetic Error options.

No Count Display

Turns off the message at the end of each query result that tells how many rows were affected by the query statement.

No Execute

Compiles a query but does not execute it.

Parse Query Only

Checks the syntax of each query and returns error messages without executing the query.

Show Query Plan

Generates a text description of the processing plan for the query as the query is processed. (To generate a graphical description of the query plan, click the Showplan button on the Query window toolbar. If the Showplan button and this check box are both selected, a text and a graphical description are generated.)

Show Stats Time

Displays the time it took to parse and compile each query statement, and the time it takes to execute each step of the query statement.

Show Stats I/O

Displays the number of scans, logical reads, and physical reads for each table referenced in a query statement. (To generate a graphical description of the processing plan, click the Statistics I/O button on the Query window toolbar. If both the Statistics I/O button and this check box are both selected, a text and a graphical description are generated.)

Limits

Specifies a row count limit.

Row Count

Specifies the number of rows the server should return before ending a query. The default (0) returns all rows.


Format Options Tab

Use this tab to set formatting options for query results.

Result Output Format

Specifies the format type for results. The choices are Column Aligned, Comma Separated (CSV), Tab Delimited, and Other Delimiter. If you select Other Delimiter, in the Delimiter box type the delimiter. The default is Column Aligned.

Right Align Numerics

Aligns numeric values in the query results to the right. This option is available only if you have selected Column Aligned in the result Output Format Box.

Verbose Prints

Displays the PRINT statements output with the query results. This option is only useful if you are executing the graphical Showplan and/or Statistics I/O.

Print Headers

Suppresses column headers in query results. If you do not want column headers to appear in the query results, clear the Print Headers check box.

Output Query

Displays the first three lines of a query with the query results.


New Task Dialog Box

Use this dialog box to create a new scheduled task.

Name

Specifies a name for the task you are creating.

Enabled

Specifies whether the task is created and scheduled, or created but not scheduled. To create and schedule a task, select this option. To create but not schedule a task, clear this option.

Type

Specifies the task type.

CmdExec

Specifies an operating-system command, or a .CMD or .EXE file to run. The command cannot contain UI components like Notepad or Paintbrush.

Distribution

Specifies a replication distribution process command.

LogReader

Specifies a replication log reader process command.

Sync

Specifies a replication synchronization process command.

TSQL

Specifies a Transact-SQL statement to be run against the selected database. The statement is limited to 255 characters and cannot contain embedded go commands.

Database

Specifies the database to execute the task in.

Command

Specifies the task text. The text can be a valid operating system command, the filename (including path) of an .EXE file, a Transact-SQL statement, or a command to one of the replication processes.

Schedule

Sets the task schedule.

On Demand

Specifies that the task will occur only when requested. Tasks that are created in association with alerts are created as On Demand.

One-Time

Specifies that the task will occur only once, at a specified date and time. After running, the task will be disabled, but not deleted.

When

Specifies a date and a time to run the task one time.

Recurring

Specifies a recurring schedule for the task. The default for a recurring task is once a week on Sunday. To change the schedule, choose Change.

Change

Specifies changes to the task's recurring schedule.

Auto Start

Specifies that SQL Server automatically starts the task when the SQL Executive service starts. This option is used for replication processes.

Add

Adds a specified task.

Options

Displays the TSQL Task Options dialog box, which is used to specify options about e-mail and Windows NT application event log notification, and retry attempts for a selected task.


Task Schedule Dialog Box

Use this dialog box to change a task schedule.

Occurs

Sets the task frequency.

Daily

Displays the Daily frame. To specify a schedule in daily increments, type the frequency of the task. For example, 3 means the task will run every third day. The default is Every 1 Day(s).

Weekly

Displays the Weekly frame. To specify a schedule in weekly increments, type the frequency of the task. For example, 3 means the task will run every third week. Then select the days on which the task should occur. The default is every 1 Week(s) on Sunday.

Monthly

Displays the Monthly frame. To specify a monthly schedule, select a specific day and month frequency, or a specific day of the week and month frequency. For example, to run a task the third day of every third month, select Day and type 3 in the appropriate boxes. Or to run a task the third Monday of every month, select "The" and then choose 3rd and Monday.

Daily Frequency

Specifies how frequently the task should occur. The task can be scheduled to run once at a specific time, or to recur every certain number of hours during a specified range of hours.

Duration

Specifies a start and an end date for the task schedule. The Start Date is the first day the task will occur (the default is the current date). You can specify an end date, which is the last day the task will occur, or No End Date, which means the task will recur indefinitely.


Manage Indexes Dialog Box

Use this dialog box to create, modify, rename, and drop indexes, and to update statistics for indexes you created while creating or modifying a table. You can manage indexes for version 4.2x servers, but some options are not available.

Table

Specifies the table to manage indexes for. To refresh the list of tables, close the Manage Indexes dialog box and refresh the Tables list in the Server Manager window.

Index

Specifies the index to create, modify, rename, or update statistics for. To create an index, choose (New Index). DRI indexes are indicated with "PKC" for PRIMARY KEY constraint or "UKC" for UNIQUE KEY constraint.

Available Columns In Table

Lists the columns to create an index on.

Columns In Index (Key)

Lists the columns in the index.

Add

Moves a selected column from the Available Columns In Table box to the Columns In Index (Key) box. You can also add a column by double-clicking the column name.

Remove

Moves a selected column from the Columns In Index (Key) box to the Available Columns In Table box. You can remove a column by double-clicking the column name.

Table Size

Displays information about the number of rows and the space used for a table. Does not include the space used by any indexes on the table.

Index Size

Displays information about the index.

Actual Size (KB)

Displays the size of an existing index, in kilobytes. Excludes all data pages, but includes the distribution page.

Estimated Min./Avg./Max. Potential Size (KB)

Displays the expected size of a new index, or an existing index with a changed definition. For indexes defined on columns of fixed-size datatypes only, Min./Avg./Max. is always the same. For indexes with definitions that include one or more variable-length columns, Min. assumes these columns are empty, Avg. assumes they are half full, and Max. assumes they are completely full.

Estimated Min./Avg./Max. Rows Per Leaf: Node Page (non-clustered index)

Estimated Min./Avg./Max. Rows Per Index Page (clustered index)

Displays the number of index rows expected to occupy the interior (node) and the exterior (leaf) pages of the B-Tree for a new index, or an existing index with a changed definition. (Note that a clustered index has no leaf pages, because the data has been excluded from the numbers.)

Estimated Min./Avg./Max. Index B-Tree Level

Displays the expected depth of the B-Tree for a new index, or an existing index with a changed definition. Deeper trees require longer data access times since more index pages must be traversed to reach the data.

User Estimate...

Specifies the percentage of the table that is unique. This option is displayed only if the Clustered box is selected and the Unique Keys box is cleared.

Index Attributes

Specifies the index attributes.

Unique Keys

Specifies to create a unique index (an index in which no two rows are permitted to have the same key values). A unique index serves as an integrity check.

Ignore Duplicate Keys

Specifies that an INSERT or UPDATE statement attempting to insert rows that violate the uniqueness constraint will execute, although rows containing duplicate information will not be inserted and a warning message will appear.

Clustered

Specifies to create a clustered index. With a clustered index, SQL Server sorts rows on an ongoing basis so that their physical order is the same as their logical (indexed) order. By definition, only one clustered index can exist per table.

Ignore Duplicate Rows

Specifies that duplicate rows are not allowed in a table. When a duplicate row is entered, the row is ignored, the INSERT action is canceled, and an error message is displayed. Unique rows are inserted normally. This option is relevant only for non-unique clustered indexes.

Allow Duplicate Rows

Specifies that a table can contain duplicate rows. If this option is set, you can create a non-unique clustered index on a table that includes duplicate rows. You can subsequently create duplicate rows with INSERT or UPDATE statement.

Sorted Data

Eliminates the sort from the index build process. Verifies that the data has been sorted by checking each index value against the previous one. If any row fails this check, the CREATE INDEX statement terminates.

Un-Sorted Data

Specifies that the data should be sorted during the index build process.

Fill-Factor %

Specifies how full SQL Server should make each index page. The amount of empty space on an index page is important because when an index page fills up, the system must take time to split it to make room for new rows. Allowable values are 1 (almost empty) through 100 (completely full). The default is 0. The fill factor is not maintained over time and is valid only when creating an index. Adjusting the fill factor affects the index size information.

Build

Creates the index.

Rebuild

Rebuilds the index.

Remove

Drops the index.

Rename

Renames the index.

Distribution

Displays the Index Distribution Statistics dialog box, which provides information about the distribution of key values in the specified index. Optimizing your queries depends on the accuracy of the distribution steps. If the number of rows in the table has changed significantly, you should run UPDATE on all indexes on that table. Also run UPDATE if data in indexed columns have changed significantly.

Check Size

Checks the size of the index and verifies that the current actual size displayed under Index Size is correct. This operation requires a table scan, which takes time to complete.


By User Tab, Object Permissions Dialog Box

Use this tab to grant, revoke, or view object permissions by user or group.

User/Group

Specifies the user or group to grant, revoke, or view permissions for. Note that the DBO is not displayed, because you cannot grant or revoke permissions from the DBO.

Member of/Group Members

Displays the group the user selected in the User/Group box belongs to. Displays members of the group when a group is selected in the User/Group box.

Object Filters

Changes how objects are displayed and viewed.

Tables

Displays tables.

Views

Displays views.

Stored Procedures

Displays stored procedures.

All Objects

Displays all objects.

Objects Granted to User/Group

Displays only the objects granted to a user or group.

Permissions

Specifies an object and which permission to grant or revoke for that object. On stored procedures, you can grant only Execute permission. To grant or revoke Select or Update permission on certain column(s), select the Column Level Permissions check box, and then select the appropriate column(s) and the appropriate permissions.

Set

Sets the current permission information.

Grant All

Grants all permissions to all users or groups.

Revoke All

Revokes all permissions from all users or groups.


By Object Tab, Object Permissions Dialog Box

Use this tab to grant, revoke, or view permissions by object. You can set permissions for objects you own.

Object

Specifies the object to grant, revoke, or view permissions for.

Group, Username

Displays the groups and users in the database. Select the group or user to grant permission to or revoke permission from, then select the appropriate permission(s). To grant or revoke column-level permissions, select the Column Level Permissions check box and then select the appropriate columns and permissions.

Set

Sets the current permissions.

Grant All

Grants permission to all users and groups for the selected object.

Revoke All

Revokes permission from all users and groups for the selected object.


Generate SQL Scripts Dialog Box

Use this dialog box to generate the scripts that are used to create database objects.

Scripting Objects

Specifies the objects to generate a script for. You can select all objects or specific objects.

Note that if you are generating a script for user-defined datatypes and you run the script in a non-empty database that already contains those datatypes, you may encounter errors.

Scripting Options

Specifies the options for the script.

Object Creation

Adds object-creation statements to the script. (For example, CREATE TABLE.)

Object Drop

Adds statements that drop objects before creating them. (For example, DROP TABLE.)

Table Indexes

Adds statements used to create indexes for a table. Provides the CREATE INDEX statement.

Table Triggers

Adds statements used to create triggers for a table. Provides the CREATE TRIGGER statement.

Table DRI

Adds statements to create declarative referential integrity (DRI) statements for a table. Provides the CREATE TABLE statement with the constraint information.

Security

Specifies the user information options to be generated with the script. You can generate statements that add all users and groups to the database, that add to SQL Server all logins of users associated with that database, and/or that add permissions for the database. Note that the SA login and the repl_subscriber/publisher logins and users are not scripted.

Object

Specifies objects to generate scripts for. Select the object, and then choose Add to move it to the Object box to the right. If you make a mistake, select the object and then choose Remove.

Script

Generates the script for the objects you have selected.

Preview

Displays the Object Scripting Preview dialog box, which is used to view and change the script before generating it.

Scripting Options

Specifies whether to generate the script as a single file, or to generate a script per object.


Object Dependencies Dialog Box

Use this dialog box to view the object that another object depends on, and the objects that depend on the selected object.

Subject Object

Specifies the object whose dependencies you are viewing.

Objects That ' ' Depends On

Displays the objects on which the object you are viewing depends.

Objects That Depend On ' '

Displays the objects that depend on the subject object you are viewing.


Drop Selected Objects Dialog Box
Drop Objects Dialog Box

Use this dialog box to drop selected objects.

Object

Specifies the name of the object(s) to drop.

Owner

Specifies the owner of the object to be dropped. You must own an object (or be an SA) to drop that object.

Type

Shows the object type.

Show Dependencies

Displays the dependencies for a selected object.

Drop All

Drops the selected object(s).


Rename Selected Objects Dialog Box
Rename Objects Dialog Box

Use this dialog box to rename an object.

New Name

Specifies a new name for the selected object.

OK

Renames the selected object.


Rules Tab

Use this tab to create, modify, or drop rules.

Rule

Specifies a name for the rule you are creating, or the names of existing rules to modify or drop.

Description

Specifies the conditions of the rule. For example, @value > 1 or @state in ("WA", "OR", "CA").

Add

Creates the rule.

Drop

Drops the rule selected in the Rule box. Before you can drop a rule, you must unbind the rule from all columns and datatypes.

Bindings

Displays binding information for the rule selected in the Rule box.


Rule Column Bindings Tab

Use this tab to bind a rule to or unbind a rule from a table column.

Tables

Specifies the table that contains the column to bind a rule to or unbind a rule from.

Column

Specifies the column to bind a rule to or unbind a rule from.

Binding

Specifies the rule to be bound or unbound. To bind a rule, select the name of that rule. To unbind a rule, select (none).

Bind

Binds the rule to or unbinds the rule from the table column.


Rule Datatype Bindings Tab

Use this tab to bind a rule to or unbind a rule from a user-defined datatype.

User-Defined Datatype

Specifies the user-defined datatype to bind the rule to or unbind the rule from.

Binding

Specifies the rule to be bound or unbound. To bind a rule, select the rule to bind. To unbind a rule, select (none).

Future Only

Specifies that the rule is bound to or unbound from only future columns that have the specified datatype. Existing columns that use the datatype are unaffected.

Bind

Binds the rule to or unbinds the rule from the selected user-defined datatype.


Manage Rule Info Dialog box
Manage Rule/Default Info Dialog Box

Use this dialog box to view binding information, and to unbind rules or defaults from a table column or user-defined datatype.

Bound Columns

Displays the table columns a selected rule or default is bound to.

Bound Datatypes

Displays the user-defined datatypes a selected rule or default is bound to.

Unbind

Specifies the column or datatype to unbind the rule or default from. You can select more than one item to unbind at a time.


Defaults Tab

Use this tab to create, modify, or drop defaults.

Default

Specifies a name for a default you are creating, or the names of existing defaults to modify or drop.

Description

Specifies a default conditions.

Add

Creates the default.

Drop

Drops the default selected in the Defaults box. Note that before you can drop a default, you must unbind it from all columns and datatypes.

Bindings

Displays binding information for the selected default.


Default Column Bindings Tab

Use this tab to bind a default to or unbind a default from a table column.

Tables

Specifies the table that contains the column to bind a default to or unbind a default from.

Column

Specifies the column to bind a default to or unbind a default from.

Binding

Specifies the default to bind or unbind. To bind a default, select the name of that default. To unbind a default, select (none).

Bind

Binds the default to or unbinds the default from the table column.


Default Datatype Bindings Tab

Use this tab to bind a default to or unbind a default from a user-defined datatype.

User-Defined Datatype

Specifies the user-defined datatype to bind the default to or unbind the default from.

Binding

Specifies the name of the default to bind or unbind. To bind a default, select the name of that default. To unbind a default, select (none).

Future Only

Specifies that a new default is bound to or unbound from only future columns that have the specified datatype. Existing columns that use the datatype are unaffected.

Bind

Binds the default to or unbinds the default from the user-defined datatype.


Manage User-Defined Datatypes Dialog Box

Use this dialog box to create, modify, and drop user-defined datatypes.

Name

Specifies the name of a new user-defined datatype. To name the datatype, select an empty row and type the name in the Name box.

Owner

Displays the owner of the user-defined datatype. This information cannot be changed.

Datatype

Displays the system-defined datatype on which to base the user-defined datatype. This information cannot be changed after the datatype is created.

Length

Displays the length of the user-defined datatype, if necessary. This information cannot be changed after the datatype is created.

Allow Nulls

Allows columns with the new user-defined datatype to contain null values. This information cannot be changed after the datatype is created.

Default

Binds a default to or unbinds a rule from a default from the selected datatype. To bind a default, select the name of the default in the Default box. To unbind a default, select (none).

Rule

Binds a rule to or unbinds a rule from the selected datatype. To bind a rule, select the name of the rule in the Rule box. To unbind a rule, select (none).

Drop

Drops the selected datatype.

Info

Displays the User Datatype Info dialog box, which is used to display information about the table columns associated with the selected datatype.

OK

Makes the specified changes and closes the dialog box.

Cancel

Closes the dialog box without making changes.

Apply Now

Makes the specified changes and leaves the dialog box open.


User Datatype Info Dialog Box

Use this dialog box to view information about which tables and columns are associated with a specified user-defined datatype.

Bound Columns

Shows the columns associated with the selected user-defined datatype.


Manage Alerts Window

Use this window to view information about server alerts and operators. You can add, edit, and remove alerts and operators, and set alert engine defaults.

Alerts Tab

Displays a list of alerts for the selected server.

Operators Tab

Displays a list of operators for the selected server.

 New Alert

Displays the New Alert dialog box, which is used to create a new alert.

 Edit Alert

Displays the Edit Alert dialog box, which is used to modify an alert.

 Delete Alert

Removes the alert selected in the Alerts tab.

 New Operator

Displays the New Operator dialog box, which is used to create a new operator.

 Edit Operator

Displays the Edit Operator dialog box, which is used to modify an operator selected in the Operators tab.

 Delete Operator

Removes the operator selected in the Operators tab.

 Refresh

Refreshes the lists of alerts and operators.

 Alert Engine Options

Displays the Alert Engine Options dialog box, which is used to configure alert engine activity.

 Manage Messages

Displays the Manage SQL Server Messages dialog box, which is used to find or create error messages.


Fail-Safe Tab

Use this tab to designate a fail-safe operator to notify; specify an unhandled event-forwarding server; and restrict forwarded events.

Operator To Notify

Specifies an operator to notify when all pager or e-mail notifications for an alert fail. An alert can fail because of an incorrect pager address, a problem with the e-mail system, or a gap in the pager schedules. Select the operator to notify and then select the method for notifying that operator: Email and/or Pager. E-mail messages or pages sent to the fail-safe operator have Fail-Safe_ at the beginning of the Subject line to distinguish them from ordinary messages.

To add a new operator, choose (New Fail-Safe Operator). To specify no operator, choose (No Fail-Safe Operator).

Server To Forward Events To

Specifies a remote server to handle all alerts for the current server. All unhandled events on the current server (events without locally defined alerts) will be sent to the Windows NT application event log on the remote server. The remote server will handle the event as if it were a local event on that server, and will raise an existing alert set for that event.

Using this option allows you to centralize alert handling for a group of servers. You can have locally defined alerts and a specified remote server to handle all alerts not locally defined. When an event is registered in the Windows NT application event log, SQL Executive determines whether any alerts are defined locally and responds to those alerts as defined. If no alerts are defined locally, SQL Executive checks for a remote server to forward the event log to. Then SQL Executive on the remote server determines whether an alert is defined for the event and responds to the alert as defined.

Forward Events For Errors With Severity Of 01 or Above

Restricts the events that are forwarded to the remote server. Since forwarded events can cause additional network load, you can limit the load by specifying a higher severity so fewer events will be forwarded. A severity of 001 causes all SQL Server events to be forwarded.


Pager Email Tab

Use this tab to specify the formats of the To, CC, and Subject lines of a pager e-mail message. This tab is useful if the pager e-mail requires special addressing to integrate with a pager service provider's e-mail system.

To Line

Creates the To line template of a pager e-mail message. Specify the prefix, whether to include the pager address, and the suffix. For example, if you type a prefix of "@PGR:" and select the Pager-Address check box and specify no suffix, the actual To line would look like:

@PGR:pageremailaddress

CC Line

Creates the CC line template of a pager e-mail message. Specify the prefix, whether to include the pager address, and the suffix. For example, if you type a prefix of "MISPager" and do not select the Pager-Address check box or specify a suffix, the CC line would look like:

MISPager

Subject

Creates a template for the Subject line of a pager e-mail message. Specify the subject prefix and suffix. For example, if you type a prefix of SQL Server Alert and a suffix of "Fix It Now", the Subject line would look like:

SQL Server AlertAlertSubjectTextFix It Now

Include Body of Email in Notification Page

Specifies that the text of the e-mail message will be sent to the pager. A notification consists of lines of text. For pager notifications where hardware character buffers are at a premium, limit the amount of text sent. To send only the subject line, clear this check box.


Server Manager Window

Use this window to navigate through your servers and the objects on those servers.

Expanding and Collapsing Lists

By clicking the plus (+) or minus (-) sign, you can expand and collapse lists in the Server Manager window. For example:

Click the plus sign (+) beside a group name to view the servers in that group.

Click the plus sign (+) beside a server name to view a list of the objects and services on that server.

Click the plus (+) sign beside a folder to view a list of objects and services in that folder.

Navigating Quickly Through a List of Servers

To quickly navigate to a particular server, in the Server box choose the server name.

Access to Menu and Toolbar Items

Certain menu and toolbar items are available only if the corresponding object is selected in the Server Manager window. For example, you cannot manage a server until you select it in the Server Manager window. You cannot manage any database objects until you select a database in the Server Manager window.

Pop-up Menus

In addition to using the menus or toolbar buttons in SQL Enterprise Manager, you can also go directly to the tasks for a given object by clicking the object with the right mouse button. A menu appears adjacent to the object and lists the tasks you can perform for that object.

Display Refresh

To update the information displayed in the Server Manager window, click the right mouse button on a folder, and then choose Refresh from the shortcut menu.

Server Status Information

Server status is displayed in the Server Manager window. The stoplight for each server indicates whether SQL Server is started, paused, or stopped. However, you can see servers status only if SQL Enterprise Manager is running on Windows NT-based computers, not when it's running on a Windows 95-based computer. A red zigzag next to the stoplight indicates the server you are currently connected to.

 Display Legend

Displays a legend for the icons used in the Server Manager window.


Server Error Log Window

Displays the current error log for the selected SQL Server. The current log is labeled Current. A new log is created each time SQL Server is started. The previous six logs are maintained, and are labeled Archive followed by a number. The lowest number is the most recently archived log.


Manage SQL Server Messages Dialog Box

Use this dialog box to search for SQL Server error messages by message text, severity, and/or error number, and by messages that are always written to the Windows NT application event log. You can also create or edit user-defined messages. Only the SA can view SQL Server messages, and only for SQL Server version 6.0 or later.

Message Text Contains

Searches for messages that contain the text typed in this box.

Severity

Searches for messages of the selected severity level in this box.

Error Number

Searches for the message that matches the error number typed in this box.

Include

Specifies whether to include messages that are always written to the Windows NT application event log, and whether to list user messages.

Logged Messages Only

Restricts the list to SQL Server messages that are always written to the Windows NT application event log. If the User Messages option is also selected, this list includes all user-defined messages configured to be written to the log. If the User Messages Only option is cleared, this list includes system messages with severity levels 19 through 25, and all user-defined messages configured to be written to the log.

User Messages Only

Restricts the search to user-defined messages. If this option is cleared, the search returns user-defined and system messages.

Find

Searches for the defined message. Messages that fit the definition are displayed in the box at the bottom of the dialog box. To sort the messages by a column attribute, click the heading for that column. Sorting is not available if the list contains more than 250 rows. The default sort is by error number.

New

Displays the New Message dialog box, which is used to create new user-defined messages.

Edit

Displays the Edit Message dialog box, which is used to edit messages. All information for user-defined messages, except error number, can be edited. For other messages, only the option to always write the message to the Windows NT application event log can be changed.

Delete

Deletes a selected message. Only user-defined messages can be deleted.

Select

Copies the selected message to the New Alert or Edit Alert dialog box. This button is displayed only if you have accessed the Manage SQL Server Messages dialog box from the New/Edit Alert dialog box.


New/Edit Message Dialog Box

Use this dialog box to create or edit a new user-defined message. You can also specify whether the message is logged to the Windows NT application event log.

Error Number

Specifies an error number for a selected user-defined message. If you are creating a message, type a number in this box. User-defined messages begin at 50001. If you are editing a message, the message error number is displayed in this box and cannot be changed.

Message Text

Specifies text for a user-defined message. If you are creating a message, type the text for the message in this box. If you are editing a user-defined message, modify the information as necessary. You cannot modify the text of system messages.

Language

Specifies the language of the user-defined message. You can not specify a language for system messages.

Always Write to Windows NT Eventlog

Specifies whether to write the message to the Windows NT application event log. You can use this option for system messages.


Rename Index Dialog Box

Use this dialog box to rename an index.

New Index Name

Specifies a new name for the index.

OK

Renames the index.


Index Distribution Statistics Dialog Box

Use this dialog box to view index distribution information.

Table Name

Displays the name of the selected table.

Index Name

Displays the name of the selected table's index.

Rows in Table

Displays the number of rows in the table.

Number of Steps

Displays the number of steps in the index.

Sampling Step Interval

Displays the number of rows between key value sampling steps.

Average Row Hits

Displays the average number of rows that would be returned by an equality qualified query on the first column of the index (for example, SELECT * FROM AUTHORS WHERE AU_ID = '172-32-1176'). The smaller the number of rows returned the better, with 1 row being ideal. For indexes on tables containing more than 200 rows, one of the following subjective ratings will also be displayed:

Optimal (exactly 1 row)

Very Good (more than 1 row but less than 0.5% of the rows)

Good (more than 0.5% of the rows but less than 1% of the rows)

Fair (more than 1% of the rows but less than 2.5% of the rows)

Poor (more than 2.5% of the rows but less than 5% of the rows)

Very Poor (more than 5% of the rows)

These ratings are provided as rough guidelines to assist in determining the effectiveness of the index and may not apply to all indexes.

Date of Last Update

Displays the last date the index distribution statistics were updated.

Distribution Steps

Displays the key value at each step of the distribution statistics.

Update

Updates the index statistics information.


Specify Table Name Dialog Box

Use this dialog box to specify the name of a new table.

New Table Name

Specifies a name for the table.

OK

Creates the table.


Object Scripting Preview Dialog Box

Use this dialog box to preview the text of the object script you are generating.

Save As

Saves the script to disk. To change the script text, choose Save As.

Close

Closes this dialog box and returns to the Generate SQL Scripts dialog box.


Drop Database Device Dialog Box

Use this dialog box to confirm that a database device is deleted.

Drop

Drops the database device and the databases and/or logs stored on the device.


alerts

By creating alerts, you can set up SQL Enterprise Manager to respond automatically to SQL Server events, either by executing a task that you have defined or by sending an e-mail and/or a pager message to a specified operator.


alias

A database username that is shared by several login IDs. An alias allows more than one person to be treated as the same user inside a database, giving all the designated users the same permissions. A common use for aliases is to allow several users to assume the role of database owner (DBO).


ANSI to OEM conversion

The AutoANSItoOEM entry controls the default conversion behavior when you connect to a server. If AutoANSItoOEM is set to ON, conversion is turned on in the following cases:

 ANSI clients to OEM servers (Windows and Windows NT)

 OEM clients to ANSI servers (Windows NT)

The Automatic ANSI to OEM option enables DB-Library to convert characters from OEM to ANSI when communicating with SQL Server, and from ANSI to OEM when communicating from SQL Server to the client. By default, this option is set on.

For Windows-based clients, the SQL Client Configuration Utility includes the Automatic ANSI to OEM option. By default, the setting for the Automatic ANSI to OEM option is set off.


application log

The Windows NT application log is a special file that records new events. It can be viewed only by using the Windows NT Event Viewer. When SQL Server is configured to use the Windows NT application log, each SQL Server session writes new events to that log. (Unlike the SQL Server error log, a new application log is not created each time you start SQL Server.)


article

The basic unit of replication. An article contains data originating from a table that has been marked for replication. One or more articles are grouped within a publication.


attribute

A qualifier of an entity or a relation describing its character quantity, quality, degree, or extent. For example, column headings are usually considered to be attributes.


auto-start options

By default, the setup program installs SQL Server and SQL Executive to run as manually started Windows NT services. Optionally, each can be configured to run as an automatically started service. For each service, this choice is called the auto-start option.


automatic synchronization

Synchronization that is accomplished automatically by SQL Server. A snapshot is taken of the table data, and along with the table schemas that snapshot is written to files for transfer. The job to copy the table schema and data is transferred, as is any other replication job, via the distribution database. No operator intervention is required.


base datatype

One of the system datatypes, which include the length for char, varchar, binary, and varbinary.


base object

An object (a table or another view) from which a view is derived. A view can have one or more underlying objects.


base table

A table from which a view is derived. A view can have one or more underlying tables. (A view can have underlying views.) Also called underlying table.


batch

A set of SQL statements submitted together and executed as a group. A script is often a series of batches submitted one after the other. A batch is compiled only once as a whole and is terminated by an end-of-batch signal (GO).


binding

Associating a default or rule with a column or datatype. The sp_bindefault system stored procedure binds a default to a column or to a user-defined datatype.

The sp_bindrule system stored procedure binds a rule to a column or to a user-defined datatype.


bulk copy program (bcp)

A command-line utility that copies SQL Server data to or from an operating-system file in a user-specified format.


bulk data copy

You can break up large transactions when using bcp to bulk copy data into a database. If you use bcp without specifying a batch size, the entire operation is performed as a single logical transaction.

Even if another user process does a DUMP TRANSACTION statement, the log records associated with the bulk copy operation remain in the log until the entire operation completes or another DUMP TRANSACTION statement is executed. This is one of the most common causes of the error 1105. You can avoid the problem by splitting the bulk copy operation into batches.


business rules

An organizational standard operating procedure that requires that certain policies (rules) be followed to ensure a correctly run business. For example: no debit without corresponding credit, no active customers to be deleted, no additional credit for those whose payments are past due. Business rules ensure that the database maintains its accuracy and integrity in a business sense.


cascading delete

A delete that deletes all related database rows or columns.


cascading updates

An update that updates all related database rows or columns.


CHECK constraints

Specify data values that are acceptable in a column. You can apply CHECK constraints to multiple columns, and you can apply multiple CHECK constraints to a single column.

When a table is dropped, all CHECK constraints are also dropped.


checkpoint

The point at which all data pages that have been changed are guaranteed to have been written to the disk.


clustered index

An index in which the logical (indexed) order of the key values is the same as the physical order in which the corresponding rows (that contain the key values) exist in the table.


column

The set of all rows in a table that have a common attribute (for example, the firstname column in the authors table of the pubs database). Contains an individual data item within a row or a record.


column-level constraints

A restriction used to enforce data integrity on a column (column-level constraints) or on a table (table-level constraints—usually multicolumn constraints). You define constraints by using SQL Enterprise Manager or by specifying various options of the CREATE TABLE statement. SQL Server provides the following types of constraints: CHECK, DEFAULT, FOREIGN KEY REFERENCE, PRIMARY KEY, UNIQUE.


common key

A key created to make explicit a logical relationship between two tables in a database. See also primary key; foreign key.


composite index

An index that uses from 2 to 16 columns.


composite key

A key composed of two or more columns. A drawback of composite keys is that they introduce more complex join processing.


connections

The number of logins or attempted logins to a SQL Server.


constraint

A restriction placed upon the value that can be entered into a column or a row. For example, age cannot be < 0 or > 110. SQL Server provides these constraints: CHECK, DEFAULT, FOREIGN KEY, REFERENCE, PRIMARY KEY, UNIQUE.


creation script

An option, that when selected, adds object-creation statements to the script. creation_script is the path and name of an article schema script used to create target tables.


data definition

The process of setting up databases and creating database objects, such as tables, indexes, constraints, defaults, rules, procedures, triggers, and views.


data definition language (DDL)

A language for modeling the structure (rather than the contents) of a database. The ability to create, modify, and remove databases and database objects.


data integrity

Accuracy and reliability of data. Data integrity is important in both single-user and multiuser environments. In multiuser environments, where data is shared, both the potential for and the cost of data corruption is high. In large scale DBMS environments, data integrity becomes one of the primary concerns and focuses.


data modification

Adding, deleting or changing information in the database (using the INSERT, DELETE, and UPDATE statements).


data synchronization

Before a new subscriber can receive replicated transactions from a publication, the publication and destination tables must contain the same schema and data. The process that accomplishes this is called initial synchronization.


data transfer

The process of copying data to or from a SQL Server.


database

A collection of information, data tables, and other objects that are organized and presented to serve a specific purpose, such as facilitation of searching, sorting, and recombination of data. Databases are stored on devices.


database consistency checker (DBCC)

Statement used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on. DBCC helps ensure the physical and logical consistency of a database, but it is not corrective.


database device

A file that databases are stored on. One database can be stored on several devices.


database name

Must correspond to the rules for identifiers and can have as many as 30 characters (for example, SALES or PAYROLL91).


database object

One of the components of a database: a table, index, trigger, view, key, constraint, default, rule, user-defined datatype, or stored procedure. Anything that has a name and consumes space in a database.


database object owner

The designated owner of a database object (tables, indexes, views, defaults, constraints, triggers, rules, and procedures). The user who creates a database object is the database object owner and is automatically granted all permissions on it. The database object owner can grant permission to other users to use that object. Database object ownership cannot be transferred.


database owner (DBO)

The creator of the database. There is only one DBO. The DBO has full privileges inside that database and determines the access and capabilities provided to other users. In his or her own database, the user is recognized as the DBO; in other databases, the database owner is known by his or her database username. DBO status can be reassigned to a different user. Only one login ID can be the DBO login ID, although other login IDs can be aliased to the DBO.


database scripts

A collection of statements used to create database objects. Transact-SQL scripts are saved as files, usually ending with the SQL filename extension.


datatype

An identifier that specifies what type of information a column holds and how the data is stored. System-supplied datatypes are provided by SQL Server; user-defined datatypes can also be created.


deadlock

A situation that arises when two users, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each user waits for the other to release their lock. SQL Server detects deadlocks and kills one user's process. See also livelock.


declarative referential integrity (DRI)

The SQL Server built-in capacity that checks the data integrity of a specific related table.


default

The database object that enables SQL Server to insert a value into a column if the user does not explicitly enter one. In a relational database management system, every data element (a particular column in a particular row) must contain some value, even if that value is NULL. Since some columns do not accept null values, some other value must be entered, either by the user or by SQL Server. Defaults specify what value SQL Server will insert when a user does not enter a value (in either a NULL or NOT NULL column). For example, you can create a default that has the value "???" or the value "fill in later", and then instruct SQL Server to enter it if a user does not make an entry.


default database

The database a user is connected to when first logging in to SQL Server.


default device

The device on which a user's database is automatically stored (unless another device is specified).


default language

The language (for example, French, German, or English) used to communicate with the server. Once the default language is set, the user is automatically logged in as using that language.


delimiter

The character used for separating elements of a list.


dependencies

The views and procedures that depend on the specified table or view, and the tables and views that are depended on by the specified view or procedure.


destination database

In replication, the subscribing database. The database that receives tables and data replicated from a publication database.


destination table

In replication, the subscribing table, created as a replicated image of a published table. A destination table in a destination database is synchronized with and contains data derived from the published table in a publication database.


device

A file in which databases are stored. One database can be stored on several devices. There are two types of devices: database devices, which store databases, and dump devices, which store backups of a database.


disk mirroring

The process that protects against media failure by maintaining a fully redundant copy of a partition on another disk.


distribute

In replication, to move transactions from the distribution database tables to subscription servers, where they are applied to the destination tables in the destination databases.


distribution database

In replication, a store-and-forward database that holds all transactions that are waiting to be distributed to subscription servers. The distribution database receives transactions sent to it from the publication server by the log reader process and holds them until the distribution process moves them to the subscription servers.


distribution process

In replication, the process that moves transactions from the distribution database tables to subscription servers, where they are applied to the destination tables in the destination databases. The distribution process is a subsystem of the scheduling engine in SQL Executive.


distribution server

In replication, the server containing the distribution database. The distribution server receives all changes to published data, stores the changes in its distribution database, and when appropriate (depending on scheduling and other variables), transmits them to subscription servers. The distribution server can be the same computer that is acting as the publication server, or a different computer. Also referred to as a distributor. See also local distribution server, remote distribution server.


domain

In Windows NT security, a collection of computers that are grouped for viewing and administrative purposes, and that share a common security database.


domain integrity

Integrity that enforces valid entries for a given column. Domain integrity is enforced by restricting the type (through datatypes), the format (through CHECK constraints and rules), or the range of possible values (through REFERENCE and CHECK constraints, and rules).


dump device

A file that stores backups of databases.


dumps

Also known as backup. Backing up a database creates a copy of its tables, data, and user-defined objects.


dynamic backup

A backup performed while the database is active.


dynamic dump

A backup, or dump, of a database or a transaction log made while the database is active. The dynamic dump makes backups as convenient and continuous an operation as possible. However, because the dynamic dump slows SQL Server somewhat, it is best to execute it when the database is not being heavily updated.


dynamic recovery

The process that detects and/or attempts to correct software failures and losses of data integrity within a DBMS.


entity integrity

Defines a row as a unique entity for a particular table. Usually enforces the primary key of a table (through indexes, UNIQUE constraints, or PRIMARY KEY constraints).


error log

File that records error information from SQL Server. The error log can be viewed using SQL Enterprise Manager or any text editor. When SQL Server is configured to use its error log, each SQL Server session creates a new log, and SQL Server retains the last six logs created.


event log

File that contains SQL Server error messages as well as messages for all activities on the computer.


exclusive lock

A lock that prevents any other transaction from acquiring a lock until the original lock is released at the end of a transaction. An exclusive lock is always applied during an update operation (INSERT, UPDATE, or DELETE).


field

A single item of information contained within a row or a record. The logical equivalent of a column.


fillfactor

Option that specifies how full SQL Server should make each index page. The amount of empty space on an index page is important because when an index page fills up, the system must take time to split it to make room for new rows.


filtering

In replication, the method by which only selected rows or only selected columns of a table are designated for replication. The method by which partitioning is accomplished. See also partitioning.


foreign key (FK)

The column or combination of columns whose values match the primary key (PK) of some other table. A foreign key does not have to be unique; in fact, foreign keys are often in a many-to-one relationship to a primary key. Foreign key values should be copies of the primary key values; no value in the foreign key except NULL should ever exist unless the same value exists in the primary key. A foreign key may be NULL; if any part of a composite foreign key is NULL, the entire foreign key must be NULL.


group

A set of database users. Groups provide a convenient way to grant or revoke permissions to more than one user. All users belong to the group public by default.


group name

A name of a group of database users. A group name can have as many as 30 characters and must be unique within the database. The characters can be alphanumeric, but the first character must be a letter or the symbols # or _ (for example, DATAOPS or OPERS8).


guest

Creating a user named guest in a database enables any user with a login ID for SQL Server to access the database as a guest user. If a user attempts to access the database but does not have a username (his or her username is not found in the database's sysusers table), SQL Server looks for a user named guest. If there is one, the user is allowed to use the database, under the permissions assigned to guest.


horizontal partitioning

In replication, the process of creating an article that replicates only selected rows from the base table. Subscription servers receive only that horizontally partitioned subset of data.


identifier

The name of a database object. An identifier can be from 1 to 30 characters long. The first character must be a letter or the symbols underscore (_), at sign (@), or pound sign (#). An identifier beginning with # denotes a temporary table. An identifier beginning with @ denotes a variable. Embedded spaces are not allowed.


IDENTITY property

Property that enables columns to contain system-generated values that uniquely identify each row within a table. When inserting values into a table that has an identity column, SQL Server automatically generates the next identifier based on the last used identity value (incremented by adding rows) and the increment value specified during column creation.


index

A set of pointers that are logically ordered by the values of a key. An index is a database object that provides access to data in the rows of a table, based on key values. Indexes provide quick access to data and can enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. In a clustered index, data is stored in the same order as the index; in a nonclustered index, data is stored differently from the index.


installation path

The drive and directory into which the SQL Server files will be copied. The default is C:\SQL60, although this can be set at installation time. After installation, this is often referred to as the SQL Server root directory.


integrated security

Security that allows a SQL Server to use Windows NT authentication mechanisms to validate logins for all connections. Only trusted (multiprotocol or named pipes) connections are allowed.


ISQL/w

A SQL Server utility that allows you to enter Transact-SQL statements and system procedures in a graphical query interface. ISQL/w also provides the capability for graphically analyzing queries.


key

A field used to identify a record, often used as the index field for a table.


keyword

Words reserved by the server to be used to define, manipulate, and access database objects.


leaf level

The bottom level of a clustered or nonclustered index. In a clustered index, the leaf level contains the actual data pages of the table. In a nonclustered index, the leaf level points to data pages rather than containing the data itself.


livelock

A request for an exclusive lock that is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A situation in which read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. See also deadlock.


local distribution server

In replication, when a publication server also acts as its own distribution server, it is sometimes called a local distribution server. In this case, the publication database and the distribution database reside on the same computer. Compare with remote distribution server.


local login identification

The login identification (login ID) a user must use to log in to a local server. A login ID can have as many as 30 characters. The characters can be alphanumeric, but the first character must be a letter (for example, CHRIS or TELLER8).


local server

The server(s) the user is logged in to. If remote servers are set up for the local server, users can access remote servers from their local server.


lock

A restriction on access to a resource in a multiuser environment. SQL Server automatically locks users out of a specific record, field, or file to maintain security or prevent concurrent data manipulation problems.


log device

Device that contains a database's transaction log.


log reader process

In replication, the process of moving transactions from the publication server transaction log into the distribution database. The log reader process is a subsystem of the scheduling engine in SQL Executive.


logical name

A name used by SQL Server to identify a device. A logical name for a device must correspond to the rules for identifiers and can have as many as 30 characters (for example, ACCOUNTING or LIBRARY).


login identification

login ID. The unique name a user uses to log in to SQL Server. A login ID can have as many as 30 characters and must be unique for that server. The characters can be alphanumeric, but the first character must be a letter or the symbols # or _. With integrated security for Windows NT, you do not need to maintain a separate login ID for SQL Server.


login security mode

Security mode that determines the manner in which a SQL Server validates a login request. There are three types of login security: integrated, standard, and mixed.


loose consistency

A replication model that allows a time lag between the moment original data is altered and the replicated copies of that data are updated—it does not guarantee that all copies will be constantly identical to the original. An advantage of loose consistency is that it supports LANs, WANs, fast and slow communication links, and intermittently connected databases. It also allows better database availability and scales much better in its implementation as compared to tight consistency. SQL Server 6.0 replication is based on a loose consistency model.


manual synchronization

Synchronization that is accomplished by a user. As with automatic synchronization, the publication server produces files containing the schema and a snapshot of the data from the published table, but with manual synchronization, it is applied to the subscriber manually, using tape or another medium.


MAPI

An e-mail application programming interface (API).


master database

Database that controls the user databases and the operation of SQL Server as a whole. It is automatically installed with SQL Server and keeps track of such things as user accounts, remote user accounts, remote servers that this server can interact with, ongoing processes, the configurable environment variables, system error messages, the databases on SQL Server, the storage space allocated to each database, the tapes and disks available on the system, and the active locks.


MASTER device

The device that is automatically installed with SQL Server and used to store the master, model and tempdb system databases and transaction logs, and the pubs sample database and transaction log.


message number

A number that uniquely identifies an error message.


mirror name

A name for a mirrored device. Must correspond to the rules for identifiers and can have as many as 30 characters.


mirroring

Continuous duplication of the information on one SQL Server device to another. Mirroring can provide continuous recovery in the event of media failure.


mixed security

Security mode that allows login requests to be validated using either integrated or standard security. Trusted connections (as used by integrated security) and nontrusted connections (as used by standard security) can be established.


model database

SQL Server−supplied database that provides a template or prototype for new user databases. Each time a database is created, SQL Server makes a copy of the model database and then extends it to the creation size requested. A new database can never be smaller than the model database. The model database contains the system tables required for each user database. It can be modified to customize the structure of newly created databases so that every change you make to model is reflected in each new database.


multiuser

The ability of a computer to support many users operating at the same time, while providing to each user the full range of capabilities of the computer system.


named pipe

An interprocess communication (IPC) mechanism that SQL Server and Open Data Services use to provide communication between clients and servers. Named pipes permit access to shared network resources.


nonclustered index

An index in which the logical order of the index does not match the physical order of the rows on disk. Clustered indexes usually provide faster access to data than nonclustered indexes.


NULL

In Transact-SQL, an entry that has no explicitly assigned value. NULL is not equivalent to zero or blank. A value of NULL is not considered to be greater than, less than, or equivalent to any other value, including another value of NULL.


nullability

The capability that determines whether or not a column can allow null values (NULLs) for the data in that column.


object

One of the components of a database: a table, index, trigger, view, key, constraint, default, rule, user-defined datatype, or stored procedure. Also called a database object.


object dependencies

The views and procedures that depend on a table or view, and the tables and views that are depended on by a view or procedure.


object owner

The user who owns the object, usually the creator of the object. Also called database object owner.


object permissions

The permissions that regulate the use of certain statements (data modification statements) on certain database objects. Permissions can be granted or revoked by the owner of the object.


ownership chain

Views can depend on other views and/or tables. Procedures can depend on other procedures, views, and/or tables. These dependencies can be thought of as an ownership chain.


partitioning

In replication, creating an article that replicates only selected information from the base table. Subscription servers receive only that subset of data from the source table. Sometimes referred to as filtering. See also horizontal partitioning, vertical partitioning.


permissions

Authorization to enforce database security. The SQL Server permissions system specifies which users are authorized to use which Transact-SQL statements, views, and stored procedures. The ability to assign permissions is determined by each user's status. There are two types of permissions: object permissions and statement permissions.


physical name

The path where a device or a mirrored device is located. For a device, the default is the path of the MASTER.DAT file followed by the first eight characters of the device's logical name. For example, if ACCOUNTING is the logical name, and the MASTER.DAT file is located in SQL60\DATA, the default physical name is SQL60\DATA\ACCOUNTI.DAT. For a mirrored device, the default is the path of the MASTER.MIR file followed by the first eight characters of the mirror device's logical name. For example, if MACCOUNT is the name of the mirror device, and the MASTER.MIR file is located in SQL60\DATA, the default physical name is SQL\DATA\MACCOUNT.MIR.


polling interval

The option that sets how often the state of the service (SQL Server or SQL Executive) is checked.


primary domain controller (PDC)

In a Windows NT domain, a server that maintains the domain's security database and authenticates user logons. It also provides a copy of the domain's security database to backup domain controllers (BDCs), which share the user login authentication load.


primary key (PK)

The column or combination of columns that uniquely identifies a table. It must always be non-null and must always have a unique index. A primary key is used for joins with foreign keys (matching non-primary keys) in other tables.


publication

In replication, a group of tables that have been made available for replication as a unit. A publication can contain one or more published tables—articles—from one user database. Each user database can have one or more publications.


publication database

In replication, a database that is the source of replicated data or a database containing tables that have been made available for replication.


publish

To make data available for replication.


publisher

In replication, a server that has made data available for replication. A publisher maintains publication databases, makes published data from those databases available for replication, and sends copies of all changes to the published data to the distribution server. Also referred to as a publisher.


pull subscription

In replication, a subscription performed while administrative focus is set on the subscription server. A subscription is created by "pulling" in a publication or an article from a publication server.


push subscription

In replication, a subscription performed while administrative focus is set on the publication server. As part of the process of creating or managing a publication, subscriptions are created by "pushing" out an article to one or more subscription servers.


query

A specific request for data retrieval, modification, or deletion.


query optimizer

The SQL Server component responsible for generating the optimum execution plan for a query.


record

A group of related fields (columns) of information treated as a unit. The logical equivalent of a row.


referential integrity (RI)

Integrity mechanism that ensures that vital data in a database—such as the unique identifier for a given piece of data—remains accurate and usable as the database changes. Referential integrity involves managing corresponding data values between tables when the foreign key of a table contains the same values as the primary key of another table.


remote distribution server

In replication, when a publication server does not act as its own distribution server, but instead is configured to send transactions that are marked for replication to a distribution database on another computer. Compare with local distribution server.


remote login identification

The login identification (login ID) assigned to a user for accessing remote procedures on a remote server. This can be the same as the user's local login ID. A remote login ID can have as many as 30 characters. The characters can be alphanumeric, but the first character must be a letter (for example, CHRIS or TELLER8).


remote server

A SQL server on the network that can be accessed through a user's local server. The setup program can install, upgrade, or configure remote servers.


remote stored procedure

A collection of SQL statements and optional control-of-flow statements stored under a name on a remote server. Remote stored procedures can be called by clients or by SQL Server.


replication

Duplication of table schema and data from a source database to a destination database, usually (but not necessarily) on separate servers.


replication topology

The Replication Topology window provides a convenient way to view the replication relationships of a server, to add subscription servers, and to open the replication management dialog boxes that are provided by SQL Enterprise Manager.


restrict

In replication, to allow replication to only selected servers. A publication marked Unrestricted is visible to and can be subscribed to by any subscription server. A publication marked Restricted is visible only to those subscription servers that have been marked for access.


restricted

In replication, specifies who can access a publication.


restricted publication

In replication, a publication marked restricted is displayed to and can be subscribed to by only those servers that have been granted access. See also unrestricted publication.


restriction clause

In replication, to partition the article horizontally, so that only selected rows are replicated, type a WHERE clause in the Restriction Clause box.


retention

In replication, the period of time that a transaction is maintained in the distribution database after it has been successfully delivered to the destination database on the subscription server.


row

In a table, a set of related fields of information (columns) that are treated as a unit and that describe a specific entity. The logical equivalent of a record.


rule

A database object that is bound to a column or to a user-defined datatype and that specifies what data can be entered in that column. Every time a user enters or modifies a value (with an INSERT or UPDATE statement), SQL Server checks it against the most recent rule that has been bound to the specified column. No table lookups are possible, but limit checking and list checking are. Data entered prior to the creation and binding of a rule is not checked.


scheduled backup

A backup operation automatically accomplished by SQL Executive as defined and scheduled by an administrator.


scheduled table refresh

In replication, specifies that at scheduled intervals all articles in the publication will be refreshed. Each time refresh occurs, all data (and optionally, the table schemas) will be applied to all destination tables. No data changes are sent between the scheduled refresh intervals. Transaction-based replication does not occur.


schema

A description of a database the DBMS generated using the data definition language.


script

A collection of statements used to create database objects. Transact-SQL scripts are saved as files usually ending with the .SQL filename extension.


server name

Name of the SQL Server, it is taken from the computer's name. Must be a valid SQL identifier. The first character must be a letter or an underscore (_). Characters following can include letters, numbers, or the symbols #, $, or _. Embedded spaces are not allowed.


server state polling

Specifies the polling interval, which is used to set how often the state of the service (SQL Server or SQL Monitor) is checked.


severity level number

Represents the severity level of an error. Valid levels are 1 through 25. Only the system administrator can add a message with a severity level of 19 through 25.


shared lock

A lock created by nonupdate (read) operations. Other users can read the data concurrently, but no transaction can acquire an exclusive lock on the data until all the shared locks have been released.


SHOWPLAN

An option of ISQL/w and SQL Enterprise Manager. Shows the query execution plan for a query.


single-user mode

A startup mode that restricts connections. Only a single user can connect, and the CHECKPOINT mechanism, which guarantees that completed transactions are regularly written from the disk cache to the database device, is not started.


SQL Executive

A component of SQL Enterprise Manager, SQL Executive provides powerful scheduling capabilities that enable replication and additionally allow you to schedule other SQL Server events, including backups, DBCC operations, and other administrative maintenance tasks. SQL Executive replaces SQL Monitor, which was included in earlier releases of SQL Server.


SQLMail

Capability of Microsoft SQL Server that includes extended stored procedures that allow SQL Server to send and receive mail messages through the built-in mail application programming interface (MAPI) client interface in Windows NT. A mail message can consist of short text strings, the output from a query, or an attached file. This SQL Server mail capability is called SQLMail.


SQL Performance Monitor Integration

Integration of Windows NT Performance Monitor with SQL Server, providing up-to-the-minute activity and performance statistics.


SQL Security Manager

A SQL Server utility provided with SQL Server version 4.2x that provides a way to manage integrated and mixed security. In SQL Server 6.0 this functionality is provided by the SQL Enterprise Manager.


SQL Service Manager

A SQL Server−supplied utility that provides a graphical way to start, pause, and stop SQL Server, SQL Monitor, and SQL Replicator. SQL Server is also integrated with the service control management of Windows NT, so you can also start, pause, and stop SQL Server, SQL Monitor, and SQL Replicator from the Services application in the Control Panel or from the Server Manager application.


SQL Transfer Manager

SQL Server−supplied utility that provides an easy, graphical way to transfer objects and data from one SQL Server to another. You can transfer from a Microsoft-based SQL Server or other SQL Server to a Windows NT−based Microsoft SQL Server. (For example, you can transfer data from a Microsoft SQL Server running on an Intel-based SQL Server to a different processor architecture.)


standalone installation

An installation of SQL Server on a computer that is not connected to a network. A standalone installation may be used to install SQL Server on a computer that will soon be but is not yet connected to a network (for example, it does not yet have a network adapter card installed.) Or, a standalone installation might be performed on a computer that is intended as a development system that will have no need for network connections.


standard security

Security mode that uses SQL Server's own login validation process for all connections. To log in to a SQL Server, each user must provide a valid login ID and password.


statement permissions

Authorization that provides the privilege to issue certain Transact-SQL statements. Statement permissions are not object-specific. They can be granted only by the system administrator or the database owner. Statement permissions are also called command permissions.


stored procedure

A precompiled collection of Transact-SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Stored procedures are stored within database, can be executed with one call from an application, and allow user-declared variables, conditional execution and other powerful programming features. SQL Server−supplied stored procedures are called system stored procedures.


subscribe

In replication, agreeing to receive a publication or an article. A destination database on a subscription server subscribes to replicated data from a publication database on a publication server.


subscription server

In replication, a subscription server maintains destination databases, which receive and maintain copies of published data. Also referred to as a subscriber.


synchronization

In replication, the process that before a subscription server can receive replicated transactions from an article or a publication, the publication and destination tables must contain the same schema and data.


synchronization process

Process that creates synchronization files containing data from published tables and applies these files, along with table schema files, to destination database tables. The synchronization process is a subsystem of the scheduling engine in SQL Executive.


system administrator (SA)

A person responsible for the administrative (SA) and operational functions that are independent of any particular application. The system administrator is likely to be a person with a comprehensive overview of SQL Server and all its applications. The SA advises application designers about the data that already exists on SQL Server, makes recommendations about standardizing data definitions across applications, and so on.

The SA operates outside the protection system, (SQL Server does no permission checking for the system administrator). The SA is also treated as the owner of whatever database he or she is using. Anyone who knows the SA's password can log in and act in the role of SA.


table

A collection of rows (or records) that have associated columns (or fields). It is the logical equivalent of a database file.


table data file

In replication, a file containing a snapshot of the data of a published table and used during synchronization as the source of data inserted into the destination table. The data snapshot is written to a file ending in the .TMP filename extension. See also table schema script.


table schema script

A script containing the schema of a published table and used during synchronization to create the destination table. The schema script is written to a file ending in the .SCH filename extension. See also table data file.


table-level constraint

Constraints that allow various forms of data integrity to be defined on an individual column (column-level constraint) or on a combination of columns (table-level constraints) when the table is defined or altered. Constraints support domain integrity, entity integrity, and referential integrity, as well as user-defined integrity.


tape dump

Dumping a database to any tape device supported by Windows NT. If you are creating a tape dump device, you must first install the tape device using Windows NT. The tape device must be physically attached to the SQL Server you are backing up.


tempdb database

Database that provides a storage area for temporary tables and other temporary working storage needs. No special permissions are required to use tempdb (that is, to create temporary tables or to execute commands that might require storage space in the tempdb database). All temporary tables are stored in tempdb, no matter what database the user who creates them is using.


thread

A mechanism that allows one or more paths of execution through the same instance of an application. Each device requires one thread and each remote site requires two threads. SQL Server takes advantage of the native thread services of Windows NT.

There are separate threads for each network, a separate thread for database checkpoints, and a pool of threads for all users.


tight consistency

A replication model that guarantees all copies will be identical to the original. It is usually implemented using two-phase commit, and it requires a high-speed LAN. It also reduces database availability and is less scalable in its implementation as compared to loose consistency.


Transact-SQL (T-SQL)

The standard language for communicating between applications and SQL Server. The Transact-SQL language is an enhancement to Structured Query Language (SQL), the ANSI-standard relational database language, and it provides a comprehensive language for defining tables, for inserting, updating, or deleting information stored in tables, and for controlling access to data in those tables. Transact-SQL includes statements, commands, control-of-flow language, stored procedures, triggers, rules, and defaults. Extensions such as stored procedures make Transact-SQL a full programming language.


transaction log

A reserved area of the database in which all changes to the database are recorded. It is stored in the syslogs system table and is used by SQL Server during automatic recovery.


transaction processing

A processing method in which transactions are executed immediately after they are received by the system.


trigger

A special form of stored procedure that goes into effect when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.


trusted connection

Authenticated connections between clients and servers. These are referred to as trusted connections. The multiprotocol and named pipe protocols provide trusted connections. Integrated security requires network protocols that support trusted connections.


underlying object

An object (a table or another view) from which a view is derived. A view can have one or more underlying objects.


underlying table

A table from which a view is derived. A view can have one or more underlying tables. (A view can have underlying views.) Also called base table.


unhandled event-forwarding server

A remote server (the Unhandled SQL Server Event Forwarding Server) to whose event log any unhandled SQL Server events on the local server (events for which there are no locally defined alerts) will be sent.


UNIQUE constraints

Constraints that enforce entity integrity on a non-primary key. Ensure that no duplicate values are entered, Null values are not allowed, and that an index is created to enhance performance.


unique index

An index in which no two rows are permitted to have the same index value, thus prohibiting duplicate index or key values. The system checks for duplicate key values when the index is created and checks each time data is added with an INSERT or UPDATE statement.


universal naming convention (UNC)

Naming convention that consists of the following format:

\\servername\sharename\path\filename


unrestricted publication

In replication, a security status. A publication marked Unrestricted (the default) is displayed to and can be subscribed to by any registered subscription server. See also restricted publication.


update statistics

Statistics that update information about the distribution of key values in specified indexes. If there was no data in the table when the index on the table was created, update the index statistics after data is added.


user databases

All of the SQL Server − supplied tables in the master database. In addition, each user database is created with a subset of those system tables. The master database and its tables are created when SQL Server is installed. The system tables in a user database are automatically created when a database is created.


username

A name known to a database and assigned to a login ID for the purpose of allowing a user to have access to that database. The abilities a user has within a database depend on the permissions granted to the username, and the permissions granted to any groups the username is a member of. A username can have as many as 30 characters and must be unique within the database. The characters can be alphanumeric, but the first character must be a letter or the symbols # or _ (for example, CHRIS or USER8).


user-defined datatype

A definition of the type of data a column can contain. It is created by the user and defined in terms of existing system datatypes. Rules and defaults can be bound to user-defined datatypes (but not to system datatypes).


variables

Defined entities that are assigned values. A local variable is defined with a DECLARE statement and assigned an initial value within the statement batch where it is declared with a SELECT statement. Global variables are predefined and maintained by the system.


vertical partitioning

In replication, creating an article that replicates only selected columns from the base table. Subscription servers receive only that vertically partitioned subset of data. Note that the replicated columns must include the primary key column(s).


view

An alternative way of looking at data from one or more tables in the database, a view is a "virtual table." A view is usually created as a subset of columns from one or more tables. The tables from which views are derived are called base tables. A view can also be derived from another view. The definition of a view (the base tables from which it is derived) is stored in the database. The data that you view is stored in only one place, the base tables. No separate copies of data are associated with this stored definition. A view looks almost exactly like any other database table. You can display it and operate on it almost as you can on any other table.


Windows NT Event Viewer

Windows NT application that allows you to view events, filter certain events, and to retain event logs.


Windows NT Performance Monitor

Windows NT utility that provides system administrators a way to monitor the performance of SQL Server. SQL Server statistics include statistics on locks, current size of transaction logs, user connections, and server performance. You can even set alerts to take any action you specify if a specified threshold is reached.


write-ahead log

A transaction logging method in which the log is always written prior to the data.