Microsoft Knowledge Base

SQLExecQuery Error Indicating Incorrect Number of Columns
Last reviewed: September 23, 1997
Article ID: Q165497
The information in this article applies to:
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel versions 5.0 and 7.0, the SQLExecQuery function may fail if you use the SQLOpen function to open an ODBC channel to an external database, and then use the SQLExecQuery function two or more times with that ODBC channel. SQLExecQuery may fail if it tries to return fewer columns than the maximum number of columns that have been previously returned while using that ODBC channel.

For example, assume that an ODBC channel is opened with SQLOpen, and SQLExecQuery is used to return two columns. Then, the SQLExecQuery function is used again, this time returning only one column. SQLExecQuery may fail the second time, because it returns fewer columns than previously returned using the same ODBC channel.

No data will be returned to Microsoft Excel if this error occurs. If SQLRetrieve is used to return data to a worksheet, it will fail to return any data (except the column header, if the 5th argument of SQLRetrieve, colNamesLogical, is set to True).

If the SQLError function is used to return error information, the error message may vary with the ODBC driver. The most common error message is "Invalid column number." Depending on the driver, other error messages are possible, such as "Number of bound columns exceeds the number of result columns."

CAUSE

This problem occurs when you use ODBC drivers that do not work around a known problem in the following files included with Microsoft Excel:

  • XLODBC.DLL (Microsoft Excel 5.0)
  • XLODBC32.DLL (Microsoft Excel 7.0)

These files contain the Microsoft Excel ODBC functions, such as SQLExecQuery and SQLRetrieve. The problem is that XLODBC.DLL and XLODBC32.DLL do not unbind columns after queries. Some ODBC drivers work around this problem by automatically unbinding the columns.

For more information about this problem, see the "More Information" section of this article.

RESOLUTION

An updated XLODBC32.DLL is available for Microsoft Excel 7.0 that fixes this problem. To download the updated XLODBC32.DLL, click the following link.

The following file is available for download from the Microsoft Software Library: 

 ~ Xlodbc32.exe
For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services
Copy the file to your C:\MSOffice\Excel\Library\MSQuery directory, replacing the older file with the same name.

There is no updated XLODBC.DLL for Microsoft Excel 5.0. Workarounds for Microsoft Excel 5.0 are listed below. Note that the updated XLODBC32.DLL will not work with Microsoft Excel 5.0, even if renamed to XLODBC.DLL because 16-bit programs such as Microsoft Excel 5.0 cannot use 32-bit DLL files such as XLODBC32.DLL.

WORKAROUND

