Registered SQL Server Properties (General Tab)

Use this tab to view or specify the following options.

Options

Server

View the registered server running the instance of Microsoft® SQL Server™ that you want to edit. Click the search (...) button to perform a search for the server you want to view.

Use Windows Authentication

Use Windows Authentication when connecting to an instance of SQL Server. It is recommended that you use this option for security because users who connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account can make use of trusted connections. Trusted connections are those validated by Windows NT 4.0 or Windows 2000.

Use SQL Server Authentication

Use SQL Server Authentication when connecting to the server.

Login name

Specify the login name you want to use when connecting to the server using SQL Server Authentication.

Password

Specify the password you want to use when connecting to the server using SQL Server Authentication.

Always prompt for login name and password

Specify that SQL Server must always prompt for the login name and password when a user connects to the server using SQL Server Authentication.

Server group

Select the server group with which to associate the server. Click the add (...) button to add a server group to the list.

Display SQL Server state in console

Specify that the SQL Server state be displayed in the console.

Show system databases and system objects

Specify that system databases and system objects be displayed.

Automatically start SQL Server when connecting

Specify that an instance of SQL Server start automatically when you connect to the server.

See Also

Registering Servers


Server Groups (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the server group.

Top level group

Specify the group to appear at the top level of the console tree in the SQL Server Enterprise Manager window.

Sub-group of

Specify the group to appear as a subgroup of a server group.

See Also

Creating Server Groups


SQL Server Enterprise Manager Properties (General Tab)

Use this tab to view or specify the following options.

Options

Poll server to find out state of server and related services

Poll the server at regular intervals and view its status using the stoplight icon.

Service

Select which service to poll.

Poll interval (seconds)

Specify the time, in seconds, between server polls.

Read/Store locally

Read and store the registration information for the servers locally.

Read/Store user independent

Read and store the registration information for the servers separately for each user.

Read from remote

Read the registration information for a remote server.

Server name

Specify the remote server name where the registration information is stored.

Reset All

Reset all of the options in this dialog box.

See Also

How to set access to your display of servers and groups (Enterprise Manager)

How to set the polling interval (Enterprise Manager)

How to set up a central store for server registration information (Enterprise Manager)


SQL Server Enterprise Manager Properties (Advanced Tab)

Use this tab to view or specify the following options.

Options

Login time-out (seconds)

Specify the number of seconds to wait before the instance returns from a failed remote login attempt.

Query time-out (seconds)

Specify the number of seconds that must elapse during the processing of a remote query before the query times out. The default is 0, which allows an infinite wait.

Packet size (bytes)

Specify the size of the Tabular Data Stream (TDS) packets. The default size of TDS packets is 4 kilobytes (KB).

Perform translation for character data

Perform a translation of character data between different client and server code pages. This option only affects data stored in Microsoft® SQL Server™ char, varchar, and text columns.

Open the console tree to the last active node

Specify that upon startup, the console tree will open to the last previously active node in SQL Server Enterprise Manager.

Reset All

Reset all of the options on this tab.

See Also

How to configure packet size (Enterprise Manager)


SQL Server Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

View the name of the instance of Microsoft® SQL Server™.

Product

View the product name.

Operating system

View the operating system on which the server is running.

Product version

View the version of the instance of SQL Server.

Language

View the default language for the product.

Platform

View the platform on which the product runs.

OS memory

View the available operating system memory.

Processors

View the number of processors.

Root directory

View the root directory.

Server collation

View the type of collation that the instance of SQL Server is using.

Autostart SQL Server

Automatically start the instance of SQL Server when Microsoft Windows NT® 4.0 or Windows® 2000 starts.

Autostart SQL Server Agent

Automatically start SQL Server Agent when Windows NT 4.0 or Windows 2000 starts.

Autostart MSDTC

Automatically start Microsoft Distributed Transaction Coordinator (MS DTC) when Windows NT 4.0 or Windows 2000 starts.

Startup parameters

Display the Server Parameters dialog box.

Network Configuration

Start the SQL Server Network Utility.

See Also

Starting SQL Server Automatically

Using Startup Options

SQL Server Network Utility


Startup Parameters

Use this dialog box to view or specify the following options.

Options

Parameter

Specify the server parameter to add or remove.

Existing parameters

View the existing server parameters.

Add

Add a server parameter.

Remove

Remove a server parameter.

See Also

Using Startup Options


SQL Server Properties (Memory Tab)

Use this tab to view or specify the following options.

Options

Dynamically configure SQL Server memory

Specify that Microsoft® SQL Server™ memory be configured immediately after you make changes to the server properties.

Use a fixed memory size

Specify a fixed memory size for SQL Server.

Reserve physical memory for SQL Server

Reserve physical memory space for SQL Server equal to the memory setting. This means Microsoft Windows NT® 4.0 or Windows® 2000 does not swap out SQL Server's pages even if the pages can be used more readily when SQL Server is idle.

Minimum query memory

Specify the minimum amount of memory that can be allocated per user for query execution. The default is 1024 kilobytes (KB).

Configured values

View or change the configured values for the options on this tab. If you change these values, click Running values to see whether the changes have taken effect. If they have not, you must restart the instance of SQL Server for the changes to be implemented.

Running values

View the current running values for the options on this tab. These values are read-only.

See Also

Setting Configuration Options


SQL Server Properties (Processor Tab)

Use this tab to view or specify the following options.

Options

Processor

Specify the processor you want the instance of Microsoft® SQL Server™ to use.

Maximum worker threads

Specify the maximum number of worker threads available to SQL Server processes.

Boost SQL Server priority on Windows

Specify whether an instance of SQL Server can run at a higher priority than other processes on the same computer. The default is 0, which is a priority base of 7. If you set this option to 1, SQL Server runs at a priority base of 13 in the Microsoft Windows NT® 4.0 or Windows® 2000 scheduler. It is recommended that you change the default only on Windows NT 4.0 or Windows 2000 systems dedicated to SQL Server.

Use Windows NT fibers

Specify that you want an instance of SQL Server to use fibers instead of threads. When using fibers, SQL Server allocates one thread per CPU and then allocates one fiber per concurrent user, up to the max worker threads value. This setting takes effect after you restart the server.

Use all available processors

Specify that you want SQL Server to use all available processors for the parallel execution of queries.

Use processors

Specify the number of processors you want SQL Server to use for the parallel execution of queries.

Minimum query plan threshold for considering queries for parallel execution

Specify the threshold at which SQL Server creates and executes parallel plans. SQL Server creates and executes a parallel plan for a query only when the estimated cost to execute a serial plan for the same query is higher than the value set for this option.

View or change the configured values for the options on this tab. If you change these values, click Running values to see whether the changes have taken effect. If they have not, you must restart the instance of SQL Server for the changes to be implemented.

Running values

View the current running values for the options on this tab. These values are read-only.

See Also

Setting Configuration Options


SQL Server Properties (Security Tab)

Use this tab to view or specify the following options.

Options

SQL Server and Windows

Specify that users can connect to the instance of Microsoft® SQL Server™ using SQL Server Authentication and Windows Authentication. This is considered Mixed Mode authentication. Users who connect through a Microsoft Windows NT 4.0 or Windows 2000 user account can make use of trusted connections in either Windows Authentication or Mixed Mode. When a user connects through a Windows NT 4.0 or windows 2000 user account, SQL Server revalidates the account name and password by calling back to Windows NT 4.0 or Windows 2000 for the information.

Windows only

Specify that users can connect to the instance of SQL Server using Windows Authentication only.

None

Disable auditing. This is the default for this setting.

Success

Audit on successful login attempts. You can record attempted user accesses as well as other SQL Server log information, and enable auditing for both security modes and you can record information on both trusted and nontrusted connections. Log records for these events appear in the Microsoft Windows® application log, the SQL Server error log, or both, depending on how you configure logging for the instance of SQL Server.

If you select this option, you must stop and restart the server for auditing to be enabled.

Failure

Audit on failed login attempts. You can record attempted user accesses as well as other SQL Server log information, and enable auditing for both security modes, and you can record information on both trusted and nontrusted connections. Log records for these events appear in the Windows application log, the SQL Server error log, or both, depending on how you configure logging for your instance of SQL Server.

If you select this option, you must stop and restart the server to enable auditing.

All

Audit on both successful and failed login attempts. You can record attempted user accesses as well as other SQL Server log information, and enable auditing for both security modes, and you can record information on both trusted and nontrusted connections. Log records for these events appear in the Windows application log, the SQL Server error log, or both, depending on how you configure logging for your SQL Server.

If you select this option, you must stop and restart the server to enable auditing.

System account

Specify that the instance of SQL Server service account is the built-in local system administrator account.

This account

Specify that the SQL Server service account is a Microsoft Windows NT® 4.0 or Windows 2000 domain account. This field is only enabled if you are using a valid Windows NT 4.0 or Windows 2000 administrator account on the computer where the registered instance of SQL Server is running.

Password

Specify the password for the Windows NT 4.0 or Windows 2000 domain account. This field is only enabled if you are using a valid Windows NT 4.0 or Windows 2000 administrator account on the computer where the registered instance of SQL Server is running.

See Also

Setting Configuration Options


SQL Server Properties (Connections Tab)

Use this tab to view or specify the following options.

Options

Maximum concurrent user connections

Specify the maximum concurrent user connections. Entering zero means there can be an unlimited number of concurrent user connections.

Default connection options

Specify the default connection options for the selected server.

Allow other SQL Servers to connect remotely to this SQL Server using RPC

Allow other instances of Microsoft® SQL Server™ to connect remotely to this server by using a remote procedure call (RPC).

Query time-out (seconds)

Specify the number of seconds that must elapse during a remote query before the query times out. Specifying zero means that an unlimited amount of time can elapse.

Enforce distributed transactions (MTS)

Protect a server-to-server procedure by using Microsoft Distributed Transaction Coordinator (MS DTC) to coordinate distributed transactions.

Configured values

View or change the configured values for the options on this tab. If you change these values, click Running values to see whether the changes have taken effect. If they have not, you must restart the instance of SQL Server for the changes to be implemented.

Running values

View the current running values for the options on this tab. These values are read-only.

See Also

Setting Configuration Options


SQL Server Properties (Server Settings Tab)

Use this tab to view or specify the following options.

Options

Default language for user

Specify the default language for server messages.

Allow modifications to be made directly to the system catalogs

Allow modifications to be made directly to the system catalogs.

Allow triggers to be fired which fire other triggers (nested triggers).

Allow nested triggers to be fired.

Use query governor to prevent queries exceeding specified cost

Select the cost query governor as a tool for preventing queries from exceeding the specified cost.

Mail login name

Specify the valid mail login name for the mail client.

When a two-digit year is entered, interpret as a year between

Specify how an instance of Microsoft® SQL Server™ interprets two-digit years. To change the time span, type the ending year. The default time span is 1950 to 2049. The beginning date is January 1, 1950, and the ending date is December 31, 2049. 99 is interpreted as 1999 and 01 is interpreted as 2001. The rule is that years less than or equal to the last two digits of the cutoff year are in the same century as that of the cutoff year. Years greater than the last two digits of the cutoff year are in the century previous to that of the cutoff year. Four-digit years are not affected by this option. If you want SQL Server to use the same two-digit cutoff year as the client, select 2030.

Configured values

View or change the configured values for the options on this tab. If you change these values, click Running values to see whether the changes have taken effect. If they have not, you must restart the instance of SQL Server for the changes to be implemented.

Running values

View the current running values for the options on this tab. These values are read-only.

See Also

Setting Configuration Options


SQL Server Properties (Database Settings Tab)

Use the Database Settings tab to view or specify the following options.

Options

Fixed

Set the default index fill factor manually. The index fill factor determines how full Microsoft® SQL Server™ makes each page when it creates a new index using existing data. When this option is cleared, SQL Server selects the optimal setting for performance.

Wait indefinitely

Specify that DB-Library must wait indefinitely for the instance of SQL Server to respond.

Try once then quit

Specify that DB-Library must try once to connect to an instance of SQL Server and then time out.

Try for minute(s)

Specify the time, in minutes, that DB-Library must try to connect to an instance of SQL Server before timing out.

Default backup media retention (days)

Set a system-wide default for the length of time to retain each backup medium after the backup has been used for a database or transaction log backup.

Recovery interval (Min)

Set the maximum number of minutes per database that SQL Server needs in order to complete its recovery procedures. The default is 0 minutes per database, which is the autoconfiguration for fast recovery.

Default data directory

Specify the default directory used for data files when new databases are created in SQL Server. Click the browse (...) button to search for an existing data directory.

Default log directory

Specify the default directory used for log files when new databases are created in SQL Server. Click the browse (...) button to search for an existing log directory.

Configured values

View or change the configured values for the options on this tab. If you change these values, click Running values to see whether the changes have taken effect. If they have not, you must restart the instance of SQL Server for the changes to be implemented.

Running values

View the current running values for the options on this tab. These values are read-only.

See Also

Setting Configuration Options

How to set a fixed fill factor (Enterprise Manager)

How to set the backup retention duration (Enterprise Manager)

How to set the recovery interval (Enterprise Manager)


Database Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

View the name of the selected database.

Status

View the status of the selected database.

Owner

View the owner of the selected database.

Date created

View the date and time the database was created.

Size

View the size of the database in megabytes (MB).

Space available

View the space available in the database.

Number of users

View the number of database users.

Last database backup

View the date and time of the last database backup.

Last transaction log backup

View the date and time of the last transaction log backup.

Maintenance plan

View details about the maintenance plan.

Collation name

View the database collation type.

See Also

Viewing a Database


Database Properties (Transaction Log Tab)

Use this tab to view or specify the following options.

Options

Transaction log files

View the file name, location, and space allocated for the transaction log files.

Delete

Delete the selected transaction log file.

Automatically grow file

Specify that the transaction log files grow automatically.

In megabytes

Specify that the transaction log files grow automatically by megabytes (MB).

By percent

Specify that the transaction log files grow automatically by percent.

Unrestricted filegrowth

Specify that the transaction log files can grow without restriction.

Restrict filegrowth (MB)

Specify the size, in megabytes, to which a restricted transaction log file can grow.

See Also

Transaction Logs


Database Properties (Options Tab)

Use this tab to view or specify the following options.

Options

Restrict access

Specify that only the users indicated in the following options can access the database.

Members of db_owner, dbcreator, or sysadmin

Specify that only members of db_owner, dbcreator, or sysadmin can access the database.

Single user

Specify that only one user can access the database at a time.

Read only

Specify that users can retrieve, but not modify, data from the database.

Model

Specify the type of recovery model for the database. For more information on recovery models, see Using Recovery Models.

ANSI NULL default

Specify whether database columns are defined as NULL or NOT NULL by default.

Recursive triggers

Enable recursive firing of triggers.

Select into/bulkcopy

Specify that nonlogged operations can be performed. This option is only available if you are viewing the database properties on a Microsoft® SQL Server™ version 7.0 server. This specifies the type of recovery model for the database. For more information on recovery models, see Using Recovery Models.

Truncate log on checkpoint

Specify that the transaction log is truncated when the checkpoint process occurs. This option is only available if you are viewing the database properties on a SQL Server 7.0 server. This specifies the type of recovery model for the database. For more information on recovery models, see Using Recovery Models.

Torn page detection

Specify that incomplete pages can be detected.

Auto close

Specify that the database is shut down after its resources are freed and all users exit.

Auto shrink

Specify that the database files are candidates for automatic periodic shrinking.

Auto create statistics

Specify that any missing statistics needed by a query for optimization are built automatically during optimization.

Auto update statistics

Specify that out-of-date statistics needed by a query for optimization are built automatically during optimization.

Use quoted identifiers

Specify that SQL Server enforce ANSI rules regarding quotation marks. Select this option to specify that double quotation marks be used only for identifiers, such as column and table names. Character strings must be enclosed in single quotation marks.

Level

Specify the database compatibility level.

List this database in the Active Directory

Specify to list this database in the Active Directory. Active Directory is a central component of the Microsoft Windows® 2000 operating system and provides a place to store information about network-based entities, such as applications, files, printers, and people. This option is only available if the database is on a server that has been added to the Active Directory.

See Also

Setting Database Options


Database Properties (Permissions Tab)

Use this tab to view or specify the following options.

Options

User/Role

View the name of the user or role.

Create Table

Specify whether permission to create a table is granted for each user or role.

Create View

Specify whether permission to create a view is granted for each user or role.

Create SP

Specify whether permission to create a system procedure is granted for each user or role.

Create Default

Specify whether permission to create a default is granted for each user or role.

Create Rule

Specify whether permission to create a rule is granted for each user or role.

Create Function

Specify whether permission to create a function is granted for each user or role.

Backup DB

Specify whether permission to create a backup database is granted for each user or role.

Backup Log

Specify whether permission to create a backup log is granted for each user or role.

See Also

Managing Permissions


Database Role Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the database role.

Permissions

Display the Database User Properties dialog box, where you can specify the login name and user name for the database user.

Standard role

Specify that the database role is standard if you are creating a new database role. Add and review members of an existing database role.

Add

Add users to the database role.

Remove

Remove users from the database role.

Application role

Specify that the database is an application role, which requires a password.

Password

Specify the application role password.

See Also

Database Roles

Viewing Roles


Database User Properties (General Tab)

Use this tab to view or specify the following options.

Options

Login name

Specify the login name of the database user.

User name

Specify the user name of the database user.

Permissions

Display the Database User Properties dialog box, where you can specify user database permissions.

Permit in database role

Specify to which database role the user belongs.

Properties

Display the Database Role Properties dialog box, where you can add users to or remove users from the role.

See Also

Users

Viewing Database Users


Database User Properties (Permissions Tab)

Use this tab to view or specify the following options. A black check indicates that the permission has been granted. A red X indicates that the permission has been denied.

Options

Database user

Specify the user for which the permissions are displayed.

List all objects

List all the objects for the selected database. Click the appropriate box to modify database user permissions.

List only objects with permissions for this user

List only the objects for which the user has permission. Click the appropriate box to modify database user permissions.

Columns

Display the Column Permissions dialog box, where you can manage the permissions for each column.

See Also

Managing Permissions


SQL Server Login Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the Microsoft® SQL Server™ login.

Windows Authentication

Use Windows Authentication when connecting to an instance of SQL Server. It is recommended that you use this option for security because users who connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account can make use of trusted connections. Trusted connections are those validated by Windows NT 4.0 or Windows 2000.

Domain

View the Windows NT 4.0 or Windows 2000 domain. This domain account provides network access to other servers in the domain.

Grant access

Grant login access to a Windows NT 4.0 or Windows 2000 account.

Deny access

Deny login access to a Windows NT 4.0 or Windows 2000 account.

SQL Server Authentication

Use SQL Server Authentication when connecting to the server. This option uses SQL Server security to validate the user.

Password

Specify the password to use when connecting to the server using SQL Server Authentication.

Database

View the default database for this login.

Language

View the default language for this login.

See Also

Logins

Managing Security


SQL Server Login Properties (Server Roles Tab)

Use this tab to view or specify the following options.

Options

Server role

Grant server-wide security permissions to a login.

Description

Describe the login permissions for a role.

Properties

Display the Server Role Properties dialog box, where you can grant server-wide permissions to a server role.

See Also

Roles

Managing Security


SQL Server Login Properties (Database Access Tab)

Use this tab to view or specify the following options.

Options

Specify which databases can be accessed by this login

Specify which database the login can access.

Database roles

Select permissions for each database role.

Properties

Display the Database Role Properties dialog box, where you can add members to or remove members from the database role.

See Also

Managing Security


Default Properties (General Tab)

Use this to view or specify the following options.

Options

Name

Specify the name of the default.

Value

Specify the value of the default.

Bind UDTs

Bind the default to a user-defined data type.

Bind Columns

Bind the default to a column.

See Also

Defaults

CREATE DEFAULT


Rule Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the rule.

Text

Specify the text of the rule.

Bind UDTs

Bind the rule to a user-defined data type.

Bind Columns

Bind the rule to a column.

See Also

Rules

CREATE RULE


Stored Procedure Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the stored procedure.

Permissions

Display the Object Properties dialog box, where you can specify stored procedure permissions.

Owner

View the owner of the stored procedure.

Create date

View the creation date of the stored procedure.

Text

View the text occurring in the stored procedure. If you want to indent a Transact-SQL statement in a stored procedure created through SQL Server Enterprise Manager, use CTRL+Tab.

Check Syntax

Check the syntax of the Transact-SQL script used to create the stored procedure.

See Also

Stored Procedures


User-Defined Data Type Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Create a user-defined data type name or review an existing user-defined data type name. This name can be used across a database to represent a data type.

Data type

Specify the data type the user-defined name represents.

Length

View or change the length of the user-defined data type.

Allow NULLs

Specify whether the data type can allow nulls.

Rule

Specify the rules associated with the user-defined data type.

Default

Specify the default for the user-defined data type.

Where Used

View where the user-defined data type is used.

See Also

Creating User-Defined Data Types


Extended Stored Procedure Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the extended stored procedure.

Permissions

Display the Object Properties dialog box, where you can specify permissions for extended stored procedures.

Path

Specify the location of the extended stored procedure. Click the browse (...) button to search for an extended stored procedure.

See Also

Extended Stored Procedures


Remote Server Properties, General Tab

SQL Server Enterprise Manager Help

Remote Server Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the remote server.

RPC

Enable the remote server to execute stored procedures on the local server using a remote procedure call (RPC).

Map all remote logins to

Specify the login ID that all remote logins must use to access the local server. This option is selected by default.

Check password

Check the password for a remote login when it connects to the local server.

Map remote logins to different local logins

Map each remote login ID with a specific local login ID for users connecting to the local server from the remote server.

See Also

Configuring Remote Servers


Linked Server Properties (General Tab)

Use this tab to view or specify the following options.

Options

Linked server

Specify the name of the linked server.

SQL Server

Identify the linked server as an instance of Microsoft® SQL Server™. If you use this method of defining a SQL Server linked server, the name specified in Linked server must be the network name of the server. Also, any tables retrieved from the server are from the default database defined for the login on the linked server.

Other data source

Define a linked server through any of the available OLE DB providers, including SQL Server, by specifying the following:

Provider name
Specify the name of the OLE DB provider managing the access to the specified linked server.
Provider options
Display the Provider Options dialog box, where you view the various attributes of the linked server.
Product name
Specify the product name of the OLE DB data source you want to add as a linked server.
Data source
Specify the OLE DB data source property corresponding to the linked server.
Provider string
Specify the OLE DB provider string property corresponding to the linked server.
Location
Specify the OLE DB location property corresponding to the linked server.
Catalog
Specify the OLE DB catalog property corresponding to the linked server.

See Also

Configuring Linked Servers


Linked Server Properties (Security Tab)

Use this tab to view or specify the following options.

Options

Local login

Specify the local login IDs that can connect to the linked server.

Impersonate

Specify that the local login ID will be used to connect to the linked server. Select this option if you are certain that the local login ID exactly matches a login ID with sufficient permissions on the linked server.

Remote user

Use the remote user to map users not defined in Local login.

Remote password

Specify the password used to map users who are not defined in Local login.

Not be made

Specify that for logins not defined in the list, a connection will not be made.

Be made without using a security context

Specify that for logins not defined in the list, a connection will be made without using a security context.

Be made using the login's current security context

Specify that for logins not defined in the list, a connection will be made using the current security context of the login.

Be made using this security context

Specify that for logins not defined in the list, a connection will be made using the login and password specified in the Remote login and With password boxes.

See Also

Configuring Linked Servers

Establishing Security for Linked Servers


Database Maintenance Plan (General Tab)

Use this tab to view or specify the following options.

Options

Plan name

Specify the name of the maintenance plan.

All databases

Generate a maintenance plan that runs maintenance tasks against all instances of Microsoft® SQL Server™.

All system databases (master, model and msdb)

Generate a maintenance plan that runs maintenance tasks against each of the SQL Server system databases. No maintenance tasks are run against user-created databases.

All user databases (not master, model and msdb)

Generate a maintenance plan that runs maintenance tasks against all user-created databases. No maintenance tasks are run against the SQL Server system databases.

These databases

Generate a maintenance plan that runs maintenance tasks only against the databases you select from the list. If you choose this option, you must select at least one database. This option is the default.

See Also

Database Maintenance Plan Wizard


Database Maintenance Plan, Optimizations Tab

SQL Server Enterprise Manager Help

Database Maintenance Plan (Optimizations Tab)

Use this tab to reorganize your data and index pages, allowing the query optimizer better access to execution plans.

Options

Reorganize data and index pages

Cause table indexes in the database to be dropped and re-created with a new fill factor. The FILLFACTOR determines how much empty space to leave on each page in the index and reserves a percentage of free space on each data page of the index to accommodate future expansion. As data is added to the table, the free space fills up because the FILLFACTOR is not maintained. Reorganizing data and index pages can reestablish the free space.

Reorganize pages with the original amount of free space

Drop and re-create table indexes in the database with the original FILLFACTOR that was specified when the indexes were first created.

Change free space per page percentage to

Drop and re-create the indexes with a new, automatically recalculated FILLFACTOR, thereby reserving the specified amount of free space on the index pages. The higher the percentage, the more free space is reserved on the index pages and the larger the index grows. Valid values are 0 to 100.

Update the statistics used by the query optimizer

Resample the distribution statistics of each index created on user tables in the database. The distribution statistics are used by Microsoft® SQL Server™ to optimize navigation through tables during the processing of Transact-SQL statements. To build the distribution statistics automatically, SQL Server periodically samples a percentage of the data in the corresponding table for each index. This percentage is based on the number of rows in the table and the frequency of data modification. Use this option to perform an additional sampling using the specified percentage of data in the tables.

Percentage of database to sample

Specify the percentage of data in the tables to sample in order to generate distribution statistics. As the percentage increases, the accuracy of the statistics increases. However, the sampling takes an increasingly long time. If the specified value does not generate a sufficient sample, SQL Server determines an adequate sample size automatically. Valid values are 1 to 100.

Remove unused space from database files

Remove any unused space from the database, thereby reducing the size of the data files.

Shrink database when it grows beyond
Remove unused space from the database only if the database exceeds the specified size, in megabytes (MB).
Amount of free space to remain after shrink
Determine the amount of unused space that will remain in the database after the database has shrunk. The greater the percentage, the smaller the amount by which the database can shrink. The value is based on the percentage of the actual data in the database. For example, if you were to shrink a 100 MB database containing 60 MB of data and 40 MB of free space, with a free space percentage of 50%,you would have 60 MB of data and 30 MB of free space left. Only excess space in the database is eliminated. Valid values are 0 to 100.

Schedule

Set the frequency at which the data optimization tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 12:00 midnight.

Change

Display the Edit Recurring Job Schedule dialog box, where you can change the default schedule.

See Also

Database Maintenance Plan Wizard

Optimizing Database Performance Overview


Database Maintenance Plan (Integrity Tab)

Use the Integrity tab to run data integrity tests, which detect corrupted data. Corruption sometimes may be caused by a hardware or software errors.

Options

Check database integrity

Check the allocation of data pages in the database.

Include indexes
Check allocations in indexes and data pages when testing database integrity.
Attempt to repair any minor problems
Automatically attempt to correct any minor problems detected during the internal data integrity tests. It is recommended that you select this option.

Exclude indexes

Check allocations only in data pages when testing database integrity. It is faster to use this test than to test the database with indexes because fewer pages are checked.

Perform these tests before backing up the database or transaction log

Execute the database or internal data integrity tests before backing up the database or transaction log. If the integrity tests detect inconsistencies, subsequent database or transaction log backups are not backed up.

Schedule

Set the frequency at which the data integrity tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 12:00 midnight.

Change

Display the Edit Recurring Job Schedule dialog box, where you can change the default schedule.

See Also

Database Maintenance Plan Wizard

Data Integrity

Database Integrity Check


Database Maintenance Plan (Complete Backup Tab)

Use this tab to make backup copies of the database, protecting against data loss in the event of a failure.

Options

Back up the database as part of the maintenance plan

Back up the entire database as part of regular maintenance tasks. Backing up the database is important because of potential system or hardware failure, or because user errors can damage the database, thus requiring a backed-up copy of the database to be restored.

Verify the integrity of the backup upon completion

Check that the backup set is complete and that all volumes are accessible if you execute the Transact-SQL statement, RESTORE VERIFYONLY.

Tape

Back up the database to the specified tape device. The tape device is attached to the computer that contains the Microsoft® SQL Server™ database being backed up.

Disk

Back up the database to the disk located on the computer containing the SQL Server database being backed up.

Use the default backup directory
Back up the database to the \Program Files\Microsoft SQL Server\MSSQL\BACKUP disk directory located on the computer that contains the SQL Server database being backed up.
Use this directory
Back up the database to the specified disk directory located on the computer that contains the SQL Server database being backed up. Click the browse (...) button to change the default disk directory used to back up the database. You can select drives only on the computer that contains the SQL Server database that is being backed up.
Create a sub-directory for each database
Create a sub-directory under the specified disk directory that contains the database backup for each database being backed up as part of the maintenance plan.
Remove files older than
Automatically delete database backups that are older than the specified period. It is recommended that you maintain a history of database backups in the event that the database must be restored to a point in time earlier than the last performed backup. Retain as many backups as disk space allows, extending as far back as you eventually may need to restore, depending on business practices.

Backup file extension

Specify the file name extension used for each file containing a database backup. The default file extension is .bak.

Schedule

Set the frequency at which the database backup tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 12:00 midnight.

Change

Display the Edit Recurring Job Schedule dialog box, where you can change the default schedule.

See Also

Database Maintenance Plan Wizard

Backing Up and Restoring Databases


Database Maintenance Plan (Transaction Log Backup Tab)

Use this tab to make backup copies of the transaction log, protecting against data loss in the event of a failure.

Options

Back up the transaction log of the database as part of the maintenance plan

Back up the transaction log as part of regular maintenance tasks. Backing up the transaction log is important in case the database must be restored to a specific point in time. Selecting this option makes the following options available.

Verify the integrity of the backup upon completion

Check that the backup set is complete and that all volumes are accessible if you execute the Transact-SQL statement, RESTORE VERIFYONLY.

Tape

Back up the transaction log to the specified tape device. The tape device is attached to the computer that contains the Microsoft® SQL Server™ transaction log that is being backed up.

Disk

Back up the transaction log to the disk located on the computer that contains the SQL Server transaction log being backed up.

Use the default backup directory
Back up the database to the Program Files\Microsoft SQL Server\MSSQL\BACKUP disk directory located on the computer running the instance of SQL Server being backed up.
Use this directory
Back up the database to the specified disk directory located on the computer that contains the SQL Server database being backed up. Click the browse (...) button to change the default disk directory used to back up the database. You can select drives only on the computer that contains the SQL Server database that is being backed up.
Create a subdirectory for each database
Create a subdirectory under the specified disk directory that contains the database backup for each database being backed up as part of the maintenance plan.
Remove files older than
Delete database backups that are older than the specified period automatically. It is recommended that you maintain a history of database backups in the event that the database must be restored to a point in time earlier than the last performed backup. Retain as many backups as disk space allows and as far in the past from which you may need to restore, depending on business practices.

Backup file extension

Specify the file name extension used for each file containing the database backup. The default file extension is .bak.

Schedule

Set the frequency at which the transaction log backup tasks (scheduled using SQL Server Agent) are executed. The default is every day, except Sunday, at 12:00 midnight.

Change

Display the Edit Recurring Job Schedule dialog box, where you can change the default schedule.

See Also

Database Maintenance Plan Wizard

Transaction Log Backups


Database Maintenance Plan (Reporting Tab)

Use this tab to save in a file or write to a table a record of the maintenance activities performed by Microsoft® SQL Server™.

Options

Write report to a text file in directory

Specify the full path and name of the text file into which the report is to be generated. The report contains details of the steps executed by the maintenance plan, including any error information. The report maintains version information by adding a date to the file name. The date is generated as a suffix to the file name but is added before the extension, in the form _YYYYMMDDHHMM (for example, "DB Maintenance Plan10_199804090838.txt").

Click the browse (...) button to change the default directory for the text file. You can select directories only on the instance of SQL Server running the maintenance plan.

Delete text report files older than
Automatically Delete text report files that are older than the specified period . It is recommended that you maintain a history of text report files so that you can check executed maintenance tasks as far back in time as you think you may ever need to check, depending on business practices.

E-mail report to operator

Specify the operator to whom the generated report will be sent through SQL Mail. Click the browse (...) button to specify an existing operator or to create a new one using SQL Server Enterprise Manager.

New

Display the New Operator Properties dialog box, where you can create a new operator.

Write history to the table msdb.dbo.sysdbmaintplan_history

Write the history report as rows to the msdb.dbo.sysdbmaintplan_history table on the server where the maintenance plan was executed. The report contains the steps executed by the maintenance plan, including database name, activity, date, result (success or failure), and any error information, with one row for each activity, per database, per execution date.

View History

Display the Database Maintenance Plan History dialog box, where you can view the history of the database maintenance plan for the local server.

Limit rows in the table to

Specify the maximum number of rows in the table. These rows represent the history for this plan only. If the number of history rows in the table for this plan exceeds this value, older rows for this plan (representing the earliest recorded history) are deleted. Setting this value can prevent the table from becoming too large and filling the msdb database (unless auto-grow is permitted). The default value is 100.

Write history to the table on server

Write the history report as rows to the msdb.dbo.sysdbmaintplan_history table on a remote server. Windows Authentication is used to connect to the remote server. The report contains the steps executed by the maintenance plan, including database name, activity, date, result (success or failure), and any error information, with one row for each activity, per database, per execution date.

Click the browse (...) button to specify a server from a list of active servers on the network.

View History

View the history of the database maintenance plan for the remote server.

Limit rows in the table to

Specify the maximum number of rows in the table representing the history for this plan only. If the number of history rows in the table for this plan exceeds this value, older rows for this plan (representing the earliest recorded history) are deleted. Setting this value can prevent the table from becoming too large and filling the msdb database (unless auto-grow is permitted). The default value is 10,000.

See Also

Database Maintenance Plan Wizard


Database Maintenance Plan History

Use this dialog box to view the history of database maintenance plans that either have completed successfully or have failed to complete. By default, the history of all plans is shown. To view a subset of the plans, specify the criteria that the plans to be viewed must have. The history of a plan is displayed only if it matches all of the specified criteria.

Options

Plan name

Specify the name of the database maintenance plan you want to view.

Server name

Specify the name of the server containing the database maintenance plan history you want to view.

Database

Specify the name of the database whose history you want to view.

Status

Specify the status of the plans whose history you want to view: Succeeded or Failed. If you click Failed, type keywords in the Keywords in message box to further narrow the scope of the plan histories displayed.

Activity

Specify the activity of the plans whose history you want to view: Optimizations, Integrity Checks, Database Backup, Transaction Log Backup.

Keywords in message

Specify the error message keywords for the history you want to view, or view the history of successful backup jobs.

Automatically apply filters

Apply the group of criteria to the entire set of database plans each time you change one of the filter settings. Clear this option until you have set all the filter criteria to your satisfaction. This option is the default.

Properties

Display the properties dialog box for the selected database maintenance plan.

Delete

Delete the selected maintenance plan history.

Refresh

Refresh this dialog box with the most current data.

See Also

Database Maintenance Plan Wizard


Send Message

Use this dialog box to send a message to a connected user or computer.

Options

Message

Type the text of the message that will be sent to the connected computer or user.

Using username

Send the message to the connected user. The user name is specified.

Using hostname

Send the message to the connected computer. You can send the message to a different computer by typing another computer name in the box.

Send

Transmit the message over the network.

See Also

SQL Server and Mail Integration

Configuring SQL Mail


Process Details

Use this dialog box to view or specify the following options.

Options

Last T-SQL command batch

View the first 255 bytes of text of the Transact-SQL statement that was executed by the selected process.

Send message

Send a message to a connected computer or user. For example, you can send a message to connected users notifying them that the server will be shut down for maintenance. The users can then exit their operations and log off the server in anticipation of the server shutdown.

Kill Process

End a process without a forewarnedexit. Use this when a nonsystem process cannot be stopped in any other way. You must be a member of the sysadmin role to terminate processes.

Refresh

Perform a new query against the server and repaint the window to get an accurate depiction of the current activity.

See Also

Monitoring with SQL Server Enterprise Manager


Trigger Properties

Use this dialog box to view or specify the following options.

Options

Name

Specify the name of the trigger.

Text

Specify the Transact-SQL syntax that defines the trigger.

Check Syntax

Check the syntax of the Transact-SQL script used to create the trigger.

Delete

Delete the trigger from the database.

See Also

Enforcing Business Rules with Triggers


Table Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the table.

Permissions

Display the Object Properties dialog box, where you can specify user table permissions.

Owner

View the name of the table owner.

Create date

View the date that the table was created in the database.

Filegroup

View the filegroup within which the table is stored.

Rows

View the number of rows in the table.

Key

View the table columns that make up the primary key.

ID

View the table columns that have identities associated with them.

Name

View the names of each table column.

Data type

View the data types associated with each table column.

Size

View the table column size in bytes.

Nulls

View the table columns that allow null values.

Default

View the table columns that have a default associated with them.

See Also

Tables


Object Properties (Permissions Tab)

Use this tab to view or specify the following options.

Options

Object

Specify the name of the object.

List all users/user-defined database roles/public

List all users and database roles for the selected database. Click the appropriate box to modify object permissions.

List only users/user-defined database roles/public with permissions on this object

List only users or database roles that already have permissions on the object. Click the appropriate box to modify database user permissions.

User/Database roles/public

View the user ID or database role.

SELECT

Grant, revoke, or deny SELECT permissions on this object.

INSERT

Grant, revoke, or deny INSERT permissions on this object.

UPDATE

Grant, revoke, or deny UPDATE permissions on this object.

DELETE

Grant, revoke, or deny DELETE permissions on this object.

EXEC

Grant, revoke, or deny EXECUTE permissions on this object.

DRI

Grant, revoke, or deny declarative referential integrity permissions on this object.

Columns

Display the Column Permissions dialog box, where you can manage permissions for each column.

See Also

Managing Permissions


View Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

View the name of the Microsoft® SQL Server™ view.

Permissions

Display the Object Properties dialog box, where you can specify user view permissions.

Owner

View the name of the view owner.

Date created

View the date on which the view was created.

Text

View the Transact-SQL script used to create the view.

Check Syntax

Check the syntax of the Transact-SQL script used to create the view.

See Also

Views


Generate SQL Scripts (General Tab)

Use this tab to generate Transact-SQL statements that create objects identical to those currently in your database. This is useful if you want to create objects on other servers with the same schema as those in your original database.

Options

Show All

View all available objects for scripting.

Preview

Preview the Transact-SQL script that will be created from the options you select.

Script all objects

Include all database objects in the Transact-SQL script.

All tables
Include all database tables in the Transact-SQL script.
All views
Include all database views in the Transact-SQL script.
All stored procedures
Include all database stored procedures in the Transact-SQL script.
All defaults
Include all database defaults in the Transact-SQL script.
All rules
Include all database rules in the Transact-SQL script.
All user-defined data types
Include all database user-defined data types in the Transact-SQL script.
All user-defined functions
Include all database user-defined functions in the Transact-SQL script.

Objects on

View the list of objects in the database that are not included in the Transact-SQL script.

Objects to be scripted

View the list of objects in the database that are included in the Transact-SQL script.

Add

Add database objects to the Transact-SQL script.

Remove

Remove database objects from the Transact-SQL script.

See Also

Documenting and Scripting Databases


Generate SQL Scripts (Formatting Tab)

Use this tab to generate Transact-SQL statements that format the database objects you are creating. This is useful if you want to drop an existing object in one database, and then re-create it with the schema of a similar object from another database.

Options

Generate the CREATE <object> command for each object

Generate a Transact-SQL statement to create each object you selected on the General tab.

Generate the DROP <object> command for each object

Generate a Transact-SQL statement to drop each object you selected on the General tab.

Generate scripts for all dependent objects

Generate a Transact-SQL statement to add dependent objects for each object you selected on the General tab.

Include descriptive headers in the script files

Include explanatory header text prefacing each Transact-SQL statement in the script.

Include extended stored properties

Include extended stored procedures in the SQL scripts you create.

Only script 7.0 compatible features

Generate a script that is compatible with Microsoft® SQL Server™ version 7.0. If you select this option, the following SQL Server 2000 options will be ignored: column level collation, user-defined functions, extended property, INSTEAD OF trigger on tables and views, indexes on views (indexed views), indexes on computed columns, reference permissions on views, and descending indexes.

Script template

View the Transact-SQL script template that results from the options you have selected on this tab.

See Also

Documenting and Scripting Databases


Generate SQL Scripts (Options Tab)

Use this tab to generate Transact-SQL statements that further refine the creation of objects on a database. You can specify security, table creation, and file saving options.

Options

Script database

Generate a Transact-SQL statement to create a script of the existing database schema.

Script database users and database roles

Generate a Transact-SQL statement to create all users and roles that have access to the database.

Script SQL Server logins (Windows NT and SQL Server logins)

Generate a Transact-SQL statement to create all logins that currently have access to the server.

Script object-level permissions

Generate a Transact-SQL statement to create all grant, revoke, and deny permissions that currently exist for each object selected on the General tab.

Script indexes

Generate a Transact-SQL statement to create indexes that currently exist for any selected tables. This option is useful only if one or more tables are selected on the General tab.

Script full-text indexes

Generate a Transact-SQL statement to create full-text indexes. This option is useful only if one or more tables are selected on the General tab.

Script triggers

Generate a Transact-SQL statement to create triggers that exist for any selected tables. This option is useful only if one or more tables are selected on the General tab.

Script PRIMARY keys, FOREIGN keys, defaults, and check constraints

Generate a Transact-SQL statement to create PRIMARY keys, FOREIGN keys, defaults, and check constraints that exist for any selected tables. This option is useful only if one or more tables are selected on the General tab.

MS-DOS text (OEM)

Save the Transact-SQL script in the format of the current Microsoft® Windows® system code page. Select this option if you will use the script in a batch operation and execute it from the command prompt using a console application such as Isql.exe.

Windows text (ANSI)

Save the Transact-SQL script in ANSI format. Select this option if the script will be used in SQL Query Analyzer or another Windows application.

International text (Unicode)

Save the Transact-SQL script in Unicode format. Select this option if the script uses special international characters that are supported only in the Unicode font. This format requires two times the disk space of either the current Windows code page or ANSI.

Create one file

Save one file that includes all Transact-SQL statements for every object you have selected.

Create one file per object

Save one file for each distinct object you have selected.

See Also

Documenting and Scripting Databases


Dependencies (General Tab)

Use this tab to view information about the dependency relationships of an object.

Options

Object

Select a database object whose dependencies you want to view.

Objects that depend on <object>

View all the database objects that are dependent on the selected object. View the owner and the sequence of each object.

Objects that <object> depends on

View all database objects on which the selected object is dependent. View the owner and the sequence of each object.

Show first level dependency only

View only first-level dependencies for the selected object.

See Also

sp_depends


Backup Device Properties (General Tab)

Use this tab to view and modify the name and location of a backup device.

Options

Name

Specify the name of the backup device.

View Contents

View the name, server, database, type of backup, date, expiration, size, and description for each backup stored on a device.

Tape drive name

Specify the tape drive name for the backup device.

File name

Specify the name and path of the disk drive. Click the browse (...) to search for the backup device.

See Also

Backup Devices


Font (Format Tab)

Use this tab to view or specify the following options.

Options

Text

Specify the format of alphabetic text.

Text selection

Specify the format of text when it is selected.

Keyword

Specify the format of Transact-SQL keywords.

Stored procedure

Specify the format of stored procedures.

System table

Specify the format of system tables.

Global variable

Specify the format of global variables.

Comment

Specify the format of comments within a Transact-SQL script.

Number

Specify the format of numeric text.

String

Specify the format of alphanumeric text contained within single quotation marks.

Operator

Specify the format of symbols used to perform mathematical computations or comparisons between columns or variables. Operators are classified as arithmetic, bitwise, comparison, or join.

Foreground

Specify the foreground character color for the content type selected in the Color box.

Background

Specify the background color for the content type selected in the Color box.

Font

Specify the font for the content type selected in the Color box.

Size

Specify the point size of the content type selected in the Color box.

Sample

View the query characters in the selected format.

Reset All

Reset all options to their original default values.


Drop Objects

Use this dialog box to view or specify the following options.

Options

Object

Specify the object to delete.

Owner

Specify the owner of the object to delete.

Type

Specify the type of object to delete.

Drop All

Drop all selected objects.

Show Dependencies

View the dependencies of the selected object to delete.

See Also

How to delete user-defined data types (Enterprise Manager)


Shrink Database

Use this dialog box to view or specify the following options.

Options

Space allocated

View the space allocated for the selected database in megabytes (MB).

Space free

View the free space for the selected database in both MB and as a percentage of total space.

Maximum free space in files after shrinking

Specify the maximum percent of free space in the database files after shrinking the database.

Move pages to beginning of file before shrinking

Specify to move pages to the beginning of the file before shrinking the database. Selecting this option may hinder performance.

Shrink the database based on this schedule

Specify to shrink the database on a selected schedule, as determined in the following option.

Change
Modify the schedule used to shrink the database.

Files

Display the Shrink Database Files dialog box, where you can specify the individual database files to shrink. This option provides more precise control when shrinking the database.

See Also

Shrinking a Database


SQL Server Backup (General Tab)

Use this tab to view or specify the following options.

Options

Database

Specify the database to back up.

Name

Specify the name of the database backup.

Description

Describe the database backup.

Database – complete

Perform a complete database backup.

Database – differential

Perform a differential database backup. A differential backup records only the changes made to the data in the database after the last full database backup.

Transaction log

Back up the transaction log.

File and filegroup

Specify the file name and filegroup to back up. Click the browse (...) button to search for a file or filegroup.

Tape

Back up the database to a tape device.

Disk

Back up the database to a disk device.

Add

Add a backup destination.

Remove

Remove the selected backup destination.

Contents

View the contents of the selected tape or disk.

Append to media

Append the backup to an existing media. The previous contents of the media remain intact, and the new backup is written after the end of the last backup on the media.

Overwrite existing media

Specify to overwrite any existing media. By overwriting backups on media, the existing contents of the backup media are overwritten with the new backup and therefore are no longer available.

Schedule

Schedule a database backup. Click the browse (...) button to view the current backup schedule.

See Also

Backing Up and Restoring Databases


SQL Server Backup (Options Tab)

Use this tab to view or specify the following options.

Options

Verify backup upon completion

Specify that the media integrity of the backup is verified upon completion.

Eject tape after backup

Eject the backup media tape after the backup is complete.

Remove inactive entries from transaction log

Remove from the transaction log all entries for completed transactions upon completion of the backup.

Check media set name and backup set expiration

Check the media set name and backup set expiration date before overwriting the media.

Media set name

Specify the media set name the media must have before they can be overwritten.

Backup set will expire

Set the backup set expiration conditions. Microsoft® SQL Server™ only uses the backup expiration information from the first backup set on the media to determine whether the entire media can be overwritten.

After

Specify the number of days after the backup is completed before the media can be overwritten.

On

Specify the date on which the media can be overwritten.

Initialize and label media

Write the Microsoft Tape Format (MTF) header to the beginning of the media. This will erase all contents and any previous media header information. The backup set expiration and media set name are not checked when initializing a media.

Media set name

Write the media name to the media as part of the MTF header.

Media set description

Write the media description to the media as part of the MTF header. This is typically what the media is used for or where it is stored.

See Also

Backing Up and Restoring Databases


Restore Database (General Tab)

Use this tab to view or specify the following options.

Options

Restore as database

Specify which database to restore.

Database

Restore the selected database.

Filegroups or files

Restore a filegroup or file.

From device

Restore from a device.

Show backups of database

Show backups of the selected database.

First backup to restore

Specify which backup to restore first. This option is displayed only if you restore a database.

Point in time restore

Restore a backup from a selected point in time. This option is displayed only if you restore a database. Click the browse (...) button to search for a backup.

Restore

Restore the selected backup.

Type

View the type of backup.

Backup set date

View the date of the backup set.

Size

View the size of the backup set.

Restore from

View the file location of the backup set.

Backup set name

View the backup set name.

Properties

View the properties of the backup set.

See Also

Backing Up and Restoring Databases


Restore Database (Options Tab)

Use this tab to view or specify the following options.

Options

Eject tapes (if any) after restoring each backup

Eject tapes after restoring each backup.

Prompt before restoring each backup

Prompt the user before restoring each backup to prevent a user from inadvertently restoring a backup.

Force restore over existing database

Force the restore over an existing database.

Restore database files as

Specify the name and location of the database files that will be restored.

Leave database operational. No additional transaction logs can be restored

Leave the database operational, which means no additional transaction logs can be restored.

Leave database nonoperational but able to restore additional transaction logs

Leave the database operational but allow additional transaction logs to be restored.

Leave database read-only and able to restore additional transaction logs

Leave the database read-only and allow additional transaction logs to be restored.

Undo file

Specify the name of the file to undo. Click the browse (...) button to search for a file to undo.

See Also

Backing Up and Restoring Databases


Provider Options

Use this dialog box to view or specify the following options.

Options

Provider options

Display the Linked Server Properties dialog box, where you can view the options for the provider selected in the Provider name box.

Linked servers using this provider

Display the Linked Server Properties dialog box, where you can view any linked servers using the providers selected in the Provider name box.

See Also

Configuring Linked Servers


Server Role Properties (Permissions Tab)

Use this tab to view or specify the following options.

Options

This server role can execute the following commands

View the commands the selected server role can execute.

See Also

Managing Permissions


Server Role Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

View the server role name.

Specify which logins are members of this security role

View the list of logins that are members of the selected server role.

Add

Add a login to the selected server role.

Remove

Remove a login from the selected server role.

See Also

Roles


External Tools

Use this dialog box to gain easier access to tools such as the Windows Systems Monitor.

Options

Add

Display the Add External Tools dialog box, where you can add an external tool to the list of current tools.

Change

Commit the changes you have made to Menu text, Command, and Parameters for the selected current tool.

Remove

Remove the selected current tool.

Menu text

Specify the text that describes the tool, which appears in the list of current tools and on the Tools menu.

Command

Specify the fully-qualified path to the external tool, including the executable file name of the tool.

Parameters

Specify the parameters with which you want the external tool to launch. The placeholders [SRV] and [DBN] can be added for substitution based on the current server and database context in the left or right pane.

See Also

How to add an external tool to the Tools menu (Enterprise Manager)


Add External Tools

Use this dialog box to view or specify the following options.

Options

Command

Specify the fully-qualified path to the external tool, including the executable file name of the tool.

Browse

Search for external tools to add.

Parameters

Specify the parameters with which you want the external tool to launch. The placeholders [SRV] and [DBN] can be added for substitution based on the current server and database context in the left or right pane.

See Also

How to add an external tool to the Tools menu (Enterprise Manager)


SQL Server Properties (Replication Tab)

Use this tab to view or specify the following options.

Options

Configure

Specify that you want to configure publishing and distribution on this server. Selecting this option starts the Configure Publishing and Distribution Wizard or displays Publisher and Distributor properties.

Disable

Specify that you want to disable publishing and distribution on this server. Selecting this option starts the Disable Publishing and Distribution Wizard.

Replication Monitor Group

Add server to a Replication Monitor group if the server is configured as a Distributor.

See Also

Implementing Replication (Enterprise Manager)


Properties (Data Files Tab)

Use this tab to view or specify the following options.

Options

Database files

Specify the file name, location, space allocated, and filegroup for the selected data files.

Delete

Delete the selected data files.

Automatically grow file

Specify that data files automatically increase in size by the amount indicated in the following options.

In megabytes
Specify the number of megabytes by which to grow the data files.
By percent
Specify the percentage by which you want the data files to grow automatically.
Unrestricted file growth
Specify that the data file growth will be unrestricted.
Restrict file growth (MB)
Specify the size in megabytes to which a restricted data file can grow.

See Also

Files and Filegroups


Properties (Filegroups Tab)

Use this tab to view or specify the following options.

Options

Filegroups

View or specify the name of the filegroup, the number of files, and the status of the filegroup.

Delete

Delete the selected filegroup. You cannot delete a filegroup until all files that are a part of it have been removed.

See Also

Files and Filegroups


User-Defined Function Properties (General Tab)

Use this dialog box to view or specify the following options.

Options

Name

View the name of the user-defined function.

Permissions

Display the Object Properties dialog box, where you can specify permissions for user-defined functions.

Owner

View the owner of the user-defined function.

Create date

View the date on which the user-defined function was created.

Text

View the syntax of the user-defined function.

Check Syntax

Check the syntax of the Transact-SQL script used to create the user-defined function.

Save as Template

Save the user-defined function text as a template.

See Also

User-Defined Functions


Linked Server Properties (Server Options Tab)

Use this tab to view or specify the following options.

Options

Option Name

View the options for each linked server.

Value

Specify the value of the options associated with each linked server. For example, view the connection timeout value or if the linked server is collation compatible.

See Also

Configuring Linked Servers


Edit SQL Server Message

Use this dialog box to view or specify the following options.

Options

Error Number

Specify the number of the error message.

Severity

Specify the severity of the error message.

Message text

Specify the message text of the error message.

Language

Specify the language of the error message text.

Always write to Windows NT event log

Specify to always write the error message to the Microsoft® Windows® event log.

See Also

Managing SQL Server Messages


Shrink Database Files

Use this dialog box to view or specify the following options.

Options

Database file

Specify the database file to shrink.

Filegroup name

View the filegroup name of the selected database file.

File ID

View the file ID for the selected database files.

File type

View the file type for the selected database files (for example, data file or log file).

Location

View the location of the selected database files.

Current size

View the current size (in megabytes) of the selected database files.

Space used

View the space used (in megabytes) of the selected database files.

Compress pages and then truncate free space from the file

Compress the database pages and then truncate the free space that is generated by compressing the pages.

Truncate free space from the end of the file

Truncate the free space from the end of the file.

Empty the file (data will migrate to other files in the file group)

Empty the selected database file. The data in the current file will move to other files in the file group.

Shrink file to

Shrink file to a specified size in megabytes.

Shrink the file later

Shrink the file at a specified date and time.

Date

Specify the date on which to shrink the database file.

Time

Specify the time at which to shrink the database file.

See Also

Shrinking a Database


Detach Database

Use this dialog box to view or specify the following options.

Options

Clear

Clear any connection to the selected database.

Connections using this database

View the number of connections to the selected database. You cannot detach a database while users are connected.

Database being replicated

View if the database is being replicated. You cannot detach a database while it is being replicated.

Status

View the status of the database. This will tell you if the database is ready to be detached, based on the criteria in the previous options.

Update statistics prior to detach

Update the database statistics prior to detaching the database.

See Also

How to attach and detach a database (Enterprise Manager)


Add/Edit Destination Database (General Tab)

Use this tab to view or specify the following options.

Options

Server name

Specify the secondary database to add to the log shipping definition. The server containing the database must be registered and running Microsoft® SQL Server™ 2000, Enterprise Edition to appear in the list. If you are editing a destination database, the Server name is read-only.

Directory

Specify the transaction log destination directory to which the logs will be backed up. Click the browse (...) button to search for an existing directory.

See Also

Log Shipping


Add Destination Database (Initialize Tab)

Use this tab to view or specify the following options.

Options

No recovery mode

Specify that the secondary database be made unavailable for use. The secondary database is placed in NORECOVERY mode as a result of either the RESTORE LOG operation or the RESTORE WITH NORECOVERY operation.

Standby mode

Specify that the secondary database be made available for use, but in read-only mode. The secondary database is placed in STANDBY mode as a result of either the RESTORE LOG operation or the RESTORE DATABASE WITH STANDBY operation.

Terminate users in database (Recommended)

Disconnect all users from the database. Log shipping will not work if there are any users connected to the secondary database you have configured for log shipping.

Take full database backup now

Take a full database backup now, rather than using an existing backup file.

Use most recent backup file

Specify the most recent backup file to use to initialize the destination database. Click the browse (...) button to search for a recent backup file.

Copy frequency

Set the frequency (in minutes) with which you want the destination server to back up the transaction logs from the source server.

Load frequency

Set the frequency (in minutes) with which you want the destination server to restore the transaction logs from the source server.

See Also

Log Shipping


Add Destination Database (Thresholds Tab)

Use this tab to view or specify the following options.

Options

Out of sync threshold

Specify the maximum elapsed time between the last transaction log backup on the source server and the last transaction log restore on the destination server.

Load time delay

Set the amount of time you want the destination server to delay before it restores the transaction log from the source server. The default for this option is zero minutes, indicating that the destination server should immediately restore any transaction log backups. Changing the time delay would provide a cushion of time if something goes wrong on the source server, allowing you to correct the problem before the corrupted log is restored onto the destination server.

File retention period

Specify how long to wait before a transaction log is deleted.

History retention period

Specify how long to wait before a transaction log history is deleted.

See Also

Log Shipping


Edit Destination Database (Thresholds Tab)

Use this tab to view or specify the following options.

Options

Out of sync threshold

Specify the maximum elapsed time between the last transaction log backup on the source server and the last transaction log restore on the destination server.

Load time delay

Set the amount of time you want the destination server to delay before it restores the transaction log from the source server. The default for this option is zero minutes, indicating that the destination server should immediately restore any transaction log backups. Changing the time delay would provide a cushion of time if something goes wrong on the source server, allowing you to correct the problem before the corrupted log is restored onto the destination server.

File retention period

Specify how long to wait before a transaction log is deleted.

History retention period

Specify how long to wait before a transaction log history is deleted from the log_shipping_plan_history table.

See Also

Log Shipping


Database Maintenance Plan (Log Shipping Tab)

Use this tab to view or specify the following options.

Options

Monitor Server

View the monitor server for the specified database maintenance plan.

Destination Server Information

View the destination server information for the specified database maintenance plan, including the names of all destination servers, the destination databases, and the sync threshold.

Add

Display the Add Destination Database dialog box, where you can add a new destination database.

Delete

Delete a destination database. This stops log shipping to the selected destination database.

Edit

Display the Edit Destination dialog box, where you can edit an existing destination database.

Remove Log Shipping

Remove log shipping for the database associated with the selected maintenance plan.

See Also

Log Shipping


Bind Rule/Default to User-defined Data Types

Use this dialog box to view or specify the following options.

Options

Rule

View the name of the rule that you wish to bind to a user-defined data type. When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column. Rules, a backward compatibility feature, perform some of the same functions as check constraints. CHECK constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the preferred, standard way to restrict the values in a column (multiple constraints can be defined on a column or multiple columns). A column or user-defined data type can have only one rule bound to it. However, a column can have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated. For more information about rules, see CREATE RULE.

Name

View the name and data type of the user-defined data type. Select each rule to bind, by checking the Bind box. Selecting the Future Only box prevents existing columns of a user-defined data type from inheriting the new rule. If Future Only is selected, the new rule is bound to any columns of the user-defined data type that currently have no rule or that are using the existing rule of the user-defined data type. For more information about binding rules, see sp_bindrule.

See Also

Creating User-Defined Data Types


Bind Rule/Default to Columns

Use this dialog box to view or specify the following options.

Options

Rule

View the name of the rule that you wish to bind to a column. When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column.

As a backward compatibility feature, rules perform some of the same functions as check constraints. CHECK constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the recommended, standard way to restrict the values in a column. One key difference between rules and check constraints is that while multiple constraints can be defined on a column or multiple columns, only one rule can be bound to a column or user-defined data type. A column can, however, have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated. For more information about rules, see CREATE RULE.

Table

Select the table containing the column you wish to bind a rule to.

Unbound columns

View the names and data types of the unbound columns in the selected table.

Bound columns

View the names and data types of the bound columns in the selected table.

Add

Bind a rule to a column. You must select an unbound column from the Unbound columns list before you click Add.

Remove

Unbind a rule from a column. You must first select a bound column from the Bound columns list before you click Remove.

See Also

Creating User-Defined Data Types


Secondary Server Log Shipping History

Use this dialog box to view or specify the following options.

Options

Show history for

Specify the databases for which you want to view the log shipping history. You can select an individual database or all databases for the selected secondary server. The history shows the status of each activity, such as copy, load, and time of completion.

Message

View any messages regarding log shipping for the specified secondary server.

See Also

Log Shipping


Log Shipping Pair Properties (Status Tab)

Use this tab to view or specify the following options.

Options

Status

View the status of the log shipping servers. For example, the secondary server may be out of sync or in a normal state.

Current date time/on monitor server

View the current date and time on the monitor server.

Last backup file

View the last backup of the source server.

Updated

View the date and time that the last backup file was updated.

Backup delta

View the time between database backups.

Last file copied

View the last file copied from the source server.

Updated

View the date and time that the last file was copied from the source server.

Copy delta

View the time between file copies.

Last file loaded

View the last file restored onto the destination server.

Updated

View the date and time that the last backup file was restored onto the destination server.

Load delta

View the time between database restores.

See Also

Log Shipping


Log Shipping Pair Properties (Source Tab)

Use this tab to view or specify the following options.

Options

Source

View the source server for the log shipping pair.

Alert threshold

Specify the threshold at which an alert is generated. The value specified in this option sets the maximum elapsed time since the last transaction log backup was made on the source server. After the time exceeds this specified threshold, an alert is generated by the monitor server.

Alert number

Specify the alert number that will be generated if the alert threshold is passed.

Enabled

Enable the alert.

Start time

Specify the starting time when alerts will not be generated, even if the alert threshold is passed.

End time

Specify the ending time when alerts will not be generated, even if the alert threshold is passed.

On days

Specify the days when alerts will not be generated between the start and end times, even if the alert threshold is passed.

View backup schedule

Display the Schedule dialog box, where you can view how often the backups are made.

See Also

Log Shipping


Log Shipping Pair Properties (Destination Tab)

Use this tab to view or specify the following options.

Options

Source

View the destination server for the log shipping pair.

Alert threshold

Specify the threshold before an alert is generated. This value specified in this option is the maximum elapsed time since the last transaction log backup was made on the source server. After the time exceeds this specified threshold, an alert is generated by the monitor server.

Alert number

Specify the alert number that will be generated if the alert threshold is passed.

Enabled

Enable the alert.

Start time

Specify the starting time when alerts will not be generated, even if the alert threshold is passed.

End time

Specify the ending time when alerts will not be generated, even if the alert threshold is passed.

On days

Specify the days when alerts will not be generated between the start and end times, even if the alert threshold is passed.

View copy schedule

Display the Schedule dialog box, where you can view how often database copies are made.

Copy is enabled

Enable database copies.

View load schedule

Display the Schedule dialog box, where you can view how often the database is restored.

Load is enabled

Enable restoring the database.

See Also

Log Shipping


Schedule

Use this dialog box to view the following option.

Options

Schedule

View the schedule for the selected log shipping event, including database backups, copies, or restores. The frequency of the selected event is displayed.

See Also

Log Shipping


SQL Server Login Properties - New Login

Use this dialog box to view or specify the following options.

Options

Name

Specify the name for the new login.

Windows Authentication

Use Windows Authentication when connecting to an instance of Microsoft® SQL Server™. It is recommended that you use this option for security because users who connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account can make use of trusted connections. Trusted connections are those validated by Windows NT 4.0 or Windows 2000.

Domain
Specify the domain to use to validate a login's network security attributes. SQL Server achieves login security integration with Windows NT 4.0 by using the security attributes of a network user to control login access. A user's network security attributes are established at network login time and are validated by a Windows domain controller.
Grant access
Grant access to the login.
Deny access
Deny access to the login.

SQL Server Authentication

Connect using SQL Server Authentication. When a user connects with a specified login name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error. For more information about SQL Server Authentication, see Authentication Modes.

Password
Specify the password to use when connecting to the server using SQL Server Authentication.

Database

Specify the default database for the selected login.

Language

Specify the default language for the selected login.

See Also

Adding a SQL Server Login


Database Role Properties (Permissions Tab)

Use this tab to view or specify the following options.

Options

Database role

Select the database role for which to view or modify permissions.

List all objects

List all objects in the database. Click the appropriate box to modify role permissions.

List only objects with permissions for this role

Specify permissions only for objects with permission in the database. Click the appropriate box to modify role permissions. This may be helpful if there are many objects, and you only need to view or modify the objects with permissions for the selected role.

Object

Specify the permissions for the selected object. You can grant or remove permissions to execute SELECT, INSERT, UPDATE, DELETE, EXEC statements. You can also grant or remove permissions to execute Declarative Referential Integrity (DRI) constraints. For more information on Declarative Referential Integrity constraints, see Parts of a Database.

Columns

Display the Column Permissions dialog box, which allows you to manage permissions for the object on a column-by-column basis.

See Also

Creating User-Defined SQL Server Database Roles


Attach Database

Use this dialog box to view or specify the following options.

Options

MDF file of database to attach

Specify the name of the MDF (master data file) of the database to attach. There can be up to 16 file names specified. Microsoft® SQL Server™ cannot attach a database if more than 16 files are specified. For more information about attaching databases, see sp_attach_db.

Click the browse (...) button to search for the MDF of the database to attach.

Verify

Verify that the specified MDF is correct.

Original File Name(s)

View all files in the database to attach. This includes data files and log files.

Current File(s) Location

View or edit all current file names and paths. The current location of the MDF file must be in the column for the attach to work, and if SQL Server cannot find the files in the specified location, the attach fails. For example, if you have changed the default location of the file before you detached it, you must specify the current location for the attach to be successful.

Attach as:

Specify the name for the database you are attaching. The database name cannot match any existing database names.

Specify database owner

Specify the database owner.

See Also

How to attach and detach a database (Enterprise Manager)


Start Job

Use this dialog box to view or specify the following options.

Options

Job name

View the name of the job to start.

Start execution at step

Select the step at which to start the job execution. You can also view the step ID, step same, and the type of step.

See Also

Running Jobs


Database Maintenance Plan (Servers Tab)

Use this tab to view or specify the following option.

Options

Server

Select the servers on which the database maintenance plan will be executed.

See Also

Database Maintenance Plan Wizard


Log Shipping Details

Use this dialog box to view or specify the following options. This dialog box is only available when one secondary database is in standby mode.

Options

SQL Server

View the monitor server name.

Use Windows authentication

Use Windows Authentication when connecting to an instance of Microsoft® SQL Server™. It is recommended that you use this option for security because users who connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account can make use of trusted connections. Trusted connections are those validated by Windows NT 4.0 or Windows 2000.

Use SQL Server authentication

Use SQL Server Authentication to connect the monitor server. When a user connects with a specified login name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error. For more information about SQL Server Authentication, see Authentication Modes.

Login Name

View the login name for the monitor server, if the server is connecting using SQL Server Authentication.

Password

View or specify the password for the monitor server, if the server is connecting using SQL Server Authentication.

Log Shipping Role

View information about the role of the monitor server.

See Also

Log Shipping


Configure SQL Server Error Logs

Use this dialog box to view or specify the following options.

Options

Limiting the number of the error log files before they are recycled.

Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of Microsoft® SQL Server™ is started. Typically, SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.

Maximum number of the error log files.

Specify the maximum number of error log files created before they are recycled. The default is six, which is the number of previous backup logs SQL Server retains before recycling them.

See Also

Using the SQL Server Agent Error Log


SQL Server Properties (Active Directory Tab)

Use this tab to view or specify the following options.

Options

Add

Add the selected to server to the Active Directory. Active Directory is a central component of the Microsoft® Windows® 2000 operating system and provides a place to store information about network-based entities, such as applications, files, printers, and people. Adding a server to the Active Directory requires local administrator privileges on the server.

Refresh

Refresh the attributes of the selected server in the Active Directory.

Remove

Remove the selected server from the Active Directory. Removing this server will also remove the databases and publications of the server from the Active Directory.


Alert Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

View or specify the name of the alert. The name is limited to 128 characters.

ID

View the ID generated for the alert by Microsoft® SQL Server™. New appears when you are creating a new alert.

Type

Specify the type of alert definition.

Enabled

Enable the alert. The alert is enabled by default.

Error number

Specify the error number that triggers the alert. Click the browse (...) button to display the Manage Server Messages dialog box, where you can view alerts according to error number. These options are only available when you select a SQL Server event alert.

Severity

Specify the severity level that triggers the alert. Available only when you select a SQL Server event alert.

Database name

Specify the database in which the error must occur to trigger the alert. Available only when you select a SQL Server event alert.

Error message contains this text

Restrict the alert to only those events containing the text specified in the error message. Available only when you select a SQL Server event alert.

Date last occurred

View the date and time the alert last occurred.

Date last responded to

View the date and time the alert last raised a response.

Occurrence count

View the number of times the alert has occurred since the count was last reset.

Reset Count

Reset the alert count.

See Also

Defining Alerts


Alert Properties (Response Tab)

Use this tab to view or specify the following options.

Options

Execute job

Specify the job to execute when the alert occurs. Click the edit (...) button to change the properties of the selected job.

New Operator

Display the New Operator Properties dialog box, where you can add an operator to respond to the alert.

Operator name

View the list of operators responding to the alert.

E-mail

Notify the operator about the alert by e-mail. An icon to the right of the check box indicates that the operator has an e-mail address defined.

Pager

Notify the operator about the alert by pager. An icon to the right of the check box indicates that the operator has a pager address defined.

Net send

Notify the operator about the alert by net send. An icon to the right of the check box indicates that the operator has a net send address defined.

Include alert error text in e-mail

Include the error message text in the e-mail notification.

Include alert error text in pager

Include the error message text in the pager notification.

Include alert error text in net send

Include the error message text in the net send notification.

Additional notification message to send

Specify any additional notification messages to send to the operator.

Delay between responses

Specify the delay, in minutes and seconds, between responses for a recurring alert.

See Also

Defining Alerts


Operator Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the operator name. The name is limited to 128 characters.

ID

Indicate the ID generated for the operator by Microsoft® SQL Server™. New appears when you are creating a new operator.

E-mail name

Specify the e-mail address of the operator. If the display name or alias name is ambiguous, then specify a fully qualified e-mail name in square brackets. For example, you can use [SMTP:myfriend@mycompany.com]. Click the browse (...) button to search the SQL Server address book.

Pager e-mail name

Specify the pager address of the operator. If the display name or alias name is ambiguous, then specify a fully qualified e-mail name in square brackets. For example, you can use [SMTP:myfriend@mycompany.com]. Click the browse (...) button to search the SQL Server address book.

Test

Send a test e-mail, pager, or net send notification.

Net send address

Specify the net send address of the operator.

Pager on duty schedule

Specify the days the operator is available to receive pager notifications.

Workday begin

Specify the time after which the operator is available to receive pager notifications.

Workday end

Specify the time after which the operator is no longer available to receive pager notifications.

See Also

Defining Operators


Operator Properties (Notifications Tab)

Use this tab to view or specify the following options.

Options

Notifications sent to this operator by

Specify to view the notifications sent to this operator by alerts or jobs.

Alert name

View the names of the alerts for which you can make an operator responsible.

E-mail

Specify that the operator will receive notification by e-mail.

Pager

Specify that the operator will receive notification by pager.

Net send

Specify that the operator will receive notification by net send.

Operator is available to receive notifications

Specify that the operator is available to receive notifications.

Send e-mail

Generate an e-mail message detailing the alert responsibilities of the operator.

By e-mail

View the date and time of the most recent e-mail notification sent to the operator.

By pager

View the date and time of the most recent pager notification sent to the operator.

By net send

View the date and time of the most recent net send notification sent to the operator.

See Also

Defining Operators


Job Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

Specify the name of the job. The name is limited to 128 characters. Job names must be unique only if they originate from the same server. A job created locally on a target server and a downloaded job from a master server can share the same name.

Source

View the server where the job originated. The default is local, which means that the job was created on the instance of Microsoft® SQL Server™ that is the local server.

Created

View the creation date and time of the job. Not yet created appears if you are creating a new job.

Enabled

Enable the job. This option is selected by default, both for new and existing jobs. A disabled job runs only if a user explicitly starts it.

Target local server

Define the job as a local job, which is a job that runs only on the local server.

Target multiple servers

Define the job as a multiserver job, which is a job that runs on multiple remote servers. This option is enabled only on a master server.

Category

Select the job category. Use job categories to organize jobs for easy filtering and grouping. By default, local jobs are assigned to the [Uncategorized (Local)] job category. Click the list (...) button to view other jobs in the same category as the one selected.

Owner

Select the job owner. This option is enabled when the user is the system administrator. The system administrator can reassign the job to another owner. By default, the owner list contains the SQL Server login ID of the job creator.

Description

Describe the job using up to 512 characters. A description can help other users on local and remote computers understand the purpose of the job.

Last modified

Display the date the job was last modified. Not applicable appears if you are creating a new job.

Change

Display the Change Job Target Server dialog box, where you can change the target server for the job. Available for multiserver jobs only.

See Also

Creating Jobs


Job Properties (Steps Tab)

Use this tab to view or specify the following options.

Options

ID

View the step identification number.

Step name

View the step name.

Type

View the step type.

On success

Display the control-of-flow action if the step succeeds.

On failure

Display the control-of-flow action if the step fails.

Move step

Modify the sequence in which the steps execute.

Start step

Select the step at which the job begins execution.

New

Display the New Job Step dialog box, where you can configure a new step to insert at the end of the list of existing steps.

Insert

Display the New Job Step dialog box, where you can configure a new step to insert above the currently selected step.

Edit

Display the Edit Job Step dialog box, where you can change the configurations of the currently selected step.

Delete

Delete the currently selected step.

See Also

Creating Jobs

Creating Job Steps

Handling Multiple Job Steps


Job Properties (Schedules Tab)

Use this tab to view or specify the following options.

Options

Note

View the current date and time on the target server.

ID

View the alert or schedule identification number.

Name

View the name of the schedule or alert.

Enabled

View the enabled status of the selected schedule or alert.

Description

View the description of the schedule or alert.

New Schedule

Display the New Job Schedule dialog box, where you can configure a new job schedule.

New Alert

Display the New Alert Properties dialog box, where you can configure a new alert.

Edit

Display the Edit Job Schedule dialog box, where you can change the configurations of the currently selected schedule or alert.

Delete

Delete the currently selected schedule or alert.

See Also

Creating Jobs

Scheduling Jobs


Job Properties (Notifications Tab)

Use this tab to view or specify the following options.

Options

E-mail operator

Specify that an operator be notified by e-mail when a Microsoft® SQL Server™ event completes. Select the name of the operator to notify by e-mail, or click the browse (...) button to add a new operator or edit the properties of an existing operator. Also, select the completion status about which the operator will be notified.

Page operator

Specify that an operator be notified by page when a SQL Server event completes. Select the name of the operator to notify by page, or click the browse (...) button to add a new operator or edit the properties of an existing operator. Also, select the completion status about which the operator will be notified.

Net send operator

Specify that an operator be notified by net send when a SQL Server event completes. Select the name of the operator to notify by net send, or click the browse (...) button to add a new operator or edit the properties of an existing operator. Also, select the completion status about which the operator will be notified.

Write to Windows application eventlog

Write a job completion event to the Microsoft Windows® application log when the job completes. Also, select the completion status for writing the event log.

Automatically delete job

Delete the job automatically when it completes, succeeds, or fails. This is dependent upon your completion status selection.

See Also

Creating Jobs

Specifying Job Responses


Job Category Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

View the name of the job category to view. If you are creating a new job category, specify a name.

Jobs in this category

View all defined jobs that are members of the job category.

See Also

Creating Jobs


Job Schedule Properties

Use this dialog box to view or specify the following options.

Options

Name

Specify the name of the schedule. The name is limited to 128 characters. Each schedule name in a job must be unique.

Enabled

Enable the new job schedule.

Start automatically when SQL Server Agent starts

Automatically start the job when SQL Server Agent starts.

Start whenever the CPU(s) become idle

Start the job whenever the CPU(s) become idle. CPU idle time is specified on the Advanced tab of the SQL Server Agent Properties dialog box.

One time

Start the job once at the specified date and time.

On date
Specify the date you want the job to start.
At time
Specify the time you want the job to start.

Recurring

Start the job according to the recurring schedule displayed.

Change

Display the Edit Recurring Job Schedule dialog box, where you can change the current recurring job schedule.

See Also

Scheduling Jobs


Edit Recurring Job Schedule

Use this dialog box to view or specify the following options.

Options

Job name

View the name of the job for which to set a recurring schedule.

Daily

Set a daily job occurrence.

Weekly

Set a weekly job occurrence.

Monthly

Set a monthly job occurrence.

Every week(s)

Specify the job frequency in week increments.

Mon

Set job to occur on a Monday.

Tue

Set job to occur on a Tuesday.

Wed

Set job to occur on a Wednesday.

Thur

Set job to occur on a Thursday.

Fri

Set job to occur on a Friday.

Sat

Set job to occur on a Saturday.

Sun

Set job to occur on a Sunday.

Occurs once at

Set the time for a job to occur once daily.

Occurs every

Set the number of hours or minutes between occurrences.

Starting at
Set the time at which the job frequency starts every day.
Ending at
Set the time at which the job frequency ends every day.

Start date

Set the date when this schedule will become effective.

End date

Set the date when this schedule will no longer be effective.

No end date

Specify that the schedule is to be effective indefinitely.

See Also

Scheduling Jobs


Job Step (General Tab)

Use this tab to view or specify the following options.

Options

Step name

Specify the name of the job step to add. The name is limited to 128 characters. Each step name in a job must be unique.

Type

Specify a job step type.

Database

Specify the database to use when using a Transact-SQL or Replication Queue Reader job step.

Process exit code of a successful command

A job step that executes a command shell process relies on the process exit code to determine the success or failure of the job step. Set this option to the successful return code of a command shell process to enable logic and notifications based on the success or failure of the job step. This option is only available when you use an Operating System Command (CmdExec) job step.

Command

Specify a procedure or command appropriate for the type selected.

Open

Open a Transact-SQL or Microsoft® ActiveX® script file. Available only when you click Transact-SQL in the Type list.

Parse

Check the syntax of the Transact-SQL or ActiveX script command. Available only when you click Transact-SQL in the Type list.

Next

Move to the next job step.

Previous

Move to the previous job step.

See Also

Creating Job Steps

Handling Multiple Job Steps


Job Step (Advanced Tab)

Use this tab to view or specify the following options.

Options

On success action

Specify the action to perform if the step succeeds.

Retry attempts

Specify the number of retry attempts to be made if the step fails.

Retry interval (minutes)

Specify the interval (in minutes) to wait before retrying the step.

On failure action

Specify the action to perform if the step fails (after performing any retries).

Output file

Specify the file in which to store the results of the Transact-SQL or CmdExec job step. Click the browse (...) button to search for a directory in which to store the output file.

View

Display the selected output file.

Overwrite

Overwrite existing file with the new results.

Append

Add the results to the end of the existing file.

Append output to step history

Add the results of the Transact-SQL job step to the history entry for this step.

Run as user

Allow the system administrator to run the Transact-SQL job step as another database user.

Next

Move to the next step.

Previous

Move to the previous step.

See Also

Creating Job Steps

Handling Multiple Job Steps


SQL Server Agent Properties (General Tab)

Use this tab to view or specify the following options.

Options

System account

Run SQL Server Agent service under the system account.

This account

Specify the Microsoft® Windows NT® 4.0 or Windows® 2000 account under which the SQL Server Agent service runs.

Password

Specify the Windows NT 4.0 or Windows 2000 account password.

Mail profile

Specify a valid MAPI profile name that has been configured and tested for the SQL Server Agent service startup account.

Test

Start and stop a MAPI session (on the server) using the specified profile.

Save copies of the sent messages in the Sent Items folder

Specify that copies of all sent messages be saved in the Sent Items folder of Microsoft Outlook®, Microsoft Exchange client, or applicable MAPI-1 e-mail client.

File name

Specify the file name for the SQL Server Agent log. The default is C:\Program Files\Microsoft SQL Server\MSSQL\LOG\SQLAGENT.OUT. Click the browse (...) button to search for the error log directory.

View

View the SQL Server Agent error log.

Include execution trace messages

Include additional execution trace messages in the error log. This option should only be selected during specific SQL Server Agent problem investigations.

Write OEM File

Enable the Sqlagent.out file (error log file) to be written as a non-Unicode file. This saves disk space, especially when the Include execution trace messages check box is selected.

Net send recipient

Specify the name of a recipient to receive network pop-up notification of errors that SQL Server Agent writes to its error log.

See Also

SQL Server Agent

How to change SQL Server services login account information (Enterprise Manager)


SQL Server Agent Properties (Advanced Tab)

Use this tab to view or specify the following options.

Options

Auto restart SQL Server if it stops unexpectedly

Automatically restart the Microsoft® SQL Server™ service if it terminates unexpectedly.

Auto restart SQL Server Agent if it stops unexpectedly

Automatically restart the SQL Server Agent service if it terminates unexpectedly.

Forward events to a different server

Forward new SQL Server events in the Microsoft® Windows® application log to the specified server.

Server
Specify the server to which to forward events.
Unhandled events
Forward only events that have not been handled locally.
All events
Forward all events, even those that have been handled locally.

If error has severity of or above

Specify the severity level for forwarding events to the selected server. The value is greater than or equal to the selected severity level.

Average CPU usage falls below

Specify the idle CPU condition by percentage. Idle is when the average CPU usage remains below the selected percent for the specified number of seconds.

And remains below this level for

Specify the idle CPU condition by seconds. Idle is when the average CPU usage remains below the selected percentage for the specified number of seconds.

See Also

How to autostart SQL Server Agent (Enterprise Manager)

How to designate an events forwarding server (Enterprise Manager)

How to set CPU idle time and duration (Enterprise Manager)


SQL Server Agent Properties (Alert System Tab)

Use this tab to view or specify the following options.

Options

To line

Specify the pager address prefix and/or suffix for the To: line.

CC line

Specify the pager address prefix and/or suffix for the CC: line.

Pager address

Specify if the pager address for the operator should be included in the To: or CC: line.

Subject

Enter up to 100 characters of text to appear before the alert name in the subject line of the alert page. The format of the subject of the page is <Prefix><Alert name><Suffix>.

Suffix

Specify the text to appear after the alert name in the subject line of the alert page. The format of the subject of the page is <Prefix><Alert name><Suffix>.

Include body of e-mail in notification page

Include the body of the e-mail in the notification page. Clear this check box to shorten the page sent.

Operator

Specify the operator to which to send fail-safe notifications.

E-mail

Notify the fail-safe operator by e-mail.

Pager

Notify the fail-safe operator by pager.

Net send

Notify the fail-safe operator by net send.

See Also

How to designate a fail-safe operator (Enterprise Manager)

How to format pager addresses (Enterprise Manager)


SQL Server Agent Properties (Job System Tab)

Use this tab to view or specify the following options.

Options

Limit size of job history log

Enable limiting the size of the job history log to avoid filling msdb.

Maximum job history log size (rows)

Specify the maximum job history log size, in rows.

Maximum job history rows per job

Specify the maximum job history rows per job.

Current job history log size (rows)

View the current size, in rows, of the job history log.

Clear Log

Clear the job history log.

Shutdown time-out interval (seconds)

Specify the maximum number of seconds that SQL Server Agent will wait for a job to finish executing before SQL Server Agent is shut down.

Master SQLServerAgent (MSX) server

Indicate the instance of Microsoft® SQL Server™ that is acting as the master SQL Server Agent for this server.

Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps

Specify that only members of the sysadmin role can execute CmdExec or Microsoft ActiveX® scripting job steps. If a user who is not a member of the sysadmin role attempts to run a job that includes these types of job steps, the CmdExec or ActiveScripting job steps will fail.

Reset Proxy Account

Edit the user name, password, and domain of the user account used by SQL Server Agent to execute jobs owned by non system administrators.

Reset Proxy Password

This option is available only when administering an instance of SQL Server 7.0.

See Also

How to reset SQLAgentCmdExec permissions (Enterprise Manager)

How to resize the job history log (Enterprise Manager)

How to set job execution shutdown (Enterprise Manager)

How to set up the job history log (Enterprise Manager)


SQL Server Agent Properties (Connection Tab)

Use this tab to view or specify the following options.

Options

Use Windows Authentication

Connect SQL Server Agent to an instance of Microsoft® SQL Server™ using Windows Authentication. If this option is selected, then the Microsoft Windows NT® 4.0 and Microsoft Windows 2000® user account specified as the SQL Server Agent service startup account must be a member of the sysadmin role in SQL Server.

Use SQL Server Authentication

Connect SQL Server Agent to an instance of SQL Server using SQL Server Authentication.

SysAdmin login ID

Specify the login ID for the system administrator.

Password

Specify the password for the system administrator.

Login time-out

Specify the maximum time, in seconds, that SQL Server Agent waits for a connection to an instance of SQL Server to be established.

Local host server

Specify the alias of the instance of SQL Server that is the local server to accommodate custom connection needs. Use the Client Network Utility to modify the available choices.

See Also

How to set a SQL Server alias (Enterprise Manager)

How to set the SQL Server connection (Enterprise Manager)


SQL Server Agent Error Log

Use this dialog box to view or specify the following options.

Options

Type

Specify the type of entries to view from the SQL Server Agent error log.

Containing text

Restrict the log entries shown to include only those containing the specified text. The search is case-sensitive.

Apply Filter

Refresh the display according to the specified filter parameters.

Type

View the type of log entry.

Date/Time

View the date and time the log entry was written.

Message

View the text of the log entry. Double-click the message to view the full message text.

See Also

Using the SQL Server Agent Error Log


SQL Mail Configuration (General Tab)

Use this tab to view or specify the following options.

Options

Profile name

Specify a valid MAPI profile name that has been configured and tested for the Microsoft® SQL Server™ service startup account. SQL Mail only supports extended MAPI.

Test

Start and stop a MAPI session on an instance of SQL Server using the specified profile.

Autostart SQL Mail when SQL Server starts

Automatically start SQL Mail when SQL Server starts. This feature is available only in Microsoft Windows NT® 4.0 and Microsoft Windows® 2000.

See Also

Configuring SQL Mail


Manage SQL Server Messages (Search Tab)

Use this tab to view or specify the following options.

Options

Message text contains

Specify the text to search for in the messages.

Find

Find the messages that meet the specified criteria.

Error number

Specify the error number to search for in the messages.

Severity

Specify the severity to search for in the messages.

Only include logged messages

Include only messages that are always written to the Microsoft® Windows® application log.

Only include user-defined messages

Include only the messages that have been created by users.

See Also

Managing SQL Server Messages

How to find a SQL Server message (Enterprise Manager)


SQL Server Message

Use this dialog box to view or specify the following options.

Options

Error number

Specify the user-defined error message number. User-defined error message numbers must be greater than 50,000.

Severity

Specify the Microsoft® SQL Server™ severity level of the message. Severity levels are between 1 and 25.

Message text

Specify the text of the message. The maximum number of characters is 255.

Language

Specify the language of the message. You must create an English version of the message before you can create the message in another language.

Always write to Windows event log

Specify that this message should be written to the Microsoft Windows® application log. You must select this option if you want your user-defined message to be monitored for alert purposes by SQL Server Agent.

See Also

Managing SQL Server Messages


Manage SQL Server Messages (Messages Tab)

Use this tab to view or specify the following options.

Options

Error

View the error number of the message.

Severity

View the severity level of the message.

Language

View the language of the message.

Logged

View if the error is always written to the Microsoft® Windows® application log.

Message text

View the text of the error message.

New

Display the New SQL Server Message dialog box, where you can add a new server message.

Edit

Display the New SQL Server Message dialog box, where you can edit a server message. You can also double-click on a message to edit it.

Delete

Delete a server message. You can delete only user-defined messages with numbers greater than 50,000.

See Also

Managing SQL Server Messages


Change Job Target Servers (Available Servers Tab)

Use this tab to view or specify the following options.

Options

Available servers

View the target servers available for running the job.

Properties

Display the Target Server Properties dialog box, where you do the following:

>
Add one or more target servers to the list of target servers on which the job will run. Alternatively, double-click on an available server.
<
Remove one or more target servers from the list of target servers on which the job will run. Alternatively, double-click on a selected target server.

Selected target servers

View the target servers on which the job will run.

See Also

Creating Jobs

Multiserver Administration


Change Job Target Servers (All Server Groups Tab)

Use this tab to view or specify the following options.

Options

Name

View the name of the target server group.

Selected

View the number of servers in a server group on which a job will run.

Selected target servers

View the target servers on which the job will run.

Add

Add a new target server group.

Delete

Delete a target server group. Individual target servers assigned to that group are not deleted.

Properties

View the properties associated with the target server group.

>

Add the servers that are members of the selected group(s) to the list of target servers on which the job will run.

See Also

Creating Jobs

Multiserver Administration


Target Server Properties

Use this dialog box to view or specify the following options.

Options

Name

View the name of the target server.

Location

Specify the physical location and/or description of the target server.

Time zone

View the time zone of the target server.

Local time

View the current date and time on the target server in its time zone. The value displayed is not updated in real time.

Date enlisted

View the local date and time that the target server enlisted.

Last poll

View the local date and time that the target server last polled the master server.

Polling interval

View the time interval between the target server's polls of the master server.

Unread instructions

View how many instructions from the master server have not been read by the target server.

Server belongs to these target server groups

View the target server groups to which the target server belongs.

See Also

Creating Jobs

Multiserver Administration


Target Servers (Target Server Status Tab)

Use this tab to view or specify the following options.

Options

Target server

View the name of the target server. If you right-click on this server, you can also view the properties of the server, check the state of SQL Server Agent, and view the SQL Server Agent error log.

Local time

View the current date and time of the target server in its time zone.

Last polled

View the local date and time that the target server last polled the master server.

Unread instructions

View how many instructions from the master server have not yet been read by the target server.

Status

View the status of the target server: blocked, OK, or offline. Offline indicates that the selected target server has not polled the master server within the last three poll intervals.

Force Poll

Force the selected target server to poll the master server.

Force Defection

Force the selected target server to defect from the master server.

Post Instructions

Post instructions for one or more target servers.

See Also

Multiserver Administration

How to view a master SQL Server Agent error log (Enterprise Manager)


Target Servers (Download Instructions Tab)

Use this tab to view or specify the following options.

Options

Target server

Filter download instructions by target server.

Job

Filter download instructions by job.

Target server

View the target servers to which the download instruction applies.

Operation

View the operation that will be performed by the download instruction.

Object name

View the name of the object that will be affected by the download instruction.

Date posted

View the local date and time that the instruction was posted.

Date downloaded

View the local date and time that the instruction was downloaded by the target server. If the target server had a problem while downloading the instruction, an error is indicated.

Instruction download status

View the most recent status for the selected download instruction.

Delete

Delete the selected download instruction. Use this with caution because the sequence of instructions is often of critical importance.

Clear

Clear the status of the download instruction, thus allowing the target server another download attempt.

See Also

Multiserver Administration


Post Download Instructions

Use this dialog box to view or specify the following options.

Options

Instruction type

Specify the type of instruction to post.

Description

Describe what the instruction will cause the target server to do.

All target servers

Specify that all target servers are to receive the instruction.

These target servers

Specify that only selected target servers are to receive the instruction.

Target server

View the name of the target server.

Local time

View the current date and time of the target server in its time zone.

Polling interval

View the interval, in seconds, at which the target server polls the master server.

Select

Specify that the target server should receive the download instruction.

See Also

Multiserver Administration

How to start a job (Enterprise Manager)


Enlist Registered Servers Into this MSX

Use this dialog box to view or specify the following options.

Options

Server name

View the name of a server that is registered but not enlisted as a target server of this master server. Only instances of Microsoft® SQL Server™ running on Microsoft Windows NT® 4.0 or Windows® 2000 are shown.

Known credentials

View whether or not the registration information for the server includes connection information. If connection information is not included, you must provide it to enlist the server.

Properties

View the properties associated with the selected server.

Enlist

Enlist all checked servers into the master server.

See Also

Multiserver Administration

How to enlist a target server from a master server (Enterprise Manager)


Multiserver Job Execution Status

Use this dialog box to view or specify the following options.

Options

Job

View job execution status by job.

Server

View job execution status by server.

Job name

Specify the name of the job to view, if you have selected to show job execution status by job.

Name

View the server name, if you have selected to show job execution status by job. View the job name, if you have selected to show job execution status by server.

Last run time

View the job execution start date and time most recently uploaded by the target server for the selected job. The most current and comprehensive information about job execution history is available by viewing the remote job history.

Last run status

View the job outcome status most recently uploaded by the target server for the selected job. The most current and comprehensive information about job execution history is available by viewing the remote job history.

Last run message

View the job outcome message most recently uploaded by the target server for the selected job. The most current and comprehensive information about job execution history is available by viewing the remote job history.

View Remote Job History

Make a connection to the target server and view job history information remotely.

Target Server Status

View the status of the target server.

Synchronize Jobs

Resynchronize all multiserver jobs on the target server, if you have selected to show job execution status by server. Resynchronize the specified job on all target servers on which it executes, if you have selected to show job execution status by job.

See Also

Multiserver Administration

How to view the job history (Enterprise Manager)


Generate SQL Script

Use this dialog box to view or specify the following options.

Options

File name

Specify the file name for the SQL script. Click the browse (...) button to select a file name to use for saving the SQL script.

MS-DOS text (OEM)

Save the Transact-SQL script in the format of the current Microsoft® Windows® system code page. Select this option if you will use the script in a batch operation and execute it from the command prompt.

Windows text (ANSI)

Save the Transact-SQL script in ANSI format. Select this option if the script will be used in SQL Query Analyzer or another Windows application.

International text (Unicode)

Save the Transact-SQL script in Unicode format. Select this option if the script uses special international characters that are supported only in the Unicode font. This format requires two times the disk space of either the current Windows code page or ANSI.

Replace alert if it exists

Specify that the script code should replace the alert if it already exists.

Include notifications sent by the alert to the operators

Include in the script any notifications sent by the alert to the operators.

Include the name of the job executed by the alert

Include in the script the name of the job executed by the alert.

TSQL batch separator

Specify the word used to separate Transact-SQL command batches in the script.

Preview

View the Transact-SQL script that will be created.

See Also

Copying Operators or Alerts to Other Servers


Generate SQL Script

Use this dialog box to view or specify the following options.

Options

File name

Specify the file name for the SQL script. Click the browse (...) button to select a file name to use for saving the SQL script.

MS-DOS text (OEM)

Save the Transact-SQL script in the format of the current Microsoft® Windows® system code page. Select this option if you will use the script in a batch operation and execute it from the command prompt.

Windows text (ANSI)

Save the Transact-SQL script in ANSI format. Select this option if the script will be used in SQL Query Analyzer or another Windows application.

International text (Unicode)

Save the Transact-SQL script in Unicode format. Select this option if the script uses special international characters that are supported only in the Unicode font. This format requires two times the disk space of either the current Windows code page or ANSI.

Replace operator if it exists

Specify that the script code should replace the operator if it already exists.

Include notifications sent by alerts to the operator

Include in the script any notifications sent by alerts to the operator.

TSQL batch separator

Specify the word used to separate Transact-SQL command batches in the script.

Preview

View the Transact-SQL script that will be created.

See Also

Copying Operators or Alerts to Other Servers


Generate SQL Script

Use this dialog box to view or specify the following options.

Options

File name

Specify the file name for the SQL script. Click the browse (...) button to select a file name to use for saving the SQL script

MS-DOS text (OEM)

Save the Transact-SQL script in the format of the current Microsoft® Windows® system code page. Select this option if you will use the script in a batch operation and execute it from the command prompt.

Windows text (ANSI)

Save the Transact-SQL script in ANSI format. Select this option if the script will be used in SQL Query Analyzer or another Windows application.

International text (Unicode)

Save the Transact-SQL script in Unicode format. Select this option if the script uses special international characters that are supported only in the Unicode font. This format requires two times the disk space of either the current Windows code page or ANSI.

Replace job if it exists

Specify that the script code should replace the job if it already exists.

TSQL batch separator

Specify the word used to separate Transact-SQL command batches in the script. This is useful if Transact-SQL job steps already contain the GO command separator.

Preview

View the Transact-SQL script that will be created.

See Also

Scripting Jobs Using Transact-SQL


View Job Category Properties (General Tab)

Use this tab to view or specify the following options.

Options

Name

View the name of the selected job category.

Jobs in this category

View the list of jobs in the selected job category.

See Also

Creating Jobs


Connection Properties

Use this dialog box to specify a connection (session) and optionally, a database (catalog) for importing meta data, if supported by the data provider. These specifications must be made to import meta data (for example, table and column information, primary and foreign keys, indexes) into Microsoft® SQL Server™ 2000 Meta Data Services. For the import of meta data to work, the data provider specified must support OLE DB schema rowsets.

After a connection is made, Data Transformation Services (DTS) reads the meta data information from the specified connection into Meta Data Services. Later, when you save a Data Transformation Services (DTS) package with Scanning Options enabled, the tasks in the package will form relationships to the imported meta data.

With the Import Metadata selection you read the meta data from only one database (catalog); when you select Scan all referenced catalogs in the Scanning Options dialog box, the meta data from all databases referenced in the package are saved to Meta Data Services.

Not all the options defined below are available for all providers. A subset of the options will be shown, depending on the provider chosen.

Options

Source

Select the data-specific driver that matches the data storage format of the source data.

File Name

Specify the database path and file name holding the data to be imported (for example, C:\MyData.xls, or \\Sales\Database\Northwind.mdb).

User name

Specify a user name for the database connection.

Password

Specify a password for the database connection.

Advanced

Display the Advanced Properties dialog box, where you can enter custom settings. For more information about OLE DB provider properties, search in the Platform SDK section in the MSDN® Library at Microsoft Web site.

UDL Filename

Specify the name of the Microsoft Data Link (.udl) file that contains the connection string.

Always read properties from UDL file

Specify that the package search for and read the connection string from the specified data link (.udl) file each time the package is executed. Changes made to the data link file between different executions of the package will be incorporated on the next run. If you select this check box, the .udl file must be deployed with the package so the package can find it and read from it. If you do not select this check box, the connection string is copied from the .udl into the package, and the file is not referenced again. Connection changes then can be modified only by editing the DTS package directly.

Properties

Display the Data Link Properties dialog box, where you configure a data link connection. Changes made in the dialog box will be incorporated into the package created during the current session and will not change the data link file.

User/System DSN

Specify the name of the existing user or system Data Source Name (DSN) that points to the data source.

New

Display the Create New Data Source dialog box, where you can create an ODBC DSN. For more information about creating an ODBC data source, search in the Platform SDK section in the MSDN Library at Microsoft Web site.

File DSN

Specify the name of the existing file DSN that points to the data source.

Server

Specify the name of the server holding the data source.

Use Windows Authentication

Specify that the package use Windows Authentication for login to the SQL Server database.

Use SQL Server Authentication

Specify that the package use SQL Server Authentication for login to the SQL Server database.

Database

List databases on the specified instance of SQL Server.

Refresh

Cause the database list to populate on computers running on Microsoft Windows® 98.

See Also

DTS Connections

Sharing Meta Data


DTS Package Versions

Use this dialog box to display the version history of a selected Data Transformation Services (DTS) package, and to edit or delete selected package versions.

The available options depend on how you saved the package:

  • You can edit and delete versions of packages saved to Microsoft® SQL Server™.

  • You can edit versions of packages saved to SQL Server 2000 Meta Data Services; however, you cannot delete package versions.

To edit or delete a package, click on a Version, then click Edit or Delete.

See Also

Deleting a DTS Package

Saving a DTS Package to SQL Server

Saving a DTS Package to Meta Data Services


Package Properties

Use this dialog box to set options for Data Transformation Services (DTS) applications.

Options

Turn on cache

Optimize the performance of DTS applications when using DTS Designer on computers running Microsoft® Windows® 2000. For this environment, the time needed to open a DTS package decreases significantly if you select this check box.

Refresh Cache

Cause the DTS application to recognize new scripting languages, custom transformations, OLE DB providers, and custom tasks that were added since the last time the cache was refreshed. This option is only available if the Turn on cache check box is selected. Registering a new task in DTS Designer will refresh the cache for tasks, but new OLE DB providers that were added will not appear in the DTS application until you refresh the cache.

Generally, click Refresh Cache after installing a new instance of Microsoft SQL Server™ 2000, registering a new DTS object, or adding a new OLE DB provider.

Show multi-phase pump in DTS Designer

Display the multiphase data pump options in transformation tasks in DTS Designer. You access the multiphase data pump options when configuring transformations in either the Transform Data Task or the Data Driven Query Task.

Turn on just-in-time debugging

Use the script debugger supplied with those products to debug your Microsoft ActiveX® scripts. This option is available only if you have Windows 2000, Microsoft Visual InterDev® 6.0 or the Microsoft Windows NT® 4.0 Option Pack installed.

See Also

Multiphase Data Pump Functionality

Debugging ActiveX Scripts


DTS Packages Logs

Use this dialog box to select from the Data Transformation Services (DTS) package logs stored on the server and view the events and details of the package execution.

Options

DTS Packages available on the server <server name>

Select the package whose logs you would like to have displayed.

DTS Package versions and log tree

View the default display, which shows all the versions of a package for which you have created logs. Expand the tree to view all the logs that you created each time you executed that package version and requested a log to be generated. The logs are listed with the oldest logs shown at the top.

Open Log

Display the Log Detail dialog box, where you can view the step execution details for the selected log.

Delete

Display the Delete Package Logs dialog box, where you can select which package logs to delete.

See Also

Using DTS Package Logs


Delete Package Logs

Use this dialog box to remove Data Transformation Services (DTS) package logs. The main purpose of removing package logs is to delete the oldest logs from the system and keep the newer, more pertinent logs. The only way to force the feature to delete the newest logs in a version is by selecting the Delete most recent log check box.

Options

DTS Package available on the server <server name>

Specify the package whose logs you want to see in the tree.

Delete all the logs for the selected version <Version x> created on <mm/dd/yy hh:mm:ss AM/PM>

Specify that all the logs in the version you have highlighted will be deleted, with the exception of the most recent one. If you want all the logs for the selected version deleted without exception, you also must select the Delete most recent log check box.

Delete all the logs for the package: <package name>

Specify that all the logs for the package that you have selected will be deleted, except for the most recent log and version. If you also select the Delete most recent log check box, all package logs and versions are deleted.

Delete most recent log

Specify that only the most recent log of the selected version will be deleted. This option is selected by default.


Log Detail

Use this dialog box to review detailed execution information on Data Transformation Services (DTS) package steps.

Options

Status

View a symbol that indicates the success or failure of the step. The green check mark indicates the step completed successfully, while the red "X" indicates an error occurred in that step. Any step not run is not logged and will not appear in the Log Detail dialog box.

Step Name

View the step in the package. This indicates the step to which the rest of the columns in this row of the log are referring.

Run status

View the code that indicates the step status. Status codes are:

  • Step Waiting = 1

  • Step In Progress = 2

  • Step Inactive = 3

  • Step Completed = 4

For more information, see DTSStepExecStatus.

Start Time

View the date and time that the step was started. The format of the date and time is yyyy-mm-dd hh:mm:ss:ms.

End Time

View the date and time that the step finished. The format of the date and time is yyyy-mm-dd hh:mm:ss:ms.

Elapsed Time

View the time it took for the step to execute. This is the difference between the Start Time and End Time fields.

Error code

View the error code. If a step executes successfully, an error code of zero is entered in this column. If the step did not execute successfully, the error code indicating the reason for step failure will be entered in this column.

Error description

View a brief text description of the error that occurred.

More Info

Expand the Log Detail dialog box to display the Task Detail section.

Less Info

Remove the Task Detail section from the Log Detail dialog box.

View Error

Display the View package log error description dialog box, where you view a detailed description of the information in the Error Description column.

Task Detail

Display detailed real-time logging information for DTS tasks. While the DTS tasks supplied with Microsoft® SQL Server™ do not provide this detailed logging information. you can write DTS custom tasks that do. For more information, see WriteTaskRecord Method.

Status

View status of the task. A green checkmark indicates the step has completed successfully. A red "X" indicates the step has failed.

Description

View detailed description of the task.

Error Code

View the error message number if errors are raised. Ok indicates that no errors have been encountered.