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:
- 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
- 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).
- 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.
- 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
- 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
- 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:
- 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.
- 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).
- 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
- 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).
- 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).
- 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.
- 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.
- If you are experiencing an increased use of tempdb in SQL Server 6.0,
review article Q135532 (referenced earlier in step 10).
- 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.
|