Data Source Wizard Screen 1

On the first screen of the wizard, you can specify the name and description of the data source, and the name of the server running Microsoft® SQL Server™ to which the data source will connect.

Name box

The data source name used by an ODBC application when it requests a connection to the data source. For example, "Personnel." The data source name is displayed in the ODBC Data Source Administrator dialog box.

Description box

An optional, description of the data source. For example, "Hire date, salary history, and current review of all employees."

Server box

The name of a SQL Server on your network. If you select a server name from the list, no further configuration is needed. If you enter the name of a server that does not exist as an entry in the SQL Client Configuration Utility, you can create a server alias for the new name on the next screen of the wizard.

You can enter "(local)" in the server box when you are using the same computer as SQL Server. The user can then connect to the local copy of SQL Server, even when running a nonnetworked version of SQL Server. SQL Server 2000 supports multiple instances of SQL Server running on the same computer. To specify a named instance of SQL Server, the server name is specified as ServerName\InstanceName.

For more information about server names for different types of networks, see the SQL Server installation documentation in SQL Server Books Online.

If the information specified on this screen is all that is needed to connect to SQL Server, you can click Finish. Defaults are used for all attributes specified on other screens of the Wizard.


Data Source Wizard Screen 2

On the second screen of the wizard, you can specify the method of authentication and set up Microsoft® SQL Server™ advanced-client entries and the login and password the SQL Server ODBC driver will use to connect to SQL Server while configuring the data source.

With Windows NT authentication using the network login ID option button

Specifies that the SQL Server ODBC driver request a secure (or trusted) connection to a SQL Server running on Microsoft Windows NT® or Windows® 2000. When selected, SQL Server uses integrated login security to establish connections using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The SQL Server system administrator must have associated your Microsoft Windows® login with a SQL Server login ID.

With SQL Server authentication using a login ID and password entered by the user option button

Specifies that the SQL Server ODBC driver not request a secure (or trusted) connection to SQL Server. When selected, SQL Server uses standard login security to establish connections using this data source. You must specify a SQL Server login ID and password for all connection requests.

Client Configuration button

Starts the Add New Network Library Configuration dialog box of the SQL Server Client Configuration.

If you specified a new name in the Server box on the first screen of the wizard, you may need to use this dialog box to add a server alias configuration entry whose name matches the name you specified in the Server box on the first screen of the ODBC wizard. The alias name must match the name you specified in the Server box. For Microsoft SQL Server, you do not usually need a server alias configuration. In most cases, the SQL Server ODBC driver can connect by using the default Net-Library and the network name supplied in the Server box on the first screen of the wizard.

Click Client Configuration if you want the connection to use a network library other than the client's default network library. Also click Client Configuration if the actual network address of the server must be specified for a successful connection. For example, when using the TCP/IP Net-Library you may need to specify the port and socket address of the server, or, if a SQL Server is listening on an alternate named pipe, you must specify the pipe name in the advanced entry.

For more information about configuring clients, see SQL Server Books Online.

Connect to SQL Server to obtain default settings for the additional configuration options check box

When selected, the SQL Server driver obtains initial settings from the SQL Server for the options on the following screens of the wizard. The SQL Server driver connects to the SQL Server named in the Server box on the first screen.

When clear, the driver uses standard defaults as the initial settings for the options on the following screens in the wizard.

Login ID box

Specifies the login ID the SQL Server driver uses when connecting to SQL Server if With SQL Server authentication using a login ID and password entered by the user is selected. This only applies to the connection made to determine the server default settings; it does not apply to subsequent connections made using the data source after it has been created.

Password box

Specifies the password the SQL Server uses when connecting to SQL Server if With SQL Server authentication using a login ID and password entered by the user is selected. This only applies to the connection made to determine the server default settings; it does not apply to subsequent connections made using the new data source.

Both the Login ID and Password boxes are disabled if With Windows NT authentication using the network login ID is selected, or if Connect to SQL Server to obtain default settings for the additional configuration options is not selected.


Data Source Wizard Screen 3

On the third screen of the wizard, you can specify the default database, how the driver should use stored procedures to support SQLPrepare, various ANSI options to be used by the driver, and whether to use a failover server.

Change the default database to box

Specifies the name of the default database for any connection made using this data source. When clear, connections use the default database defined for the login ID on the server. When selected, the database named in the box overrides the default database defined for the login ID. If the Attach database filename box has the name of a primary file, the database described by the primary file is attached as a database using the database name specified in the Change the default database to box.

Using the default database for the login ID is more efficient than specifying a default database in the ODBC data source.

