INF: Diagnostic Tips for the Microsoft SQL Server ODBC Driver (140895)


The information in this article applies to:

  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
  • Microsoft Open Database Connectivity
This article was previously published under Q140895

SUMMARY

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

MORE INFORMATION

Diagnosing connection errors:
  1. A connection attempt using an ODBC data source fails and a call to SQLError() 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 Windows NT account the application is running under, or it is an ODBC 2.5 system data source. If the application is running as a Windows NT service, the data source must be a system data source.
  2. If a connection attempt fails and a call to SQLError() returns:
          szSqlState = "IM003", *pfNativeError = 0,
      szErrorMsg="[Microsoft][ODBC Driver Manager] Specified driver
               could not be loaded"
    the driver manager could not successfully load the driver DLL, SQLSRVR.DLL for Win16 or SQLSRV32.DLL for Win32. Make sure valid versions of these DLLs are in the client's path. Note that for the Microsoft ODBC ODS driver, the corresponding DLLS are ODSGATE.DLL (Win16) and ODSGT32.DLL (Win32).
  3. If a connection attempt fails and a call to SQLError() returns:
          szSqlState = "S1000", *pfNativeError = 126,
      szErrorMsg="[Microsoft][ODBC SQL Server Driver] Unable to load
               communication module.  Driver has not been
               correctly installed."
    The SQL Server driver could not load the SQL Server client side network library. Verify the ODBC data source against Knowledge Base article 137635 (INF: ODBC SQL Server Connection Parameters) to ensure a proper network library name was given. Verify that a valid version of the network library DLL is in the client's path. This can also sometimes occur if the DLLs and files making up the underlying network components, such as Novell's SPX/IPX, or a TCP/IP stack, are not properly installed. Verify the components with the network administrator, or reinstall the client network components.
  4. SQLError() returns:
          szErrorMsg = "[Microsoft][ODBC SQL Server Driver]TDS buffer
                 length too large(#0)"
    This error generally occurs when attempting a named pipe connection from a Windows for Workgroups 3.11 client to a Windows NT 3.5 or later server in a Novell network. The Windows for Workgroups client needs to have some of its network files updated.
  5. Attempts to connect with the 2.50.0121 version of the Win16 SQLSRVR.DLL using the Win16 DBMSSPX3 network library fail and SQLError() returns:
          szSqlState="01000", pfNativeError = 253,
      szErrorMsg="[Microsoft][ODBC SQL Server driver][DBMSSPX3]
               ConnectionRead(SPXListenForSequencedpacket())."
    You can get an updated driver that resolves the problem in SQL Server 6.0 Service Pack 1. You can also reset your sp_configure network packet size in SQL Server to 512 to eliminate the problem until you obtain the service pack.
  6. For other network connectivity errors, SQLError() will generally return errors as defined in article 137634 (INF: ODBC SQL Server Driver Network Messages). Review this article for information on dealing with the connectivity/network errors. You can also review article 138541 (INF: ODBCPING.EXE to Verify ODBC Connectivity to SQL Server) on using the ODBCPing utility to test connections. Diagnosing general errors:
  7. If the szErrorMsg string returned by SQLError() starts with:
          "[Microsoft][ODBC SQL Server driver][DBMSxxxx]"
    where DBMSxxxx is one of the network library names listed in article 137634 (previously referenced), the problem is a connectivity or network problem, so you should go back up to step 6.
  8. If the szErrorMsg string returned by SQLError() starts with:
          "[Microsoft][ODBC SQL Server driver][SQL Server]"
    The error is coming from SQL Server itself. The pfNative variable returned by SQLError() is the SQL Server error code. Follow the directions for this error number in either the SQL Server 4.2 "Troubleshooting Guide" or in Chapter 25 of the SQL Server 6.0 "Administrator's Companion." Also, you should review the problem with the database administrator, and attempt to replicate the problem in ISQL/w after setting the options discussed in article 135533 (INF: Differences in SQL Behavior Between ODBC and ISQL).
  9. 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 additional information, please see the following article in the Microsoft Knowledge Base:

    138761 : INF: Differences in Column Nullability

  10. If you are experiencing differences in behavior between DB-Library and ODBC clients, review articles 135533 (referenced earlier), 135532 (INF: SQL Server 6.0 ODBC Driver Changes Tempdb Usage), and 149921 (INF: ODBC ANSI upgrade changes from SQL Server 6.0 to 6.5).
  11. If you are experiencing differences in behavior when you upgrade from SQL Server 4.2x to SQL Server 6.0, review articles 135532 (INF: SQL Server 6.0 ODBC Driver Changes Tempdb Usage), 135533 (INF: Differences in SQL Behavior Between ODBC and ISQL), and 138761 (INF: Differences in Column Nullability).
  12. If you are experiencing differences in behavior when you upgrade from SQL Server 6.0 to SQL Server 6.5, review article 149921 (INF: ODBC ANSI Upgrade Changes from SQL Server 6.0 to 6.5). Sites upgrading from SQL Server 4.21a to SQL Server 6.5 should also review the articles listed in step 10 above.
  13. 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 has been run in the target server. See articles 137636 (INF: Relationship of the ODBC Driver to INSTCAT.SQL), and 137633 (INF: Addressing Access Conformance Errors in the SQL ODBC Drvr) for additional diagnostics on Microsoft Access conformance errors.
  14. If you are experiencing an increased use of tempdb in SQL Server 6.0, review article 135532 (referenced earlier in step 10).
  15. If you are experiencing syntax errors from either SQL Server itself:
          szSqlState = "37000", *pfNativeError = 170,
      szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
               Line 1: Incorrect syntax near '*'."
    or from the ODBC SQL Server Driver, review article 139655 (INF: Tracing SQL Generated by MS SQL Server ODBC Driver) for information on the options to trace the SQL commands in an ODBC environment. In general, if you are using a front-end product which generates ODBC SQL and you need to verify that the generated SQL is correct, you can use either the ODBC Administrator trace or the ODBCSpy utility. If you need to verify that the Transact-SQL being generated by the driver is correct, you can use the SQL Server 4032 trace flag.
If you encounter problems not listed above, do a keyword search of the Microsoft TechNet compact disc (or your preferred source for the Microsoft Knowledge Base) for articles that mention similar symptoms.
Modification Type:MajorLast Reviewed:9/1/2006
Keywords:kbinterop KB140895
©2004 Microsoft Corporation. All rights reserved.