Microsoft Excel 5.0

  1. To access ORACLE 7.x databases, use Visigenic's 16-bit ORACLE 7 driver.

    This ODBC driver is manufactured by Visigenic, a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability. We do not guarantee that the current version of this driver still successfully works around the problem.

    For other databases, look for a driver that works around the problem as described above. Major ODBC driver vendors are Intersolv, Visigenic, and OpenLink.

    For information about how to contact Intersolv, Visigenic, and OpenLink, query in the Knowledge Base for one of the following articles:

    ARTICLE-ID: Q65416 

       TITLE     : Hardware and Software Third-Party Vendor Contact List, A-K

   ARTICLE-ID: ID: Q60781
   TITLE     : Hardware and Software Third-Party Vendor Contact List, L-P

   ARTICLE-ID: Q60782
   TITLE     : Hardware and Software Third-Party Vendor Contact List, Q-Z

  2. Arrange your code so that lines returning fewer columns appear before lines returning more columns. For example:

    Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400. 

       Sub ArrangeLines()
       Dim Chan As Integer

       'Connect to the server:
       Chan = _
           SQLOpen("DSN=OraDSN1;UID=User1;PWD=Password1;" & _
           "SERVER=T:Server1:ORCL;")

       'Return 1 column, then retrieve that column into a worksheet:
       SQLExecQuery Chan, "SELECT CUSTOMER.CUSTID FROM " & _
           "SCOTT.CUSTOMER CUSTOMER"
       SQLRetrieve Chan, Worksheets("Sheet1").Range("A1"), , , True

       'Return 2 columns, then retrieve those columns into a worksheet:
       SQLExecQuery Chan, _
           "SELECT CUSTOMER.CUSTID, CUSTOMER.REPID FROM " & _
           "SCOTT.CUSTOMER CUSTOMER"
       SQLRetrieve Chan, Worksheets("Sheet1").Range("B1"), , , True

       'Disconnect:
       SQLClose Chan
   End Sub

  3. Close the connection before returning fewer columns than have been returned before. For example: 

       Sub Reconnect()
       Dim Chan As Integer

       'Connect to the server:
       Chan = _
           SQLOpen("DSN=OraDSN1;UID=User1;PWD=Password1;" & _
           "SERVER=T:Server1:ORCL;")

       'Return 2 columns, then retrieve those columns into a worksheet:
       SQLExecQuery Chan, _
           "SELECT CUSTOMER.CUSTID, CUSTOMER.REPID FROM " & _
           "SCOTT.CUSTOMER CUSTOMER"
       SQLRetrieve Chan, Worksheets("Sheet1").Range("A1"), , , True

       'Disconnect, then reconnect:
       SQLClose Chan
       Chan = _
           SQLOpen("DSN=OraDSN1;UID=User1;PWD=Password1;" & _
           "SERVER=T:Server1:ORCL;")

       'Return 1 column, then retrieve that column into a worksheet:
       SQLExecQuery Chan, "SELECT CUSTOMER.CUSTID FROM " & _
           "SCOTT.CUSTOMER CUSTOMER"
       SQLRetrieve Chan, Worksheets("Sheet1").Range("A1"), , , True

       'Disconnect:
       SQLClose Chan
   End Sub

  4. Use DDE (Dynamic Data Exchange) to use Microsoft Query to get the data and return it to Microsoft Excel.

    For information about how to do this, please see the following article in the Microsoft Knowledge Base: 

          ARTICLE-ID: Q142358

          TITLE     : Visual Basic Example Using ODBCLogon


  5. Pad each query to have as many columns as the maximum number of
   columns. For example:

    

       Sub PadColumns()
       Dim Chan As Integer

       'Connect to the server:
       Chan = _
           SQLOpen("DSN=OraDSN1;UID=User1;PWD=Password1;" & _
           "SERVER=T:Server1:ORCL;")

       'Return 2 columns, then retrieve those columns into a worksheet:
       SQLExecQuery Chan, _
           "SELECT CUSTOMER.CUSTID, CUSTOMER.REPID FROM " & _
           "SCOTT.CUSTOMER CUSTOMER"
       SQLRetrieve Chan, Worksheets("Sheet1").Range("A1"), , , True

       'Return 1 column of actual data, plus a column full of the string
       '"PadCol":
       SQLExecQuery Chan, _
           "SELECT CUSTOMER.CUSTID, 'PadCol' FROM " & _
           "SCOTT.CUSTOMER CUSTOMER"

       'Bind the column of actual data:
       '(Note: The XLODBC.DLL function SQLBind does not perform the same
       'kind of binding as the ODBC API function SQLBindCol discussed
       'above)
       SQLBind Chan, 1, Worksheets("Sheet1").Range("A1")

       'Retrieve the column of actual data into a worksheet,
       'discarding the padded column:
       '(if SQLRetrieve's 2nd argument, destinationRef, is omitted, then
       'only columns bound by SQLBind will be retrieved)
       SQLRetrieve Chan, , , , True

       'Disconnect:
       SQLClose Chan
   End Sub


  6. Padding columns as described in workaround #4 above can slow down very
   small queries that come after very large queries. To improve
   performance, you could keep 2 or more connections open, using each
   connection for different maximum amounts of columns.

    
   The example below illustrates opening 3 connections (although users
   are prompted for their username and password only once). The first
   connection is used only for single-column queries, the second
   connection is used for up to 10 columns, and the third connection is
   used for any number of columns:

    

       Sub MultiConnect()
       Dim Chan1 As Integer, Chan2 As Integer, Chan3 As Integer
       Dim Col As Integer
       Dim Uname As String, Pword As String
       Dim Rg As Object

       'Get the username and password:
       '(to prompt for both on one dialog, use a Dialogsheet instead)
       Uname = InputBox("Please enter your username:", , , , , , , 2)
       Pword = InputBox("Please enter your password:", , , , , , , 2)

       'Create three connections to the server:
       '(a maximum of 35 connections are allowed in Microsoft Excel)
       Chan1 = SQLOpen("DSN=OraDSN1;UID=" & Uname & _
           ";PWD=" & pword & ";SERVER=T:Server1:ORCL;")
       Chan2 = SQLOpen("DSN=OraDSN1;UID=" & Uname & _
           ";PWD=" & pword & ";SERVER=T:Server1:ORCL;")
       Chan3 = SQLOpen("DSN=OraDSN1;UID=" & Uname & _
           ";PWD=" & pword & ";SERVER=T:Server1:ORCL;")

       'Return 200 columns, then retrieve those columns into a worksheet:
       SQLExecQuery Chan3, _
           "<SQL Select statement to return 200 columns>"
       SQLRetrieve Chan3, Worksheets("Sheet1").Range("A1"), , , True

       'Return 190 columns of actual data, plus 10 columns full of the
       'string "PadCol":
       SQLExecQuery Chan3, _
           "<SELECT Clause>, 'PadCol', 'PadCol', 'PadCol', " & _
           "'PadCol', 'PadCol', 'PadCol', 'PadCol', " & _
           "'PadCol', 'PadCol', 'PadCol' <FROM Clause>"

       'Bind the 190 columns of actual data:
       Set Rg = Worksheets("Sheet2").Range("A1")
       For Col = 1 To 190
           SQLBind Chan3, Col, Rg.Offset(0, 1)
       Next Col

       'Retrieve the 190 columns of actual data into a worksheet,
       'discarding the 10 padded columns:
       SQLRetrieve Chan3, , , , True

       'Return 10 columns, then retrieve those columns into a worksheet:
       SQLExecQuery Chan2, _
           "<SQL Select statement to return 10 columns>"
       SQLRetrieve Chan2, Worksheets("Sheet3").Range("A1"), , , True

       'Return 6 columns of actual data, plus 4 columns full of the
       'string "PadCol":
       SQLExecQuery Chan2, _
           "<SELECT Clause>, 'PadCol' , 'PadCol', 'PadCol', " & _
           "'PadCol', <FROM Clause>"

       'Bind the 6 columns of actual data:
       Set Rg = Worksheets("Sheet4").Range("A1")
       For Col = 1 To 6
           SQLBind Chan2, Col, Rg.Offset(0, 1)
       Next Col

       'Retrieve the 6 columns of actual data into a worksheet,
       'discarding the 4 padded columns:
       SQLRetrieve Chan2, , , , True

       'Return 1 column, then retrieve that column into a worksheet:
       SQLExecQuery Chan1, _
           "SELECT CUSTOMER.CUSTID, FROM SCOTT.CUSTOMER CUSTOMER"
       SQLRetrieve Chan1, Worksheets("Sheet5").Range("A1"), , , True

       'Disconnect:
       SQLClose Chan1
       SQLClose Chan2
       SQLClose Chan3
   End Sub


