Microsoft KB Archive/165497

= 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.

Microsoft Excel 5.0
  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   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   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   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   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   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, _ "" SQLRetrieve Chan3, Worksheets("Sheet1").Range("A1"),, , True

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

'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, _ "" 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 </li></ol>

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