Chapter 10 - Programming Troubleshooting

Article
01/28/2010

Problems reported by the Microsoft SQL Server ODBC driver are not all related to ODBC errors. The driver is the interface between an ODBC application and all of the SQL Server components. All SQL Server errors are returned through the SQL Server ODBC driver. Diagnosing errors reported by the driver includes:

Diagnosing ODBC connection errors.
Diagnosing general ODBC errors.

Troubleshooting the SQL Server ODBC Driver

Troubleshooting the Microsoft SQL Server ODBC driver should include a review of the following issues:

If applications prepare a lot of SQL, it is recommended that these clients release the SQL as soon as possible to free memory. For more information, see "Prepared Execution" in Microsoft SQL Server Building Applications.
Few or no servers appear in the Register Server Wizard in SQL Server Enterprise Manager after either clicking Next in the Welcome dialog box, or executing osql-L.

Microsoft Windows NT enumerates servers, but Microsoft Windows 95/98 does not. On Windows 95/98, the only servers enumerated either by using the Register Server Wizard or by executing osql-L are those servers configured explicitly using the Client Network Utility.

Windows NT enumerates servers by network broadcast, listening for a fixed time period. Only those servers either added explicitly or those receiving the broadcast and replying within the specified time limit are enumerated.

Therefore, on a Windows NT server, it is possible to see a large list
of enumerated servers at one point in time, and then see a smaller list of enumerated servers just a few seconds later. This absence of enumerated servers can be caused by one or more temporarily busy or unavailable network computers.
On Windows 95/98, a connection cannot be established to any ODBC application if the Shared Memory Net-Library file named dbmsshrn.dll was either renamed or deleted. This Shared Memory Net-Library file is required for local connections.
All driver-specific connection attributes are reset when an application uses connection pooling. After a connection is complete, an application using connection pooling must set all driver-specific connection attributes.
To eliminate the row count messages from DML statements inside a procedure, use SET NOCOUNT ON as the first statement in the procedure.
Character and binary parameters are padded to the size specified in SQLBindParameter. To disable, clear the AnsiNPW check box.
The code page of the server must be installed on the client workstation unless automatic translation is disabled.
Parameters must be specified as SQL_PARAM_INPUT unless the parameters are used with a stored procedure and declared as OUTPUT parameters.
When a 2.65 or earlier version of the SQL Server ODBC driver is used and connected to a server running SQL Server 7.0, the server classifies the application as a pre-SQL Server 7.0 client and does not support certain features introduced in SQL Server 7.0. For more information, see "Connecting Early Version Clients to SQL Server 7.0" in Microsoft SQL Server Building Applications.

For more information about the changes from the version 2.0 ODBC driver to the version 3.0 ODBC driver, see the Microsoft ODBC 3.0 Programmer's Reference and SDK Guide.

See Also

In Other Volumes

"Programming ODBC SQL Server Applications" in Microsoft SQL Server Building Applications

"Programming Stored Procedures" in Microsoft SQL Server Database Developer's Companion

"SQLBindParameter" in Microsoft SQL Server Building Applications

"SQLSTATE Messages" in SQL Server Books Online

Diagnosing ODBC Connection Errors

This topic discusses how to diagnose issues that may arise when connecting to Microsoft SQL Server using the SQL Server ODBC driver.

A connection attempt using an ODBC data source fails and a call to SLQDiagRec () returns:
```
szSqlState = "IM002", *pfNativeError = 0,
```

szErrorMsg="[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

The ODBC driver manager could not find the ODBC data source. Make sure the data source name was given correctly. Also make sure the data source name was defined using the same Microsoft Windows account the application is running under, or it is a system data source. If the application is running as a Microsoft Windows NT service, the data source must be a system data source.

For more information, see "Adding a Data Source" in *Microsoft SQL Server Building Applications*.

A connection attempt fails and a call to SQL Error () returns:
```
szSqlState = "IM003", *pfNativeError = 0,
```

szErrorMsg="[Microsoft][ODBC Driver Manager] specified driver could not be loaded"

The driver manager count not load the driver DLL successfully, Sqlsrv32.dll. Make sure a valid version of Sqlsrv32.dll is in the client's path.

A connection attempt fails and a call to SQLError () returns:
```
szSqlState = "S1000", *pfNativeError = 126,
```

szErrorMsg="[Microsoft][ODBC Driver Manager] Unable to load communication module. Driver has not been correctly installed."

The SQL Server driver could not load the SQL Server client Net-Library. Verify that the ODBC data source specifies a valid Net-Library name. Verify that a valid version of the Net-Library .dll is in the client's path. This may also occur if the .dlls and files making up the underlying network protocol stack, such as Novell's SPX/IPX, or a TCP/IP protocol stack, are not installed properly. Verify the components with the network administrator, or reinstall the client network components.

