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
The Change the default database to box specifies the name of the default database for any connection made using this data source. When this box is 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 contains 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 box
The Attach database filename box 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 the Create temporary stored procedures for prepared SQL statements and drop the stored procedures check box is 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.
Only when you disconnect option button
The 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
The 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
The 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
The 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 include data truncation on execution of an INSERT or UPDATE statement, and 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
The 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.