IMPORTANT: This article contains information about modifying the registry. Before you
modify the registry, make sure to back it up and make sure that you understand how to restore
the registry if a problem occurs. For information about how to back up, restore, and edit the
registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Description of the Microsoft Windows Registry
The information presented in this article is for troubleshooting purposes only. There may be other parameters that are not documented here.
When you create a system DSN in the ODBC Data Source Administrator in Control Panel, and you use the Microsoft SQL Server ODBC driver, the DSN settings are saved in a registry key under the following with the same name of your DSN:
<B>HKEY_LOCAL_MACHINE\Software\ODBC\Odbc.ini</B>
If you create a user DSN, the settings are saved in a registry key under the following:
<B>HKEY_CURRENT_USER\Software\ODBC\Odbc.ini</B> <BR/><BR/>
The Microsoft SQL Server ODBC Driver setup also writes parameters to the Odbc.ini registry entry. There are several parameters in the Microsoft SQL Server ODBC Driver setup that have a default value.
When you first start to configure a DSN for SQL Server you will see that some of the parameters already have values. If you do not change the default value of a parameter you will not see a corresponding registry entry.
For example, there is a parameter in the setup named
Use ANSI quoted identifiers, which is selected by default. If you clear that parameter you will see an entry in the corresponding DSN, under Odbc.ini in the registry, named
QuotedId and the value is
No. By default the value is
Yes and you will not see the value in the registry if you keep the default value in your setup.
In case of authentication, the default is SQL Server Authentication. If you select
NT Authentication you will see a registry entry named
Trusted_Connection set to
Yes. Therefore, if you do not have any entry in the registry that corresponds to
Trusted_Connection, this means that you are using SQL Server Authentication. You can also set this
Trusted_Connection to
No from your program to get SQL Server Authentication.
The following table lists several parameters and their default values:
| Parameter Name | Default Value | Other Possible Values | Remarks |
|---|
| Server | DSN name | Any server name. | Change this value to point to your server. |
| Database | None | Any database name inside the preceding server. | Change this value to point to your database. |
| Language | us_english | Any valid language name for your server. | SQL Server language name. SQL Server can store messages for multiple languages in the sysmessages system table. If you connect to a SQL Server with multiple languages, the Language setting specifies which set of messages are used for the connection.
|
| UseProcForPrepare | 1 | 0 or 2 | Starting with Microsoft SQL Server driver version 3.7, or later, this property is disabled. Microsoft SQL Server 7.0, and later, always uses a temporary stored procedure whether or not you prepare a SQL statement before execution. |
| Trusted_Connection | No | Yes | This property determines whether you want to use SQL Server Authentication or NT Authentication. Trusted_Connection=NO means that you want to use SQL Authentication. |
| QuotedId | Yes | No | This determines whether to use quoted identifier (for example, a double quote around the table name) or not. |
| AnsiNPW | Yes | No | This determines whether to use ANSI null padding and warnings or not. If set to Yes and you have character data of size 10 (not varchar) in your SQL Server, and you select that data, it returns all 10 characters. If the data only has 2 characters (like "AA"), you will get AA with 8 blank spaces padded at the end. |
| AutoTranslate | Yes | No | Determines whether binary data is treated like character data. |
| QueryLog_On | No | Yes | Determines whether a long running query will be logged. |
| QueryLogTime | 30000 in milliseconds | Any integer number. (This setting is used when QueryLog_On is set to Yes.) | Digit character string that specifies the threshold (in milliseconds) for logging long-running queries. Any query that does not receive a response in the time specified is written to the long-running query log file. |
| QueryLogFile | C:\Query.log | Any valid filename on your disk. (This setting is used when QueryLog_On is set to Yes.) | Full path and name of the file used to log long-running queries.
|
| Regional | No | Yes | Respect client workstation settings for the region when converting date, time, and currency values to character strings. This setting should only be specified for applications that only display data, not for applications that process data.
|
| StatsLog_On | No | Yes | Enables driver performance logging. The default value is "Disables Driver Performance Logging". |
| StatsLogFile | C:\Stats.log | Any valid filename in your disk. (This setting is used when StatsLog_On is set to Yes.) | Full path and name of the file used to record SQL Server ODBC driver performance statistics. |
In future versions of the SQL Server ODBC driver there may be some additional parameters. All of the parameters have a default value, so it will not affect your current setup. This is why some parameters have no entries in the registry.
If you select the default value for a parameter there will be no corresponding registry entry. You will find a registry entry only when you change the default value. You can also overwrite the default behavior by setting the keywords in your connection string.
There are two other keywords that are also very important, Network and Address. Neither of these two keywords are found under the SQL Server ODBC entries in the registry.
These keywords control the network connectivity. You can set up the network protocol that your client uses to connect to SQL Server by clicking the
Client Configuration button in the
ODBC Data Source Administrator for SQL Server. If you edit the
Client Configuration, you will find a corresponding registry entry under:
<B>HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo</B>
You can use the following in the connection string of your application:
| Keyword | For TCP/IP | For Named Pipes |
|---|
| Address | YourServerName,ThePortNumber (default 1433) | \\.\pipe\sql\query |
| Network | DBMSSOCN | DBNMPNTW |
: You can also use the preceding keywords when you are connecting to SQL Server using the OLE DB Provider for SQL Server (SQLOLEDB) directly with your connection string.