Attach database filename

Specifies the name of the primary file for an attachable database. This database is attached and used as the default database for the data source. Specify the full path and file name for the primary file. The database name specified in the Change the default database to box is used as the name for the attached database.

Create temporary stored procedures for prepared SQL statements and drop the stored procedures check box

When clear, the Microsoft® SQL Server™ driver does not create stored procedures to support the SQLPrepare ODBC function. When selected, the SQL Server driver creates temporary stored procedures to support the SQLPrepare ODBC function. This check box and its associated options are only relevant when connecting to older versions of SQL Server.

Only when you disconnect option button

Specifies that temporary stored procedures created for SQLPrepare are dropped when the SQLDisconnect ODBC function is called. This allows the driver to reuse stored procedures if the same SQL statement is prepared multiple times and reduces the overhead associated with dropping the stored procedures while the application is running. Selecting this option for an application that runs for a long time without disconnecting, or for an application that issues a lot of SQLPrepare calls, can lead to a build up of temporary stored procedures.

When you disconnect and as appropriate while you are connected option button

Specifies that temporary stored procedures created for SQLPrepare are dropped when SQLDisconnect is called, when SQLFreeHandle is called for the statement handle, when SQLPrepare or SQLExecDirect is called to process a new SQL statement on the same statement handle, or when a catalog function is called. Some overhead is generated because the temporary stored procedures are dropped while the application is running, but this prevents a build up of temporary stored procedures for long-running applications.

Use ANSI quoted identifiers check box

Specifies that QUOTED_IDENTIFIERS be set on when the SQL Server ODBC driver connects. When selected, SQL Server enforces ANSI rules regarding quote marks. Double quotes can only be used for identifiers, such as column and table names. Character strings must be enclosed in single quotes:

SELECT "au_id"
FROM "authors"
WHERE "au_lname" = 'O''Brien'

When clear, applications that use quoted identifiers, such as the Microsoft Query utility that comes with Microsoft Excel, encounter errors when they generate SQL statements with quoted identifiers.

Use ANSI nulls, paddings, and warnings check box

Specifies that the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDINGS options be set on when the SQL Server driver connects.

With ANSI_NULLS set on, the server enforces ANSI rules regarding comparing columns for NULL. The ANSI syntax "IS NULL" or "IS NOT NULL" must be used for all NULL comparisons. The Transact-SQL syntax "= NULL" is not supported.

With ANSI_WARNINGS set on, SQL Server issues warning messages for conditions that violate ANSI rules but do not violate the rules of Transact-SQL. Examples of such errors are data truncation on execution of an INSERT or UPDATE statement, or encountering a null value during an aggregate function.

With ANSI_PADDING set on, trailing blanks on varchar values and trailing zeroes on varbinary values are not automatically trimmed.

Use the failover SQL Server if the primary SQL Server is not available check box

Specifies that when a failover server is defined for the SQL Server specified in the data source, the SQL Server driver collects connection information for the failover server when it connects to the specified primary server. If the application loses its connection to the primary SQL Server, it cleans up its current transaction and attempts to reconnect to the primary SQL Server. If the driver detects that the primary server is not available, it automatically connects to the failover server. This option is disabled if the server does not support failover operations.


Data Source Wizard Screen 4

On the fourth screen of the wizard, you can specify the language to be used for Microsoft® SQL Server™ messages, character set translation, and whether the SQL Server driver should use regional settings. You can also control the logging of long-running queries and driver statistics settings.

Change the language of SQL Server system messages to box

Each SQL Server can have multiple sets of system messages, with each set in a different language (English, Spanish, French, and so on). If a data source is defined against a server that has multiple sets of system messages, you can specify which language you want to use for system messages. In the list, click the language. This option is unavailable if only one language is installed on the SQL Server.

Perform translation for character data check box

When selected, the SQL Server ODBC driver converts ANSI strings sent between the client computer and SQL Server by using Unicode. The SQL Server ODBC driver sometimes converts between the SQL Server code page and Unicode on the client computer. This requires that the code page used by SQL Server be one of the code pages available on the client computer.

When cleared, no translation of extended characters in ANSI character strings is done when they are sent between the client application and the server. If the client computer is using an ANSI code page (ACP) different from the SQL Server code page, extended characters in ANSI character strings may be misinterpreted. If the client computer is using the same code page for its ACP that SQL Server is using, the extended characters are interpreted correctly.

Use regional settings when outputting currency, numbers, dates, and times check box

