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 SQL Server Books Online.
• 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 and Windows 98 do not. On Windows 95 or Windows 98 operating systems, 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 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 the Windows 95 or Windows 98 operating systems, 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 SQL Server Books Online.

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.

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 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 SQL Server Books Online.

• 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 could not load the driver DLL, Sqlsrv32.dll, successfully. 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 SQL Server Books Online.

• 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 SQL Server Books Online.
• 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 SQL Server Books Online.
• If you are experiencing Microsoft Access conformance errors, or if calls to the ODBC catalog API functions are failing, check that the right version of Instcat.sql was run in the target server. For more information, see SQL Server Books Online.
• 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 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 SQL Server Books Online.