INF: Diagnostic Tips for the Microsoft SQL Server ODBC Driver

Last reviewed: June 25, 1997
Article ID: Q140895

The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 & 6.5
  • Microsoft Open Database Connectivity

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.
    
       For additional information, please see the following article in the
       Microsoft Knowledge Base:
    
       ARTICLE-ID: Q136481
       TITLE     : INF: Running ODBC Applications as Windows NT Services
    
    

  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 Q137635 (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.
    
       For additional information, please see the following article in the
       Microsoft Knowledge Base:
    
       ARTICLE-ID: Q121258
       TITLE     : PRB: Connect to SQL on NT 3.5 from WFW 3.11 via Named Pipes
    
    

  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. See article Q137781 ("INF: How to Obtain SQL Server
       v6.0 Service Pack 1) for the location of the Service Pack. 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.
    
       For additional information, please see the following article in the
       Microsoft Knowledge Base:
    
       ARTICLE-ID: Q136575
       TITLE     : FIX: SQL Server 6.0 Service Pack 1 Fixlist
    
    

  6. For other network connectivity errors, SQLError() will generally return errors as defined in article Q137634 (INF: ODBC SQL Server Driver Network Messages). Review this article for information on dealing with the connectivity/network errors. You can also review article Q138541 (INF: ODBCPING.EXE to Verify ODBC Connectivity to SQL Server) on using the ODBCPing utility to test connections.

Diagnosing general errors:

  1. 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 Q137634 (previously referenced), the problem is a connectivity or network problem, so you should go back up to step 6.

  2. 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 Q135533 (INF: Differences in SQL Behavior Between ODBC and ISQL).

  3. 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:

    ARTICLE-ID: Q138761

       TITLE     : INF: Differences in Column Nullability
    
    

  4. If you are experiencing differences in behavior between DB-Library

        and ODBC clients, review articles Q135533 (referenced earlier),
        Q135532 (INF: SQL Server 6.0 ODBC Driver Changes Tempdb Usage), and
        Q149921 (INF: ODBC ANSI upgrade changes from SQL Server 6.0 to 6.5).
    

  5. If you are experiencing differences in behavior when you upgrade from

        SQL Server 4.2x to SQL Server 6.0, review articles Q133177 (INF:
        Changes to SQL Server 6.0 That May Affect 4.2x Apps), Q135532 (INF: SQL
        Server 6.0 ODBC Driver Changes Tempdb Usage), Q135533 (INF: Differences
        in SQL Behavior Between ODBC and ISQL), and Q138761 (INF: Differences
        in Column Nullability).
    

  6. If you are experiencing differences in behavior when you upgrade from

        SQL Server 6.0 to SQL Server 6.5, review article Q149921 (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.
    

  7. 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
        Q137636 (INF: Relationship of the ODBC Driver to INSTCAT.SQL), and
        Q137633 (INF: Addressing Access Conformance Errors in the SQL ODBC
        Drvr) for additional diagnostics on Microsoft Access conformance
        errors.
    

  8. If you are experiencing an increased use of tempdb in SQL Server 6.0,

        review article Q135532 (referenced earlier in step 10).
    

  9. 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 Q139655 (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.


Additional query words: sql6 debug tshoot troubleshoot diagnostic
dblib
Keywords : kbinterop SSrvDB_Lib SSrvGen
Version : 2.5 6.0 6.5
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.