Specifies that the driver use the regional settings of the client computer for formatting currency, numbers, dates, and times in character output strings. The driver uses the default regional setting for the Microsoft Windows® 95/98, Microsoft Windows NT®, or Microsoft Windows® 2000 login account of the user connecting through the data source. Select this option for applications that only display data, not for applications that process data.

Save long running queries to the log file box

Specifies that the driver log any query that takes longer than the Long query time value. Long-running queries are logged to the specified file. To specify a log file, either type the full path and file name in the box or click Browse to select a log file by navigating through existing file directories.

Long query time (milliseconds) box

Specifies a threshold value, in milliseconds, for long-running query logging. Any query that takes longer than this number of milliseconds to run is logged.

Log ODBC driver statistics to the log file box

Specifies that statistics be logged. Statistics are logged to the specified file. To specify a log file, either type the full path and file name in the box or click Browse to select a log file by navigating through existing file directories.

The statistics log is a tab-delimited file that can be analyzed in Microsoft Excel or any other application that supports tab-delimited files.


SQL Server Login Dialog Box

The Microsoft® SQL Server™ ODBC driver displays the SQL Server Login dialog box when you call an ODBC connection without specifying enough information for the driver to connect to a SQL Server.

When the SQL Server Login dialog box is first displayed, it contains only the:

Server box or the name of the data source.

Use Trusted Connections check box.

Login ID box.

Password box.

OK, Cancel, and Options buttons.

If you click the Options button, the dialog box expands to also contain the Options group:

Database box

Language box

Application Name box

Workstation ID box

Server box

The name of a SQL Server on your network. If you select a server name from the list, no further configuration is needed. If you type in a server name, it must be an advanced entry in the SQL Client Configuration utility or the network name of a server running SQL Server. This box is replaced with the data source name (DSN) if you specified a DSN for the connect.

You can enter "(local)" in the server box when you are using the same computer as SQL Server. The user can then connect to a local copy of SQL Server, even when running a nonnetworked version of SQL Server.

For more information about server names for different types of networks, see the SQL Server installation documentation in SQL Server Books Online.

Use Trusted Connection check box

When selected, specifies that the SQL Server ODBC driver request a secure (or trusted) connection to a SQL Server running on Microsoft Windows NT® or Microsoft Windows® 2000. When selected, SQL Server uses integrated login security to establish connections using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The SQL Server system administrator must have associated your Windows network ID with a SQL Server login ID.

When clear, SQL Server uses standard login security to establish connections using this data source. You must specify a login ID and password for all connection requests.

Login ID box

Specifies the SQL Server login ID to use for the connection if Use Trusted Connection is not selected. If Use Trusted Connection is selected the Login ID box is disabled.

Password box

Specifies the password for the SQL Server login ID used for the connection if Use Trusted Connection is not selected. If Use Trusted Connection is selected the Password box is disabled.

Database box

Specifies the default database to use on the connection. This overrides the default database specified for the login on the server. If no database is specified, the connection uses the default database specified for the login on the server.

Language box

Specifies the national language to use for SQL Server system messages. The SQL Server must have the language installed. This overrides the default language specified for the login on the server. If no language is specified, the connection uses the default language specified for the login on the server.

Application Name box

Optionally specifies the application name to be stored in the program_name column in the row for this connection in master.dbo.sysprocesses.

Workstation ID box

Optionally specifies the workstation ID to be stored in the hostname column in the row for this connection in master.dbo.sysprocesses.


Microsoft SQL Server 2000 Copyright and Disclaimer

This document, including sample applications herein, is provided for informational purposes only and Microsoft makes no warranties, either express or implied, in this document. Information in this document, including samples, URL and other Internet Web site references, is subject to change without notice. The entire risk of the use or the results of the use of this document remains with the user.

The primary purpose of a sample is to illustrate a concept, or a reasonable use of a particular statement or clause. Most samples do not include all of the code that would normally be found in a full production system, as a lot of the usual data validation and error handling is removed to focus the sample on a particular concept or statement. Technical support is not available for these samples or for the provided source code.

Unless otherwise noted, the example companies, organizations, products, people, and events depicted herein are fictitious and no association with any real company, organization, product, person, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

©1996-2000 Microsoft Corporation. All rights reserved.

Active Directory, ActiveX, BackOffice, CodeView, Developer Studio, FoxPro, JScript, Microsoft, Microsoft Press, Microsoft SQL Server, MSDN, MS-DOS, Outlook, PivotChart, PivotTable, PowerPoint, Visual Basic, Visual C++, Visual Studio, Win32, Windows 2000, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Version: 8.00.000