Workarounds for Microsoft Excel 5.0 for Visual Basic and C++ Developers

 


  1. Using 16-bit Visual Basic 4.0, write an OLE server DLL designed to
   access databases. The DLL could use DAO to get data, and could expose
   functions similar to XLODBC functions. The functions could even have
   the same names and arguments (but you wouldn't need to recreate all of
   XLODBC's functionality, which would be a lot of work; recreate only
   the functionality you need). Then, use that DLL instead of XLODBC in
   MIcrosoft Excel.


  2. Do all database access coding directly to the ODBC API. If you choose
   this route, these two books might be useful: "Microsoft ODBC 2.0
   Programmers Reference and SDK Guide", MS Press; and "Teach Yourself
   ODBC Programming in 21 Days" by Bill Whiting, Bryan Morgan, and Jeff
   Perkins, SAMS Publishing. The "Teach Yourself" book is designed for
   both C++ and Visual Basic developers.

    

STATUS

 


This problem is corrected in Microsoft Excel 97 for Windows.



MORE INFORMATION

 


When XLODBC.DLL and XLODBC32.DLL process SQLExecQuery, they must issue the
ODBC API function "SQLBindCol" to bind (assign the storage address in
memory and the data type) each column to be returned. After processing
SQLExecQuery, XLODBC.DLL and XLODBC32.DLL fail to issue a
"SQLFreeStmt(hstmt, SQL_UNBIND)" command to unbind the columns after
executing a query. SQLFreeStmt is an ODBC API function designed to stop the
processing associated with a query, and its SQL_UNBIND argument is designed
to release all column buffers bound for that query. SQLFreeStmt and its
SQL_UNBIND argument are documented on pages 337-339 of the ODBC 2.0
Programmer's Reference, Microsoft Press.