For other network connectivity errors, SQLDiagRec() will return errors in which the szErrorMsg string has this format:
```
[Microsoft][ODBC SQL Server Driver][Net-Libraryname]
```

Net-Libraryname is the display name of a SQL Server client Net-Library (for example, Named Pipes, Shared Memory, Multiprotocol, TCP/IP Sockets, NwLink IPX/SPX, or Banyan VINES). The remainder of the error messages contains the Net-Library function called and the function called in the underlying network API by the TDS function. The *pfNative* error code returned with these errors is the error code from the underlying network protocol stack. Work with the network administrator or your SQL Server support vendor to determine a likely cause for the network error.

Diagnosing General ODBC Errors

This topic discusses how to diagnose issues that may arise when using the Microsoft SQL Server ODBC Driver.

The szErrorMsg string returned by SQLDiagRec() starts with:
```
"[Microsoft][ODBC SQL Server driver][DBMSxxxx]"
```

If DBMSxxxx is the name of a SQL Server Client Net-Library, the problem is a connectivity or network problem. For more information, see "Diagnosing ODBC Connection Errors" in this volume.

The szErrorMsg string returned by SQLDiagRec() starts with:
```
"[Microsoft][ODBC SQL Server driver][SQL Server]"
```

The error is coming from SQL Server. The pfNative variable returned by SQLError() is the SQL Server error code. Follow the directions for this error number in ODBC Error Message Format. Also, you should review the problem with the database administrator.

If you are experiencing difficulties with stored procedures that reference temporary tables on SQL Server 6.0 or later, make sure that the CREATE TABLE statements for the temporary tables specify NULL or NOT NULL for each column. For more information, see "CREATE PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference.
If you are experiencing differences in behavior between DB-Library and ODBC clients, or in ODBC clients upgraded from earlier versions of SQL Server, these could be due to different options set by the SQL Server ODBC driver. For more information, see "Effects of SQL-92 Options" in Microsoft SQL Server Building Applications.
If you are experiencing Microsoft Access conformance errors, or if calls to the ODBC catalog API functions are failing, ensure the right version of Instcat.sql was run in the target server. For more information, see "Upgrading the Catalog Stored Procedures (ODBC)" in Microsoft SQL Server Building Applications.
If you are experiencing syntax errors from either SQL Server:
```
szSqlState = "37000", *pfNativeError = 170,
```

szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server] Line 1: Incorrect syntax near '*'."

or from the ODBC SQL Server Driver, you need to determine if the problem is with the SQL statements given to the SQL Server ODBC driver by the application, or with the Transact-SQL statements generated by the driver. 

Use the ODBC trace to see the SQL statements passed from the application to the SQL Server ODBC driver. The ODBC trace is activated from the **ODBC** icon in Control Panel. 

If the trace shows that the SQL statements coming from the application are not using valid Transact-SQL or ODBC SQL syntax, diagnose the application.

If the trace shows that the application is passing valid Transact-SQL or ODBC SQL statements to the driver, use SQL Server Profiler to trace the Transact-SQL statements sent from the SQL Server ODBC driver to SQL Server. For more information, see "Monitoring with SQL Server Profiler" in *Microsoft SQL Server Administrator's Companion*.

Troubleshooting SQL-DMO

After a successful installation of the Microsoft SQL Server version 7.0 client utilities, SQL-DMO files can be found in these locations with these names:

In the %SystemRoot%\system32 directory, Sqlwoa.dll.

In addition to the above files, a successful installation of SQL-DMO includes:

ODBC version 3.5
SQL Server ODBC driver version 3.70
Odbcbcp.dll

If the installation of client utilities fails, it is possible that not all SQL-DMO files will be present or some may not be registered properly.

To register the SQL-DMO components

From \Mssql7\Binn\Resources\<language> directory, execute:
```
\MSSQL7\BINN\REGSVR32 SQLDMO.RLL
```

From any directory, execute:

\MSSQL7\BINN\REGSVR32.EXE \MSSQL7\BINN\resources\1033\SQLDMO.RLL

SQL Namespace

To use SQL Namespace (SQL-NS), you must have these files in these locations with these names and the SQL-DMO files mentioned above:

In the %SystemRoot%\system32 directory, Sqlwoa.dll.

If the installation of client utilities fails, it is possible that not all SQL-NS files will be present or some may not be registered properly.

To register the SQL-NS components

From \Mssql7\Binn\Resources\<language> directory, execute:
```
\MSSQL7\BINN\REGSVR32 SQLNS.RLL
```