Because the command to unbind the columns was not issued, the columns are
incorrectly left bound. When a subsequent SQLExecQuery is processed, the
columns to be returned with that query are correctly rebound. However, if
fewer columns are to be returned in that query than the maximum number of
columns that have been previously returned, the extra columns are still
bound. In some cases, the ODBC driver will intercept this problem and
return an error to XLODBC.DLL and XLODBC32.DLL. If the driver does not
intercept the problem, then the ODBC Driver Manager (ODBC.DLL) returns
error #S1002 with the description "Invalid column number" to the ODBC
driver, and the driver passes that error on to XLODBC.DLL and XLODBC32.DLL.
In both cases, no data is returned to XLODBC.DLL and XLODBC32.DLL. In ODBC,
it is illegal to try to return a different number of columns than the
number of bound columns, and the ODBC Driver Manager will not allow it.


Most ODBC drivers shipped by Microsoft (and some drivers shipped by other
vendors) work around this problem by automatically unbinding the columns.
Exceptions to this are the following drivers:


  • The 16-bit ORACLE 7 driver included in the Microsoft ODBC Driver Pack
   Fulfillment Kit 2.0 (SQORA7.DLL), which can be used with Microsoft
   Excel 5.0.


  • The 32-bit ORACLE 7.0 & 7.1 driver included in the Microsoft ODBC
   Drivers Appnote WX1220 (VSORAC32.DLL, version 2.00.0000), which can be
   used with Microsoft Excel 7.0 and later. VSORAC32.DLL version
   1.03.0009, which was included with an earlier version of the WX1220
   Appnote, successfully worked around the problem (by automatically
   unbinding the columns as described above), but that version is no
   longer available. Versions 1.03.0009 and 2.00.0000 are the only
   versions Microsoft has shipped. Note: WX1220 is also distributed as
   GE1263.


  • The 32-bit ORACLE 7.2 & 7.3 driver (MSORCL10.DLL) available in some
   Microsoft developer products such as Microsoft Visual Basic 5.0 and
   Microsoft Developer Studio 1.0. This driver has not been thoroughly
   tested with Microsoft Excel, and it is not designed to work around the
   problem described in this article.


  • The Microsoft Visual FoxPro drivers (VFPODBC.DLL) for Microsoft Visual
   FoxPro 3.0 & 5.0), which can be used with Microsoft Excel 7.0 and
   later. NOTE: The Microsoft FoxPro 2.6 driver (ODBCJT32.DLL, with
   MSXB3032.DLL) successfully works around the problem (by automatically
   unbinding the columns as described above).


  • The Macintosh FoxPro & dBase driver included with Microsoft Excel 5.0
   for Macintosh (Q+E ODBC FoxPro Driver).

    

The problem will occur with all of the above drivers. In addition, the
problem will occur with any other driver that does not work around the
problem by automatically unbinding the columns as described above.
 

Keywords          : xlquery xlvbahowto kbprg kbbuglist
Version           : 5.0 5.0a | 5.0 5.0c 7.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbbug
Solution Type     : kbcode










 	









	






================================================================================










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:  September 23, 1997
	

	©1997 Microsoft Corporation. All rights reserved. Legal Notices.